Finance and Operations tables in Dataverse

[HMSNemanja]

Hi,

We have recently purchased a Microsoft Fabrics service which we'll be using as our data platform throughout our organization.

Previously, we integrated our Finance and Operations data with the BYOD concept to an Azure SQL which we used as a Data warehouse, however we would like to setup and integrate our F&O data with our Microsoft Fabrics.

According to Microsoft documentation, this should be possible to do through Power platform.

Currently, our Finance and Operations environment is linked to a Power platform environment through Lifecycle Services, which has Dataverse enabled.

However we're not able to see our Finance and Operations tables under the make.powerapps.com => Tables menu.

Our goal is to do this without enabling Dual-Write, since there's no need to dual-write for our case.

We've looked through the official Microsoft documentation for this but are not able to find the issue.

The configuration in LifeCycle Services is according to Microsoft Documentation for linking Finance and Operations with Power Platform.
My security role in the environment is Dataverse System Administrator, and I'm able to see other tables which are not Finance and Ops.

Any help or guidance on why we cannot see the Finance and Operations tables in our Dataverse would be much appreciated!

[SpaceAnalytics]

Hi HMSNemanja,

Thanks for your reply. I can not test this myself as I do not have the necessary rights on the Power App side of things. I would need to know how to instruct the right people and I'd preferably do this only once I have a full overview of a possible solution. I do know that they have full administrative rights to F&O and Power Apps.

[HMSNemanja]

Alright, so you have "sql row version change tracking" enabled in your F&O environment and you've got it connected through LCS to your power platform environment.
So basically, when you go to make.powerapps.com => Tables, you see the Dataverse tables (or entities, based on your dual-write configuration).
I'm quite positive by now, having read several community threads, official Microsoft documentation and have had dialogues with other Dataverse Engineers, you're not supposed to have F&O tables here.
So what happens when you go to make.powerapps.com => Tables and use the Analyze => Link to Fabric option is, it links your entire Dataverse by default. During the preview of this function, it was possible to manually select which tables to export, this was removed when the function was released to PA.
The link itself creates a Lakehouse inside the Fabric capacity & workspace you selected, and if you go to make.powerapps.com => Azure Synapse Link, you'll see a new synapse link called "Microsoft OneLake"

Option 1:
Now, you can edit this link by clicking Manage Tables, and add your F&O tables (We have the same sync issue you noticed, I have a case with Microsoft on that)

This will automatically include the conversion to Delta Parquet in your OneLake, meaning you will not waste any compute in your Fabric capacity for the actual conversion into parquet.
By that point, you have, according to Microsoft's medallion definition, a bronze layer in place.

1. The scenario above was our starting point as well.Like previously mentioned, I have ongoing cases with Microsoft regarding:
   Finance and Operations tables in Dataverse
2. Deselecting Dataverse tables from Microsoft Onelake synapse link
3. Synchronization issues when using Link to Fabric with Finance and Operations tables

(I will keep this thread up to date with the responses I get from Microsoft)

Option 2 :

Create a separate Azure Synapse Link in your Power platform environment to a different ADLS Gen2, use "Enable incremental update folder structure" to see Finance and Operations tables:

This means you'll be incrementally exporting data from your Finance and Operations application to your Datalake with the given interval.
From there, you can create a new Lakehouse in your Fabric workspace, and add your ADLS Gen2 as a shortcut (Navigate to the Lakehouse => Get Data => New shortcut)

You will then have to create spark notebooks for converting the CSV files from your "External" ADLS Gen2 to Delta Parquet and insert the data into tables in your Lakehouse. This means you will consume compute capacity for the conversion (something that you do not do with the Link to Fabric option).
There's quite a lot of documentation on the actual scripts online, it was not very complicated using pyspark for the conversion and spark sql for the merge. Once that is done, you have your Bronze layer.
With this option, you can atleast deterministically choose which tables you export to Fabric, however you immediately consume more compute and lose the "near realtime" feature which Microsoft claims that you get with the "Link to Fabric" option.


Option 3 (unverified):
According to Microsoft documentation:
https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-view-in-fabric

(specifically this section):

You cannot remove tables once they've been linked.

1. However, if you go to "Manage tables" in your "Microsoft OneLake" Azure Synapse Link, and let it load up the tables (this takes time depending on the amount of tables you have), you can:
   Switch to "D365 Finance and Operations"
2. Switch back to Dataverse
3. Deselect one table at the time (if I deselect more than 7 tables, the interface bugs out and won't let me save)
   
   
4. Wait a few moments (the "Save" button goes from disabled to enabled again) and click Save.
5. A new dialogue window appears, asking you to confirm the removal of the tables you've deselected  
   
6. Hit accept and let it save.

This does in fact remove the tables you deselected from your link to Fabric, however, it will put your entire Synapse link into a strange state, because if you navigate to the Microsoft OneLake link and click "Tables", all tables will have a "Deleted" Fabric state:

However it seems that this state is only applied for all tables while the actual deletion of the tables you deselected takes place. After the deletion of your deselected tables, the other tables return back to their "Created" Fabric state (it took about 2h in my env).

All of this being said, we're currently investigating option 1 with Microsoft. Meanwhile, we're developing for Option 2.
I'm simultaneously experimenting with Option 3, since this is not documented, it might be a undiscovered.....feature. 
Oh, and the Fabric capacity you provision has absolutely nothing to do with the sync issue you're experiencing from Option 1, meaning you will not see improved sync times if you turn up capacity.

Apologies for the long post, and hope it helps you out somehow.

[HMSNemanja]

Hi SpaceAnalytics,

Short version:

>Enable change tracking on your Finance and Operation tables

>Enable "Sql row version change tracking" in Finance and Operations

(https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-select-fno-data)

>Create a connection between your Finance and Operation environment to Power platform in the LifeCycle Services (The power platform environment needs to be created in the same region as your F&O app is hosted in, and it needs to have Dataverse enabled)
https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-view-in-fabric#c...

See my previous reply to AndersMellis on how you can take it from there.

Let me know if you have any more questions or if any help is needed (The Microsoft documentation regarding this is a jungle).

[SpaceAnalytics]

Hi HMSNemanja,

Many thanks so far for your explanation and guidance. I will try to make progress from here and keep an eye out for further comments/progress on your support tickets!

Regards

[HMSNemanja]

New information from Microsoft Support regarding the Synchronization between Finance and Operations tables and Microsoft Fabric when using "Link to Fabric" https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-view-in-fabric

"We did start working with collaborating engineers in order to identify the exact reason on why the performance could take as much time as it does in your occasion. 

There has been an update on the investigation confirming that it might take up to 60 minutes to update data in OneLake including the conversion to delta parquet format. If you selected a table that contains a lot of data, the initial load time might take longer.

Further information on the matter is available in the following article: 

https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-view-in-fabric#l...

There has been confirmation that our Product Group has planned updates in this field in the future, however no ETA is available as of the time of this email. "

The answer above is in reference to the following Note in the official documentation :

[Anonymous]

Hi @HMSNemanja, thank you for all this information. 

this is a bit worrying. From my customers point of view, 60 minutes to update data is not acceptable and no way near close-to-real-time that the Export To DataLake fyunctionality offers.

[HMSNemanja]

Hi AndersMellis.

Agreed.

We've had previously been told by Microsoft, on two separate occasions, that the sync happens within 10 minutes. 
That being said, the MS engineer that told us this also agreed with us that the documentation is unclear right now, so perhaps there's a bit confusion on their side as well on this.


Heading into a Fabric AMA session today with some different MS engineers, hoping to get some new information during that session.

Will keep the post updated with further news.

[HMSNemanja]

New response from Microsoft Finance and Operations support engineer regarding my "Option 1" description:
"

We confirm that the same interval applies to Finance and Operations linked tables.

You are following the correct way to export the Finance and operations tables.

Please note that, we are following up with the PG team to see if there is any chance to enhance the performance for this environment. 

We will keep you posted once we have an update from the team.
"
Please refer to my previous comments for the configuration for "Option 1", and what the synchronization interval for it is.

[Anonymous]

Thanks for the updates, @HMSNemanja! Hoping for some quick action from PG team then.

[SpaceAnalytics]

Hello HMSNemanja,

We have just succeeded in connecting F&O tables using your Option 1 description. We are now waiting for the initial synchronisation.

Knowing that our required refresh interval is daily (or at max two times a day), would you agree that option 1 is now a viable solution for our situation? Option 2 requires more overhead and development which I'd like to avoid.

Again, your help is much appreciated.