Which if faster? Load into Data Model/Create Relationships, or Merge Queries

[Txtcher]

Our organization does not use Power BI to share reports. We are limited to using Excel. We have an API connector to Salesforce. The objects are huge, and I find if I create merges in Power Query, they can sometimes take forever.

So my question is:  Would it be quicker to load the object tables (filtered down as much as possible) into the Data Model, create relationships, and use DAX formulas vs. creating merges in PQ to combine it all into a single table?

[ZhangKun]

Because there is no specific scenario, I can't help you choose which one to choose. My work scenarios are more Excel, including pivot tables (created from models) and detail tables (DAX queries).

The biggest advantage of DAX is that it is fast in calculation and search. If you can't use query folding and need complex pivots or calculations (such as monthly accumulation, subtotals, etc.), then you should choose to import it into the DAX model. If you just load detailed data or simple pivots, you don't need to import the DAX model.

If you use a DAX model and need detailed data, you can use DAX Studio to export it to a table in the form of a dynamic link (DAX query).

[uzuntasgokberk]

Hello @Txtcher ,

1) Make sure that steps are folder query folding.(İt increases performance really good. For detailed information I wrote a blog about it: target=_blankhttps://medium.com/microsoft-power-bi/boosting-power-bi-performance-with-query-folding... İf it is not folded or folded use neccesary applied steps. 

2) Create star schema and make effective relationships. Dim tables, Fact tables etc. İf you are using snowflake schema, make sure use corretcly relationship(Avoid Bi-Directional and Many-to-Many Relationships Against High Cardinality Columns. İf you are using both relationship make sure CROSSFILTER dax funtion in your measures). 

3) Create optimize dax formulas. For example use SWITCH functions instead of IF.

4) Replace the Auto-Generated Date Table with a Custom Date Table in Your Model.

...

Best Regards,
Gökberk Uzuntaş

LinkedIn: https://www.linkedin.com/in/g%C3%B6kberk-uzunta%C5%9F-b43906198/

Medium: https://medium.com/@uzuntasgokberk

İf this post helps, then please consider Accept it as solution and kudos to help the other members find it more quickly.

[Txtcher]

Thank you. I will be sure to check out the link you offered. However, Salesforce does not offer much in the way of query folding.

So, in summary are you saying that yes, loading to Data Model, creating relationships, etc. is faster than merging all the data into a single table? (Which we sometimes have to do anyway because the client wants to see the data in a table form.)

[v-aatheeque · Accepted solution]

Hi @Txtcher ,
Thanks for reaching out to Microsoft Fabric Community forum.

I understand you're looking to compare two approaches loading data into the Data Model and creating relationships vs. merging queries in Power Query based on performance in Excel.

• When you load data into the Excel Data Model and establish relationships, you're leveraging PowerPivot, the engine behind the Data Model, to perform the heavy lifting. The VertiPaq engine, used by PowerPivot, compresses and optimizes data for DAX queries. By loading separate tables and creating relationships, you're using a more efficient columnar database structure, which is scalable for large datasets. This approach avoids loading everything into memory as a single flat table.
• Once data is in the Data Model, you can use DAX to create calculated columns, measures, or aggregates based on the relationships, adding flexibility and enhancing analysis.
• Merging large tables in Power Query can be slow, particularly with datasets from Salesforce. Since Power Query processes data in memory, large datasets can make the process time-consuming and resource-intensive. Merging is computationally intensive, and after the merge, the resulting large flat table can hinder further performance.
• Loading into the Data Model and Creating Relationships
• This approach works best when:Working with large datasets and complex relationships.
• You need efficient aggregations or reporting.Combining multiple data sources.
• Merging in Power Query,This method is ideal when:Handling small to medium datasets with simple relationships.Data transformation is required before reporting (e.g., generating a flat table)
• You need to perform complex data transformations with intermediate results.
• Loading data into the Data Model and using relationships is faster and more flexible, especially for large datasets. It minimizes upfront processing time and leverages Excel’s efficient in-memory analytics. Data Model and Relationships are more efficient for large datasets and complex reporting, while Merging in Power Query is better suited for smaller datasets and simpler needs, especially when a flat table is required.

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
 
if still you have problems on it please feel free to let us know !
 

[Txtcher]

Thank you so very, very much for the detailed explanation. I am excited to start reviewing some of my regular reports to modify and hopefully improve performance.  Again, thank you so much!