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

Real Time data to keep only latest version of each record

Hi,

 

We like the feature of feeding PowerBi from azure stream analytics and showting the data in real time, as well as keeping the history. 

 

Is it possible to configure PowerBi to only keep the latest version of a record, identified by key? 

 

We send regular updates of our records in real time to PowerBi and with every new update, the old data becomes redundant. 

 

This old redundant data is however visibile in all reports and result in incorrect view. 

 

What's the best way to go around this?

 

Thanks,

Stefan

3 REPLIES 3
v-haibl-msft Super Contributor
Super Contributor

Re: Real Time data to keep only latest version of each record

@stefan_uk

 

If your data are grouped by date and time, I think you can add a filter to your report in Power BI. So that you can filter the data to make it show the latest records. You can also take a look at this article which provide the solution on stream analytics side. Hope it helps.

 

Best Regards,

Herbert

stefan_uk Frequent Visitor
Frequent Visitor

Re: Real Time data to keep only latest version of each record

Hi Herbert,

 

the data is not groupped by datatime, it's groupped by a custom key (RecordKey in the example below).

 

Example input data into stream analytics

 

Time         RecordKey   RecordValue

T                      r1                  10

T                      r2                  11

T+1                 r1                   12

T+2                 r1                   13

T+3                 r2                   14

T+4                 r1                   15

 

Lets assume this is how data arrives to stream analytics. For rows with same time, the records arrive together in an array.

 

The requirement is at any time to show latest 'RecordValue' for each 'RecordKey' in PowerBI, in real time

 

Expected values displayed in PowerBI

 

Time     Values

T           r1: 10, r2: 11

T+1      r1: 12, r2: 11

T+2      r1:13,  r2: 11

T+3      r1: 13, r2: 14

T+4      r1: 15, r2: 14

 

Can time window in stream analytics solve this problem, and keep data coming real time?

E.g. configuring a 'day' time window in stream analytics - is this going to calculte the aggregated view for all records for each update (T, T+1, T+2,...) and send to PowerBI? That would suggest stream analytics holds all the records throughout the time window.

 

Can we then configure PowerBI to always only display the latest records? 

 

Thanks,

Stefan

v-haibl-msft Super Contributor
Super Contributor

Re: Real Time data to keep only latest version of each record

@stefan_uk

 

As far as I know, Power BI Service will always get all records form your data source. You cannot control it to only get the latest records. We can find a similar idea here which has already been voted.

So I think you can do something on the ASA side to control the output which sent to Power BI Service. I found this document and maybe you can do it with SQL query in ASA. If you have problems with ASA side, you can ask for help in the ASA forums.

 

Best Regards,

Herbert

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 140 members 1,496 guests
Please welcome our newest community members: