[cfr]

Greetings. My disclaimer/ rant:

I'm new to the whole star schema/ dimensional modeling/ not OLTP type of design thing so please bear with me here. The newbie knowledge that I do have was obtained from reading Kimball's "the data warehouse toolkit, second edition" about a year ago. I do realize that not everyone agrees with his school of thought, but from what I can tell you pretty much need to pick a side and go with it, and this won the coin toss.

With all my ranting out of the way, here it goes:

I was asked to see if we could redesign our reporting DB to improve performance and my initial thought was a star schema. From the newbie point of view this would consist of:

Fact table: This would be any numeric data that would need to be aggregated on, plus foreign keys back to the dimension tables.

Dimension tables: What I would refer to as "group by" tables. Time, product, location, etc. would all be considered dimension tables.

I do realize there's much more to it, but am trying to keep it simple for the time being. Anyways, I recently discovered that this particular DB doesnt really have any numeric data that would go into the fact table. In other words, this DB doesnt have anything that would typically be aggregated on in a fact table. This is not to say that they don't do any calculations, but the needed calculations are mostly off of the dates, not numeric data. These caluculations typically consist of (simplified):

ArrivalDate - OrderDate = DaysInTransit

or something similar. From what I recall, I should never store date info in the fact table for various reasons? That being the case, this calculation would need to be done on a Dimension table.

With all this out, my questions are as follows (finally):


1. Does it make sense to design a star schema that consist of no numeric data that will need to be aggregated in a fact table, but only consists of columns for foreign keys back to the dimension tables?

2. Does it make sense to design a star schema that will consist of dimension tables which is where all of my calculations will be done?

3. Do I need to scrap the whole star schema design and go with a different appoach?

TIA, cfr

[r937]

1. sure -- you can always count them (COUNT is a valid aggregation, just as SUM, MIN and MAX)

2. yes

3. nope

i'd like to hear more about not storing dates, because my first reaction to that idea is "wtf?"

what if you're tracking sales by day of week? how you gonna know which days have low sales if you don't know which day a sale occurred on?

maybe i'm still a data warehousing noob, but something's not right...

[blindman]

Originally Posted by cfr

The newbie knowledge that I do have was obtained from reading Kimball's "the data warehouse toolkit, second edition" about a year ago. I do realize that not everyone agrees with his school of thought, but from what I can tell you pretty much need to pick a side and go with it, and this won the coin toss.

Yet another Kimball dupe. I suppose as long as you understand that you are building a reporting database and NOT a data warehouse, you are a step ahead of the other people who have bought into his "one schema fits all" approach.

Originally Posted by cfr

I was asked to see if we could redesign our reporting DB to improve performance and my initial thought was a star schema.

Your initial thoughts should have been: indexes, query efficiency, and hardware.

Originally Posted by cfr

1. Does it make sense to design a star schema that consist of no numeric data that will need to be aggregated in a fact table, but only consists of columns for foreign keys back to the dimension tables?

It makes no less sense than desiging a star schema for anything else.

Originally Posted by cfr

2. Does it make sense to design a star schema that will consist of dimension tables which is where all of my calculations will be done?

Your calculations will not be done "in" the dimension table, but rather in the joined result set of the fact and dimension tables.

Originally Posted by cfr

3. Do I need to scrap the whole star schema design and go with a different appoach?

You need to figure out your requirements and then design a database around them, instead of coming up with a schema and trying to force your requirements into it.