How to Get Usage Statistics from On-Premise Power BI Report Server?

[Anonymous]

Hi all,

We are using on premise Power BI Report Server to host reports developed with our Power BI Desktop. What we want to track now is the number of users that visit these reports. We will like to know which user visited a report and how often. We will like to know the number of daily, monthly and yearly visits. How can we obtain this from the Power BI Report Server?

[jopasqui]

Hello @Anonymous 

You will find 2 ways to report usage statistics on Power BI Report Server :

* using the old way (like in SSRS), you can query the Report Server DB and retrieve all kind of information, the most famous view to query is ExecutionLog3, which will help you retrieving data on all kind of action on the report server (refresh on a specific DataSourceDataModel, report execution for a specific report ID by a specific user ID). Using other tables as dimensions, you can build a nice data model on Power BI.  Some documentation on executionLog: https://docs.microsoft.com/en-us/sql/reporting-services/report-server/report-server-executionlog-and-the-executionlog3-view?view=sql-server-2017 

* the other way, using the API of PBIRS (oData), and GET functions, you can get refined information (some type IDs are traduced in human language for example). The API uses the DB tables and views, but add information. Some information is tricky to get and will require few skills in M, for example if you want to get History of refresh execution, you will need to build an M function to rebuild URLs. Api swagger : https://app.swaggerhub.com/apis/microsoft-rs/PBIRS/2.0

Please note that data retention is 60 days by default, but can be modified. More information here : https://blogs.msdn.microsoft.com/shiyangqiu/2016/09/12/reporting-service-execution-log-retention/

[karimm]

Hi there

Thank you for your inputs.

Can you please advise how to connect to this ReportServer DB?

I guess I should be using SSMS but what are the connection details to be used?

[Anonymous]

In SSMS the Report Server database can be found on whichever server that you host Power BI RS. If you connect to that server, you should be able to see the database and the views using the 'Object Explorer' pane:

Hope that helps.

[karimm]

Thank you for your inputs.

I have found that the server name can be found by going to Start -> Services  and looking for all entries with "Sql Server (Instance Name)".

[Anonymous]

HI,

any hint where to find the usage statistics e.g. Number of Report Hits via the API?

Best Regards

[Anonymous]

Hi,

We didn't use the API. We ended up exploiting the execution log table

Regards,

[Anonymous]

Hi divo,

You can use the same queries on the ReportServer database for Power BI, as the ones you can use for a traditional SSRS (Reporting Services) database. If you search for 'SSRS Usage Statistics', you get tons of sample queries. You must tweak them a bit though, and filter some records; some (render/report) types are different.