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.
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.
Resolver IIHello!
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_no
In 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.