One of the biggest challenges there is while handling with a Power BI dataset is to monitor the quality of the data published for analysis. This post aims to teach a way to understand which data is available in the service and its quality. Monitoring it’s one of my golden rules to have an amazing Data Warehouse.
Context
Whenever a Power BI dataset gets refreshed there isn’t great information about the process. There is nothing about the data available or the quality of it and there is only a manual checking process. That’s why it’s quite harder to answer “Which data do I have available?” without actually accessing a cockpit report.
Solutions
As said before, a cockpit report in Power BI service is a possible solution but requires manual intervention to understand the quality of the data and lacks the possibility to set alerts.
Another possible solution is to build an Excel report using the “Analyze in Excel” option but without some sort of programming, it will end with the same issues as the cockpit report.
Nevertheless, any of the above solutions don’t sound quite good for some sort of monitoring process that every BI team should have.
What’s Next?
“Analyze in Excel” provides a connection string therefore that connection string can be used to connect directly to the portal and retrieve the information needed for monitoring.
The process
Let’s get a Power BI report.
Then we get the file after choosing “Analyze in Excel” option.
After that let’s get Microsoft Visual Studio Code and copy the connection string.
After that, there is a connection string that can be used as a SQL Server Linked Server.
Need to fill the name, select Analysis Services as Provider and paste the Connection String copied from the ODC file to the Provider String field.
After configuring the General tab, it comes to the Security page where it should select “Be made using this security context” and fill with the Power BI username and password.
With this done, we can start querying the Analysis Services instance.
It accepts DAX or MDX as expected.
/*DAX QUERY*/
SELECT 'DAX' as [SourceQuery], *
FROM Openquery(WWIPBI, 'DEFINE
MEASURE ''Fact Sale''[Quantity] = SUM(''Fact Sale''[Quantity])
EVALUATE
SUMMARIZECOLUMNS
(
''Calendar''[Calendar Year],
"Total Qty", [Quantity]
)
ORDER BY [Calendar Year]')
/*MDX QUERY*/
SELECT 'MDX' as [SourceQuery], *
FROM Openquery(WWIPBI,
'SELECT
[Total QTY]
ON 0,
NON EMPTY
ORDER(
[Calendar].[Calendar Year].[Calendar Year]
,[Calendar].[Calendar Year].current_member.properties(''key'')
,ASC)
ON 1
FROM [Model]')
It even accepts DMV queries!
SELECT *
FROM Openquery(WWIPBI,
'Select * from $System.discover_commands')
Final Notes
Should you do this? As a production solution, don’t think so. It’s a linked server solution that lacks scalability and for sure requires constant monitoring and maintenance.
This could be avoided if there was a connection to the workspace like Power BI Premium enables users to do (check here the XMLA Endpoints). Let’s see if the future will bring this feature also to Pro users.
