BI Architecture in Microsoft Fabric for Dynamics 365 Business Central

[MorenaBI]

Hello, this is my first post, so please be understanding.

I'm designing a BI architecture in Microsoft Fabric, where the main data source will be Dynamics 365 Business Central. The goal is to create the most flexible and scalable solution that can be easily adapted to different Business Central scenarios.

At this stage, the project is based on knowledge gathered from online resources — I haven’t yet had the opportunity to work directly with Business Central. Let’s say it’s a preparation phase for something that’s coming.

I’ve found that the most reliable, flexible, and best-performing way to bring data from BC to Fabric is using the BC2ADLS tool, so I’ve designed the architecture based on that assumption.

Key architecture points:

1. Workspaces: One workspace per environment (dev/prod), containing all medallion layers. I also considered having separate workspaces for each layer, but I prefer to keep things as simple as possible.
2. BC data injection: BC2ADLS loads data only into the production workspace. For dev, I'm using shortcuts to connect to production data. If I need to change a notebook that transforms raw files into tables, I can manually copy raw data to a dev folder and run tests there — though I don’t expect much development work after the initial implementation.
3. Other sources: Currently unknown, but assumed to be ingested through simple pipelines using copy data activities.
4. Bronze (Lakehouse) → Silver (Lakehouse): There are two methods:
   – For already structured data that doesn’t need additional transformation/cleaning, I use shortcuts.
   – For data that requires transformation, I use pipelines that run notebooks.
5. Gold layer (Warehouse): Built via a pipeline that runs stored procedures in the gold warehouse, transforming the data into facts and dimensions for reporting purposes.
6. CI/CD: Set up with GitHub and Fabric deployment pipelines.
   – The dev workspace is linked to the main repository.
   – New development is done via branched workspaces, then merged to main.
   – Final dev artifacts are deployed to the prod workspace using Fabric deployment pipelines.
7. Of course, a metadata structure will also be implemented to support consistency, automation, and maintainability across pipelines and layers.

I’d love to hear your suggestions on this solution.
- Do you see any potential threats or weaknesses?
- Any better ideas on how to organize the architecture?
- Are shortcuts as fast as direct tables when used in transformations or reporting?

- I also thought about creating a separate Lakehouse dedicated only to Business Central data, to keep it decoupled from other data sources. I’m still unsure whether this added separation brings real value or just unnecessary complexity.

Thanks in advance for your insights!

[v-achippa · Accepted solution]

Hi @MorenaBI,

Thank you for reaching out to Microsoft Fabric Community.

Thank you for the detailed explanation, it is great to see this level of clarity in the planning stage.

• Your approach using BC2ADLS is well justified. It provides better control and flexibility especially when implementing a full medallion architecture.
• Shortcuts between workspaces are effective and within the same tenant and region, generally perform very well due to Fabric's caching and OneLake infrastructure. They are nearly as fast as direct tables for most use cases.
• If Business Central is the core source and likely to be reused across domains, then creating a dedicated Lakehouse for business central data could provide cleaner separation. Otherwise continuing with a single lakehouse for now with folders and metadata works fine too.
• Using one workspace per environment is a clean and manageable approach especially at the beginning. As your solution grows, consider domain based workspaces with shared datasets or shortcuts to maintain governance and clarity.
• Your CI/CD setup with GitHub and Fabric deployment pipelines looks great. Using branch workspaces and then merging to main for production deployment is an ideal workflow, it supports proper DevOps practices.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @MorenaBI,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[OldDogNewTricks]

Have you considered this:  Link all Dynamics 365 data to Microsoft Fabric | Microsoft Learn

We have just started using it to get Dynamics CRM data into our Fabric enviornment.  It mirrors tables directly into Fabric without pipelines.  Fair warning, we have not built anything on it; only established the connection.

Pros:
Data stays in Dynamics, I have HEARD that it should only increase our Dynamics storage costs by about 5%, Reports can be built and deployed using Fabric capacity then embedded back into Dynamics.

Cons:
Automatically replicates all tables where "track changes" is implemented, table changes (e.g. new columns, etc...) in Dynamics may require a manual "relink", "link" is set-up with user credentials that could change and requires admin privileges in both systems (we used a service account)

[MorenaBI]

Thanks! I’ve actually considered that approach as well. In the end, though, I found BC2ADLS to be a better fit for my use case.

[nilendraFabric]

Hi @MorenaBI 

thanks for sharing such a detailed

The single workspace approach, while simpler, may present challenges as your organization scales. Consider implementing domain-based organization strategies for larger deployments, as domains can help manage multiple workspaces while maintaining governance and security boundaries. This becomes particularly relevant if you plan to serve multiple business units or geographical locations.

Your consideration of creating a dedicated lakehouse for Business Central data is ideal. 

performance characteristics  of shortcurs depend on several factors including regional proximity, network latency,

Microsoft Fabric employs various performance optimization strategies, including intelligent caching, to ensure seamless operation even with remote data sources

[MorenaBI]

Thanks for the feedback! For broader report distribution or creating department-specific lakehouses, I was indeed planning to follow the domain-based approach and use shortcuts from the main lakehouse.

As for shortcut performance, I was mainly referring to the scenario within a single tenant—between different Fabric objects within it.