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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
OneWithQuestion
Post Prodigy
Post Prodigy

PowerBI hosting Excel vs Excel Services 2013 on prem (can Excel call back to SSAS Tabular)?

With it now being possible to deploy Excel workbooks/dashboards to PowerBI... how does that compare to Excel Services in 2013?

 

Could I now use PowerBI to host Excel workbooks that have pivot tables back to SSAS Tabular data connections?

 

With SharePoint Online/O365 there isn't a way to publish Excel to it, you have to use Excel Services 2013 on premise.

 

Could I now publish all my Excel workbooks with SSAS Tabular data connections via Power BI pro service?

 

Has anyone else done much with this?

 

Thanks!

5 REPLIES 5
OneWithQuestion
Post Prodigy
Post Prodigy

bump... anyone ever host their Excel workbooks in PowerBI?

We work with this scenario , Excel when created on the Tabular Model connection cannot be refreshed directly in Power BI. One would need to create a Power Pivot Model on Top of it in excel to be able to automatically refresh the same .

Here is what we do :

1> Created The Power PIvot Model from SSAS Tabular Model .

2> Create Pivot on top of Power Pivot Model in excel  .

3> Upload the file to ondrive .

4> Import from ondrive to Power BI service .

 

 

We can then set them to refresh using Personal gateway.

 

 

It would makes things o much easier for us if we could just refresh the excel pivot on tab models directly in Power BI . 

 

 

Regards,

Mitsu

Does doing that import ALL the SSAS Tabular data into Excel?

 

Additionally, can that Excel workbook refresh on open or does it have to be a scheduled event?

When you create a Power Pivot with SSAS as a source , you will be able to design a query to pull the information on the same . It will bring in the data as per the query . You would need to scedule a refresh for the same in Power BI  Via Personal Gateway  at this point . There will soon be ability to refresh the same via enterprise gateway as well .

Now running it through PowerPivot though would cause the SSAS Tabular roles (security) to not be available right?

 

Since running it through PowerPivot won't allow it to pass credentials?  Is that true?

 

If I am reading this right:

https://msdn.microsoft.com/en-us/library/gg399165(v=sql.110).aspx#cubeinteraction

 

That means that all the data is copied from SSAS Tabular INTO Excel PowerPivot:

 

----------

How PowerPivot Interacts with Analysis Services Cubes

 

When you use the wizard to connect to an Analysis Services data source, the internal xVelocity in-memory analytics engine (VertiPaq) composes an MDX query against the Analysis Services multidimensional database and then downloads the data into the workbook. The data cannot be refreshed and is not automatically updated when the data in the cube changes.

Data that you import to a PowerPivot workbook is self-contained after the data import phase. Rather than considering a PowerPivot workbook as a browsing surface for an existing cube, you should consider it as a workspace where you can obtain useful subsets of cube data and from that derive new analyses that are independent from the cube and from other data sources.

--------------

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors