Moving my Tabular models to Power BI

[edwardrmiles]

I'm currently running a Tabular Model on SQL Server 2012.  When I look at the datamodelling and DAX functionality in Power BI is appears to be the latest version, that for Tabular is only available in SQL Server 2016.  Does anyone have opinions on the pros and cons of migarting a fairly complex but quite small model from Tabular to Power BI?

[greggyb · Accepted solution]

Pros: Most up-to-date DAX; Data Insights; Q&A

Cons: Level of effort; missing role-based security; missing KPIs; missing hierarchies; 250MB data model size limit.

What features in PBI are drawing you? Is there something that you've been actively missing in SSAS that is solved in PBI? If yes, it might be worthwhile. If you're eyeing some things as nice to have,  but there's no present need, I'd recommend holding off.

[edwardrmiles]

Thanks for the useful info.  There a few features, like support for many to many relationships, that I'm told are available in SQL Server 2016.  If Power BI has them it might work as a chepaer alternative to a full SQL upgrade, however lack of support for roles and the 250 MB size limit would be an issue

[greggyb]

No native N:N yet in PBI, but with bi-directional relationships, you can use a bridge table (the same model idiom for any extant version of Tabular) and not have to write special measures, so long as every table involved in the N:N relationship is using bi-directional relationships.

[Seth_C_Bauer · Accepted solution]

@edwardrmiles You don't have to migrate anything. You can just direct query your Tabular model using either the Analysis Services Connector, or the new Enterprise Gateway.

The Enterprise Gateway is in preview, but will eventually replace the AS connector. 

Here are just a few of the Pros and Cons off the top of my head:

Pros:

You can use features like data insights, Q and A in the Service

Cons: 

You lose the ability to use Row Level Security

You lose version control / 1 model as a source

You lose Direct Query (always the latest data) - you now have to schedule refresh (max 8x per day)

You are now restricted by files size (250mb)

Pro or Con depending on how you look at it.

You are essentially taking a tabular model as a source and breaking it into seperate reports that would need to be managed.

Your data no longer resides only on-premises, but is now pulled into the cloud along with your model

[greggyb · Accepted solution]

Pros: Most up-to-date DAX; Data Insights; Q&A

Cons: Level of effort; missing role-based security; missing KPIs; missing hierarchies; 250MB data model size limit.

What features in PBI are drawing you? Is there something that you've been actively missing in SSAS that is solved in PBI? If yes, it might be worthwhile. If you're eyeing some things as nice to have,  but there's no present need, I'd recommend holding off.