Model design issue For Many Dimension and Fact Table

[Vanchy_Liao]

Hi Everyone,

I am using Database Table & View to construct Power BI Model.

My goal is combining multiple dimension tables into one dimension table to control all the fact tables.

There are two benefits to me:

1. Cleaner modeling picture, because the relationship line between models will be in good order

2. By setting cross filter direction to "Both" between dimension table and meta table, it will gain better performance on the interaction of each slicer

The ideal model picture is like below:

dim1      dim2     dim3

    |             |            |

    | <->- meta -<->|

                  |

    |¯¯¯¯¯¯¯¯¯|¯¯¯¯¯¯¯¯|

fact1     fact2     fact3

The Schema is like below:

1. dim1: dim_id1, attr1

2. dim2: dim_id2, attr2

3. dim3: dim_id3, attr3

4. meta: meta_id, dim_id1, dim_id2, dim_id3

5. fact1: meta_id, value

6. fact2: meta_id, value

7. fact3: meta_id, value

My Problem is that:

1. If I use a view to represent meta_table, I have to union all the fact tables and it will be performance tragedy when refreshing data. (every fact table near 1 million rows)

2. If I use a table to represent meta_table, it will face the meta_id updating issue when fact tables inserting new rows or deleting rows.

Is there any good suggestion to help me achieve these?

1. slicer can cross filter easily

2. cleaner model

3. better performance when refreshing data

thank you for replying

[amitchandak]

@Vanchy_Liao , You should try to be in Star Schema. 3 Dims can join with Three Facts. Do not merge or append unless that is the option 

https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

https://www.youtube.com/watch?v=vZndrBBPiQc&feature=youtu.be

[Vanchy_Liao]

Hi @amitchandak ,

     I realize the importance of star schema, but I want the slicer with cross filter interations.

    How can I achieve this? 

    Thanks for your reply ^^

[amitchandak · Accepted solution]

@Vanchy_Liao , if you have an objective to restrict the slicer value because of another slicer refer to this video

https://www.youtube.com/watch?v=cyOquvfhzNM

In your case, you need a measure which from three facts. example

countrows(Fact1) +countrows(Fact2)  +countrows(Fact3) 

[Vanchy_Liao]

@amitchandak ,

Thank you for your advise and I decide to change my model into star schema