Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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 , 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)
Thank you for your advise and I decide to change my model into star schema
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |