User Profile
Udo_S
Advocate I
Joined 2 years ago
User Widgets
Contributions
Re: Dynamics to Fabric: Recommended Architecture with “Link to Microsoft Fabric”?
Hi everyone, First off, a big thank you for all the valuable contributions in this forum. After internal testing and brainstorming sessions, we have decided on an architecture approach that I would like to present below. I welcome any comments or feedback. The Context We have only three data sources: Dynamics 365 (Dataverse), an MSSQL database (on-premise), and a file server with CSV files. The source data can be easily separated into fact and dimension data. The data volumes are very manageable, not exceeding 200,000 rows. The planned refresh cycles range from "every 30 minutes" to "once daily." We are a small team, so we must prioritize low complexity, stability, and easy maintainability. However, since we share capacity with other departments, we also need to be mindful of efficient CU consumption. The Approach We plan to use a classic Medallion architecture: Bronze Lakehouse Silver Lakehouse Gold Warehouse (with Semantic Modell using Direct Lake Mode) Configuration Warehouse Since we are pursuing a metadata-driven approach, we use an additional Configuration Warehouse to store configuration data in tables for use in pipelines and notebooks (we discarded the approach of using a Lakehouse with JSON config files). Bronze Layer: We plan to use Pipelines. There will be one pipeline per refresh cycle (e.g., hourly). Inside the pipeline, a metadata lookup controls a ForEach activity that executes the Copy activities. The raw data from Dynamics is stored in Parquet format in the "Files" section of the Bronze Lakehouse. Silver Layer: We plan to use Notebooks, triggered within the pipeline immediately after the respective Copy activity. While this doesn't provide a strict separation between Bronze and Silver ingestion time, it reduces complexity. The schemas for the Silver tables are stored in JSON format in a table within the Configuration Warehouse. We see an advantage in maintaining schema data centrally. Gold layer: We plan to use either Notebooks or Materialized Lake Views (many thanks to Andreas_Laeubli for the tip). Since the data in the Silver layer is already largely separated into Fact and Dim tables, we are leaning towards Materialized Lake Views. The feature seems to be fairly new and sounds very promising (see https://youtu.be/WVVyin5pH4M?si=cpJATKrdlri-oJa6). However, we don't have any hands-on experience with this feature yet, but we will definitely test it. Why not "Link to Fabric" (aka Fabric Link)? First, thanks to svenchio for the excellent summary (Dataverse Meets Fabric (Series)). Setting up "Link to Fabric" via the Power Apps Admin Center (make.powerapps.com > Select Environment > Tables > Analyze > Link to Microsoft Fabric) triggers an initial process for all tables: Tables are exported to Parquet files in Dataverse. A Lakehouse with shortcuts to these files is created in Fabric. An item is created under "Azure Synapse Link" in Power Apps to manage tables after the initial process (see Announcing Major Updates to Dataverse + Fabric Integration). For us, this initial process for all tables took several hours. It is unclear why tables cannot be excluded before the initial link. Once finished, we deleted the auto-generated Lakehouse (as we didn't need shortcuts to everything) and removed unneeded tables from the Fabric Link via the "Manage Tables" workflow. Finally, we created a new Lakehouse and added shortcuts only for the required tables. So far, so good. The problem is the lack of control over the refresh. According to Microsoft, the refresh takes "15 to 60 minutes." In our tests, changes in Dynamics sometimes only appeared in the Lakehouse after several hours. There are similar reports here in the forum. Unfortunately, this makes the Fabric Link unusable for us at the moment. Why no Dataflow Gen2? Dataflows are very powerful, and the low-code/no-code approach makes them easy to create and administer. However, the CU consumption is disproportionately high. Furthermore, we find the deployment of Dataflows to be too complicated. Thanks for your thoughts!Re: Dynamics to Fabric: Recommended Architecture with “Link to Microsoft Fabric”?
Hi deborshi_nag , Thank you very much for your reply. On your points: Point 1: You’re right that the Delta and Parquet files are stored in Dataverse and therefore consume Dataverse storage. However, for our architectural decision this is a lower priority for now. Point 2: We’re familiar with the page you linked, but Microsoft has changed the setup described there in the meantime. More up-to-date documentation can be found here: https://learn.microsoft.com/en-us/power-apps/maker/data-platform/fabric-link-to-data-platform. The initial setup is done via https://make.powerapps.com/ under Analyze > Link to Microsoft Fabric. After that, the initial Delta and Parquet files are created in Dataverse, which can then be accessed via shortcuts from a Lakehouse. In addition, a link named “Microsoft OneLake“ is created under “Azure Synapse Link”. Here you can also exclude tables from the Fabric Link (via Manage tables), so the statement “All tables chosen by default” in the table you shared is not entirely correct. An Azure Synapse Link with ADLS Gen2 is not an option for us, and my understanding is that Fabric Link is intended as the “modern” alternative to that approach. In our tests, the biggest problem is the time it takes for an update in Dataverse to become visible in Fabric. This time seems arbitrary, and some updates do not show up in Fabric even after hours. That currently makes Fabric Link unusable for us, and my hope is that the community has practical experience on how to handle this.Dynamics to Fabric: Recommended Architecture with “Link to Microsoft Fabric”?
Hello, we are currently redesigning our reporting architecture for Microsoft Dynamics and evaluating “Link to Microsoft Fabric” as ingestion mechanism. Our planned target architecture is: A Fabric Lakehouse with Dynamics shortcuts as the raw data layer (no separate bronze layer) A silver Lakehouse for curated and transformed data A gold Warehouse with a semantic model for Power BI (Direct Lake) Our current implementation concept is: Use Dataflow Gen2 to read from the Dynamics shortcuts and apply cleansing and business logic Use Fabric Pipelines (copy activities) to persist the results into the silver Lakehouse and the gold Warehouse This would result in approximately 12–16 Dataflow Gen2 artifacts and 12–16 Pipelines, with a mix of daily, hourly and 30-minute refresh intervals. Before finalizing this design, we would appreciate guidance on the following points: Architecture validation Is this a recommended / supported architecture when using Link to Microsoft Fabric with Dynamics data, or are there known pitfalls with this pattern? Transformation strategy For this scenario, is Dataflow Gen2 the preferred transformation layer, or would Spark notebooks (or a hybrid approach) be more aligned with Fabric best practices in terms of scalability, stability, and CU efficiency? Operational behavior of Dynamics shortcuts Do you have guidance or real-world experience regarding: how and when shortcuts created via Link to Microsoft Fabric are refreshed expected refresh duration and concurrency behavior implications for downstream refreshes (Dataflows, Pipelines, Direct Lake models) At the moment, the internal refresh mechanics of the Dynamics shortcuts are not fully transparent to us, and we want to ensure a stable, low-complexity, and CU-efficient design from the start. Any insights from the community, MVPs, or the product team would be highly appreciated. Thank you in advance.Seamless connection between an n8n workflow and data stores in a Fabric workspace
n8n is the new rising star in node-based workflow automation. Unfortunately, there are no native nodes in n8n for connecting to Fabric, specifically to read data from or write data to a Lakehouse or Warehouse. In my opinion, a seamless connection between an n8n workflow and data stores in a Fabric workspace would be a huge asset for both tools.Re: Best practice for error logging
Hi DiKi-I , As some members have already pointed out, there is probably not a single best practice, but I will describe an approach we are following. I will explain this in detail and with a concrete example, since I believe this is more helpful than a generic description. The example looks as follows: 1. I have a pipeline ("PIP_Run_DF2_TST") that executes a Dataflow Gen2 (could also be a Notebook or a Copy activity) on a schedule. 2. If the execution of the dataflow fails, the error message along with a timestamp should be written into a Lakehouse table. 3. From this table, you can then, for example, create a simple semantic model and visualize it in a Power BI dashboard. The example consists of these components: 1. A dataflow "DF2_Do_Something_TST" that should be monitored for errors. 2. A pipeline "PIP_Run_DF2_TST" that executes the dataflow on a schedule (e.g., hourly). 3. A notebook "NB_ErrorLog_TST", to which the error parameters are passed from the pipeline. 4. A Lakehouse "LH_Error_Events_TST" with a table "errorlog", into which the error parameters should be continuously written. Let’s assume you want these values to be returned in case of an error and stored in the Lakehouse: EventTime: Timestamp (Datetime) of the error DataflowName: Name of the dataflow whose execution failed ErrorDescription: Description of the error First create an empty Lakehouse "LH_Error_Events_TST". The Lakehouse table "errorlog" can, for example, be created via a temporary PySpark notebook used only to create the table (with the Lakehouse connected as data item): %%sql CREATE TABLE IF NOT EXISTS dbo.errorlog( EventTime timestamp, DataflowName STRING, ErrorDescription STRING ) USING DELTA Then create an (initially) empty notebook "NB_ErrorLog_TST", connected to the Lakehouse as a data item. Next, create a pipeline with a Dataflow activity and a Notebook activity. The Notebook activity is connected to the Dataflow activity via the On fail output. The Notebook activity is linked to the (still empty) notebook "NB_ErrorLog_TST". Now create three parameters of type "String" (Datetime is not available here) in the Notebook activity with these values: Name Value Description p_event_ts @utcNow() Timestamp of the error p_dataflow_name DF2_Do_Something_TST Name of the dataflow (hardcoded) p_error @string(activity('Dataflow1').Error.Message) Error message The pipeline should look like this: (A detailed list of parameter-values can be found here: https://learn.microsoft.com/en-us/fabric/data-factory/expression-language) Finally open the notebook "NB_ErrorLog_TST", which receives the error parameters from the pipeline and writes them into the Lakehouse. The notebook concists of three simple code cells: 1. A cell with the import statements. 2. A cell with the parameter definitions. 3. A cell in which the parameter values from the pipeline are written into a dataframe, which is then appended as a new row to the Lakehouse table. Cell 1: # Import statements from datetime import datetime from pyspark.sql import Row from delta.tables import DeltaTable Cell 2: # Parameters with random default-values - parameter-names must be excactly the same as the pipeline-paramter-names # "Toggle parameter cell" must be activated p_events_ts = datetime.now() p_dataflow_name = "Default DataflowName" p_error = "Default Error" Important: "Toggle parameter cell" must be activatet for cell 2. Cell 3: # At "On fail" error-values are passed over from pipeline to notebook and can be stored in a dataframe df = spark.createDataFrame([ Row(EventTime=p_events_ts, DataflowName=p_dataflow_name, ErrorDescription=p_error) ]) df.write.mode("append").saveAsTable("errorlog") # Dataframe with error-values will be appended to table "errorlog" That's it. The only thing left to do is to schedule the pipeline and wait for an error to occur. If an error occurs ("On fail"), the error values are passed from the pipeline to the notebook, and then from the notebook to the lakehouse table. You can reuse the notebook in as many pipelines as you like, and you can extend the parameters to suit your needs. And you can replace the dataflow activity with any activity that has an "On fail" output. And of course, you are not limited to the "On fail" output. Instead, you can also connect the notebook to "On success" or "On completion" and adjust the parameters accordingly. I hope this helps. Best regards, UdoRe: Dataflows Gen2 with Warehouse or Lakehous Destination constantly fail on refresh
Hi v-echaithra , Thanks for reaching out! Unfortunately, the issue wasn’t permanently resolved; we’ve since escalated it to Microsoft. If Microsoft provides any concrete findings, I’ll be happy to share them in the forum. However, no further support from the forum is needed. Best regards, UdoRe: Dataflows Gen2 with Warehouse or Lakehous Destination constantly fail on refresh
Hi martymwas , Glad to hear we are not the only ones facing this issue 😉 We rebuilt the whole setup - dataflows and the lakehouse - a few weeks ago. It ran fine for a while, but then, all of a sudden, the dataflows started failing again. Yesterday I disabled staging for all the queries in our dataflows (Screenshot). Since then, the dataflows have been working. Fingers crossed...Re: Dataflows Gen2 with Warehouse or Lakehous Destination constantly fail on refresh
Hello tayloramy and spaceman127 , Thanks for your reply. First, I should probably mention that we have narrowed the issue down to the "write process" or, more precisely, the authentication against the target data sink (Lakehouse and Warehouse). We are dealing with multiple Dataflow Gen2 instances, using various data sources and data structures. The problem is always the same: • The flows run smoothly for a certain period—sometimes several days, sometimes only briefly — and then suddenly, they fail and continue failing afterwards. • The time of day does not matter. The detailed error message is: There was a problem refreshing the dataflow: 'Data source credentials are missing or invalid. Please update the connection credentials in settings, and try again.'. Error code: 999999. However, this message is misleading, since the credentials (we are using an organizational account) are neither invalid nor missing. And they work for a certain period and then fail without any reason. I believe the problem is unfortunately not trivial. My hope was that someone from the community had encountered the same issue and could provide a solution or workaround. Unfortunately, that does not seem to be the case. We will escalate the issue to Microsoft. Best regards, Udo
