Forum Discussion
Very Slow Performance on Group By
Hi TheCreepster1 ,
Don't append and merge at all.
Set your model up in the STAR SCHEMA structure, where your Sales and Inventory tables are the FACT tables, and any common fields (e.g. Location, Product etc.) are aggregated into DIMENSION tables that are then related to both fact tables.
You can then write measures that span both fact tables, as well as display data from both in visuals using the dimension table fields to maintain commonality between them.
https://docs.microsoft.com/en-us/power-bi/guidance/star-schema
Another option would be to have your SQL query written on the source as a view, rather than being a native query from Power Query. Presuming both of your tables are on the same DB/server, your append/group steps should be able to be folded back to the source, which would be significantly quicker at performing these functions than Power Query.
Pete
Hi TheCreepster1 ,
Don't append and merge at all.
Set your model up in the STAR SCHEMA structure, where your Sales and Inventory tables are the FACT tables, and any common fields (e.g. Location, Product etc.) are aggregated into DIMENSION tables that are then related to both fact tables.
You can then write measures that span both fact tables, as well as display data from both in visuals using the dimension table fields to maintain commonality between them.
https://docs.microsoft.com/en-us/power-bi/guidance/star-schema
Another option would be to have your SQL query written on the source as a view, rather than being a native query from Power Query. Presuming both of your tables are on the same DB/server, your append/group steps should be able to be folded back to the source, which would be significantly quicker at performing these functions than Power Query.
Pete
Hi,
Don't know why this didn't even cross my mind! Everything was set up to join as a star schema but I had it in my head I needed to smash the inventory and sales together for the end user. Unfortunately the two data sources are coming from different databases, hence the inability to do it in the SQL back end but I've managed to sort it through your first suggestion, thank you!
