Power BI (cloud) + SSAS Cubes

3
votes

1) Can Power BI (online/cloud-based) use our local SSAS cube directly as a data source?

2) If no, and I assume it is no, then can we upload our SSAS cube(s) to be used as a data source, and how do we do that, preferably incrementally (if it is possible to do that incrementally)?

3) If SSAS cubes cannot be used, then I assume that we have to use data built into the SSAS Tabular Model, and use DAX to query it?

4) If this is true, then how do we send data to there? Do we have to define the tabular model locally and ship the stored results (since the tabular model is in memory, I’m not sure that that even makes sense), or do we send constituent tables to the cloud and build the tabular model structures there.

5) If I build this in an SSIS package (which I gather I do), is it an SSIS package that is built and maintained locally (meaning on our existing database, running MSSQL 2012 w/ Analysis Services, the way our existing SSIS packages are), or is it built and maintained in the Power BI Online environment in the cloud?

We're looking at using the PowerBI Preview to deploy dashboards and scorecards based on data that we collect on-premises. I'm assuming that we'd use that OData plugin to make data available in the cloud, for starters...?

edit: thanks for reading!

3
sql-serverssisssasbusiness-intelligencepowerbi
I believe for #1, you just need to set up a Data Management Gateway. – billinkc
jamesserra.com/archive/2015/01/… – mmarie
on this link stackoverflow.com/questions/35729888/… you can find some help how to connect SSAS cubes to PBI – Niraj

3 Answers

2
votes

Regarding 1): Power BI for O365 cannot as yet connect to an SSAS tabular or multidimensional model directly as a source. However, the new Power BI Preview (released December 14) does allow a direct connection to an SSAS tabular model with the new connector. So it is very likely that Microsoft will soon release a similar connector for SSAS multidimensional, as they did for power view on SharePoint, first releasing tabular then multidimensional.

2): There is no direct connection to user-maintained SSAS multidimensional models no matter where they are stored. There is a direct connection to tabular models with Power BI Preview, whether the tabular model is on-prem or in azure.

3), 4) & 5): If the purpose is to "deploy dashboards and scorecards based on data that we collect on-premises" then take a close look at Power Query and Power Pivot and at the Power BI Designer. Also take a look at the developer tools that are available here: https://msdn.microsoft.com/powerbi/

2
votes

This is an old question, and things have changed between then and now. In today's context using Power BI Gateway and Power BI desktop you can connect your on-premise SSAS cubes to power BI cloud and schedule your cubes to refreshed automatically. Earlier it used to support only tabular model, but as of now it supports both tabular and multidimensional SSAS cubes. The only thing which is not yet available is you cannot live connect a multidimensional cube but you can schedule it to be auto refreshed. However with SSAS tabular you can live connect to Power BI.

1
votes

We do this for a number of customers... you first need to consider the volume of data in your models as this will dictate your requirement for using Analysis Services (Tabular, as quite righty pointed out Multidimensional is not an option as the in memory model of SSAS Tabular is suited to PBI).

Then you need to think about how the dashboard will be updated and how automated this process can be made using Power Query + Power Pivot - using your OneDrive for Business could be an option to make life simpler should you not be able to use SSAS Connector in the preview.

Finally, depending on your source OData and a few other connections can be automatically refreshed on a schedule (CRM for example)...

Hope that helps.