Can't refresh OLAP Cube generated by PowerBI through "Analyze in Excel"

[lo_p_ez]

I have a dashboard in PowerBI that gets refreshed every day through our Data Gateway. I also have an Excel pointing to that dashboard. I'd like this Excel to be already refreshed when I open the Excel. This is why I'm doing a little C# program that does that for me after the Data Gateway refreshes the dashboard. But I'm unable to do this... Here's my code:

Excel.Application excelApp = new Excel.Application();
excelApp.DisplayAlerts = false;
Excel.workbook workbook;

// ...

workbook.RefreshAll(); // this doesn't refresh the connection

// ...

foreach(Excel.WorkbookConnection wc in workbook.Connections){

    // I've also tried wc.OLEDBConnection.Reconnect()
    // Also I've tried to manually enable refresh file on open and then from here just switch that off
    // so when it opens it refreshes by default

    wc.OLEDBConnection.Refresh()

}

// ...

Through wc.OLEDBConnection I'm able to successfully access its properties (type, OLAP, RefreshDate,etc.) and add them to my logs, so I don't understand why it's not able to refresh it.

PD: this workbook.RefreshAll() code works for non-OLAP-connected spreadsheets.

Any help is appreciated! 🙂

[Eric_Zhang]

@lo_p_ez

Can you be more specific on how to have an Excel pointing to the dashboard?
By the way, you question seems more an Excel developement one, maybe more better support from the dedeciated forum.

[lo_p_ez]

By pointing to, I mean through the PowerBI addin. This addin makes a connection to a dashboard in PowerBI. In the background Excel creates a connection that the user can refresh manually, I want to aumate this piece.

[v-sihou-msft]

@lo_p_ez

When pinning tables onto dashboard via Power BI Add-in in Excel, it actually just upload the snapshot of the table (which is image) onto dashboard.

The Refresh in "Pin Manager" will just re-upload an new image. It's not dataset, we can't do schedule refresh on Power BI. And within this add-in, currently there's no automation option supported. See: Power BI publisher for Excel.

So for your requirement, you should upload Excel workbook onto Power BI Service to generate a dataset. Then you can schedule refresh with Gateway. See: Refresh a dataset created from an Excel workbook on a local drive.

Regards,