Search code examples
powerbipowerquerypowerbi-desktop

Load current data from the Oracle database into Power BI and save/archive it there daily

Every day I load the current data from an Oracle database into PowerBI via SQL query. This table is then called tabLoad, which always contains the same columns, although there is also the valuationtime column in the form DD.MM.YYYY, which indicates today's/current key date. This source query happens daily and only contains the current data from today. So I can currently only present the report as a daily report.

In the future, however, I would like every report user who views the report to also have the choice to look at past days, for example what the data looked like a week ago. However, for this I need a historicization of my data based on the valuationtime column.

Is there somehow a way in PowerQuery to save the data from the tabLoad table into a tabHisto table every day after it has been successfully loaded from the database. This means that every day this tabHisto would grow by an additional day, row by row.

The tabLoad table looks like this - with data for today (07.05.2024):

enter image description here

Tomorrow the table structure will look the same, only the values will probably change, the valuationtime will then definitely be 08.05.2024.

Solution

  • Simple answer is no I'm afraid. PowerBI doesn't store historic data and re-streams from the source each time. If you want historic data to be displayed, you need to store that somewhere outside of Power BI to ingest.

    Why don't you store it in your Oracle database and consider using incremental refresh if the data size is very large?

    https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview