cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PQEBI
Frequent Visitor

Real-time and historical data of same data set

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! 

1 ACCEPTED SOLUTION
lbendlin
Super User III
Super User III

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)

View solution in original post

6 REPLIES 6
lbendlin
Super User III
Super User III

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)

View solution in original post

PQEBI
Frequent Visitor

@lbendlin 

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

v-lionel-msft
Community Support
Community Support

Hi @PQEBI ,

 

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.

@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). 

PQEBI
Frequent Visitor

@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
Super User III
Super User III

 

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.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps