Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

Reply
Anonymous
Not applicable

SSAS Tabular Modelsing Question

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?

 

ssas_question.png

1 REPLY 1
Anonymous
Not applicable

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

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors