Chapter 7. Reporting on the Modern Data Warehouse
There are a lot of tools available these days when it comes to reporting. In this book, we'll use Power BI for several reasons:
- It has a free version. We'll talk about the different versions later in this chapter.
- It can connect to a wide variety of data sources.
- There are many transformations and merges that can be applied to the data sources from it.
- It is easy to use and has a lot of visualizations available.
Power BI is not that new in the reporting world; it has been around for almost 5 years now. It has evolved since then and continues to evolve. Microsoft releases an update to Power BI monthly.
From a modern data warehouse perspective, we need a reporting tool that is flexible enough to accommodate a wide variety of users, from beginners or casual users to power users. As we will see later in this chapter, Power BI can address all of these mentioned users and can also connect to many data sources, be it on the cloud or on-premise.
The next...
In this section, we will discuss various types of BI depending on the use cases.
This is the type of BI or reports that users will work upon themselves. In this scenario, they use Power BI for desktop to connect to the data sources they want to, whether they are in the data warehouse or anywhere else on the cloud, such as Twitter feeds, data lakes, Spark, and so on.
Most of these sources require that users import data in the Power BI data model to their local machine. If the data source contains a large amount of data, this can consume a lot of resources. But in this mode, data can be altered, in the sense that the source data can be merged or transformed. No live connection is needed to connect to the data as it is imported to Power BI's underlying model. This model is meant to allow users to explore their data and modify it to suit their needs better. They have almost complete control over it.
If the data source is an SQL Server relational database...
So far in this chapter, we've talked about data publication. There are several ways to consume Power BI dashboards and reports:
- Web browser: This the most common way to consume the data when we are provided a valid URL for the reports.
- Power BI mobile: This is a client application (app) that is available for Windows 10, iOS, and Android. It has the capability to connect to Power BI on the cloud or on premises to a Power BI report Server. It is optimized for tablet and touchscreen usage.
- Power BI embedded: This allow us to integrate Power BI dashboards and reports in custom applications. They are mainly used by ISVs and developers to distribute their reports from inside their applications.
Having all of these possibilities to consume dashboards and reports gives us more flexibility when we design modern BI solutions.
Creating our Power BI reports
Now that we have explained what Power BI is, it is time to create our first reports with it. The Power BI development life cycle starts with Power BI for desktop. We create our reports with it and then publish them on the cloud or on-premise on our local Power BI Report Servers.
This chapter will focus on two paradigms of BI as explained in the previous sections: personal and team BI.
Reporting with on-premise data sources
Our first Power BI report will use the on-premise World Wide Importers we talked about earlier in this book. We will first connect to the World Wide Importers relational database using Power BI for desktop.
If you haven't created an account yet, create one now; it's free. If you already have a Power BI account, select the link as highlighted in the following screenshot:
Once connected, uncheck Show this screen on startup and click on the Get data.
In the Get Data window, select SQL Server database and click on Connect:
As shown in the following screenshot...
Now that we have created a report on the on-premise data warehouse, we'll add some data from the cloud to it. We'll add the table we created using Databricks. To extract data from there, the cluster must be an interactive one and must also be running. To connect to Spark from Power BI, we need to use a connector that is in preview but works quite well for now.
To connect to SQL Server on-premise, we used the direct query mode. Direct query is very convenient because it does not require importing the data into the model, but it has some limitations. The one that affects us the most is that we cannot use it when the model is connected to more than one database. We're going to connect the model to the Spark cluster, so we'll lose the direct query capability of SQL Server, but our Power BI model will contain data from multiple sources.
To connect to the Spark cluster, we'll use the JDBC connection available with the premium version of our Databricks cluster. As shown in...
Power BI is a great tool to report on data warehouse and cloud data. This chapter showed how we can link data that is not necessarily part of a traditional data warehouse. As a recap from Chapter 1, The Modern Data Warehouse, a modern data warehouse is based on multiple data sources, both on-premise and cloud-based. ADF allows us to load all data necessary for a modern data warehouse, that is, from multiple data sources.
