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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Vanchy_Liao
Advocate I
Advocate I

Model design issue For Many Dimension and Fact Table

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

 

1 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) 

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@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

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 ^^

@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) 

@amitchandak ,

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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