January 17, 2008 at 11:02 am
Let's say you have a dimension that rolls up as a nice hierarchy, like Company -> Department -> Office. I know you can model this all in one table (star) or as three separate tables (snowflake).
Now, let's say you have two different fact tables. The first one, FactExpense, is at the granularity of the Office - it tracks expenses, and each expense is tied back to a particular office. The second one, FactBudget, is actually at the granularity level of the Department - budgets are done only down to the department level and office never comes into it.
My thought is that in this scenario, you couldn't roll all levels of the hierarchy into one table, because that would mean your dimension is intersecting with at least one of the fact tables at the wrong grain - it would intersect FactBudget at the office level even though that fact table is the grain of the department. So you would need at least two different dimensions, like DimCompanyDepartment and DimOffice, snowflaked off each other so they could intersect the two fact tables at the right grain.
The competing thought on my design team is that you could have all three things in one dimension table, DimCompanyDepartmentOffice, and for the FactBudget table you would just arbitrarily pick one of the Offices (say, always the first office for a department) as the intersection point for the fact and dimension. So, if DepartmentA had two offices, Office1 and Office2, and you're recording that DepartmentA had a 2007 budget of 400K, you would make an entry in the BudgetFact table with the amount and the dimensional key from DimCompanyDepartmentOffice that represents Office1.
That solution, while it might technically work, seems wrong to me because the fact and dimensions are at different grains. Am I correct in looking at it this way?
February 15, 2008 at 9:28 am
bump!
I wanna here from people on this one... I am new to DW world and this seems like a good scenario.
February 16, 2008 at 1:59 pm
I always use star schema.
Are you using analysis service to create the cube? If you do, then you don't have to worry about the fact table. It will ask you the hierarchy of the department table. It will create a cube for each level.
If you use RDBMS to create the fact table, you have to roll up each level to get the measure but it may have many sparsity in the table.
For example:
Company-> Division->Department
CompanyA, Division1, Accounting
CompanyA, Division1, IT
CompanyA, Division2, Accounting
CompanyA, Divsion2, IT
CompanyB, Division1, Accounting
CompanyB, Division2, Accounting
If the measure is number of employee, then the real fact table has the following records
SELECT Company, Division, Department, COUNT(Num of Employee)
FROM Fact_stage
GROUP BY Company, Division, Department
SELECT Company, Division, COUNT(Num of Employee)
FROM Fact_stage
GROUP BY company, division
SELECT Company, Count(Num of Employee)
FROM Fact_stage
GROUP BY Company
March 25, 2008 at 8:50 am
I always prefer a star schema design. However, the actual practice varies.
Technically, you have facts at diffferent levels of granularity and they should go into separate fact tables. The question then comes down to how to build the dimension table(s) to meet this requirement.
If you have rigid heirarchy (3 levels in your example) but rather than assign the budget to a specific deparatment (1st one) you could create a specific entry in the dimension table where the department is BUDGET (or other value that is not the name of an office and can be easily identified as the budget amount). This way you can link the facts to a row in the dimension table and you can have both the budget and actual facts in the same fact table.
Alternatively, you could use a parent-child dimension table which would give you an entry for each department against which you can hang the fact (without the need to create a dummy entry as in the previous option). If you are using Analysis Services then it is easy to create a parent-child dimension.
Jez.
May 14, 2008 at 10:38 pm
my solution will be, in your expenses fact table, your dimension key will refering to office and your budget fact table, the dimension key is refering to department. So your fact table has it own characteristic. so you don't need to create duplicate dimension.
May 26, 2008 at 8:19 am
Hi,
I presume you are using SSAS 2005. It supports Measuregroups, meaning you can have multiple fact tables in your cube with different granularity.
You can have a single dimension, say, DimCompanyDepartmentOffice which has Company --> Department --> Office hierarchy. Have a single cube with 2 fact tables (measure groups). The Expenses measuregroup is mapped to the dimension at "Company" level and Budget measuregroup is mapped to the same dimension at "Department" level.
Revert for any clarifications.
Regards,
Vijay.
Warm Regards,
Neel aka Vijay.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply