Substitute for EXCEL to SSAS MDX solution with Power BI

[EduardD]

I need to migrate a solution from Excel Pivot Tables connected to MDX cubes to a similar UI in Power BI or Excel with a Power BI Semantic Model or SQL Server as the data source. I am considering the following three options:

1. Connect Excel to Power BI using the Analysis Services Connector: This allows users to continue using the familiar Pivot Table design. My concern with this approach is performance, I read that Tabular is still get converted to MDX in Excel that would degrade a performance. 

2. Create a Matrix Visual in Power BI Report and Enable Personalized Visualization: This enables users to slice data as they wish. However, there are issues with the flexibility of Totals and Subtotals, as Personalized Visuals do not allow enabling/disabling totals.

3. Use a Matrix Visual with Field Parameters for Rows/Columns and Measures: This includes creating field parametrs for all possible attributes (50+) and values (all possible measures), allowing users to populate the matrix view as they prefer. However, there are multiple issues with this option: the Matrix Visual does not expand rows when selecting extra field parameters, hence requiring manual steps from the end user to expand. Additionally, there are the same issues with flexible totals and subtotals for attributes as mentioned above. 
   
   Are thre any other options I'm missing here. Any feedback on the options listed above. What would be the best choice from a performance and data volume perspective (we have dozens of millions records to process)?

[v-veshwara-msft]

Hi @EduardD ,
We wanted to follow up again regarding your issue. If the responses provided were helpful please mark the helpful reply as 'Accept as Solution' to help others in the community with similar queries.
Please let us know if you need assistance.
Thank you.

[v-veshwara-msft]

Hi @EduardD ,

We just wanted to check in again regarding your issue. If you’ve found a reply helpful, marking it as the solution and leaving a kudos would be greatly appreciated--it helps the community and others with similar questions.

If you’re still facing challenges or have further questions, please let us know.

Thank you.

[Poojara_D12]

Hi @EduardD 

Migrating from Excel PivotTables on MDX cubes to Power BI or SQL Server requires balancing performance, flexibility, and user adaptability. Connecting Excel to Power BI via the Analysis Services Connector keeps the PivotTable UI but suffers from MDX conversion performance issues. Using a Power BI Matrix Visual with Personalized Visualization allows interaction but lacks dynamic totals/subtotals toggling. A Matrix Visual with Field Parameters offers customization but requires manual row expansion. A better Excel-based alternative is using "Analyze in Excel", which connects directly to the Power BI dataset with DAX, improving performance. For large-scale structured reporting, Power BI Paginated Reports handle millions of records efficiently and allow scheduled Excel exports with a PivotTable-like format. The best choice depends on user preference—Excel users benefit from "Analyze in Excel," while Power BI users get more control with Field Parameters or Paginated Reports.

 

[EduardD]

that is not right: A better Excel-based alternative is using "Analyze in Excel", which connects directly to the Power BI dataset with DAX

[v-veshwara-msft]

Hi @EduardD ,
Thank You for Posting in the Microsoft Fabric Community!

Here’s an analysis of your options for transitioning to Power BI while balancing performance and usability, along with helpful links to Microsoft documentation.

Feedback on Existing Options:

1. Excel + Power BI via Analysis Services Connector:
   This option is familiar for Excel users but has performance limitations. Excel sends MDX queries to Power BI’s Tabular Model, converting them to DAX. This translation can slow down performance, especially for large datasets. A live connection can help mitigate this but won’t fully eliminate the issue.
   Connect to Analysis Services data in Power BI Desktop - Power BI | Microsoft Learn

2. Power BI Matrix Visual + Personalized Visuals:
   A modern and user-friendly reporting option. Users can slice and interact with data, but the inability to enable or disable totals and subtotals limits its flexibility for detailed analysis.
   Enable Personalized Visuals in Power BI

3. Power BI Matrix Visual + Field Parameters:
   Field Parameters allow users to dynamically choose rows, columns, and measures, offering high flexibility. However, manual row expansion and subtotal customization are limitations that can affect usability.
   Use report readers to change visuals (preview) - Power BI | Microsoft Learn

Additional Options:

1. Excel PivotTables Connected to Power BI Datasets:
   Avoid MDX-to-DAX translation issues by connecting Excel PivotTables directly to Power BI datasets using DAX. This improves performance while retaining the familiarity of Excel.
   Connect Excel PivotTables to Power BI Datasets

2. Analyze in Excel (Hybrid Approach):
   Allows users to work directly with live Power BI datasets in Excel, providing flexibility and improved performance without MDX translation overhead.
   analyze-in-excel 

3. Power BI Paginated Reports:
   These reports are ideal for large datasets, offering detailed tabular layouts with full control over totals and formatting. However, they lack interactivity compared to standard visuals.
   Create and use the paginated report visual - Power BI | Microsoft Learn

Best Recommendations for Performance (Millions of Records):

• Primary Recommendation:
  Connect Excel PivotTables directly to Power BI datasets for the best combination of performance and familiarity. This setup minimizes MDX-related overhead and uses Power BI’s efficient DAX-based querying.
  Power BI semantic model experience in Excel - Power BI | Microsoft Learn

• Alternative Recommendation:
  For a Power BI-centric solution, use Matrix Visuals with a highly optimized data model. Ensure scalability by implementing aggregations, partitions, and DirectQuery for real-time data access.
  User-defined aggregations - Power BI | Microsoft Learn
  DirectQuery in Power BI - Power BI | Microsoft Learn
  
  

Hope these help.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and a kudos would be appreciated.

Best Regards.

[EduardD]

Thank you, For additional options: are you sure option 1 and 2 don't use the same DAX to MDX converter?

[v-veshwara-msft]

Hi @EduardD ,
Thank you for your question.
Here's the clarification regarding Option 1 (Excel PivotTables Connected to Power BI Datasets) and Option 2 (Analyze in Excel Hybrid Approach):

1. Option 1: Excel PivotTables Connected to Power BI Datasets:
   In this option, Excel directly connects to Power BI datasets using DAX for querying the data. This avoids the MDX-to-DAX conversion issues, providing better performance and efficiency, especially with large datasets.

   Power BI semantic model experience in Excel - Power BI | Microsoft Learn

2. Option 2: Analyze in Excel (Hybrid Approach):
   This approach allows Excel to interact with live Power BI datasets using DAX as well. When you connect Excel to Power BI datasets, DAX is used for querying, similar to Option 1. However, if you're connecting to Analysis Services models (instead of Power BI datasets), MDX would be used. So, for Power BI datasets specifically, MDX is not involved.

   Create Excel workbooks with refreshable Power BI data - Power BI | Microsoft Learn
   
   

I hope this clears up the confusion! Please feel free to ask if you have any further questions.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and a kudos would be appreciated.

Best Regards.

[EduardD]

Are you sure?  I tested both using this approach: https://www.youtube.com/watch?v=Bp88acT-Ma8. Excel produced MDX query for both scenarios. 

[v-veshwara-msft]

Hi @EduardD ,

Thanks for getting back,

You're right, both Excel PivotTables connected to Power BI datasets and "Analyze in Excel" generate MDX queries, since Excel treats Power BI datasets as OLAP sources, just like SSAS Tabular models. Power BI then translates those MDX queries into DAX, which can introduce performance overhead depending on the data complexity. I apologize for any confusion in my earlier response.

As @Poojara_D12  pointed out, the best approach really depends on your users' needs:

1. Excel users may prefer PivotTables, but MDX conversion can impact performance.
2. "Analyze in Excel" also uses MDX, though optimizing the Power BI dataset (e.g aggregations, pre calculated tables) can help.
3. Power BI Matrix Visuals with Field Parameters or Paginated Reports offer alternatives, though they come with some limitations in how users can interact with the data.
4. If structured, large-scale reporting is the priority, Paginated Reports is a strong option. They efficiently handle large datasets and support exporting to Excel in a well formatted, structured layout similar to PivotTables.
   
   

Hope this helps. 

If you find any reply helpful please consider marking it as solution to help others with similar queries.

Best regards,
Vinay.

[v-veshwara-msft]

Hi @EduardD ,
We just wanted to follow up regarding your issue. If the responses provided were helpful please mark the helpful reply as 'Accept as Solution' to help others in the community with similar queries or if you've found a solution please consider sharing it here to assist others.

Thank you.