Migrate a Power Query or Power BI file to a local SSAS instance

In Visual Studio there is a wizard to migrate an Excel Power Pivot model to a SSAS model. But this will not bring over the M-queries unfortunately. But there is a workaround to achieve this. It requires SQL Server 2017 or higher:

The steps:

  1. Import the Excel file in Power BI Desktop, save and close the pbix-file
  2. Open Azure Analysis service, open the Web Designer and create a new model where you import the pbix
  3. Open that model with Visual Studio (this will actually create a download that holds the VS-file)
  4. Open that file in Visual Studio, load the data, build and change the deployment target from Azure to you local SSAS-database before deploying.

See how it goes:

Warning: There are some limitations for the M-functionalities in SSAS (see here for example: General Overview by Microsoft or Use your own SQL … by Chris Webb), so you might want to give it a thorough test before rolling out. There are missing a lot of data sources currently, like web-queries for example who will hopefully soon be added as well.

This method has been described by Soheil Bakhshi here before: http://biinsight.com/import-power-bi-desktop-model-to-ssas-tabular-2017-using-azure-analysis-services/ 

Enjoy & stay queryious 😉

GuestPost: Newbie to Newbie Learning M-Language as your first Programming Language

Foreword from Imke: “Happy to publish my 2nd guest post here: I met Rafael Knuth in the Technet-forum where it was a joy to see how quickly he was set on fire by the M-language. When he vented the idea about starting a newbie-to-newbie-series where he would share his learning experiences and perspectives as an “Excel-guy”, I was quick enough to engage him for an intro on my site. As it turns out, he is a VERY talented communicator as well, but just see for yourself”:

I’m just a regular corporate marketing guy in his late forties with no formal programming education. However, one day I woke up and decided to teach myself to code. I had no plan whatsoever, and my learning journey was anything but a carefully planned venue. It was rather accidental fumbling & stumbling, accompanied by loads of frustrations, with frequent, prolonged breaks to recover from my failed attempts to teach myself to code.

What makes learning to code so hard?

These are the main obstacles in my views:

1) Lack of time
As a professional in a corporate environment, it’s nearly impossible to put 20 hours a week aside to teach yourself a new skill from scratch, without major sacrifices in other areas of your life.

2) Your brain’s “wrong” wiring
What makes learning so hard is the amount of knowledge you have to unlearn: “Why is my program not doing what I expect?” Because you set the wrong expectations. Rewire your brain.

3) Complexity of the subject
Coding is a hard piece of candy, bluntly speaking. There is good reason why there’s such a dramatic undersupply with good developers.

4) Lack of applicability of your knowledge
So, you did that course on Python at Codecademy. How do you put your newly acquired skills at work? Unless you prove me wrong, my answer is: Not at all.

5) Unrealistic expectations
Become a Data Scientist in a 6 month bootcamp. You will find tons of offerings like that. So, basically what it says, is: “You can be smarter than all those guys who put years and years into studying programming, mathematics, acquiring PHDs – just join our course and you’ll get there in no time.” Good luck with that.

Microsoft M-Language comes to your rescue

Read more