Data Lake in a Day - Lab 5 creating the Power BI report

In this lab I show you how to create a Power BI report with the data both on the lake and in SQL DW (Azure Synapse Analytics). You can find the instructions for this lab at github.com/davedoesdemos/Data... to follow along yourself.
In part one of the Data Lake in a Day series I showed you how to set up the infrastructure for the whole workshop with a simple one click deployment using an ARM template. If you've not already done so, go and run through that video at • Data Lake in a Day 1 -... .
In part two of the series I took you through Lab 1, this lab showed you how easy it is to ingest data from a database into the data lake. See that video at • Data Lake in a Day 2 -...
Part three introduced you to what a data lake is and what it's for, and can be found at • Data Lake in a day - S...
Part four was the second session covering how a data lake is organised and is at • Data Lake in a Day - S...
In part five I covered lab 2 where we downloaded weather data for various cities and can be found at • Data Lake in a Day - L...
In part 6 I showed you how to use Data Factory with Mapping Data Flows to process and curate the data to produce a data model. • Data Lake in a Day - L... and then in the last episode I showed you how to load that data into Azure Synapse Analytics (SQL Data Warehouse) • Data Lake in a Day - L... .
0:00 - Introduction to the lab
1:27 - Swap out the Weather Data for a bigger data set
2:10 - Create the report and import data
4:42 - Connect Power BI to Azure Synapse Analytics for live query
5:50 - Create a date table with Calendarauto()
6:16 - Configure data relationships
8:20 - set up aggregations for the composite model
9:50 - Create the report
11:10 - Enrich sales data with weather information
11:39 - wrap up
You can find the whole one day workshop at github.com/davedoesdemos/Data... including all lab materials, data and instructions.
If you're new to data lakes please ask any questions you have below. Also please comment if you found this workshop series useful or if you'd like to see more of this kind of content.
For all of my other demos, go to davedoesdemos.com or go straight to the GitHub page at github.com/davedoesdemos/Demo.... Also please subscribe to the channel to make sure the latest demos show up in your playlist!
Many thanks to all who have watched this series. It's been an epic project over two years to create this content to help people understand data platforms so I really appreciate all of the support!

[@samuelrocha9079]

Very good explanation, Dave! It is helping me a lot. Thank you!

[@samuelrocha9079]

One quick question, you are using Direct Query instead of Import for Synapse, why? It is because Synapse is very powerful processing data? Thank you again

[@DaveDoesDemos]

This was purely because it was a demo/training day, no real reason other than showing how :)

[@DaveDoesDemos]

Be the first to comment and win some kudos! What would you like to see next on this channel?

[@pranavkumar9030]

Hi Dave nice explanation with real-time tips to enhance the performance, would like to know in detail about CDC using Azure Data Factory

[@DaveDoesDemos]

@@pranavkumar9030 what do you mean by CDC here?

[@pranavkumar9030]

@@DaveDoesDemos Change Data Capture I tried in ADF, But there is no option in ADF except we can do with MSSql server with some tweaks, but for other sources like oracle, MySQL, or Postgres we can't, have to use third-party tools like Streamsets or Qlik Replicate. I am wondering if that you can point out any sources so that we can accomplish CDC without any third party tolls in Azure Data Factory. Thanks in advance.

[@DaveDoesDemos]

@@pranavkumar9030 unfortunately this is different for every platform. Even SQL has some constraints around it. Building a full solution will depend on what you're building it around. If there is a service bus architecture you might be better capturing there rather than the database. In an ideal world the database should be designed with time stamps on all rows such that you can just query for all rows after a given time (or between them, using tumbling windows). Alternatively, some platforms allow you to read from the log files to get this information. Occasionally you might be completely stuck for a way to do this and need to ingest a whole table every day and replace the data set in the lake entirely - obviously this only works on smaller tables but the majority of tables which don't include a date will tend to be smaller. The larger tables are usually some kind of ledger and will often include a time anyway. If you're really stuck on this look for a local MS partner who should be able to help out, or reach out to your local MS sub if you're at a bigger customer as you may have CSAs who can help.

[@pranavkumar9030]

@@DaveDoesDemos Thank you Dave for your complete reply 👍