Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Limit Dimension Data based on Fact Table in Power Query

There are 2 tables: 1. Dimension Table D - EMPNO (25,000,000 rows) sourced from Oracle DB 2. Fact Table F - Excel source join on D.EMPNO=F.EMPNO using Merge queries in Power Query.   The perform...
Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

     

    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 

  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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

BA_Pete's avatar
BA_Pete
Super User
4 years ago

Hi Anonymous ,

 

Ok. My guess is that you're applying a query step to your dimension table that is breaking query folding or causing unnecesary processing. Using the method that we have, Power Query should be streaming the F[EMPNO] values to your SQL server in a native query, and should be really fast. Can you right-click the final step in your dimension query and check whether the 'Native Query' option is lit up or greyed-out please?

 

If it's greyed-out, select your dimension table as we have it set up now, go to the Home tab, select Advanced Editor and copy all the code in there. Then paste the whole lot into a code window here using this button:

 

Before you close the code window, please just overtype any sensitive connection string values (file paths, server paths etc.) with 'XXX', but keep the code structure itself, as this may be important.

I should be able to see if there's anything breaking query folding or causing unnecessary processing time that we can work around.

 

Pete

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi BA_Pete 

Below are the steps I have done:

DIM_EMPLOYEE=

let
    Source = Oracle.Database("XXX", [HierarchicalNavigation=true, Query="SELECT * FROM XX.DIM_EMPLOYEE"])
    
in
    Source

 

Create a reference table as below:

Filtered_Emp=

Filtered_Emp= let
    Source = DIM_EMPLOYEE,
    #"Filtered Rows" = Table.SelectRows(Source, each List.Contains(List.Buffer(EmpList),[CUSTOMER_NUMBER]))
in
    #"Filtered Rows"

 

The native query is greyed out.

I have disabled load for everything except the F and  Filtered_Emp. When I hit apply in Power Query, it tries to load all the 2 million records athough the Filtered_Emp has 200 records only.