Following on in my series, in this blog post I am going to use the dataflow Gen2 in Microsoft Fabric to load the data into a lake house table.
By doing this, it will allow me to store the data in a delta lake table.
In this series I am going to show you all the steps I did to have the successful outcome I had with my client.
Part 1 – Enabling Log Analytics, generating the KQL queries.
Part 2 – Storing the Log Analytics Queries in Azure Blob Storage.
Part 3 – Using Microsoft Fabric Dataflow Gen2 to store the data in the Lakehouse. (This blog post)
Part 4 – Creating a Power BI dataset and report using DirectLake.
Creating the Dataflow Gen2 to load the data into the Lakehouse tables
I will now create the dataflow gen2 to copy the data from my azure blob storage account and load it into the lakehouse tables.
I first changed the type to “Data Factory”.
I then clicked on “Dataflow Gen2 (Preview)”
NOTE: I will have a link to the Power Query Template which you can use if you don’t want to follow all the steps but rather just get the data.
I first created some parameters which will be used in the function.
The function then required an input of StartDate.
The way that I did it and got it to work was to first create a function which I could use to loop through each of the files. I will put the code for my function at the end of this blog post.
Next, I created a new connection to Azure Blob Storage where I connected using my parameters above.
I then filtered it down so that I had got a list of the dates of my files.
I then invoked my Custom function and used the column “DateTime” as the parameter.
I then expanded the columns, and I could then see the data from my JSON files as shown below.
The final piece to configure is to load the data into my lakehouse.
Where it says Data destination on the bottom right-hand side I clicked on the plus sign.
I then selected Lakehouse
In the window I left the default for the connect to data destination set to Lakehouse and clicked on Next.
On the choose destination target window I first made sure to select “New Table”
Next, I gave the name of my table to be “DAX_Queries”
Now in the Lakehouse selection panel I navigated to my lakehouse where I wanted to store this table.
I clicked on Next.
On the screen “Choose destination settings” I first changed the Update method to Append as shown below.
Underneath was the column mapping and it did ask the question that there were some unsupported characters, could it fix it for me.
I clicked on “Fix it”.
I could then see that it had replaced my column names that had spaces with Underscore as shown below.
NOTE: Don’t worry we can fix this later when we create our dataset in the next blog post.
I clicked on Save settings.
One last thing I did was to rename my dataflow before clicking on Publish.
I clicked on my dataflow name.
I then renamed it and pressed TAB for the name to take effect.
I then clicked on Publish.
I could then see my dataflow being created and starting to import the data into my lakehouse table.
Once it has finished loading, I went into my lakehouse and I could now see the table.
NOTE: I did have to refresh the lakehouse for the table to be shown.
I now have successfully loaded the data from my Azure blob storage into my lakehouse.
What I could then do is to schedule my dataflow to run daily by right clicking on the three dots next my dataflow and select Settings
Under refresh I then set it to refresh daily at 10:30AM (which is after my logic app runs)
I then click Apply to apply the refresh settings.
Summary
In this blog post I have demonstrated all the steps that I used to create my Dataflow Gen2 which takes the output data from my logic app and then inserts it into the lakehouse table.
In the next blog post I will show you how I created my dataset and Power BI report off of the lakehouse table.
Here is the link to my Power Query Template if you want to import it: FourMoo – DAX Queries from Azure Storage.pqt
Thanks for reading and I hope you found this useful.
Be the first to comment