Help: Weekly Report - OLAP Cube refresh use dynamic slicers, export to xlsx file to add comments

[r96359]

I have an xlsx workbook with a OLAP Cube Query that gets refreshed weekly in a manual process.

I need to reinvent this to be cloud based and automated. Need guidance on architecture to get this working.

Please share your ideas 🙂

Overall Scope Includes:

1. Initiate OLAP Cube refresh on a weekly schedule (Power Automate)

2. Refresh OLAP Cube report weekly that is a table of 10 columns (Power Query in Azure or PowerBI published report?)

3. Dynamic Slicers or filters to set date for Friday of current week and Last week Ex: 2/23/2024 (Power Query or DAX?)

4. Dynamic Slicers or filters to set current quarter and year ex: 2024Q1 (Power Query or DAX?)

5. Limit results to top 20 of specific cost column  (Power Query or DAX?)

6. Generate a static xlsx file on SharePoint that represents current weeks data set so that others can add comments to the right of the table to justify results (Power BI doesn't support XLSX export. Help! ?)

7. Email link of new XLSX file on Sharepoint (Power Automate)

Many thanks!!

[r96359]

@lbendlin l wish I could but am stuck with the OLAP Cube and live query. I can't find a good process to take a PowerBI report into xlsx unless I export via csv. Any recommendations? 

[lbendlin]

From Excel you can connect directly to the semantic model and run DAX queries.  Not so sure about MDX but most likely possible as well.

[r96359]

If the excel file is sitting on sharepoint, can I use Powe Automate to refresh DAX Queries? Would that be using Power Apps for the query and Power Automate to call on it?

I tried Office Scripting but that does not support data queries like this one.

Thanks!

[lbendlin]

no need for PowerApps.

Office Scripts can refresh all connections in an Excel file...

[lbendlin]

Drop the OLAP cube, move everything to a Semantic Model.  Do your XLSX export via Power Automate.