Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
r96359
Frequent Visitor

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

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!!

5 REPLIES 5
r96359
Frequent Visitor

@lbendlin 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? 

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.

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!

no need for PowerApps.

 

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

lbendlin
Super User
Super User

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

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors