Quick Intro on XMLA Endpoint & TOM
XMLA endpoints are connectivity channels in-order to interact with Analysis services. This protocol can be used to programmatically query PowerBI Premium datasets via API's. TOM ( Tabular Object Model) is a .NET library ( extension of AMO client library)
that translates your scripts( eg- C# script) to HTTP requests against your XMLA endpoints. The cover image above shows the same and also displays the TOM hierarchy
your TOM model (.bim file ) should adhere to. Basically TOM helps to create a new Database instance( Analysis services) from scratch and automates migration of AAS models to PBI premium.
What are we trying to accomplish?
What are the prerequisites & steps to set up your VS code
How to query a PBI model and possible automation ideas.
Setting up PBI file and VS code
Firstly open-up a sample PowerBI desktop file. In my case I have created a sample PowerBI file connected to an excel file. Create some visualizations and publish it to your Premium workspace in cloud ( ensure your XMLA is read/write enabled).
Identify the port your PBI file is running, generally I use Resource monitor to get the PID( look for "msmdsrv.exe") and then fire netstat command to get this info...see below and grab the 4 digit number 59055. this will be handy further.
3. Install VS code editor and .NET core SDK https://dotnet.microsoft.com/download/visual-studio-sdks
4. Run the "dotnet –info" in the VS terminal to confirm the installed SDK ( i had installed 3.1.413 .NET SDK)
5. Now create a folder(in my case it is "test") in your c->users->yourname->TOM. From command line utility you could navigate to this path and run the "dotnet new console" command which creates a C# console app in this folder path
6. Now reference the TOM library using the below command.. and once done you are ready to query your PBI DS. If all is well so far, there should no errors ....if there are some compatibility issues check your .NET SDK runtime and try the below command again.
7. Here I have some screenshots of C# code with comments...which will be quite self-explanatory.
8. In the below image I just need to grab my XMLA endpoint and the local port instance ..the commented code does the job of iterating your tables in your local instance however we will look at the one which is pushed to PowerBI service.
9. Next 2 screenshots are pretty self explanatory..also remember to remove username and password if you are using MFA in your organization.. In the last portion we are trying to add a measure to a specific DS however you could automate movement of measures from 1 PB file to other or DS's.
10. This piece of code gives you the DS refresh capability and i also put in few stats on refreshed and last processed time.
The final output is as follows:- use the dotnet build and run commands to run the
code. FYI-> my Datamodel has 2 tables Orders & Users ( localdate tables show up because the "autodatetime" feature was enabled.)
TOM opens up and whole lot of possibilities..infact in one of our on-going R&D POC we are in process of automating migration of Tableau datasets to PowerBI. …Interesting isn't it?
I would recommend you check Tabular Editor as it is a more versatile tool and built for managing and automating AAS and Premium Datasets!! rather than take the VS route
* images and sources are from Microsoft and certain portion of code snippets are referred are from https://powerbidevcamp.powerappsportals.com/articles/programming-datasets-with-TOM/
Love to hear your comments...Happy blogging!!
Comments