Extracting SSAS Tabular Models w/ PowerShell

As a response to a comment on a previous blog post on how to extract SSAS Multidimensional [MD] databases with PowerShell, I decided to write a new blog post, to address the tabular version [Tab].

The main differences working with MD and Tab, programatically, is that MD is represented by XML for Analysis and Tab is using JSON. In management studio this makes no difference however, as you paste XMLA and JSON using the same query type; XMLA (I wonder when/if that will change?)

Obviously, the two technologies MD and Tab are vastly different in almost every other aspect as well, but for the scope of this exercise, we will keep it at that.

Just as in the previous example, we will be using the ability to load assemblies in PowerShell and leverage the functionality the product team has provided. With Analysis Services comes a library of objects to programatically access and manage an Analysis Services instance.

The namespace for MD:
https://docs.microsoft.com/en-us/dotnet/api/microsoft.analysisservices?redirectedfrom=MSDN&view=analysisservices-dotnet

The namespace for Tab:
https://docs.microsoft.com/en-us/dotnet/api/microsoft.analysisservices.tabular?view=analysisservices-dotnet

In this documentation, you can dig into the details of options available. All of this extensible from both C# and PowerShell.

Now, back to the problem at hand. We wanted to extract the models from one or more servers, to deploy to another (single) server them or even just persist them locally. To do this, we need to load the Tab version of the assembly, which is that first difference to the original script. Next we need to leverage different functionality within the assembly, to export the json.

The script in all it’s simplicity 🙂

Intelligent Cloud Conference 2019

This year the schedule for the Intelligent Cloud Conference is at a level of it’s own. The committee behind the conference has managed to bring in Azure and Data Superstars from all over the globe. So if you are new to the business I will try to round up some of the biggest topics that will be covered at the conference. If you are veteran, you will know the impact with just a quick glance at the schedule.

Regardless if you are in business with a Modern Data Warehouse approach, is doing Big Data Analytics or maybe just curious to know what the cloud has to offer in your area of interest, this conference will be the one you’d want to attend.

There will be three (3) Data Platform pre-cons, on 8th of April, that will be of interest to you, especially if you are doing Power BI, Data Warehousing or Advanced Analytics.

Modern Data Warehouse: Simon Whiteley, Monday (09:00-17:00)

The number of data & analytics components available in Azure has exploded over the past couple of years – understanding which components should be in your tool-belt and what part each plays can be a daunting task, especially given the speed technology is advancing at. However, if you want to meet the challenges of the growing data landscape, you have to adopt distributed cloud architectures!

Advanced Analytics and AI: Leila Etaati, Monday (09:00-17:00)

This training is designed for data science, data analysis and who want to do machine learning by writing R or Python code. A unique opportunity to learn from Lelia Etaati.

Definitive Power BI Architecture: Reza Rad, Monday (09:00-17:00)

This workshop is designed for data architect or administrator, who is designing the architecture of leveraging Power BI in a solution. Someone who wants to understand how all components of Power BI are sitting beside each other to build the whole solution. This training is designed for understanding the strategy of using Power BI rather than the development of it.

If you are into Azure stuff, we’ve got you covered as well, have a look here at the complete pre-con offer.

The Key Note will be hosted by none other than Rohan Kumar, CVP of Azure Data and this is really something to look forward to as well. I’m not sure I need to tell you this is rather big, but in case you’re in doubt, check out some of the preparations going into it:

LinkedIn does not allow for links :/

After this brilliant start we have two days of awesomeness lined up for you, regardless if you are into Azure or Data Platform. I know from the planning sessions, that the Azure geeks (MVP #1, #2, #3 and #4 – Hope I left no one out) were snickering a lot, like 4th graders, going over the submissions. All should be well with the Azure tracks. 👍

Looking at the Data Platform sessions I really have to pinch myself. I am truly proud of the job done by Just and Claus. Because I cannot recall a more power packed program at a Nordic conference, ever.

We have incredible Data Platform names like

Matthew Roche – Expect the unexpected
James Serra – Get ready for vast knowledge
David Peter Hansen – Insights from the trenches
Marco Russo – Learn from the Master
Simon Whiteley – Drinking from the fire hose
Christian Wade – “Clicky Clicky, Draggy Droppy” – enough said
and the list goes on and on…

We are covering sessions on

Azure SQL Database
Containers (and SQL Server)
Power BI
Azure Analysis Services
Databricks
Azure IoT
Azure Data Factory (v2)
Stream Analytics
R, AI & Cognitive Services & ML
Cosmos DB

If this was a party, it’s the one party you don’t want to miss…
Sign up here, now – thank me later 😉



Speaking at #SqlSaturday #Pittsburgh #770

September 29th I will be hosting two (2) sessions at the SqlSaturday event in Pittsburgh, Pennsylvania, US.

My first session will be on the basics of Power Query with some tips and tricks added to spice things up a bit. See details here.

My other session will be on Windows/Azure IoT and Raspberry PI. For that session, I used to carry a breadboard with my own poor soldering applied, but for this event I’ve bought a Grove PI starter kit which I am really excited to demonstrate as well. See more detail here.

The coincidence of a SqlSaturday and Pittsburgh Steelers playing in the same vicinity is something I experienced first time almost 5 years ago, as I went to my first SqlSaturday in Cambridge (which is a smashing event btw) where I got to see my first Steelers game as they played Minnesota Vikings at Wembley stadium

This time it will be on Heinz Field, taking on Baltimore Ravens – Classic arch rivalry in the AFC North. Wouldn’t be surprised if the final score was 2-0. But still very excited to go!

Speaking at the Power BI World Tour in Copenhagen

In just little over a week the Power BI World Tour will be stopping by in Copenhagen, Denmark. More precisely in Lyngby at Microsoft HQ. Copenhagen was luckily reelected for hosting the World Tour again this year, which I think will be very beneficial to the local Power BI adoption and community to say the least.

Copenhagen

Last time around I was honoured by having one of my sessions selected. This year I get to have two sessions. I am super excited about that!

My first session will be on Tuesday 11th about Power BI Best Practices. From the trenches of some of our own projects I have gathered a list of things to do in a project, to make your life easier.

My other session will be an Introduction to Power BI and Power Query (M). The query language is one of the few things of late that blown my mind in terms of capability and versatility. I will be showing you how to get started with the basics.

I am so looking forward to spending a couple of days with other Power BI Professionals, foreign and domestic!

#TSQL2SDAY #101 Round-Up : My Essential SQL Server Tools

Tuesday 3rd of this Month I invited people in the SQL Server community to share which tools are essential to their daily work. I was really overwhelmed by the number of stories that the topic triggered. 22 in total took the time to write down and share which tools they use for their work chores.
Going through 22 posts and aggregating them has been taking more time than I had hoped for, since my trusted laptop broke down – blinking codes are well and alive I tell you!

Going through the lot, I found some similarities to the posts, and have categorized them accordingly. But first off a BIG thank you to all how participated!

Without further ado, here goes.

Relational Heavy Lifting

Kamil Nowinski (b|l|t) takes us through the classic stuff, I mean, the real classic stuff – some would call it vintage – by showing how Total Commander still has a place in the tool belt, this century 😉

Matthew McGiffen (b|l) shows how to set up a Central Management Server, in order to execute queries against multiple instances in ad-hoc scenarios, seamlessly. Very nice tip. Matthew also did a second post, lining up multiple tools he’s written about in the past, nicely aggregated in this post.

Jess Pomfret (b|l|t) does a really nice post on how Powershell and the dbatools has changed her way of working. Jess even provides some useful snippets to get you going. I share the same enthusiasm for Powershell as Jess does, and was very pleased to see homage paid to the dbatools – incredible tool. Best of luck on your speaking adventures!

Marek Masko (b|l|t) has a huge post on classic DBA tools as well as a pleasant surprise on testing using tSQLt. Also some good pointers to free community scripts and tools as well. Great read!

Tracy Boggiano (b|l|t) covers dbatools and a specific Powershell Command and T-SQL Stored Procedure, but also on Telegraf and VS Code.

Dan Clemmens (b|l|t) goes all in on DBA tools for statistics, execution plans and tracing, even including the legendary diagnostic scripts from Glenn Berry.

Steve Jones (b|l|t) has a huge list of free and paid tools, from SQL Server sentric tools to a good deal of process related tools – i.e. DevOps and such.
Also Steve manages to sneak in a reminder on the password thingy magicky, that, according to domain expert Troy Hunt we all should rely on, be it pwsafe or any other tool like that.

Doug Purnell (b|l|t) is short and concise in his praise of Ola Hallengren maintenance scripts  and Jared Zagelbaums extension of those in Powershell.

Warren Estes (b|l) is praising the usual suspects in the DBA field, but adds a couple of interesting options for productivity and benchmarking/testing and also rounds up a couple of SentryOne products.

Devon Leann Ramirez (b|l|t) is offering a thorough introduction to their free plan explorer offering. Devon also makes a good point in marking the company’s presence on the community. If you want the quick tour, head over to Vimeo.

Rob Farley (b|l|t) talks about two things I really hold dear; Coffee… and I forgot the other thing. No really, Rob has an excellent blog post on Live Query Stats (LQS), and what some of the use cases are for that feature/tool. There are more ways of using LQS than I had thought about – thanks for sharing!

Riley Major (b|l|t) share his story on how he works with Management Studio and how the cool could be improved to further support a common way of working. Besides the tips on SSMS Riley also lists his favorite (and not so favorite) tools.

The BI Power in Power BI

James McGillivray (b|l|t) is first and foremost writing about my trusted travel mate; The Kindle (App) as his favored tool of the trade. Besides that treasure trove books can be, James also has some pointers to daxformatter and a theme generator which is pretty hefty!

Community Zone

Jo Douglas (b|l|t) argues that the most important tool for any professional is networking and community, and it’s hard not to agree completely. Jo also writes about some great points of where to begin this journey.

Jason Brimhall (b|l|t) brings up the aspect of blogging itself as a great tool of the trade, and I have to agree here and couldn’t have stated it more clearly that Jason:

Blogging helps you become a better technical person.

Googlefoo is also described in Jason’s blog for this party, and he manages to sneak in a reference to his extensive work and blogging on Extended Events, which in itself is an awesome contribution.

Reid DeWolfe (b|l|t) offers a quick write up on classic DBA must haves; SQL Prompt, Plan Explorer and GitHub/SourceTree. Reid also describes some of the main benefits of the tools.

Other

Garland MacNeil (b|t) brings another perspective into the party, by writing from a borrows laptop – not sure it was intentional, but I guess the exercise is very rich in terms of learning. I know others have been there too:

Chrissy LeMaire (b|l|t) has, surprisingly enough, not written about dbatools, and if you believe in that you may call me Bill 🙂
In Chrissys blog post you’ll find a great list of auxiliary tools for all the things you do around programming; Screen shot/Image handling, code repositories, clip board management and video editing tools.

Josh (b) gives us the DevOps perspective of a Database DBA/Developer in a not so uncommon scenario – well, I think we’ve all been there at some point. Some prominent 3rd party tooling is getting some ❤

The Other Tools

Catherine Wilhelmsen (b|l|t) offers a completely different and refreshing view on tools that were completely new to me, at least. Going from database modeling to data generators to time keeping tools and beyond.

Finally, No Tools

Hugo Kornelis (b|l|t) makes a good argument on not becoming addicted/dependent on the presence of certain tools in order to perform your job. I guess this applies in particular, when you’re a consultant and can’t always BYOD. Apart from that Hugo really likes SQL Prompt and Plan Explorer 😉


The Tools Mentioned (in no particular order)

dbatools dbatools  PowerShell  dbareports  SQL Server Management Studio
 Redgate SQL Compare  Minionware  Sentry One Plan Explorer dbachecks  SQL Operations Studio
 SQL Database Modeler  Dynamic Restore Script  Scooter Software Beyond Compare  Redgate DLM Dashboard  Ola Hallengrens’s maintenance scripts
 Trello  SQL Server Data Tools  Passwordsafe  Sublimetext  Notepad++
 Redgate SQL Prompt  Mockaroo  Dropbox Visual Studio Code  SQLCover
 Sourcetree  SQLNexus  Coblis  tSQLt  Advanced Theme Generator (PowerBI)
 DAX Formatter  R Studio  Scale SQL Clear Trace   PSSDiag  Devart’s dbForge
 Toggl  Grammarly  SCD Merge Wizard Statisticsparser  Adam Machanics whoisactive
 Winmerge  Mythicsoft Agent Ransack  Redgate SQL Search    Glenn Berry’s Diagnostic Scripts