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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

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
Not applicable

@lbendlin 

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

v-lionel-msft
Community Support
Community Support

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
Not applicable

@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
Not applicable

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

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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