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
Anonymous
Not applicable

how to handle unneeded dimension keys requirement

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?

3 REPLIES 3
v-janeyg-msft
Community Support
Community Support

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.

vjaneygmsft_0-1624616344956.png

 

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.

v-janeyg-msft
Community Support
Community Support

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.

vjaneygmsft_0-1624260289394.png

 

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.

Anonymous
Not applicable

@v-janeyg-msft 

 

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?

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.