search

Bertverbeek4PS / bc2adls

Exporting data from Dynamics 365 Business Central to Azure data lake storage or MS Fabric lakehouse
MIT License
60 stars 22 forks source link

Adds Environment column to exported data #118

Closed y0m0 closed 2 weeks ago

y0m0 commented 7 months ago

Hi @Bertverbeek4PS, first all of thank you for keeping the project alive and maintaining it!

We are currently using bc2adls for a couple of our customers where we have the requirement to extract data from multiple companies and environments and consolidate it across them all. Therefore we simply added a $Environment column to the exported data. Even if our requirement might not be that common I still consider this a good addition without any drawbacks. I also considered simply editing the CopyBusinessCentral notebook to add the environment name by passing it via a parameter, but I think it's cleaner this way.

A little extra info: I am aware that it is recommended to export data from different environments to different lakes, but we opted to not do it because it will involve creating additional pipelines/notebooks/sjd to handle the consolidation. To solve this instead I modified the CopyBusinessCentral notebook to handle data from multiple environments coming into the same lake. I didn't add it to the PR because it might not be relevant for everyone, but can do so if people might find it useful.

Bertverbeek4PS commented 7 months ago

Thanks @y0m0

I'm wondering why you would have the environment name also in the export. Because every company is unique. So in that way I would say that companyname should be sufficient enought.

y0m0 commented 7 months ago

I see your point. There can't be duplicate companies in a single environment, but as far as I know there is no guarantee that the companies are unique across different environments. I believe nothing is stopping a customer from doing something like this: Environment-A / Company-A Environment-B / Company-A

Bertverbeek4PS commented 7 months ago

I see your point. There can't be duplicate companies in a single environment, but as far as I know there is no guarantee that the companies are unique across different environments. I believe nothing is stopping a customer from doing something like this: Environment-A / Company-A Environment-B / Company-A

@y0m0 indeed you can have the situation accross all environments. But in my eyes that would be very rare. Because it isn't possible to have that also for the taxes etc.

But if that is a use case maybe we can put it on a setting to export also the environment and also in a setting in the notebook. In that case the user can choose what he wants.

I can see that is a point for including the environment fields to it but then for consolidation reason.

y0m0 commented 7 months ago

Sounds like a sensible idea, I can modify my PR accordingly.

martin-baylis commented 4 months ago

Sounds like a sensible idea, I can modify my PR accordingly.

Environment and Company GUID in the export would be extremely useful. These are needed in the situation where you refresh from Prod to Sandbox. When doing this since the extension data for BC2ADLS migrates in an active state, you run the risk of data from the new copy (refresh into Sandbox) pushing data to your configured (Prod) destination (ADLS or OneLake).

bjarkimg commented 3 weeks ago

I see your point, but could we be overcomplicating things here? We have one customer with three environments, resulting in three lakehouses that are then merged into a single warehouse.

I'm thinking specifically about custom fields that vary across environments—these aren't always the same per table and could differ due to localization. Within a single environment, fields are consistent across all companies, so that isn't an issue, but across different environments, we do have these variations.

Bertverbeek4PS commented 2 weeks ago

I see your point, but could we be overcomplicating things here? We have one customer with three environments, resulting in three lakehouses that are then merged into a single warehouse.

I'm thinking specifically about custom fields that vary across environments—these aren't always the same per table and could differ due to localization. Within a single environment, fields are consistent across all companies, so that isn't an issue, but across different environments, we do have these variations.

That is indeed a very good point @bjarkimg! Environments aren't always the same in schema and we cannot check that when exporting it. So I will close this pull request.