Initial data load for incremental refresh on a nearly useless datetime column

[JTsoi]

Hi there, to whoever is reading this, thanks for stopping by.

I am running into an issue with trying to initially load a whole dataset into a Power BI report that uses incremental refreshing. I have a bunch of requirements stacked against me, and was wondering if there was any way around them. I'm hitting multiple problems, but they all stem from the Microsoft Dynamics 365 Business Central API I'm using.

I have my StartRange and EndRange parameters set up, and if I do an incremental refresh on Power BI Desktop, I can see it retrieving the data within those two parameters just fine. The problem comes with the data I'm working: The datetime column I'm using is the [systemLastModified], and about 100% of all the values are basically set to "0001-01-01T00:00:00Z" because we're transitioning our finance data from an old ERP system to Dynamics 365, and systemLastModified doesn't exist in the old system, so it just defaults to 0001-01-01 in Dynamics 365 after being ported.

If I try to do an initial data load, I'm hampered by the UI of Power BI and that I can only load data to an extent of 120 years. 

I can't modify the API either to return a different date, I thought about trying to replace the 0001-01-01 date with some other fake date like 1950-01-01 or 2077-01-01, but it's not my API, it's Microsoft's API. If I do a mass replacement of the dates, it would happen AFTER the API REST call, so I would lose all the benefits of query folding. I'm also restricted by my Power BI Pro license, so I can't use the fancy features like connecting to XMLA endpoints to edit refresh policies after publishing.

Any way to deal with this stupid datetime column? Despite the requirements, if I have to buy Power BI Premium I will, but I'd rather not.

Thanks,

Jonathan T.

[GeraldGEmerick · Accepted solution]

@JTsoi I would look into writing an extension/script that updates your system last modified dates in Dynamics 365 to something reasonable.

[JTsoi]

Minor addendum to this post after solution was accepted: I got the field name wrong, the field is actually called "SystemModifiedAt".

[tayloramy · Accepted solution]

Hi @JTsoi, 

I agree with @GeraldGEmerick. You want to get this cleaned up in Dynamics directly. Anything you do to try and resolve this externally is going to be a patch work solution at best. 

Is the old ERP still up? Are you still in progress of the transition or is the transition over now? 

The way I would approach this is to wipe it all out of D365 and do the import from the old system again, but this time have some ETL step that sets that modified date to something reasonable. 

[JTsoi]

Hi tayloramy, sorry for the slow response, I've been out sick for a couple of days.

Fortunately for me, my team leader, who also did the transition from the old ERP to Dynamics 365, basically thought "this is a hell of a risky change, let's give us some buffer space" and literally scheduled a year's worth of experimenting and user acceptance testing just to make sure the transition wouldn't screw us over when we do it for real. On my side, one of the tasks I'm working is basically porting over older Power BI reports that used the old ERP's data, and I saw incremental refreshing  and went "ooh shiny" and tried adding it.

What I'm hearing is that this is fundamentally more of a source data problem and not a Power BI sort of problem. I'll talk with my team lead and see what he can do. He's already informed me that modifying the data during the actual migration itself isn't possible, but afterwards might be possible. I'm still cautious because I still don't believe you can modify the systemLastModified field in Dynamics 365 directly, but I've been wrong before!

I'll accept yours and GeraldGEmerick's posts as solutions, thanks for your answers letting me take up your time! Thank you as well cengishanarslan, even if I can't use your solution.

Jonathan T.

[cengizhanarslan]

1. Remove incremental refresh temporarily
2. Load all historical data once
3. Publish the dataset
4. Re-enable incremental refresh
5. Refresh again (now only new rows are processed)

[JTsoi]

Hi cengizhanarslan,

I did actually give this a try, but unfortunately in order for me to enable or disable incremental refresh, I have to republish the report which overwrites the dataset/semantic model in Power BI Service with my local semantic model. At that point, any of the partitions that were made from the previous refresh are gone and the Power BI Service will try to do another "first-time" refresh, which is the refresh that misses all of the "0001-01-01" systemLastModified rows.

The other way for me to remove/enable incremental refresh is connecting to the workspace with other tools (SQL Server Management Studio, or Tabular Editor 2/3). In order to make that connection, I need the workspace connection URL, which I can get by going to my "Workspace settings"...but only if I have a Power BI Premium license, I'm stuck on Power BI Pro at the moment.

Thanks for giving a hand regardless,

Jonathan T

[GeraldGEmerick · Accepted solution]

@JTsoi I would look into writing an extension/script that updates your system last modified dates in Dynamics 365 to something reasonable.

[JTsoi]

Hi GeraldGEmerick,

I did consider that, but unfortunately the [systemLastModified] in Dynamics 365 is a system field that is read-only, so modifying them directly is out of my reach for now. I did have a a slightly insane moment of thinking about writing a script to loop through every single financial transaction in our system (around 4 - 5 million rows of g/l entries and 60000-ish budget entries) just to make an empty update just to force the systemLastModified field to change, but I'm going to leave that as a last resort, it feels a rather bit heavy of a hammer to use for this problem.