Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Salvador
Responsive Resident
Responsive Resident

store historical data from Power BI Online

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:

 

1.png

 

 

 

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?

1 ACCEPTED SOLUTION
Salvador
Responsive Resident
Responsive Resident
8 REPLIES 8
Salvador
Responsive Resident
Responsive Resident

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
Impactful Individual
Impactful Individual

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
New Member

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
Responsive Resident
Responsive Resident

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.

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

v-yuezhe-msft
Employee
Employee

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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"

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.