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
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 BA_Pete
I followed the steps that you suggested but still no change in the performance.
let
Source = Oracle.Database("XXXX", [HierarchicalNavigation=true]),
XX = Source{[Schema="XX"]}[Data],
DIM_EMPLOYEE = XX{[Name="DIM_EMPLOYEE"]}[Data],
#"Filtered Rows" = Table.SelectRows(DIM_EMPLOYEE, each List.Contains(List.Buffer(Fact[EMPNO]),[EMPNO]))
in
#"Filtered Rows"
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 ,
You shouldn't need to use Direct Query mode any more.
I believe you only wanted to use DQ due to the large number of rows in your dimension table. If we can get this method to fold to the source, it will work very quickly in Import mode as you will only actually be importing the number of rows that correlate to to values in your fact table (about 126 IIRC).
Delete your DQ dimension table in Power Query, then set it up in exactly the same way again using Import mode. Then check to see if 'Native Query' is lit up on your final step or not.
Pete
No problem, glad we got there in the end. I happily accept kudos as payment 😉
Regarding additional changes, you should be able to make any additional transformations that can be converted into Oracle SQL code. Changing column names should be fine, as this will just be converted to 'AS' aliases in the SQL, but quite often changing data type to text, for example, will fail, as this generates an ambiguous VARCHAR conversion, which PQ won't take a risk on.
There's plenty of resources which outline in detail which transformations will fold to your source or not, but good old trial and error with your favourite/most-used functions is probably the best way. I'm sure, going forward, you now see the immense benefit you can get by ensuring your queries fold to the source.
Pete
Hi BA_Pete
I tried to perform the suggested steps from scratch and when I am applying the filter to limit the rows in the dimension, it says"This stepsresults in a query that is not supported in DirectQuery mode".
Hi BA_Pete
It worked 🙂 But I also noticed that the moment I add any steps to the Dimension i.e. rename column/change datatype, the Native query option is greyed out and it started loading the data again. I will probably add a note in PQ not to add any steps in future else it might stop working.
Thank you so much BA_Pete for the solution.
Hi BA_Pete
It worked 🙂 But I also noticed that the moment I add any steps to the Dimension i.e. rename column/change datatype, the Native query option is greyed out and it started loading the data again. I will probably add a note in PQ not to add any steps in future else it might stop working.
Thank you so much BA_Pete for the solution.