I recently gave a talk to the London PowerBI UserGroup and I kicked things off with a confession – “I don’t do much report building in PowerBI”. Perhaps an odd way to qualify myself to speak to that particular audience. But I am, however, a cloud solution architect – I spend my time designing large scalable cloud systems to process vast amounts of data and PowerBI is a common tool used on top of these systems.
Why then, do we accept the lack of controls available within PowerBI? Given any other end-user system I’d want to know about performance bottlenecks, about data model efficiency and, more than anything, I’d want it in source control.
First and foremost, the talk is available here.
The key to it all, is realising that PowerBI Desktop, when running, starts a SQL Server Analysis Services processes in the background. It doesn’t just use the same engine as Tabular, it literally runs tabular in the background without telling you.
Open up a PowerBI Desktop file and, after you’ve seen the “initialising model…” window, you’ll see this process in the background – one for each PBID session.
So – if the model is using Tabular in the background, we must be able to actually connect to the model!
First – Find your Temporary SSAS Port
There are two straight forward ways we can achieve this:
1. By far the easiest, is to open up DaxStudio if you have it installed.
When you open DaxStudio, it gives you a Connect window, which lists all of the PowerBI processes you have running in the background, as well as any Tabular services:
When you connect to a PBI file here, you’ll see the Port listed
In this case, my port is 5524 -be aware that this will change every time you open PowerBI Desktop, so you can’t hardcode anything looking for your “powerbi port”.
2. Alternatively, you can find the “msmdsrv.port.txt” file related to your specific instance.
Take a look in your user appdata folder, you should find a Microsoft/Power BI Desktop/ folder with some analysis services details:
C:UsersAppDataLocalMicrosoftPower BI DesktopAnalysisServicesWorkspaces
You’ll see an instance for each of your PBI Desktop instances, I’ve only got one at the moment:
Inside this folder, in another folder called “Data”, you’ll find the file we’re looking for:
Opening this file, we see:
Pretty straight forward, and no DAX required. Obviously if you have multiple instances, you’ll need to figure out which of these relates to the instance you’re after.
Connect via SSMS
Now that we know our port, we can simply open up management studio, connect to analysis services and enter “localhost:” and the port number from earlier.
Once connected, you’ll see a model connection – each PBIX file will have a GUID for this instance, but you can drill down and see the objects underneath, exactly as you would with a Tabular model:
You can now write queries, browse the model and basically treat it as a Tabular instance. The Database itself will use a generated GUID, and several internal tables will do the same – you can see above that a hidden data table has been created for every datekey included in my model.
We’ll discuss the applications of this in my next post – namely how this unlocks performance tuning, monitoring and source control.
Introduction to Data Wrangler in Microsoft Fabric
What is Data Wrangler? A key selling point of Microsoft Fabric is the Data Science
Jul
Autogen Power BI Model in Tabular Editor
In the realm of business intelligence, Power BI has emerged as a powerful tool for
Jul
Microsoft Healthcare Accelerator for Fabric
Microsoft released the Healthcare Data Solutions in Microsoft Fabric in Q1 2024. It was introduced
Jul
Unlock the Power of Colour: Make Your Power BI Reports Pop
Colour is a powerful visual tool that can enhance the appeal and readability of your
Jul
Python vs. PySpark: Navigating Data Analytics in Databricks – Part 2
Part 2: Exploring Advanced Functionalities in Databricks Welcome back to our Databricks journey! In this
May
GPT-4 with Vision vs Custom Vision in Anomaly Detection
Businesses today are generating data at an unprecedented rate. Automated processing of data is essential
May
Exploring DALL·E Capabilities
What is DALL·E? DALL·E is text-to-image generation system developed by OpenAI using deep learning methodologies.
May
Using Copilot Studio to Develop a HR Policy Bot
The next addition to Microsoft’s generative AI and large language model tools is Microsoft Copilot
Apr