Converting a many-to-many multidimensional cube to tabular

Question

Asking this question because a solution I can see is really complicated.

I have a fact table "DailyMeasures" which has granularity person+day, like this:

Person       Day         MeasureHoursAwake    MeasureWeight   Dimension1    Dimension2
Person A    20210101     16                   100             <value1>     <value2>          
Person B    20210101     16                   80              <value1>     <value3>          

Then, I also have a fact table "DailyMeals" which has lower granularity, person+day+meal type, like this:

Person       Day        DimensionMealType     MeasureCalories    MeasureHoursSpent
Person A    20210101   <breakfast>                  500                        0.5
Person A    20210101   <dinner>                     1000                       1
Person B    20210101   <dinner>                     1500                       2

In my multidimensional model I've used the many-to-many relationships between DailyMeals and Dimension1 / Dimension2 because that table is not connected to the dimensions right away. The bridge table is DailyMeasures because if the person has meals they have data in that table.

I also have another fact table "DailyWorkout" which is similar to DailyMeals in structure with several records for a person+day, used in the same many-to-many through DailyMeasures way.

Now, I'm trying to convert this into a tabular model.
I'm getting several weird calculation behaviors, specifically related to involvements of DimensionMealType which is relevant only for meals but seem to affect the rest of the model.

My question is a general one:
What is the best way of handling these connections in a tabular model?

From what I could find from similar examples it looks like people solve this with separate facts which are not connected to each other.
In my case it would mean I would need to load all the dimension connections into the lower grain facts, like this:

DailyMeals

Person       Day      DimensionMealType     MeasureCalories    MeasureHoursSpent   Dimension1    Dimension2
Person A    20210101   <breakfast>          500                 0.5                 <value1>        <value2>  
Person A    20210101   <dinner>             1000                1                   <value1>        <value2>  
Person B    20210101   <dinner>             1500                2                   <value1>        <value3>  

and basically have 3 fact tables (DailyMeasures, DailyMeals, DailyWorkout) not related to each other at all.

Is there a better way?

Answer 1

From what I could find from similar examples it looks like people solve this with separate facts which are not connected to each other.

In terms of general modelling approaches for tabular this is definitely the best approach and there is no other better way.

Typically the "extra" foreign key columns are at a much lower grain than the fact table so they compress really well. As a general rule fact to fact relationships should be avoided where ever possible. The extra storage of "duplicated" foreign keys is more than offset by better performance and simpler measures that don't suffer from "weird behaviours".

Answer 2

Hi @Alex_404 ,

There some methods shared online to migrate SSAS Multidimensional Cubes to Tabular.

It seems temporarily there is no better way than you have known to meet your needs.

You may keep this topic to see if others have good ideas.

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October

Answer 3

In my multidimensional model I've used the many-to-many relationships between DailyMeals and Dimension1 / Dimension2 because that table is not connected to the dimensions right away.

Note that in the data, these relationships are actually M:1, because a single DailyMeals record will have just one person+day value. It'd've been a better design for the MD cube as well to add those foreign keys to the lower grain facts and set up direct relationships.