Dimension Hierarchy - Facts by various levels

Hello,

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.

Thanks!

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