Fabric end-to-end use case: Analytics Engineering part 2 - Reports
Published: 2023-10-06 11:00:05

---
title: Fabric end-to-end use case: Analytics Engineering part 2 - Reports
published: true
date: 2023-09-28 07:00:30 UTC
tags: 
canonical_url: https://dataroots.io/research/contributions/fabric-end-to-end-use-case-analytics-engineering-part-2-reports
---

Welcome to the fifth part of a 5-part series on an end-to-end use case for Microsoft Fabric. This post will focus on the analytics engineering part of the use case.

In this series, we will explore how to use Microsoft Fabric to ingest, transform, and analyze data using a real-world use case. The series focuses on data engineering and analytics engineering. We will be using OneLake, Notebooks, Lakehouse, SQL Endpoints, Data Pipelines, dbt, and Power BI.

## All posts in this series

This post is part of a 5-part series on an end-to-end use case for Microsoft Fabric:

- [Fabric end-to-end use case: overview & architecture](https://dataroots.io/research/contributions/fabric-end-to-end-use-case-overview-architecture?ref=dataroots.ghost.io)
- [Fabric end-to-end use case: Data Engineering part 1 - Spark and Pandas in Notebooks](https://dataroots.io/research/contributions/fabric-end-to-end-use-case-data-engineering-part-1-spark-and-pandas-in-notebooks/?ref=dataroots.ghost.io)
- [Fabric end-to-end use case: Data Engineering part 2 - Pipelines](https://dataroots.io/research/contributions/fabric-end-to-end-use-case-data-engineering-part-2-pipelines/?ref=dataroots.ghost.io)
- [Fabric end-to-end use case: Analytics Engineering part 1 - dbt with the Lakehouse](https://dataroots.io/research/contributions/fabric-end-to-end-use-case-analytics-engineering-part-1-dbt-with-the-lakehouse/?ref=dataroots.ghost.io)
- [Fabric end-to-end use case: Analytics Engineering part 2 - Reports](https://dataroots.io/research/contributions/fabric-end-to-end-use-case-analytics-engineering-part-2-reports/?ref=dataroots.ghost.io)

## Use case introduction: the European energy market

_If you’re following this series, feel free to skip this section as it’s the same introduction every time._ 🙃

Since Russia invaded Ukraine, energy prices across Europe have increased significantly. This has led to a surge of alternative and green energy sources, such as solar and wind. However, these sources are not always available, and the energy grid needs to be able to handle the load.

Therefore, most European energy markets are converging towards a model with **dynamic energy prices**. In a lot of European countries, you can already opt for a dynamic tariff where the price of electricity changes every hour. This brings challenges, but also lots of opportunities. By analyzing the prices, you can optimize your energy consumption and save money. The flexibility and options improve a lot with the installation of a home battery. With some contracts, you could even earn money by selling your energy back to the grid at peak times or when the price is negative.

In this use case, we will be ingesting Epex Spot (European Energy Exchange) day-ahead energy pricing data. Energy companies buy and sell energy on this exchange. The price of energy is announced one day in advance. The price can even become negative when there will be too much energy on the grid (e.g. it’s sunnier and windier than expected and some energy plants cannot easily scale down capacity).

Since it’s quite a lot of content with more than 1 hour of total reading time, I’ve split it up into 5 easily digestible parts.

We want to ingest this data and store it in OneLake. At one point, we could combine this data with weather forecasts to train a machine learning model to predict the energy price.

After ingestion, we will transform the data and model it for dashboarding. In the dashboard, we will have simple advice on how to optimize your energy consumption and save money by smartly using a home battery.

All data is publicly available, so you can follow along in your own Fabric Workspace.

## Reporting

I am by far not the best at creating dashboards and reports. I know how to get something going and visualize what I want to see, but there are lots of people more experienced at this than I am. Nevertheless, to showcase how easy it is to create a Report with the Power BI experience in Fabric, I will create a paginated report to visualize our electricity pricing data. This is an excellent way to conclude our end-to-end use case through Fabric.

I've put this under the umbrella of analytics engineering as typically an analytics engineer will create the visualizations that go with the data marts. Of course, this is not set in stone and you might find different kinds of roles doing this task across different organizations.

## Creating a report

Let's start by creating a new report. We can do this by clicking the _+ New report_ button in the top left corner of the Power BI experience in Fabric. Fabric then asks us to select a dataset to use for the report. Every Lakehouse on Fabric is automatically exposed as a dataset as well, so just look for the name of your Lakehouse from the previous posts and select it. Fabric can even automatically suggest a report based on your data. For now, we will just create a blank report. You can find the button to create a blank report under the chevron 🔽 button in the bottom right corner.

![](https://dataroots.ghost.io/content/images/2023/08/report-pick-dataset.png)
_Selecting a dataset for the report_

## Map visualization

Our report is going to have multiple pages and on the first page, we're going to visualize the pricing data for Europe for the current month.

Our first visualization is going to be a map with circles indicating the average monthly electricity price for that country. This should give us a helicopter view of the pricing data for Europe. Click the _Map_ 🌍 icon in the Visualizations pane to create a map. Then, on the Data pane, expand the `mrt_avg_price_per_month` data mart and drag the `country` column to the Location field that popped up in the Visualizations pane. Drag the `month_name_short` to Legend. You can use the chevron 🔽  on the right to select _Rename for this visual_ and type _month_ to make sure users just see _month_ there instead of the actual column name. Drag the `avg_month_price` to Bubble size. Click on the chevron 🔽 next to the `avg_month_size` you just dragged and make sure the _Average_ aggregation is selected.

![](https://dataroots.ghost.io/content/images/2023/08/report-map-settings.png)
_Configuring the map visualization_

Next, switch the tab on top of the Visualizations pane from the _Build visual_ to the _Format visual_ tab. New tabs appear under a search bar. In the first tab, _Visual_, you can change the style of the map under _Map settings_. I picked _Light_. Under _General_ and _Title_, you can change the title of the visualization to something like _Average price per country per month_.

![](https://dataroots.ghost.io/content/images/2023/08/report-map-format.png)
_Formatting the map visualization_

This should give you a map that looks like this:

![](https://dataroots.ghost.io/content/images/2023/08/report-map.png)
_The map visualization_

I'm happy with that result for now, so let's move on to the next visualization.

## Average daily price per market in a line chart

The map visualization is great at giving us an impression of how electricity pricing in different countries compares to each other, but it doesn't tell us anything about the price fluctuations, how prices evolve over time, or how prices between different markets are related to each other.

In the next visualization, we're going to use a Line chart to visualize the pricing evolution on a daily basis per market. Click the Line chart icon 📈 to put a line chart on the canvas. Then, open the `mrt_avg_price_per_day` data mart under _Data_ and drag the `date` column to the _X-axis_, `avg_day_price` to _Y-axis_, and `market` to _Legend_. Make sure the selected aggregation for the `avg_day_price` is _Sum_.

![](https://dataroots.ghost.io/content/images/2023/08/report-line-chart-settings.png)
_Configuring the line chart visualization_

Then switch the Visualization pane from the _Build visual_ to the _Format visual_ tab. Click on the _General_ tab that just appeared and fill in an appropriate title like _Average daily price by market_.

![](https://dataroots.ghost.io/content/images/2023/08/report-line-chart-format.png)
_Formatting the line chart visualization_

This should give you a line chart that looks like this:

![](https://dataroots.ghost.io/content/images/2023/08/report-line-chart.png)
_The line chart visualization_

Resize and drag the visualizations to fill the page and you end up with a first page on the report that looks like this:

![](https://dataroots.ghost.io/content/images/2023/08/report-page-1.png)
_The first page of the report_

Right away, we notice that most prices follow the same trends, but that the Scandinavian countries have significantly lower prices than the rest of Europe.

## Details for a specific market with a multi-row card

Let's add a second page to the report that shows us the details for a specific market. We can do this by clicking the ➕ sign at the bottom of the canvas. By right-clicking on the pages, you can also rename them. I renamed the first page to _Europe - month_ and the second page to _Belgium - day_.

I would like to get a quick overview of the prices and for that, I chose a Multi-row card. So click on the icon for a Multi-row card to add it to the canvas. Then open the `mrt_simple_advice` view under _Data_ and drag the same `price_cent_kwh` column 4 times to the _Fields_ section on the _Visualizations_ pane. Then, use the chevrons 🔽 to set the aggregations to _Minimum_, _Average_, _Median_, and _Maximum_ respectively and rename the fields to _Lowest price (cent/kWh)_, _Average price (cent/kWh)_, _Median price (cent/kWh)_, and _Highest price (cent/kWh)_.

![](https://dataroots.ghost.io/content/images/2023/08/report-multi-row-card-settings.png)
_Configuring the multi-row card visualization_

## Filters

The problem we have right now is that this visualization shows us the prices for all markets and all days. We only want to see the prices for Belgium (or for your market if applicable). Therefore, expand the _Filters_ pane to the left of the _Visualizations_ pane and drag the `date` and the `market` columns to the _Filters on this page_ section. For the `date`, you can set the _Filter type_ to _Relative date_ and set the 2 dropdowns to _is in this_ and _day_. For the `market`, you can check the _Require single selection_ checkbox and select your market.

![](https://dataroots.ghost.io/content/images/2023/08/report-filters.png)
_Configuring the filters for the second page_

## Displaying advice using a table

For our last visualization, we're using a _Table_ to display the advice itself. Click the Table icon to add a table icon to the canvas and drag the columns `start_time`, `price_cent_kwh`, and `simple_advice` to the _Columns_. Use the chevrons 🔽 to rename the columns to _start time_, _price (cent/kWh)_, and _simple consumption advice_. Also, make sure to set the aggregation for the `price_cent_kwh` column to _Don't summarize_.

![](https://dataroots.ghost.io/content/images/2023/08/report-table-settings.png)
_Configuring the table visualization_

We then end up with our second page looking like this:

![](https://dataroots.ghost.io/content/images/2023/08/report-page-2.png)
_The second page of the report_

Finish your work by clicking _Save report_ from the _File_ menu and open the report in _Reading view_ to see the final result.

## Conclusion

This concludes our end-to-end use case for Microsoft Fabric. We've seen how to ingest and transform data, model it into data marts using dbt, and visualize it using Power BI. I have a special bonus prepared to showcase an interesting use case for the _Data Activator_ in Fabric, but since that is still in private preview, I will wait until it is publicly available before publishing that post.

I know you could do a lot more with this data, the modeling, and the visualizations, but I hope this gives you a good impression of how easy it is to get started with Fabric and how you can use it to build a data platform that is easy to use for both data engineers and data analysts.