Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a data warehousing / reporting requirement problem that I have to account for in all of my PBI reports. All of my PBI reports sit on a data warehouse. Most dimension tables in my data warehouse are type 2 (i.e., we retain history on most columns). My PBI reports typically filter the fact tables. My users don't want unnecessary dimension data appearing in the report (meaning no data from dimension keys that aren't loaded). So, after loading in all of my filtered fact tables, I generate tables of all dimension keys loaded. In the dimension tables load, I do an inner join on these keys to filter the dimension tables to only what is necessary. This seems wrong to me and often times hurts the load performance. Is there a better way to do this? Or, should I even be doing this at all?
Hi, @Anonymous
Since I don’t know about type2, I misunderstood what you mean. After my search, I basically understand your needs. If your data is large, I recommend that you directly filter the latest data records(dimension tables) in pq.
Follow it:
Remove Duplicates and Keep the Last Record with Power Query - Excelerator BI
If it doesn't solve your problem, Please feel free to ask me, I will create a sample file to show you. You need to tell me which method you used.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
For tables that are not needed, you can try to cancel the two buttons of “enable load” and “include in report refresh". If you are using DQ mode, you can also change the dimension table to import mode and use mixed mode.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
That isn't what I mean. I'm not having trouble with disabling load / include in report refresh. My trouble is that I have to frequently filter my dimension tables down to those keys present in the filtered fact tables (as opposed to all keys in the data warehouse). The only way that I know how to do this is via an inner join after the filtered fact tables have loaded. Joins hurt load performance. My question is... Should I even be doing this kind of filtering at all?
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |