Is it better to use SSAS or Power BI for analysis?

[bjoshi]

Hi,

Which is a better design solution - using SSAS for analysis and using Power BI only for visualization or doing the analysis and visualization both in Power BI?

Thank You 

[dexterz]

SSAS and SSRS(power bi rs）is power bi enterprise On-Premise solution.

http://www.sqlservercentral.com/blogs/the-database-avenger/2017/02/14/power-bi-in-ssrs-t-sql-tuseday...

[bjoshi]

Hi @dexterz,

Thank You. That was a useful article.

[Phil_Seamark · Accepted solution]

Hi @bjoshi,

Depends on your data.

The good news is that Power BI Desktop is essentially a cut down version of SSAS Tabular.  So if you build a model in PBI Desktop, it's pretty easy to port to SSAS if need (and vice versa).

I'd say if your data volumes are small to medium and it's not too complex then there would me more advantages in using PBI over SSAS. However when the data model becomes more complex then it starts to favour SSAS.  

In saying that, an efficient Power BI Desktop model can import many billions of rows and perform well - and it's cheaper than buying an SSAS seat (on premise or Azure)

[Bokazoit]

Hi Phil

That is an answer I would like to try and get Your thoughts on because I am having problems with a fairly small dataset in my power BI datamodel.

It consists of 7 million rows and is a very simple model that in most cases performs well. But we are nowing facing performance issues with a single measure that makes the model use 15+ seconds to update the numbers.

The measure is a distinct count on a column with member numbers and the calculation needs to for each month in the chart count the distinct amount of members 12 month back. So that the question asked: "what is the distinct amount of paying members in May and a year back?" and "what is the distinct amount of paying members in April and a year back?" etc.

I am aware that the measure needs to calculate a year for each month, but with that small amount of rows I am puzzled. And we tried the community and got no answer that would change that speed.

So now I am actually looking into using a SSAS cube on an azure cloud as solution to this performance issue. Can that be true?

[bjoshi]

Hi @Phil_Seamark,

That makes things clearer for me with few more questions raised in my mind:

When and why would you want to port PBI model to SSAS?

I read in a few articles that it is better to port PBI model to SSAS for production.

Currenly, I am analyzing and developing reports in PBI and uploading it to PBI service with workspaces for each client. I upload respective reports to respective clients workspace. A PBI username and password has been created for each client in our organisation's Azure Active Directory. The username and password is provided to the client to access PBI. This is the production for us. Is this a good way? Or Is there a better way?

Thanking You,

Binit

[Phil_Seamark]

Hi @bjoshi,

One obvious reason why you might port to SSAS from PBI Desktop is the 1GB file size limit.  If your model is big (and I mean big) then moving to SSAS Tabular is easy.  Or you can try to optimise your model.

Another is that SSAS has much better support for incremental refreshing of data, which is a big hole in PBI desktop at the moment. 

SSAS gives you better control over data refresh times as well (assuming live connect).

There are a few reasons, there are more, but SSAS does cost more too.

[shekarkola_pom]

Dear @Phil_Seamark

I love the answer, but just want bit more clarification. are we only concern about data model size and complexity, should we also think about performance.

Kindly advise on following:

• When we use Tabular model (in-memory) rather than Power BI models, does it make any difference?
• Is the in-memory method applicable for a model designed in Power BI desktop and hosted at Power BI report server?

[martynhale]

Hi @bjoshi

Thanks for your thoughts on Power BI vs. SSAS. I'm new to SSAS so I'm looking for some clarification.

I'm pitching for a job where the client is likely to go Power BI Premium / Power BI Reporting Services - the only reason is their data is deemed as highly confidential. If this is the chosen route, will I have to build the DM in SSAS or can it be done in Power BI Desktop?

Also, would you please expand on your comment "...port to SSAS from PBI Desktop..."

Many thanks,

Martyn Hale

[bjoshi]

Hi @martynhale,

It is fine to go Power BI Premium/ Power BI Reporting Services if your data is highly confidential. In regards to building DM, it can be done either in SSAS or Power BI. Just quoting @Phil_Seamark's answer here:

"Power BI Desktop is essentially a cut down version of SSAS Tabular. So if you build a model in PBI Desktop, it's pretty easy to port to SSAS if need (and vice versa).
I'd say if your data volumes are small to medium and it's not too complex then there would me more advantages in using PBI over SSAS. However when the data model becomes more complex then it starts to favour SSAS.
In saying that, an efficient Power BI Desktop model can import many billions of rows and perform well - and it's cheaper than buying an SSAS seat (on premise or Azure)"

Also, "...port to SSAS from PBI Desktop" means moving the model built in PBI Desktop to SSAS tabular. You might want to do this for the following reason that @Phil_Seamark mentioned:

"One obvious reason why you might port to SSAS from PBI Desktop is the 1GB file size limit. If your model is big (and I mean big) then moving to SSAS Tabular is easy. Or you can try to optimise your model.
Another is that SSAS has much better support for incremental refreshing of data, which is a big hole in PBI desktop at the moment.
SSAS gives you better control over data refresh times as well (assuming live connect).
There are a few reasons, there are more, but SSAS does cost more too."

My suggestion would be:
If you are building a larger datawarehouse for the client, build you model in SSAS and use Power BI as a Presentation layer (Only for visualization). If you are only building smaller data marts then you can build the model in Power BI Desktop and also use it as a presentation layer. In case, your model becomes too large and complex, you can move the model to SSAS tabular.

Thanks,

Binit