0

I finally decided to ask this (after a lot of google searching):

So we use Power BI for data visualization and thus in it are some calculated dashboards / data outputs which are used to monitor data quality etc. I want to be able to historical log these results so that over-time we can monitor progress i.e. was data quality improved. This is the end of the initial problem.

One approach to this problem was to connect to PowerBI from the MS-SQL side - hoping we can then set timed triggers to do the log by READING THE POWER-BI DASHBOARDS: So how do I query that (I have already developed a method to determine the connection using the Power-BI port as described here: EXPORTING DATA FROM POWER BI DESKTOP TO MS-SQL

This is a screenshot from one of my MS-SQL connections through "Analysis Services": enter image description here

I am assuming the objects named like "LocalDateTable_" are the actual BI analysis I want to query. "New Query" is an MDX type of Query. Should I go this route for my problem (logging powerbi analyses)?

Chagbert
  • 722
  • 7
  • 16
  • Power BI is a modelling / visualisation tool that runs against _existing_ data. You should be extracting data from the same place that Power BI is rather than extracting it from Power BI. Where is your power BI report getting its data from? – Nick.Mc Apr 17 '18 at 10:32
  • yes, you're right - I should Ideally be getting this log from the data source. However, we use PowerBi to do the analysis thus the computational queries in PowerBI: it is this output I would like to log – Chagbert Apr 17 '18 at 13:37

1 Answers1

0

At first this sounds crazy but on reflection I guess it was only a matter of time, and a sign of the maturity of Power BI solutions ...

I would use the SQL Server Profiler to capture the queries generated while you use your dashboard & report.

https://insightsquest.com/2017/05/07/profiler-trace-for-power-bi-desktop/

Then I would build an SSIS package to run the MDX queries and deliver the datasets to SQL Server, with extra columns e.g. StartTime.

Mike Honey
  • 14,523
  • 1
  • 24
  • 40
  • indeed using profiler is one way of doing it, however these PBI dashboards are created by some other colleague specializing on Data Visualization (not profiling). Also I'm not sure Profiler would capture the OUTPUT from those queries - except only profiling the statements of the query itself? – Chagbert Apr 18 '18 at 09:55
  • No Profiler wouldnt capture the output. That's why I advised to build an SSIS package to run the MDX queries. I don't have any interest in how you divide the task between yourself and your colleague. – Mike Honey Apr 18 '18 at 22:43
  • Thanks Mike. I understand. Will go with the long way of SSIS. – Chagbert Apr 23 '18 at 20:03