PowerBI Usages Data In Excel Not Loading Due To Many Records. How to remove report views with 0.

[palashaich]

Hello there,

I need to bring PowerBI usages data in a SQL server table. I was not able to hit the XMLA endpoint due to authentication issue.

The alternate solution I am going with is to pull data in a excel using Analze In Excel Feature. Later, I am refreshing the excel and populating in DB.

The excel data not refreshing due to many records. I am getting for each users for all the reports and dates combination of records even though there are no views for few days and few reports. The data was coming in excel as 0 report views. If I can remove all the rows with 0 report views in excel, the number os records will be less. Please let me know how to remove records with 0 report views. If I filter only for one users and value filter in date with report views > 0, it works. While trying to get data for all the users, the report views > 0 doesn't work. Also, how can I get the data only for last 3 days.

Thanks,

Palash

[GilbertQ]

Hi @palashaich 

I would suggest rather use the Reporting metrics API to get the data from the Power BI Service. 

This will allow you to get all the data down and store it locally. You could then use the CSV extract to quickly and easily import this data into SQL Server.

Here is a blog post on how to achieve this: Usage Monitoring with the Power BI API – Activity Log (Audit Log) Activity Event Data – Jeff Pries (...