Forum Discussion
Limit Dimension Data based on Fact Table in Power Query
Ok. Your current query setup won't allow query folding.
Can you try connecting without using an explicit SQL statement please?
Connect to the DB again, and leave the SQL statement blank:
Just hit OK, then select your dim_employee table from the table list and import.
Assuming this goes as planned, select the [EMPNO] column in your new dimension table and filter just one value. This should be fast to implement and sets us up a new step with the correct structure.
Edit that new step in the formula bar, so it uses our streaming list filter, something like this:
= Table.SelectRows( previousStepName, each List.Contains( List.Buffer( factTableName[EMPNO] ), [EMPNO] ) )This setup should allow PQ to stream the [EMPNO] values from your Excel source into the SQL native query sent to the SQL source for your dim_employee table.
Pete
Hi Anonymous ,
If you right-click your #"Filtered Rows" step, is 'Native Query' now selectable, or is it still greyed-out?
If it's still greyed out, try changing one of the column data types to the same type, but not a text column. For example, change a date type column to date type, or a decimal type column to decimal. This is purely to force PQ to re-evaluate the query and hopefully force it to generate a native query to source.
If native query IS selectable, then you're probably at the limit of optimisation. The Oracle Data Access Client (ODAC) requirement isn't the best in this regard.
Pete
Pete
Hi Anonymous ,
The first thing you can try is to shortcut your buffered list filter:
Remove your 'Buffered List' query then change the filter step in your dimension table to something like this:
Table.SelectRows(
#"Changed Type",
each List.Contains(List.Buffer(List.Distinct(F[EMPNO])), [EMPNO])
)
To be honest, I can't imagine this will speed things a up a whole lot, but worth a try.
The next thing you can do is to delete the merge and just send both tables to the data model. Relate them on D[EMPNO] = F[EMPNO]. It's likely to be significantly quicker than doing an unfolded Power Query merge.
Pete
Hi BA_Pete
Thanks for your response.
I have tried it but doesn't make much difference:
Table.SelectRows(
#"Changed Type",
each List.Contains(List.Buffer(List.Distinct(F[EMPNO])), [EMPNO])
)As the fact data is excel file based, I cannot join them in the database at the moment.
As a workaround, I have used DirectQuery mode however, the report is slow.
Any other ideas please?
Hi Anonymous ,
Did you try removing the merge and relating in the data model?
I think the fastest setup you'll get to in this scenario would be to use the buffered list filter on the dimension table in PQ, then relate both in the model.
You could also try filtering your dimension table in other ways in PQ before loading. To have 25M employee numbers in a dimension table I have to assume it goes back many years. Is it feasible to filter to maybe only employees active in the last few years? Maybe filter it for certain departments and create separate reports for each department?
This PQ filter will also be included in visual queries sent to the source when using Direct Query.
Pete