Lesson 10 – Ingest, transform, analyze and visualize data in a Lakehouse using Microsoft Fabric

This blog serves as your guide in creating a lakehouse, importing sample data into the delta table, implementing necessary transformations, and ultimately generating reports.

Prerequisites

• If you are existing Power BI user, you can launch https://app.powerbi.com/   and start your free trial with Microsoft Fabric.
• For non -Power BI users, sign up in power BI free license. Once you have Power BI login, you can use Microsoft Fabric (preview).

Refer Lesson – 3 Getting started with Microsoft Fabric blog

 Step 1 – Create workspace

Follow the steps to create a workspace in Microsoft Fabric.

• Sign into Power BI
• Select Workspace (1) –> New Workspace (2)

Take a look at the Lesson – 4 Fabric workspace blog post for more detailed information.

Step 2 – Create Lakehouse

Follow the steps to create lakehouse

• In the workspace view, click on “New,” then select “More Options” from the dropdown list.

• Choose “Lakehouse (preview)” under the Data Engineering option.

• A prompt window will appear; type the desired name for the lakehouse you want to create and click “Create”.

Refer Lesson 7 Getting started with Microsoft Fabric Lakehouse for detailed information.

Step 3-Ingest Data

2. Select the “Get Data” dropdown arrow, and you will see the options displayed below. Choose “Text/CSV” to import the dataset.

• The subsequent window will appear, click the “Browse” button to choose the Olympic dataset you downloaded.

• The Olympic dataset has been uploaded. Click Next.

• The dataset preview is visible now, click on “Create” to proceed.

• In the following window, take the following actions:
  • In the “Properties” pane, provide a name for the dataset.
  • In the “Data Destination” section, choose “Lakehouse”.
  • Click “Publish” to load the dataset into the lakehouse you created.

• Go to the workspace you made. Look for the Lakehouse you created and the associated dataflow. Click on the specific Lakehouse that you’ve set up.

• Within the Lakehouse Explorer, you’ll now see the dataset located under the “Tables” folder.

• Select the SQL analytics endpoint from the Lakehouse drop-down menu at the top right corner of the screen. Once you do that, the following pane will appear. Choose “New SQL Query” to begin writing SQL queries.           

• This query retrieves the count of participants for each medal type from the “Olympic dataset” and groups the results by the “medal” column. The alias “Number of Participants” is assigned to the count for clarity in the output. Click Run to run the query.

Step 4 – Visualize the data

• Go to the workspace you established and choose the Lakehouse semantic model for visualizing the data.

In the Semantic Model pane, you will find a list of all available tables. Additionally, you have the flexibility to create reports, whether from scratch, through a paginated report, or by allowing Power BI to automatically generate a report based on your data.

• Following are the ways to Auto create the report.

1. select Auto-create under Create a report – It will automatically generate a Power BI report based on the dataset you have loaded.

2. In the Workspace view, click on More options (…) next to the Lakehouse semantic model, and then select “Auto-create report”. It will automatically generate a Power BI report based on the dataset you have loaded.

• Power BI automatically generates a measure for the row count, aggregates it across different columns, and creates various charts, as depicted in the image. You can save this report for future use by selecting “Save” from the top ribbon.