Lakehouse tutorial: Building reports in Microsoft Fabric

In this section of the tutorial, you create a Power BI data model and create a report from scratch.

Prerequisites

Before you begin this tutorial, you need to complete these steps:

• Create a lakehouse
• Ingest data into the lakehouse
• Prepare and transform the data using notebooks and Spark runtime

Build a report

Power BI is natively integrated in the whole Fabric experience. This native integration includes a unique mode, called DirectLake, for accessing the data from the lakehouse to provide the most performant query and reporting experience. DirectLake is a groundbreaking new capability that allows you to analyze very large semantic models in Power BI. With DirectLake, you load parquet-formatted files directly from a data lake without needing to query a data warehouse or lakehouse endpoint, and without needing to import or duplicate data into a Power BI semantic model. DirectLake is a fast path to load the data from the data lake straight into the Power BI engine, ready for analysis.

In traditional DirectQuery mode, the Power BI engine directly queries the data from the source for each query execution, and the query performance depends on the data retrieval speed. DirectQuery eliminates the need to copy data, ensuring that any changes in the source are immediately reflected in query results. On the other hand, in the import mode, the performance is better because the data is readily available in memory without having to query the data from the source for each query execution, however the Power BI engine must first copy the data into the memory at data refresh time. Any changes to the underlying data source are picked up during the next data refresh (in scheduled as well as on-demand refresh).

DirectLake mode now eliminates this import requirement by loading the data files directly into memory. Because there's no explicit import process, it's possible to pick up any changes at the source as they occur, thus combining the advantages of DirectQuery and import mode while avoiding their disadvantages. DirectLake mode is the ideal choice for analyzing very large semantic models and semantic models with frequent updates at the source.

1. From your wwilakehouse lakehouse, select SQL analytics endpoint from the Lakehouse drop-down menu at the top right of the screen.

   

2. From the SQL analytics endpoint pane, you should be able to see all the tables you created. If you don't see them yet, select the Refresh icon at the top. Next, select the Model tab at the bottom to open the default Power BI semantic model.

   

3. For this data model, you need to define the relationship between different tables so that you can create reports and visualizations based on data coming across different tables. From the fact_sale table, drag the CityKey field and drop it on the CityKey field in the dimension_city table to create a relationship. The New relationship dialog box appears.

   

4. In the New relationship dialog box:

   • Table 1 is populated with fact_sale and the column of CityKey.

   • Table 2 is populated with dimension_city and the column of CityKey.

   • Cardinality: Many to one (*:1).

   • Cross filter direction: Single.

   • Leave the box next to Make this relationship active selected.

   • Select the box next to Assume referential integrity.

   • Select Save.

     

   Note

   When defining relationships for this report, make sure you have a many to one relationship from the fact_sale table (Table 1) to the dimension_* tables (Table 2) and not vice versa.

5. Next, add these relationships with the same New relationship settings shown in the previous step, but with the following tables and columns:

   • StockItemKey(fact_sale) - StockItemKey(dimension_stock_item)
   • Salespersonkey(fact_sale) - EmployeeKey(dimension_employee)
   • CustomerKey(fact_sale) - CustomerKey(dimension_customer)
   • InvoiceDateKey(fact_sale) - Date(dimension_date)

   After you add these relationships, your data model is ready for reporting as shown in the following image:

   

6. Select New report to start creating reports/dashboards in Power BI. On the Power BI report canvas, you can create reports to meet your business requirements by dragging required columns from the Data pane to the canvas and using one or more of available visualizations.

   

7. Add a title:

   1. In the Ribbon, select Text box.

   2. Type in WW Importers Profit Reporting.

   3. Highlight the text, increase the size to 20, and move it to the upper left of the report page.

8. Add a Card:

   1. On the Data pane, expand fact_sale, and check the box next to Profit. This selection creates a column chart and adds the field to the Y-axis.

   2. With the chart selected, select the Card visual in the visualization pane. This selection converts the visual to a card.

   3. Place the card under the title.

      

9. Add a Bar chart:

   1. On the Data pane, expand fact_sales and check the box next to Profit. This selection creates a column chart and adds the field to the X-axis.

   2. On the Data pane, expand dimension_city and check the box for SalesTerritory. This selection adds the field to the Y-axis.

   3. With the bar chart selected, select the Clustered bar chart visual in the visualization pane. This selection converts the column chart into a bar chart.

      

   4. Resize the Bar chart and move it under the title and Card.

      

10. Click anywhere on the blank canvas (or press the Esc key) to deselect the bar chart.

11. Build a stacked area chart visual:

    1. On the Visualizations pane, select the Stacked area chart visual.

       

    2. Reposition and resize the stacked area chart to the right of the card and bar chart visuals created in the previous steps.

    3. On the Data pane, expand fact_sales and check the box next to Profit. Expand dimension_date and check the box next to FiscalMonthNumber. This selection creates a filled line chart showing profit by fiscal month.

    4. On the Data pane, expand dimension_stock_item and drag BuyingPackage into the Legend field well. This selection adds a line for each of the Buying Packages.

       

12. Click anywhere on the blank canvas (or press the Esc key) to deselect the stacked area chart.

13. Build a column chart:

    1. On the Visualizations pane, select the Stacked column chart visual.

       

    2. On the Data pane, expand fact_sales and check the box next to Profit. This selection adds the field to the Y-axis.

    3. On the Data pane, expand dimension_employee and check the box next to Employee. This selection adds the field to the X-axis.

       

14. Click anywhere on the blank canvas (or press the Esc key) to deselect the chart.

15. From the ribbon, select File > Save.

16. Enter the name of your report as Profit Reporting.

17. Select Save.

Next step

Clean up resources