Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Dimension Hierarchy - Facts by various levels

2 posters

Go down

Dimension Hierarchy - Facts by various levels Empty Dimension Hierarchy - Facts by various levels

Post  presks Wed May 27, 2009 6:23 pm


I am working on a new project (with Oracle OBIEE) where most data needs to be seen by a terminal(game or cahsier). The terminals are located in different venues in various cities and states. Some terminals may just be at a city or a state level (i.e no actual physical venue). I am planning to build a terminal dim with hierarchy (State -> City -> Venue -> Terminal). With this dimension hierarchy all my data needs to be dimensioned by terminal. I have certain scenarios where data is not at terminal level. For eg. player balance which is at the state level. I am now confused and need to decide that if it makes more sense to breakdown the terminal dimension into various mini dimesions (one for each level - terminal, venue, city, state). With this approach I get

o Flexibility to dimension measures by different levels of hierarchy (province or city) and not the full hierarchy (i.e. terminal)
o Each dimension has only one record to identify a fake/dummy row.

But I loose
o Drill down reporting
o Ability to answer questions like # of terminals in a venue, # of cities in a state without a fact table
o Ability to easily implement row level security (user from venue should not be able to see city data)

I would appreciate comments on both the approaches so that i can make a calculated decision.



Posts : 1
Join date : 2009-05-27

Back to top Go down

Dimension Hierarchy - Facts by various levels Empty You probably need aggregate dimensions

Post  Andrea Vincenzi Fri Jun 05, 2009 8:36 am

From what you say, it looks like a typical case where you have different facts that naturally connect to different levels of a dimension. The correct way to model this is to design "aggregate dimensions" in addition to the base dimension. In this way you can attach the facts to the dimension table at the right level. To use your example, player balance will be connected to the dimension that has only the state level (and it doesn't make sense to drill down because this measure does not exist at lower levels).

Aggregate dimensions are part of the design of all data warehouses, and are used to connect to aggregate facts. For a detailed description of how aggregates work please refer to the "Mastering DW aggregates" book.

Hope this was useful,
Andrea Vincenzi
Andrea Vincenzi

Posts : 8
Join date : 2009-02-04
Age : 69
Location : Rome (Italy)

Back to top Go down

Back to top

- Similar topics

Permissions in this forum:
You cannot reply to topics in this forum