Export PowerBI model to SSAS Tabular

Question

is there an easier way to export a PowerBI model to SSAS tabular? I saw the article by Jeroen ter Heerdt (maybe it's here), but wow what a hassle. is my better option to export the few tables I have to text or something and then import that into SSAS Tabular?

Looks like MSFT wants me to use Looker. Yikes.

Answer 1

In general, moving to SSAS is moving the wrong direction. New features are being poured into Microsoft Fabric as the future direction. Even Power BI Desktop is becoming a second-class citizen, and SSAS is a mere after-thought at best. I would think long and hard before moving anything to SSAS in 2023 or beyond...

But maybe you have TOP SECRET data that can't be in any cloud. Maybe your boss is making you. You have Reasons. I still don't recommend the path in the article. I've had no end of troubles getting XMLA scripts to work in SSMS. They generate fine, but they get weird syntax errors when I run them. I haven't successfully gotten this to work in years. Maybe it's my old SSAS version. Dunno. Long gave up on it.

If you have a simple model, it's faster to rebuild it, but you will run into complications when moving Power BI to SSAS:

• Power BI supports more data sources than SSAS
• SSAS does queries differently.
  • You create a Datasource query that connects to the source, and then Tables or Expressions can reference the Datasource. Tables don't access data directly, but always through a Datasource.
• Power BI often is ahead of SSAS in features. At a minimum, the Compatibility Level is often higher, so you have to take that into account as the article mentions, but more importantly, your model can lose features when you migrate them.
• Power BI allows Many to Many relationships, but at least my version of SSAS does not. Newer versions may, but check if this applies to you.
• Measures are a pain in SSAS. You enter them manually via "the grid".

I'm sure this is just scratching the surface.

I don't know if it still works, but one method that worked for some cross Power BI conversions for me is ALM Toolkit. You run a comparison between Power BI and SSAS, and it will generate a script to add what's missing between models. Not all definitions are supported by this tool, but it is my preferred tool for moving measures, though I'm always migrating from SSAS to Power BI. I don't use it for queries and table for the reasons mentioned above. The last time I tried using ALM Toolkit, I hit some new problems with the latest version of Power BI not being supported. I haven't had time to circle back to that.

Another tool you might look at is Michael Kovalsky's ModelAutoBuild. It builds models from an Excel template. I think somewhere else he has a tool to generate the template for an existing model. I know I looked at it, and I know there is some reason I went a different route, but I couldn't tell you why! YMMV

When migrating, I strongly recommend using DMV (Dynamic Management Views) queries to see your metadata before and after conversion. They are invaluable, and super easy to run from DAX Studio. Most of my SSAS to Power BI conversion process is driven by exports of DMVs.

So long story short, don't do it, but if you try, it won't be easy no matter how you try! Manually rebuilding it may be faster if you have a simple model.