May 3, 2007 at 12:22 pm
I am new to data warehousing and I am having trouble with some tables that act as dimensions and fact tables. For example, I am collecting work logged against projects.
Work(fact) has
Measure:
- WorkHours
Dimensions:
- Person
- Date
- Project
This seems straight forward, but then when I start reviewing the project dimension, it has its own measures (budget, ROI) and its own dimensions (date, sponsor) so it seems like it should be a fact table as well.
How would I model this? Do I create a ProjectFact table and a ProjectDim table?
May 4, 2007 at 10:04 am
In AS2005 you can use more than one fact table in the same cube.
Also, you can denormilize the tables, making only one; something like this:
select *
from project left join work on project.project = work.project
May 4, 2007 at 12:41 pm
I understand that I can have more the one fact table, but the question is, "Is project a fact table or a dimension table?"
When looking at work it is obviously is a dimension of work, but at the same time projects have their own dimensions (date and sponsor) and measures (budget, ROI) which makes it look like a fact table. So which is it?
May 4, 2007 at 3:18 pm
May be both, that depends on what you want to analyze.
If you just want to categorize the work by project, then project is a dimension. But if you also want to compare something like budget vs. real cost, then project is also a fact table.
A table can be fact and dimension at same time.
May 4, 2007 at 3:27 pm
Thanks!
Ok, so it sounds like I should probably make the project table a dimension and a fact table.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply