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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.