Real-time and historical data of same data set

[Anonymous]

Currently, I have a report that is based on a direct query that brings in real-time data as it relates to patient exam information. In addition to providing the real-time data, I am also wanting to provide the ability to view the historical data of the same data set. I'm not sure what the most efficient way would be to achieve this, but I was thinking, in addition to the direct query, I could also import the same data set in order to capture/store historical information? Basically, I want to be able to add a date slicer to the report and provide the ability to not only see real-time data, but also the ability to view historical data. Any suggestions on how to achieve this would be greatly appreciated! 

[lbendlin · Accepted solution]

As @v-lionel-msft  mentioned - you should store the historical data at the source and access it as needed (via Direct Query) and load the frequently needed current data into memory (using import mode, but just for a small portion of the data)

[Anonymous]

@lbendlin 

Makes total sense. Again, I appreciate your feedback! This is very helpful information

[v-lionel-msft]

Hi @Anonymous ,

Is there no historical data stored in your data source(SQL Server)?
You should store historical data in the data source, and then use the "Direct Query" mode to connect to the data source and display real-time data and historical data in Power BI.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-lionel-msft 

Yes, all of the historical data is stored within SQL Server. Currently, I have a PBI Report that is based on a Direct Query that is pulling the most recent data (every 15 min.) as it relates to Scheduled Exams for today's date. In addition to this, I am also wanting to give the users the ability to look back in time. At this point, I'm thinking of Importing all of the data (3 yr. lookback) as opposed to Direct Query and refreshing it every hr. I was trying to achieve the best of both worlds, but I don't think it's necessary to refresh the data that often (each hr. should suffice). 

[Anonymous]

@lbendlin 

All of the data is stored on a SQL Server. I meant to say "Import" the historical data, not store it within Power BI. 

Let me further educate myself on what you are suggesting as this might be a possible solution to what I'm trying to achieve. 

Appreciate the feedback! 

[lbendlin]

How do you plan to store the historical information?  Does your data source have that capability or did you hope Power BI could do that for you ? (Hint: it can't - I don't think hybrid streaming datasets are an option for your scenario)

Have a look at Aggregations.  They work similar to the OLAP cubes of old - the aggregated tables are like the precomputed cubes, and the direct query raw tables are like the db passthrough queries.

For you the aggregations would be the historical data, and the direct query the real time-ish data.