Limit Dimension Data based on Fact Table in Power Query

[LD2022]

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 performance is extremely slow using Import/DirectQuery mode. I tried to limit the rows in Dimension table by following the below steps:

1. Buffered List to extract unique list of EMPNO in F - EmpList 

Emplist = let
Source = List.Distinct(F[EMPNO]),
#"Buffered List" = List.Buffer(Source)
in
#"Buffered List"

2. Filter Dimension D  i.e. Table.SelectRows(#"Changed Type",each List.Contains(EmpList, [EMPNO]))

It works but it is extremely slow when "Enable Load" is enabled for Dimension D. Although the Step (2) returns few rows(129 rows), when I apply the changes in Power Query, it loads all the rows for Dimension D.

I was hoping to get some guidance if there is anything wrong or can be done differently.

[BA_Pete · Accepted solution]

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]

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

[LD2022]

Hi @BA_Pete 

I will try to establish relationship and see if it is any faster.