store historical data from Power BI Online

[Salvador]

Hello,

I would like to store historical data from my Power BI reports in order to keep track of some KPI’s, to use analyze their evolution from a historical perspective.

My dataset consists of some tables from our Dynamics CRM (Odata source), and three tables on an excel file. At the moment, the best thing I’ve been able to get, is a CSV export using top right menu in Power BI charts/tables, or an R script that I have to execute manually every day, week or month depending on the periodicity of my historic logs.

The fact that this is a manual process and that I want to extract the historical data from several reports makes this process impractical, so I’d like to know if there’s a way to automatize it. Thinking about it we see only three possibilities:

1. Getting out of the box historic data in Power BI: Many people are asking for this but we don’t know at which stage will Microsoft come with a solution
2. Somehow automating the R scripts so that they run periodically
3. Setting up an external Python script that would connect to the internal database of Power BI and would store that data in a historic log database as explained in the following diagram:

I don’t know if the third option is even viable. How do I connect to Power BI’s internal database (online ideally vs desktop)? Could somebody throw some light into this matter? Any comments on options 1 and 2 or any other potential solutions would also be appreciated? Has somebody cracked the historic log problem with Power BI?

[Salvador · Accepted solution]

Hi,

My solution in this post 😄 http://community.powerbi.com/t5/Desktop/Storing-and-using-information-from-a-dynamic-data-source-usi...

[tej147]

Hi Salvador,

The script file for task scheduler is not found on your onedrive.
Can we a have a different solution for this?

Please let me know. It's urgently required. Thank you.

[masplin]

Hi Salvador

Looks like exactly what i'm looking for, but I'm a real PowerShell numptie so going to take a while to understand.  Can I just check your script is design to out putt he whole of a table in Power BI Desktop including any calcuated columns?

Is there any way to use the same approach to export a specific visual that contains measures rather than column values? For example i have a clauation that works out the totla vaue of outstanding quotes, but want to create a time series of this calculation.

Cheers

Mike

[mireis]

I'm curious what R script you used to store and display historical data.  My data's status fields change over time, and it'd be really useful if Power BI took a snapshot of the data periodically and stored that snapshot, in order to plot trends, etc.  It sounds like you have that solution in hand using R.  Can you point me to the resources you used to construct the R script?

[Salvador]

Hello Mireis,

I ended using Powershell and the task sheuduler to solve this issue, it runs power bi desktop every night to save csv files from some tables I want to track, and then re-feeds them into my dataset. 

I'm working on a guide on how to do this, since I was able to set it up thanks to the Power BI community, and will publish it here when it's ready (+/- 2 weeks)

Regards,

Salva.

[mireis]

Ah, v-yuezhe-msft posted some links above, I just didn't spot them.  I'll give them a look!

[v-yuezhe-msft]

Hi @Salvador,

Firstly, an idea about automatically exporting data on different time basis has been submitted in this link, please vote it up.

Secondly, to some extent, you can use R script to export data to CSV when updating your dataset, please check alanhodgson’s reply about this method in this thread.

Thirdly, I am afraid that you are not able to connect to Power BI internal database, Power BI manages the storage of data and metadata using Azure BLOB and Azure SQL Database in a secured way, please review this article to get more details.

Thanks,
Lydia Zhang

[Salvador]

Thanks Lydia, voted, I wish that they implement that soon.

Imke from Thebiccountant.com has given me a solution that seems to fit my needs, I still need to put it in practice 😄


"

Hi,

other solutions that I’m aware of is:

1. Power Update: http://poweronbi.com/power-update-features/
2. Using Powershell-script and use a task-scheduler to run it : https://github.com/djouallah/PowerBI_Desktop_ETL

BR, Imke"