The core of Microsoft’s self-service business intelligence (BI) toolset is the Power BI data engine (also known as Power Pivot). It is integrated into both Power BI Desktop and Excel (2010 and later) and forms the foundation on top of which you will build your analytical reports and dashboards. This chapter provides you with some background information on why Power BI is such an important tool and what makes it perform so well. The chapter also provides you with an overview of the Power BI Desktop interface and gives you some experience using the different areas of the interface.
Why you should use Power Pivot
The xVelocity in-memory analytics engine
Exploring the Power BI Desktop interface
Analyzing data in a Power BI report
Why Use Power BI?
You may have been involved in a traditional BI project consisting of a centralized data warehouse where the various data stores of the organization are loaded, scrubbed, and then moved to an online analytical processing (OLAP) database for reporting and analysis. Some goals of this approach are to create a data repository for historical data, create one version of the truth, reduce silos of data, clean the company data and make sure it conforms to standards, and provide insight into data trends through dashboards. Although these are admirable goals and are great reasons to provide a centralized data warehouse, there are some downsides to this approach. The most notable is the complexity of building the system and implementing change. Ask anyone who has tried to get new fields or measures added to an enterprise-wide data warehouse. Typically, this is a long, drawn-out process requiring IT involvement along with data steward committee reviews, development, and testing cycles.
What is needed is a solution that allows for agile data analysis without so much reliance on IT and formalized processes. To solve these problems, many business analysts have used Excel to create pivot tables and perform ad hoc analysis on sets of data gleaned from various data sources. Some problems with using isolated Excel workbooks for analysis are conflicting versions of the truth, silos of data, and data security.
So how can you solve this dilemma of the centralized data warehouse being too rigid while the Excel solution is too loose? This is where Microsoft’s self-service BI toolset comes in. These tools do not replace your centralized data warehouse solution but rather augment it to promote agile data analysis. Using Power BI, you can pull data from the data warehouse, extend it with other sources of data such as text files or web data feeds, build custom measures, and analyze the data using powerful visuals to gain insight into the data. You can create quick proofs of concepts that can be easily promoted to become part of the enterprise-wide solution. Power BI also promotes one-off data analysis projects without the overhead of a drawn-out development cycle. When combined with the Power BI Service portal, reports and dashboards can be shared, secured, and managed. This goes a long way to satisfying IT’s need for governance without impeding the business user’s need for agility.
Power BI Desktop is a free tool for creating reports.
Easily integrates data from a variety of sources.
Handles large amounts of data, upward of tens to hundreds of millions of rows.
Includes a powerful Data Analysis Expressions (DAX) language.
Has data in the model that is read-only, which increases security and integrity.
Enables sharing and collaboration
Scheduling and automation of data refresh
Can audit changes through version management
Can secure users for read-only and updateable access
Now that you know some of the benefits of Power BI, let’s see what makes it tick.
The xVelocity In-Memory Analytics Engine
The special sauce behind Power BI is the xVelocity in-memory analytics engine (yes, that is really the name). xVelocity allows Power BI to provide fast performance on large amounts of data. One of the keys to this is it uses a columnar database to store the data. Traditional row-based data storage stores all the data in the row together and is efficient at retrieving and updating data based on the row key, for example, updating or retrieving an order based on an order ID. This is great for the order-entry system but not so great when you want to perform analysis on historical orders (say you want to look at trends for the past year to determine how products are selling, for example). Row-based storage also takes up more space by repeating values for each row; if you have a large number of customers, common names like John or Smith are repeated many times. A columnar database stores only the distinct values for each column and then stores the row as a set of pointers back to the column values. This built-in indexing saves a lot of space and allows for significant optimization when coupled with data-compression techniques that are built into the xVelocity engine. It also means that data aggregations (like those used in typical data analysis) of the column values are extremely fast.
Another benefit provided by the xVelocity engine is the in-memory analytics. Most processing bottlenecks associated with querying data occur when data is read from or written to a disk. With in-memory analytics, the data is loaded into the RAM memory of the computer and then queried. This results in much faster processing times and limits the need to store preaggregated values on disk. This advantage is especially apparent when you move from 32-bit to 64-bit operating systems and applications, which are the norm these days.
Another benefit worth mentioning is the tabular structure of the Power BI data model. The model consists of tables and table relationships. This tabular model is familiar to most business analysts and database developers. Traditional OLAP databases such as SQL Server Analysis Server (SSAS) present the data model as a three-dimensional cube structure that is difficult to work with and requires a complex query language called Multidimensional Expressions (MDX). I find that in most cases (but not all), it is easier to work with tabular models and DAX than OLAP cubes and MDX.
Setting Up the Power BI Environment
Downloading Power BI Desktop
Setting options for Power BI Desktop
One thing to be aware of is that Microsoft has been releasing monthly updates that include new features. Make sure you get notified and install these updates when they are released. You can check which version you have installed by selecting the Diagnostics tab in the Options window. If you are like me and want to play with upcoming features that are still in development, you can turn them on in the Preview Features tab in the Options window.
Now that you have installed and set up the Power BI Desktop development environment, you are ready to explore the interface.
Exploring the Power BI Desktop Interface
The Power BI startup screen
You can use this screen to launch recent reports, start a new report using recent sources, or start a new report with a new data source. In the middle of the screen, you can sign into the Power BI Service to collaborate with others or sign up for a 60-day free trial of Power BI Pro. On the left side of the screen, you can link to the Power BI blog and the Power BI forums.
There is a limited Power BI Free version; however this book is based on the Power BI Pro version.
Creating a report in Power BI Desktop
Switching views in the Power BI Desktop
Viewing data in the Data view tab
The Model view tab
The Home menu in the Report view
The Visualizations and Fields windows
Now that you are familiar with the various parts of the Power BI Desktop report designer, it’s time to get your hands dirty and complete the following hands-on lab. This lab will help you become familiar with working in Power BI Desktop.
To complete the labs in this book, make sure you download the starter files from https://github.com/Apress/beginning-power-bi-3ed.
In this following lab, you will
Install Power BI Desktop
View the various tabs of Power BI Desktop
Explore the data using a matrix
- 1.
Go to https://powerbi.microsoft.com/en-us/desktop/ and download and install Power BI Desktop.
- 2.
Launch Power BI Desktop and dismiss the startup screen.
- 3.On the File menu, select Options and Settings and then Options. You should see the Options window (see Figure 1-10).
Figure 1-10Viewing the Options settings
- 4.
On the Global Data Load tab, make sure the Auto date/time for new files option is unchecked.
- 5.
View some of the other setting options available.
- 6.
Open the Chapter1Lab1.pbix file located in the LabStarterFiles folder.
- 7.You should see a basic matrix showing sales by year and country, as shown in Figure 1-11.
Figure 1-11Using a matrix table
- 8.Click anywhere in the matrix. You should see the visual properties and the field list on the right side, as shown in Figure 1-12.
Figure 1-12The visual properties and field list
- 9.
In the visual properties, there are drop areas for the rows, columns, and values referred to as wells. You drag and drop the fields into these wells to create the matrix.
- 10.
In the Fields list, expand the DimProductCategory table. Find the EnglishProductCategoryName field and drag it to the rows well under the CalendarYear field.
- 11.In the Visualizations window, select the paint roller icon and expand the Row Headers tab. Turn on the +/- icons for expanding the matrix rows (see Figure 1-13).
Figure 1-13Turning on the +/- icons
- 12.Notice that you can now expand and collapse the matrix (see Figure 1-14). Investigate some of the other format settings available for the matrix.
Figure 1-14Expanding and collapsing rows in a matrix
- 13.Click on an empty area on the page so that the matrix is not selected. Select the Slicer visual as highlighted in Figure 1-15 and add the EnglishProdutCategoryName.
Figure 1-15Adding a slicer to the report
- 14.
Rearrange the slicer and the matrix on the page and notice selecting categories in the slicer filters the matrix.
- 15.On the left side of the designer, switch to the Data view (see Figure 1-16).
Figure 1-16Switching to the Data view
- 16.
Explore the data in the different tables using the Fields list on the right side of the designer.
- 17.
Go to the ProductAlternateKey column in the DimProduct table. Notice that it’s grayed out. This means it’s hidden in the Report view. You can verify this by switching back to the Report view and verifying that you cannot see the field in the field list.
- 18.
In the FactInternetSales table, click the Margin column. Notice this is a calculated column using the SalesAmount and the ProductStandardCost. It has also been formatted as currency.
- 19.
There is a measure called Total Sales Amount in the FactInternetSales table. Click the measure and note that in the formula bar above the table is the DAX code used to calculate the measure.
- 20.
On the right side of the designer, switch to the Model view. Observe the relationships between the tables which are indicated by the lines connecting the tables.
- 21.If you hover over the relationship with the mouse pointer, you can see the fields involved in the relationship, as shown in Figure 1-17.
Figure 1-17Exploring relationships
- 22.
Take some time to explore the Model, Data, and Report views. (Feel free to try to break things!) When you’re done, save the file and close Power BI Desktop.
Summary
This chapter introduced you to Power BI Desktop. You got a little background into why Power BI can handle large amounts of data using the xVelocity engine and columnar data storage. You also got to investigate and gain some experience using the Power BI Desktop designer. Don’t worry about the details of how you develop the various parts of the model and reports just yet. That will be explained in detail as you progress through the book. In the next chapter, you will learn how to get data into the model from various kinds of data sources.