cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Only Import matching records with out using merge query

Hi all,

 

I have a large dimension table (2M rows) and a Fact table with about 100k. The relationship is 1:M between Dimension and Fact.

 

The 100k records in the fact may reference the same key in the dimension for multiple records, but overall the fact only references about 10k unqiue keys in the dimension. I only want to load those 10k matching records into the model.

 

If I try to do an inner join, it appears I have to import the entire dimension first and then do the merge, so it does not save me any refresh time/data loading in this case. Is there a way to filter that dimension to only import records that exist in the fact without loading the entire dimension and use query folding for optimal loading? Seems like an easy task, but I couldn't see a clear way to achieve this.

 

Thanks!

1 ACCEPTED SOLUTION

@Anonymous ,

 

Merge them on dimension query. Don't expand the fact table, so you won't duplicate your data.

After that, just drop the new column generated by the merge.

 

The idea is to merge and drop the column, keeping the unique values for your dimension.



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

9 REPLIES 9
camargos88
Super User III
Super User III

Hi @Anonymous ,

 

"If I try to do an inner join, it appears I have to import the entire dimension first and then do the merge, so it does not save me any refresh time/data loading in this case"

 

You can check if any step is breaking the folding. Check if you have the option to "View Native Query".

https://docs.microsoft.com/pt-br/power-query/power-query-folding

 

If PQ can apply folding query on your source, the merge (inner) should work.



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Anonymous
Not applicable

Another issue I forgot to mention when merging: I no longer have my standalone dimension, which I need to have unique keys to join to other tables. If I merge into the fact, I will no longer have unique keys.

 

Is it possible to filter the Dimension Key column based on the list of values of keys in the fact? 

@Anonymous ,

 

You don't need to expand the rows for the fact table. Just drop it after the merge.

It will keep the dimension table with unique values.



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Anonymous
Not applicable

I still have to load the full dimension in order to use it as a query/data source in the merge, correct? Or should I load the dimension in direct query to use with the merged imported query? 

@Anonymous ,

 

If Power Query can push the merge (Folding Query) to the source, it will be done there.

 

Load both tables and merge them, check the steps if you can see the native query after or on the merge step. If you can see the join there, PQ is pushing it to your source.



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Anonymous
Not applicable

I can see the native query in the Fact where I merged the dimension with an inner join. 

 

The dimension table itself is still loading all 2m rows to the model. How can I prevent this? Do I need to delete it? Switch to DQ? Would writing an explicit SQL query cause performance issues? If I merge the fact onto the dimension, the dimension will lose uniquness. 

@Anonymous ,

 

Merge them on dimension query. Don't expand the fact table, so you won't duplicate your data.

After that, just drop the new column generated by the merge.

 

The idea is to merge and drop the column, keeping the unique values for your dimension.



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

Anonymous
Not applicable

Ah, I think I see now. I can also expand using 'aggregated', then remove the column as well to make sure query folding takes place.

 

Thank you for the quick replies!

@Anonymous ,

 

You don't need this extra step the assure the folding query, once you merge them, the folding will be applied. Just remove the column with the table merged content.



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.