Welcome to dbForumz.com!
FAQFAQ    SearchSearch      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

Dimension design problem - star/snowflake?

 
   Database Forums (Home) -> Data Warehouse RSS
Next:  time series against a cube in SSAS2005, how to???..  
Author Message
McMishkoff

External


Since: Jan 17, 2008
Posts: 2



(Msg. 1) Posted: Thu Jan 17, 2008 10:28 am
Post subject: Dimension design problem - star/snowflake?
Archived from groups: microsoft>public>sqlserver>datawarehouse (more info?)

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?

 >> Stay informed about: Dimension design problem - star/snowflake? 
Back to top
Login to vote
Display posts from previous:   
Related Topics:
Design for star schema - Dear newsgroup readers, I am working on suggesting a data warehouse design for an insurance company. The business logic is that there are Customers and each Customer has a Captive. Also, each Captive has a Cession. On the technical side, there is at..

DW design question - Dimension for currency - Hi everybody, I struggle with a DW design issue that I hope somebody can provide a solution for? I have a traditional Star Schema, and are building an OLP cube on top. The development is taken place using SQL Server 2005. For the purpose of this...

Basic Design Question Currency Dimension or New Measure - Hi All, I am in the middle of a decision and I would like to ask experts for their ideas. I have Sales Reporting System on MSSQL 2000 + AS + RS So far I have used only USD as reporting currency, now I am going to add EURO and Local Currencies. My..

SSAS problem for grouping inside dimension - I have a dimension which contains student details and the no of rows is about 1600000, now in a normal dimension it allows 64000 rows, if more rows were there in 2000 analysis services then grouping was done under level properties so as to specify..

FACT table design problem--banking domain - Our customer is from foreign exchange department of the bank. Because they never have a DW project experience before, they don't know their requirement. We collect their OLTP system snapshots to understand their business and what columns on the screen..
   Database Forums (Home) -> Data Warehouse All times are: Pacific Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum



[ Contact us | Terms of Service/Privacy Policy ]