SSAS Tabular Model or Power BI Model

[Yted]

I am trying to get some information on whether you should be building the data model in Power BI or develop it in SSDT for deployment to SSAS Tabular server. What are the pros / cons for each? How do you decide? 

I have noticed that if you connect to the SSAS Tabular model on server, that you need to go back to that model to make any changes. If you do it in Power BI it's stored within the .pibx file. If you use SSAS server then it's only one model to manage?

Any assistance / guideance is appreciated.

[Anonymous]

Am shaking an old tree, and hope some apples drop on me!

I have most of my clients data on a SQL Server, and the SSAS is on the same server on premises. Now if I use Power BI Services, I will need a gateway, and data will travel from premises to azure, and the results will flow back.  Pretty wasteful for my case? Am I missing something? Apart from Citizin BI vs Enterprise BI.

TYSM for your thoughts, feedback and Ideas.

[Anonymous]

Now that Data Flows are coming out, I would love to understand the difference between Data Flows and Tabular SSAS cubes.  It seems like data flows are very flexible and require less tooling, so if they could compare in performance, cubes would be unnecessary.

[Anonymous]

Hi @Yted,

As @BhaveshPatel mentions this is can be a question of data governance. Having SSAS control your model can be very useful for things like corporate KPIs.

Some other considerations are:

Version control of pbix can be challenging, different versions of the file always creep out.

Distributing large pbixs over the network can below.

I'd definitely review the use cases before deciding where the final model resides.

[Greg_Deckler]

@Yted, to add to what @BhaveshPatel said, you can import a Power BI model to SSAS, but currently it is rather an involved process to actually accomplish that. See the following blog articles:

http://biinsight.com/import-power-bi-desktop-model-ssas-tabular-2016/

http://biinsight.com/connect-to-power-bi-desktop-model-from-excel-and-ssms/

It is currently relatively complex to get the data imported and then refresh is a whole other ball of wax. I really could not recommend it currently as a production solution.

There is an Idea out there to make this more streamlined:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/10882251-import-powerbi-desktop-m...

I would vote for that.

As to your question, it is really a question of a few trade-offs/decisions that need to be made. With SSAS it is a much more complex setup. You are likely going to need a SQL Server database as well as SSIS packages in the mix. The reason is that SSAS data import only supports a limited number of data sources (compared to Power BI):

https://technet.microsoft.com/en-us/library/hh230968(v=sql.130).aspx

So, first issue is complexity. SSAS adds complexity and using just Power BI makes things relatively simple.

A couple of things that might rule one or the other approach out immediately:

• Too much data. If you have too much data for Power BI to handle, then you need to use SSAS and Direct Query to live query the data
• If your connection type is not supported by SSAS data source import or SSIS (that would be harder to believe) then you might be forced to use Power BI Desktop

[Anonymous]

@Greg_Deckler,

Hello,,, is there a guideline of how much is too much for PowerBI Import .

My use case is to develop PBI Reports via Data stored in Azure HDInsight.... I have been giving it a thought to move the model to Azure Analysis Tabular,  however the data size is going to be a problem is a thought that I want to be convinced of..

Are there size estimation techniques that help in the PBI world. I am new to this,,, so pardon me if this sounds silly.

thanks in advance.

[BhaveshPatel]

They are both running on the same Vertipaq Engine. SSAS is more suitable for the Corporate BI development and PowerBI  is product more suitable for self service bi deployments. However, the underlying technology is similar other than SSAS tabular is more rich in number of features such as enhanced partiiton and RLS features. more configurable.

However, PowerBI models are transferrable to SQL server 2016. It means you can develop your model in powerbi or powerpivot and later on can import in SSAS Tabular. 

Complete BI Life Cycle  and Microsoft is filling the gaps in these products to make the more and more beautiful.

I Love it!

[Anonymous]

@BhaveshPatel  Good points. I would like to comment on your last sentence "Complete BI life cycle and Microsoft is filling the gaps..."

The CI/CD story is far from complete in Power BI. The reasons are as follows:

1. Power BI templates PBIT are binary files.
2. You want to use GIT for storing your PBIT files and then a CI/CD to publish the PBIT to PBIX into the desired workspace. The binary nature of PBIT makes this very difficult. (A 3rd party solution like ALM toolkit can be used, but nevertheless the developer experience is poor)
3. If I have a moderately complex data model (10-20 tables) and we are team of 2-3 develops, then there is no way to do a Pull Request review OR even a effect GIT version comparison.

Microsoft must open up the format of PBIT. 

Microsoft must make it possible to publish a PBIT to a Workspace (via REST).

Let me know if you disagree. Happy to learn.

Thanks