Forum Discussion
How to calculate with data across multiple tables
Hello!
Exactly, in theory, the closer to the data source you make the transformations to organize the data, the better the performance can be (of course, it is important to test to validate).
For example, a SQL query to join the columns of the "order_table" table with the "order_sub_table" table would look like this:
SELECT ot.order_date,ot.order_no,ot.shipper_code,ot.consignee_code,ost.order_quantity,ost.comm_type
FROM order_table ot
LEFT JOIN order_sub_table ost
ON ot.order_no = ost.order_noIn Power BI Desktop, when importing data from a database, in "Advanced options" a "SQL Statement" text box appears for you to paste your SQL query, like the query above. This step would be important to avoid carrying out many merge operations within Power BI Desktop, and the organization into fact and dimension tables would be important to facilitate the creation of metrics and relationships between tables.
Resolver IIHello!
Analyzing your scenario, to avoid very heavy MERGE commands in PowerQuery (as your data source is the SQL database) an alternative could be to bring the data through SQL queries, where you could even build the fact and dimension tables (if you are not familiar with this dimensional modeling technique, take a look here), as this would leave the data organized to create metrics directly in the fact table (a FACT_ORDERS table could be created, for example).
If it is not possible for you to do this, you can also improve MERGE performance by creating dataflows from the Power BI service instead of using PowerQuery from Power BI Desktop (more details about Power BI dataflows here).
Thanks marcosvin. Went through the dimensional modeling technique guide and learnt quite a number of key concepts there (esp snowflake and role-playing dimenions). But sorry being new to PBI I need your further advices.
For "an alternatice could be to bring the data through SQL queries", do you mean to add SQL statements to merge the columns of multiple tables when I first connect to a DB ? Not sure if I understand correctly, given my existing DB tables' structures are not well organized in terms of the PBI requirement, I should kind of manipulate the tables, putting facts / dimensions together before they are taken into PBI desktop for further handling.
If that's the case, I will go on to explore this as I have no knowledge in writing those statements now.
- marcosvin
Hello!
Exactly, in theory, the closer to the data source you make the transformations to organize the data, the better the performance can be (of course, it is important to test to validate).
For example, a SQL query to join the columns of the "order_table" table with the "order_sub_table" table would look like this:
SELECT ot.order_date,ot.order_no,ot.shipper_code,ot.consignee_code,ost.order_quantity,ost.comm_type
FROM order_table ot
LEFT JOIN order_sub_table ost
ON ot.order_no = ost.order_noIn Power BI Desktop, when importing data from a database, in "Advanced options" a "SQL Statement" text box appears for you to paste your SQL query, like the query above. This step would be important to avoid carrying out many merge operations within Power BI Desktop, and the organization into fact and dimension tables would be important to facilitate the creation of metrics and relationships between tables.