Mastering Power BI: Build Business Intelligence Applications Powered with DAX Calculations, Insightful Visualizations, Advanced BI Techniques, and Loads of Data Sources

CHAPTER 1

Understanding the Basics

Introduction

In this chapter, we will learn about the basics of Power BI. Power BI is growing in popularity due to the functionality it provides to the business users. This chapter will cover the basics of Power BI as a Business Intelligence application. It will start with the Business Intelligence fundamentals and explain the terms and technologies in the BI paradigm.

We will learn about Power BI and understand how it works. This chapter is important as it will lay the foundation of all the subsequent chapters in this book.

Structure

In this chapter, we will discuss the following topics:

Understanding Business Intelligence (BI)?

Concepts of the Star and Snowflake schema

Power BI and its components

Installation of Power BI Desktop

Power BI Desktop Interface

Overview of the data used in the book

Folder setup for learning

Objectives

Understanding of the Business Intelligence concepts is a key to success in Power BI. After completing this chapter, you will be able to explain what is Business Intelligence and its terminology like the Star schema, and the Snowflake schema. You will also be able to differentiate between the Dimension and Fact tables, which is the key to designing powerful data models and visualizations. You will also learn what is Power BI and how it works.

Understanding Business Intelligence

Before defining the term Business Intelligence (BI), let’s understand the terms data and information. In the world of Information Technology (IT), data can be anything – text, numbers, or images in a digital format. The data is raw, unorganized, or arbitrary, but should be in a format that is understandable to a computer system. Once loaded, the data is transformed, processed, and interpreted by the system to produce meaningful and contextual information.

In the business world, data and information are closely related and thus used interchangeably.

BI relates to the set of technologies and techniques that collect and categorize an organization's data and presents meaningful information in a format that helps in better decision making. The BI applications allow the developers to collect vast amount of data from diverse sources, transform the data according to the business requirements, and present it in a visual format – tables and charts. BI does not make decisions for an enterprise, but eases the analysis of data to arrive at actionable results.

Advantages of a Business Intelligence (BI) system

An enterprise can drive huge benefits by implementing a BI System, which are as follows:

Data management: BI system facilitates the collection of data from diverse sources. This data is stored in an enterprise-wide data warehouse or a data mart. Since the data is centrally stored, it helps in producing a single version of the truth.

Information broadcasting: The information is delivered in a visual format that is understandable to the users. It helps in the quick delivery of information in the form of interactive dashboards, tables, charts, and maps. The users can get to the data faster and collaborate with the information.

Secure delivery: BI System also supports secure information delivery, -that is, the data is contextual and is delivered on a need-to-know basis. The visualizations can be developed that display the different data based on the organization's role or organization structure.

Adhoc analysis: The business users can use the self-service BI applications to perform their own data analysis. Doing so will reduce the dependency on the IT technical team.

Business Intelligence components

Before plunging into a BI application like the Power BI, it is important to learn about some of the following BI components:

Data sets

The core use of a Business Intelligence application is to enhance the understanding of data. The data can come from disparate sources. The data can be sourced from legacy systems, relational database, cloud, or from various file-based applications, such as Excel, CSV, or flat files. The data can be unstructured, such as emails or webpages.

Extract, Transform, and Load (ETL)

ETL is a process of data integration and is used to combine disparate data arriving from multiple sources. In an ETL process, the data is extracted from the source, transformed to aggregate or to implement business rules, and then loaded into the target system. The data loaded in the target system is used for analysis. The ETL process is used to build a data warehouse.

Data warehouse

A data warehouse is a process of managing large amounts of data in an organization. It is designed to assist in the BI tasks, especially in analytics. Given a large amount of historical data, a data warehouse enables faster data query and analysis. A typical data warehouse contains historical data, which is derived from a variety of sources, such as operational or transactional databases.

A data warehouse works as a central repository of the aggregated data from multiple sources and provides an organization with a single version of truth. Since it contains historical records, it empowers the data scientists and data analysts in improved decision making and predictive analysis.

A typical data warehouse contains the following:

A relational database to store and manage the data. This relational database is created in any of the applications, such as Oracle, SQL Server, or db2, etc.

An ETL process to extract data from the multiple sources, transform, and aggregate the data according to the organizational needs and load the data in the data warehouse.

Data analysis and visualization applications, such Power BI to assist in the analysis of the data.

The data warehouse is a core component of a BI implementation.

Data mart

Data mart is similar to the data warehouse but contains only the specific business data within an organization. A data warehouse is a central repository of an enterprise-wide data, while a data mart contains the subset of data pertaining to a specific business or user function. Both the data warehouse and the data marts are used for reporting and analysis. A data mart can be sourced from a data warehouse.

A typical data warehousing environment is shown in the following diagram:

Figure 1.1: Data warehousing environment

The preceding figure 1.1 shows how the data warehouse and the data marts are created, and are explained in words as follows:

An ETL process is the run process to extract the data from the various operational or transactional databases or tables.

This data is stored in the staging tables.

A different ETL process is created to extract and transform this data, which is loaded in the data warehouse database.

From the data warehouse, separate business or user specific data marts are created.

A data warehouse is typically created for the reporting and analytical needs of the organization. It helps in the data analyses by reducing the number of tables and joining the ones that are atypical of an operational database.

Data model

A data model displays how the different data entities are related in a data warehouse environment. It presents a pictorial format, showing different tables and the relationships between them. In BI, a data model represents the organization's data and should be designed for a faster data access. It should contain all the data categories, hierarchies, and filters. The most popular data model used in a data warehouse is the dimensional model, which is also called the Star schema. The two kinds of tables in a star schema are the Dimension and the Facts table.

Dimensions and facts: Dimension and Facts tables are the main ingredient of any Business Intelligence implementation. These tables are used to form the Star or the Snowflake schemas, which are designed as part of building a data warehouse or a data mart.

Dimension table: The dimension tables contain the descriptive or qualitative attribute of the data. For example, the customer dimension may contain information about the customer, such as the name, address, contact number, and so on. The dimension fields usually contain the characters or the textual type of data. The dimension tables are constructed from the operational or transactional relational database. The dimension tables contain the primary key with the respective foreign key in the fact table. A dimension table provides context to a fact table.

There are different types of dimension tables, some of the commonly used ones are as follows:

Slowly Changing dimensions (SCD): It is a dimension table where the row of the data in the table varies with time. It is used to track the current and historical data. SCD is implemented in the following 3 ways:

Type1. In type1 SCD, the existing row of data is simply overwritten. No history is maintained, and the existing data is lost.

For example, consider the following employee record:

Table 1.1: Employee record

If employee John changes his department to HR, no history will be maintained. The record will simply be overwritten:

Table 1.2: Employee changes department

Type2: The type2 SCD keeps the complete history of the data by creating a new record with the Start date and the End date. Only one record will be active at a time. This is the most popular way of storing the historical data.

For example, consider the following employee record:

Table 1.3: Employee history with Active indicator

If employee John moves to a new department, HR, a new record will be added to keep the history:

Table 1.4: Employee history with only one department Active

Type3: In type3 SCD, the history of the data is maintained by using the Current_Value and the New_Value columns. It is cumbersome to maintain the history, as it is limited by the number of columns needed to store the historical data. This technique is not frequently used.

For example, consider the following employee record:

Table 1.5: Employee history using current and previous value columns

If employee John moves to a new department, HR, the history is maintained by putting the new department under the Current_Value column:

Table 1.6: Employee record with changed current value

Conformed dimensions: A dimension table is said to be conformed if it has the same context and content when used with the different fact tables. The two conformed dimension tables will be exactly the same even if used in the different data marts. Such tables, when used in visualizations, provide a single version of the truth to the users. The time dimension is the best example of the conformed dimension, because the definition of the attributes such as year, month, quarter, etc., will be the same across the organization.

Role playing dimensions: A single dimension table can be joined multiple times to a fact table. This can be done by creating multiple copies of the dimension. These copies of the dimension tables can connect to the fact table based on the context. A good example of the role playing dimension will be the time dimension, which will join with the customer order table to get the order date, ship date, and delivery date.

Fact table: The fact table contains the foreign keys of all the dimension tables. It stores the measurable or the quantitative attribute of the data. For example, a fact table may contain products purchased by the customer. The fact fields are the metric fields and generally contain data of the type number. You can aggregate on the fact fields, such as sum (sales).

Apart from storing measures, some of the other common fact tables are as follows:

Fact-less-fact table: This fact table contains only the foreign keys of the dimension tables and does not contain any measure values.

Conformed fact tables: Similar to the conformed dimensions, the conformed fact tables are used across multiple dimension models.

The dimension and fact tables are the basis of a star-schema. Such tables are designed by consolidating multiple tables from an operational database.

Star schema

Star schemas are created in a data warehouse and data mart environments. It consists of the facts and dimension tables. The shape of a star schema is such that the fact table is in the middle, surrounded by multiple dimension tables. The schema assumes the shape of a star and hence the name.

A star schema supports querying huge amount of data stored in a typical data warehouse storage system. The queries run against the star schema are faster due to the reduced number of joins used to query the data.

An example of a star schema is as follows:

Figure 1.2: Star schema

Since the fact table contains the foreign keys of the dimensions tables, during the ETL process, the dimension tables are loaded before the fact tables.

Snowflake schema

A snowflake schema is also used in the data warehousing and data marts. It is an extension of the star schema. In a star schema, each dimension is stored in a single dimension table, whereas in a snowflake schema, a dimension explodes or has a lookup table. This further extension of the dimension tables gives a picture of a snowflake, and hence the name. In the following diagram, the product dimension is connected to another dimension table, Category:

Figure 1.3: Snowflake schema

In the Power BI, the developer connects to a data source and creates a data model. Care should be taken to create a data model close to a star schema or a snowflake schema.

Key Performance Indicator (KPI)

Key Performance Indicators (KPI's) are performance pointers that depict the overall health of an organization. A good