Forum Discussion
Helper IIIConnecting to a star schema DW
Hi all,
I think this is a pretty rookie question.
I'm attempting to connect to a pre-built star schema datawarehouse (SQL Server). I am using one fact and 4 dimensions as my test.
The one fact takes a very long time to load in its entirety. I'm only looking for a subset of data (last 14 days). To do that I would join in my calendar dimension and filter on the last 14 days
To do this, I select my fact and the appropriate dimenion in "Get Data". They load and open in Powerquery. I cannot however set the relationship in PQ - that step seems to come in the data modelling pane. However that is after the PowerQuery loads the data which takes a very long time due to the large size of data
How should I be doing the join and prefiltering the data before it gets into PowerBI? My current solution is to use "Merge query" and merge the fact and dimensions together in PQ - but this poses a data redundancy issue when I add more facts that use the same dimensions (I also cannot use one slicer across all facts easily). I imagine there's a better simple way that I am overlooking.
Thank you,
2 Replies
- MattAllington
Community Champion
Ip this is normally a query folding issue, but it could be a missing index in your database source. Read my article about query folding here https://exceleratorbi.com.au/how-query-folding-works
BSLATTERHi Matt,
I read through the article and it seems to relate to the PowerQuery "Applied Steps" and making sure they have query folding enabled. Thank you for this - as it was very informative.
However - my issue is that I don't want to join the two tables in a single powerquery query. I would like one of the tables (the dimension) to be used in a separate PQ query and then joined to the fact (and other fact) queries int he data modelling "Model" pane on PowerBI.
Unfortunately I also rely on the dimension to filter the fact so that I can know the values to keep the fact performant when loading.
I also want to use values from the dimension in a slicer to filter values from both of my other facts.
Using "query folding" was this how you would solve the problem?:
New example: I have two facts. SalesTransactions and InventoryTransactions. I then also have a Calendar dimension.- I create a PQ query with SalesTransactions and Calendar. I merge them together using "merge queries" and filter on the last 12 days. These two steps appear in "Applied Steps" and have query folding on them
- I then grab my InventoryTransactions and the Calendar dimension (again) in a separate query to filter on the last 12 days
- I then create a third query that is just the Calendar dimension
In the Model I join the third query to the other two on the surrogate key they contained from the SQL DW and I can then use the Calendar dimension as a slicer
