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
jnickell
Helper V
Helper V

Creating time series data out of Power BI Dataflows

I'm hoping that someone has figured this out, I have not been able to find an answer so far.  

 

What I'd like to be able to do is point a PowerBI dataflow (at our ERP's SQL database for example) and run "refreshes" that are timestamped.  Essentially I'd like to be able to say this is what "x" looked like on this date (or week, or month).  The next scheduled refresh would have a different timestamp, so I'd be able to visualize the changes in data, when the data itself is not preserving this level of transformation.  I'd like this to be configured once and 'let it run'.  

 

As I understand it what I can do is setup entities to have a "data before 'x'" and a "data after 'x'".   The Before dataflow could be set so that it only does the first refresh and the After dataflow refreshes periodically.  Any 'changes' in the After datasource would replace overwrite the existing data in the After dataflow.  

 

I'll admit that my understanding of Power BI is fairly basic at this point, but from what I'm seeing so far the only thing I could do that follows the 'configure once and let it run' mentality would have me exporting from the source ERP data to static files which the dataflow (folder datasource?) would then consume.  I'm then maintaining these exported static files so that data won't be removed from the dataflow on the next refresh.  Now I've got 3 copies of the data (source, exported static files, and the dataflow).  This seems redundant.  

 

Am I missing something, or should I be looking beyond the DataFlows framework?

8 REPLIES 8
Yousra
Advocate I
Advocate I

Hello, have you found a solution to this requirement? I have the same requirement and I tried the Incremental Refresh but it doesn't work. I've added a custom column with 

DateTimeZone.FixedUtcNow())

No, I abandoned the approach for now.  Sorry.

Anonymous
Not applicable

@jnickell  -

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

Natel, thanks for taking the time to respond.  

My understanding of the incremental refresh is that it provides a way to reduce import times, but it's not going to date/time stamp the data it pulls in.  We don't currently have Premium, but from what I understand from the documentation, I'm going to need some kind of data/time field in the dataset for it determine what should be included in a given increment.  Is that correct?  Some of our data isn't going to include that.  

 

The tool from RadaCad looks like it could have some promise, my only concern is that it winds up creating new Datasets rather than growing the data inside the original dataset.  I'm sure that I could 'get around' that, but would be preferred that we didn't need to.

Anonymous
Not applicable

@jnickell - Yes, it does need to key off of a date column, which is a simple, intuitive way to differentiate updates/inserts. However, if you want an entire snapshot you could add a column that refers to the current date (like SQL GETDATE). Then all of the rows would come in with the date stamped on each row.

Thanks for your continued thoughts, if we were to use the GetDate function in SQL, wouldn't "the last update's" be lost because it's no longer going to match?  By match, I mean that it's no longer going to be "in the dataset" and therefore removed from the refreshed query results. 

Anonymous
Not applicable

@jnickell - I can't test this because I don't have access to Premium currently, but I'm thinking that if you choose "Store Rows in the Last 1 Month" and "Refresh Rows in the Last 1Day", then one month worth of daily snapshots would be preserved and a new one would be added each day.

I think, based on what I found below, that the incremental refresh 'might' do what I was talking about, but it also makes me nervous about leveraging it in this way.  I don't have Premium either.  

Filter date column updates

The filter on the date column is used to dynamically partition the data into ranges in the Power BI service. Incremental refresh isn't designed to support cases where the filtered date column is updated in the source system. An update is interpreted as an insertion and a deletion, not an actual update. If the deletion occurs in the historical range and not the incremental range, it won’t get picked up. This can cause data refresh failures due to partition-key conflicts.

 

From here:

https://docs.microsoft.com/en-us/power-bi/service-premium-incremental-refresh

 

Wondering if anyone else out here has or is experimented with this?

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.

Top Solution Authors
Top Kudoed Authors