Back around 2015, Microsoft introduced the current Power BI platform to replace its “Power BI for Office 365” SharePoint/Excel-based offering that most people have long forgotten, with a focus of offering a self-service experience to business users. This product became so successful that a few years later Power BI Premium was positioned as “a superset of Analysis Services“, i.e. Power BI was to also become the future of Microsoft enterprise BI offering. But now came a conundrum, with two very distinct target audiences, toolsets, and approaches, living under one roof.

While working one dataset at a time can be done with Power BI Desktop – a piece of software clearly inspired by PowerPoint and Excel – Power BI Premium features inherited from Analysis Services, such as Object Level Security or Partitions, are by and large not supported in that tool. A perhaps even more crippling limitation is that Power BI Desktop is handling one dataset at a time, with no scripting abilities that can scale across several datasets, let alone an entire enterprise.

We’ve already covered in depth how various third-party dev tools have stepped in to complement Power BI Desktop. In this entry we will introduce in a logical sequence what are the programmatic options in the Power BI ecosystem. The intended audience is people coming from the self-service space to ease their way into enterprise tooling, as people coming from enterprise BI have already been using that stuff for a decade or more.

1. There’s a REST API for That, Right? Right?!The First Rule of APIs: Randomly partial functional coverageMicrosoft has done a good job promoting the fact many things can be done with Power BI via its set of REST APIs, which they’ve made in part available to “citizen developers” via Power Automate UI connectors. This provides an accessible path and learning curve from performing “clicky draggy” UI-driven manual steps in Power BI Desktop to a workflow-based automated approach, within reach of power users that are not professional developers or IT sysadmins.

So people first thinking of automating Power BI tasks will be tempted to believe these APIs cover all possible automation scenarios. Let’s cut to the chase, that’s not the case. The scope of these APIs covers embedding, admin/governance, and content management. What they do not cover however are the inners of a Power BI dataset, so if you want to script data modeling steps such as creating a relationship between tables or establishing an RLS rule, you’re out of luck with the REST APIs as of 2023.

1. PowerShell to the Rescue?The first tool that the adventurous power user is likely to run into past the REST APIs is PowerShell, which Microsoft also has promoted to some extent to its Power BI user base. But here’s where things start to be confusing, as “PowerShell” means different things:

“PowerShell is a cross-platform task automation solution made up of a command-line shell, a scripting language, and a configuration management framework. PowerShell runs on Windows, Linux, and macOS.”

documentation – let’s same three things the same way, because we’re Very Smart People

Citizen developer discovering an ancient inscription that reads “PowerShell”.
Carbon14 dates it to 8.000BC?PowerShell can run on desktops but also in Azure and third-party clouds, which opens up automation and orchestration possibilities, albeit less in a less user-friendly way than UI-based Power Automate. For Power BI purposes, see these modules, and for modeling purposes more specifically, this list of cmdlets. Among them, the Invoke-ASCmd cmdlet lets you “execute an XMLA script, TMSL script, Data Analysis Expressions (DAX) query, Multidimensional Expressions (MDX) query, or Data Mining Extensions (DMX) statement against an instance of Analysis Services.”

We’re about to talk more about TMSL and XMLA, if you’re interested in reviewing the alphabet soup inherited from Analysis Services then refer to this entry. PowerShell is definitely not a business user-friendly option, but it has its place for people used to admin automation via a command line, as it’s been around for a long time.

1. Better Luck with Tabular Model Scripting Language (TMSL)?With TMSL we’re fully transitioning to the Analysis Services toolset, with this mouthful:

“Tabular Model Scripting Language (TMSL) is the command and object model definition syntax for tabular data models at compatibility level 1200 or higher. TMSL communicates with Analysis Services through the XMLA protocol, where the XMLA.Execute method accepts both JSON-based statement scripts in TMSL as well as the traditional XML-based scripts in Analysis Services Scripting Language (ASSL for XMLA).”

documentation – they sure love their jargon over at Microsoft

TMSL: The Matrix Silicon Layer?
Not quite.TMSL can be executed from SSMS, SSIS, SQL Agent, or the aforementioned PowerShell, the latter being how TMSL can be executed from cloud platforms. SSMS can generate basic TMSL scripts for you from its UI. With TMSL, we can unlock access to what’s going on within a tabular model and its tables, models, and relationships. It’s relatively accessible if you’re familiar with JSON files. Yet, it’s not the programmatic Graal because of functional and performance limitations. In my opinion TMSL’s best fit is to execute ad hoc operations via SSMS, rather than full-fledged automation.

1. Tabular Object Model (TOM): Now We’re Talking with Scalable, Powerful C# ScriptingDear Power BI citizen developer, you’re not in Kansas anymore:

This guy models!

“The Tabular Object Model (TOM) is an extension of the Analysis Management Object (AMO) client library, created to support programming scenarios for tabular models created at compatibility level 1200 and higher. As with AMO, TOM provides a programmatic way to handle administrative functions like creating models, importing and refreshing data, and assigning roles and permissions.

TOM exposes native tabular metadata, such as model, tables, columns, and relationships objects. A high-level view of the object model tree, provided below, illustrates how the component parts are related.”

documentation – what a way with words, these writers are such elegant poets!

With TOM you can build entire datasets programmatically from scratch, including advanced features such as custom partitions, translations, or perspectives that many Power BI users don’t even know exist. But to use it you’ll have to learn at least a modicum of the C# language, set up a desktop and/or cloud environment with the adequate libraries (i.e. .Net DLLs), and learn the syntax and scope of the various AnalysisServices namespaces.

By conquering the TOM super power, we’re now morphing into the final form of the enterprise developer end boss! Read Programming Power BI datasets with the Tabular Object Model if you want to dive in, and watch the video below:

1. If the TOM is the Bees Knees, What About Tabular Editor Scripting? And Why All These Overlapping Options?Citizen Developer morphed into BI Final Boss, ready to shred complex data modeling problemsWait, there’s more? We’re almost there, but yes there’s an optional twist to TOM scripting. Tabular Editor is primarily known as a desktop GUI tool, but it is built on top of its own API using libraries that can be scripted. The TOMWrapper.dll namespace is very similar to the underlying TOM but adds features needed by Tabular Editor and aims to offer more convenience and abstraction than Microsoft’s libraries. See this video for more details.

Scripting in Tabular Editor also uses C#, so if you’re upskilling for TOM you’re prepping for TE and vice versa.

C# for complete newbsSo what to make of all these developer options? Microsoft has this to say:

“Although both TMSL and TOM expose the same objects, Table, Column and so forth, and the same operations, Create, Delete, Refresh, TOM does not use TMSL on the wire. TOM uses the MS-SSAS-T tabular protocol instead […]

The decision to use one or the other will come down to the specifics of your requirements. The TOM library provides richer functionality compared to TMSL. Specifically, whereas TMSL only offers coarse-grained operations at the database, table, partition, or role level, TOM allows operations at a much finer grain. To generate or update models programmatically, you will need the full extent of the API in the TOM library.”

More documentation – what a joy!

And here they state explicitly that these tools are not mutually exclusive:

“TOM represents a new and powerful API for Power BI developers that is separate and distinct from the Power BI REST APIs. While there is some overlap between these two APIs, each of these APIs includes a significant amount of functionality not included in the other. Furthermore, there are scenarios that require a developer to use both APIs together to implement a full solution.”

Programming Datasets with the Tabular Object Model (TOM)

I hope this clears things up. In conclusion, I think it’s unlikely that Microsoft will invest in adding advanced scripting in Power BI Desktop, as they seem content with relying on third-party tools while neglecting to keep SSMS/SSDT tooling up to date. If you want to manage Power BI datasets at scale, there’s no way around learning and using the APIs and XMLA endpoint scripting, as well as the cloud-based orchestration platform of your choice to make these API calls.

The post Making Sense of Power BI Programmatic Options: REST APIs, PowerShell, TMSL, TOM, Tabular Editor Scripting first appeared on Olivier Travers.