I'm building an SSAS Tabular Cube and have a modeling questions (my example here is simplified).
Let's say I have dimensional data for Region and Warehouse, where one region can contain several warehouses. Then I have 2 fact tables with different grain; Sales facts is on Region-level and Inventory facts are on Warehouse-level. Now I can see 2 different ways of building the data-model (see image):
A. Region and Warehouse are put in 2 different tables and a relationship between them defines their 1-n hierarchy. Each table then has a relationship to their facts.
B. Region and Warehouse are put in the same table (called Location). This table has some rows on Region-level (where Warehouse column is empty) and some rows on Warehouse-level. This enables the different fact tables to relate the same key.
I see that there are some pros/cons with each approach, but can't decide on which is better.
Pros with A:
- Easier to develop and maintain.
- If relationship between Sales and Region is made bidirectional then Sales facts can filter out warehouses.
Pros with B:
- Faster performance because less relationships and keys?
- All location-relevant dimensions in the same table.
- Fewer tables to maintain.
What approach would you say is better and why?
Hi @Anonymous,
I prefer to choose the first approach. Based on this article, it is better to avoid having cubes with a single dimension. A single dimension almost always contains multiple logical business entities. From the perspective of the user, the different business entities would be more cleanly modeled and navigated as separate dimensions.
In addition, as the issue is more related to SSAS, please post the question in the SQL Server Analysis Services forums at https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=sqlanalysisservices to get better support.
Thanks,
Lydia Zhang
