Concept: Introduction to Data Modelling
Power Pivot for Excel utilizes the same data modeling engine as Power BI, bridging the gap between IT professionals and business users Traditionally, data modeling has been an area dominated by BI experts, but with the advent of Power Pivot and Power BI, this essential skill is now accessible to a wider audience.
Data modeling involves the systematic collection and organization of data from multiple sources, allowing for logical relationships to be established among the data This process enhances and prepares the data for efficient use, enabling users to access different subsets without the need for custom queries each time.
The data modelling process includes:
• Determining the optimal structure and shape of the source data to analyse, including whether to bring in all the data, full data, or summary data.
• Loading the data from the source into the data model (Power Pivot for Excel in this case).
In Power Pivot, establishing logical relationships between different tables functions similarly to using VLOOKUP() in Excel, but with the advantage that the data remains in the source table.
• Defining data types (e.g., specifying whether a column of data is numeric or a column of currency values or a column of text fields).
By generating new insights from source data, you can analyze concepts that are not inherently present in the original dataset For instance, when working with a transactional data table that includes cost and selling prices, you can enhance the data model by incorporating calculations for margin and margin percentage Although these metrics may not be explicitly included in the source data, once they are modeled, they can be consistently reused by users of your workbook, thereby enriching the analytical capabilities of the data.
• Giving meaningful names to your new business insights (i.e., to your measures).
Learning the DAX language and integrating data tables in Power Pivot for Excel is essentially mastering data modeling, a concept that may seem daunting but is manageable By the end of this book, you will be on your way to becoming a skilled data modeler with Power Pivot It's important to remember that while you can learn to perform data modeling, achieving expertise requires years of practice, experience, and a commitment to lifelong learning.
Pivot Tables vs Power Pivot
Many individuals are curious about the distinctions between Power Pivot and traditional pivot tables In this article, I will clarify these differences to help you understand them easily and effectively.
A pivot table is an essential tool for summarizing and visualizing data, allowing users to connect to a data source and generate dynamic totals and subtotals As data sets grow in size and complexity, the utility of pivot tables increases, making it easier to interpret and analyze information Integrated directly within Excel, pivot tables also leverage the full range of Excel's features, enhancing their effectiveness in data management.
Data Sources for Pivot Tables
Historically, there have been two main types of data sources that you can connect to with a pivot table: flat tables and data cubes.
Connecting to a Single Flat Table
To connect to a single flat table inside Excel, click in the table, select Insert, PivotTable, and off you go There are some limitations with this approach, however:
• It is very common to have to do a lot of VLOOKUP()s (or similar operations) to be able to join data from different data sources into a single flat table.
Excel has a limit of 1 million rows; however, when using numerous VLOOKUP() functions in a single flat table, you may encounter performance issues long before reaching this limit.
These two issues have historically prevented Excel from being a scalable BI tool But Power Pivot changes that, as you’ll see in a few moments.
Pivot tables can be effectively utilized to connect directly to SQL Server Analysis Services multidimensional cubes from Excel, a powerful feature often overlooked Many large enterprises offer these multidimensional data cubes for reporting, enabling users to easily access and analyze data through pivot tables However, this direct connection to reporting cubes remains a less common application compared to the typical single-table usage of pivot tables in Excel.
Power Pivot revolutionizes the way pivot tables connect to data by introducing a superior method for data sourcing While traditional Excel has constraints in handling large datasets for reporting, Power Pivot eliminates these limitations, allowing for virtually unlimited database sizes This advancement enhances the capabilities of pivot tables, making data analysis more efficient and effective.
Power Pivot is a data modelling tool that allows you to prepare your data in a way that pivot tables can use
Data modeling involves organizing and structuring data to facilitate its use in reporting tools, like pivot tables, eliminating the necessity for writing new database queries repeatedly.
Power Pivot is an add-in for Microsoft Excel that integrates a tabular database from Microsoft SQL Server Analysis Services It enables users to manage Power Pivot databases seamlessly within Excel, using an intuitive user interface to create and manipulate these databases directly.
Import data from many different data sources.
Logically join separate tables of data together so the data works together without the need for VLOOKUP() formulas.
Improve your raw data to develop new derived metrics For instance, by utilizing source data that includes sell price and cost price, you can generate valuable measures such as Margin $ and Margin %, which can then be integrated into your reports for enhanced insights.
Assign appropriate business names to the measures.
Apply to the data business formatting that will be applied throughout the data model.
Once a Power Pivot data model has been built and configured, the end user can use the data repeatedly to quickly build multiple pivot table reports inside Excel.
Concept: Loading Data
To begin writing DAX and utilizing Power Pivot, it's essential to first load your data Power Pivot imports a full copy of the source data into the data model, marking the initial step in the process After the data is loaded, you can share your workbooks without requiring others to have direct access to the original source data.
When loading data, it's crucial to determine which tables and columns to import, as well as the desired structure of the data In the upcoming section, you will load pre-prepared data, but it's important to recognize that selecting the appropriate data is a vital aspect of the data modeling process For this book, these decisions have already been made for you.
To get started with the sample AdventureWorks database featured in this book, visit http://xbi.com.au/learndax to download it Once downloaded, unzip the file and store it in a convenient location for easy access.
Here’s How: Enabling Power Pivot in Excel
The Power Pivot menu may or may not be visible in your version of Excel Follow these steps to see if it is visible and, if not, enable it:
1 Open a new Excel workbook Look for the Power Pivot tab in the ribbon, shown in the figure below
If you see it, you don’t need to follow the rest of these steps.
2 If you don’t see the Power Pivot tab, select File, Options, Add-Ins.
3 Scroll to the bottom of the window and select COM Add-ins from the Manage list Then click Go.
4 In the COM Add-ins dialog that appears, check the Microsoft Power Pivot for Excel check box and then click OK.
If you're using Excel 2013 or later and cannot locate the Power Pivot add-in, it likely means that your current version of Excel does not support this feature To access Power Pivot, you will need to upgrade to a different version of Excel For detailed information on which versions include Power Pivot, please visit http://xbi.com.au/versions.
5 On the PowerPivot tab, click Manage.
The Windows taskbar displays two distinct windows when you hover over the Excel icon: the traditional Excel window and the Power Pivot window.
Power Pivot is an integrated application within Excel that enhances data analysis capabilities Throughout this guide, you'll be instructed to toggle between Excel and Power Pivot If the Power Pivot window is not visible, you can easily access it by navigating to the Power Pivot tab in Excel and selecting "Manage."
Direct Load to Power Pivot or Power Query?
Power Pivot for Excel has always allowed you to load data directly into the Power Pivot data model from within the Power Pivot window, as shown below.
Since Excel 2016, Microsoft has embedded Power Query directly in Excel, under the Data, Get & Transform Data menu.
Currently, multiple software builds of Excel 2016 exist, which may cause your version to appear different from the image provided If the Data tab in your Excel looks dissimilar, take a moment to examine it closely to identify the location of the Power Query features.
This change of name to Get & Transform Data is unfortunate, in my view, and I prefer to use the name Power Query.
Note: I use the term Power Query in this book, but remember that you launch Power Query from the Get & Transform Data menu.
With the introduction of Power Query, users can choose to load data into Power Pivot using either the legacy method or the more modern Power Query from the Get & Transform Data menu There are several advantages to opting for the newer Power Query approach over the traditional legacy method.
When using the legacy Power Pivot method to load data, changing the data source is not feasible For instance, if data is initially imported from a spreadsheet and later needs to be sourced from SQL Server, the only option is to delete the existing table and re-import the data This process, while seemingly straightforward, results in the loss of all relationships and measures associated with the table, posing significant challenges for users.
• Power Query allows you to easily manipulate data during load in ways that are not possible using the legacy Power Pivot approach.
Currently, loading data with Power Query is slower than using the traditional Power Pivot method, often taking an additional 15 to 20 seconds While this delay is not significant enough to be unmanageable, it is noteworthy for users seeking efficiency.
The upcoming "Here's How" section outlines the process of loading data with the legacy Power Pivot method I encourage you to read it and practice on your own machine for better understanding However, for the remainder of this book and when creating your own data models, I highly recommend utilizing the Power Query data loading method, which will be detailed in a subsequent "Here's How" section.
Here’s How: Data Load Using Power Pivot
This section describes how to load the following tables from the AdventureWorks Access database using the legacy Power Pivot data load approach:
Follow these steps to load data into a workbook for use in Power Pivot:
1 In the Power Pivot window, select Home, From Database (see #1 below), From Access (#2).
2 Browse to the location of the sample database you downloaded and unzipped earlier in this chapter and then click Next.
3 Accept the default option in the Table Import Wizard dialog (as shown below) and then click Next.
To select the five views located at the bottom of the list, simply place a check mark in the box next to each view Be sure to observe the distinct icons that differentiate queries/views from tables.
5 Click Finish, and the wizard imports your data.
Once you close the Table Import Wizard in Power Pivot, you'll find five new tabs representing the tables you imported from the source files, such as an Access database These tabs contain complete copies of the data, eliminating the need for the original source files until you're ready to refresh the data This feature of Power Pivot allows for easy updates; when the data changes, you can simply refresh it, ensuring your workbooks are always up-to-date.
Here’s How: Data Load Using Power Query
This section describes how to load the following tables from the AdventureWorks Access database using the Power Query data load approach:
Then you will prepare these tables for use in Power Pivot.
Follow these steps to load data into a workbook for use in Power Pivot using Power Query:
1 If necessary, open another new blank Excel workbook.
2 Click Data (see #1 below), Get Data (#2), From Database (#3), From Microsoft Access Database (#4).
3 Browse to the location of the sample database you downloaded and unzipped earlier in this chapter and then click Import.
To begin, check the "Select Multiple Items" box, and then choose the top five views from the list by marking the checkbox next to each option.
The data loads directly into the Power Pivot data model.
Different icons are used to distinguish between queries/views and tables in the system The icons located at the top of the list, such as those next to "Calendar" and "Customers," represent views or queries, while the icons at the bottom, next to "Budget" and "BudgetPeriod," signify tables.
Concept: Measures
Measures have been a fundamental feature in Microsoft BI tools, particularly in SQL Server Analysis Services, for many years Essentially, a measure is a DAX formula that directs Power Pivot to perform calculations on data, similar to how a formula functions in an Excel cell However, unlike Excel formulas that operate on specific cells, measures analyze the entire data model, allowing for comprehensive calculations across the dataset In summary, think of a measure as a powerful formula designed to derive results from the data you have loaded.
The term "measure" has evolved in Power Pivot for Excel, initially introduced in Excel 2010 In the 2013 release, Microsoft rebranded it as "calculated field." Fortunately, in later versions, the terminology returned to "measure," reflecting its original meaning in data analysis.
2016, Microsoft reversed its decision and used the term measure again, and it seems to be sticking with that.
Techniques for Writing DAX Measures
There are three places you can write DAX measures:
In the Power Pivot window, you can create a measure directly in the formula bar by specifying the measure name, followed by a colon and the formula It is important to ensure that there are no spaces between the measure name, the colon, and the equals sign.
In the Power Pivot window, you can create and modify measures in any blank cell located in the calculation area at the bottom, ensuring to use a colon in your entries.
In Excel, you can create measures by accessing the Measure dialog through the Power Pivot tab; simply click on the Measures button and select New Measure to begin.
Excel users are advised to write DAX formulas in the Measure dialog box, ideally after creating a pivot table for context This approach allows users to see the measure's results immediately upon clicking OK, providing instant feedback on the accuracy of the formula and ensuring it meets their expectations.
For Excel users, mastering the creation of new measures may initially feel unfamiliar, but with practice, it becomes easier I recommend following a structured approach to writing these measures to enhance your proficiency.
1 Create a new blank pivot table connected to your data model (or use an existing one if you already have something appropriate).
To enhance your pivot table, incorporate relevant data by adding the [Category] field from the Products table into the Rows section This will provide a clearer organization of your data and facilitate better analysis.
3 Click inside the pivot table, navigate to the Power Pivot tab, click the Measures button (#2), and then select New Measure (#3) The Measure dialog appears.
To ensure efficiency and avoid costly rework, it's crucial to follow the outlined process steps and utilize the Measure dialog as a guide Neglecting any step may lead to missed tasks, resulting in wasted time and additional effort By consistently adhering to this structured approach, you can streamline your workflow and enhance productivity.
To store your measure effectively, choose the appropriate table from the Table Name drop-down menu It is advisable to place the measure in the table corresponding to the source of your data In this instance, since the data originates from the Sales[ExtendedAmount] column located in the Sales table, you should select "Sales" from the drop-down options.
5 In the Measure Name text box (#2), give the new measure a meaningful, unique name, such as To- tal Sales.
When creating DAX formulas, it's essential to use the specific names provided in the examples of this book In your future work with your own data, opt for descriptive and meaningful names that include spaces, rather than abbreviations For instance, using "Total Sales" instead of "TS" will help prevent confusion for you and others later on.
6 In the Formula box (#3) write the DAX formula.
7 Click Check Formula (#4) to check whether the formula you wrote is syntactically correct Fix any errors, if needed.
8 Select an appropriate formatting option from the Category list (#5) and make suitable selections for Symbol and Decimal Places in the area to the right of the Category list.
9 Click OK to save the new measure (#6).
The Description box is typically left empty, but feel free to utilize it if you wish It serves as a reference and does not influence the functionality of the formulas.
10 After you click OK, you get immediate feedback about whether everything is working as you expect, as shown below.
Adhering to this step-by-step approach will save you time by preventing the need for corrections later on By practicing this method from the beginning, you'll cultivate positive habits that will benefit you in the long run.
To create a measure in Power Pivot, simply drag a column from a table into the Values section of the PivotTable Fields list, which generates an implicit measure While this method is similar to traditional pivot tables, it is advisable to avoid using implicit measures when working with Power Pivot for better performance and flexibility.
There are several reasons to avoid creating implicit measures:
Excel automatically generates implicit measure names, such as [Sum of ExtendedAmount], which can be unclear and unhelpful In contrast, using explicit names like [Total Sales] enhances clarity and understanding, making it easier to interpret data.
• No formatting is applied when you drag to create an implicit measure.
DAX Topic: SUM(), COUNT(), COUNTROWS(), MIN(), MAX(), COUNTBLANK(), and DIVIDE()
This chapter introduces fundamental DAX formulas to help you begin your journey with DAX Most functions discussed here utilize a column as their sole parameter, exemplified by the formula =FORMULA(ColumnName) Notably, the function =COUNTROWS(Table) requires a table as its parameter, while DIVIDE() is explained later in the chapter.
In this chapter, all functions except DIVIDE() are aggregation functions that process inputs from a column or table to summarize the data in various ways For instance, the Sales[ExtendedAmount] column contains over 60,000 rows, making it impractical to input the entire column into a pivot table, as the table cannot accommodate such a large volume of data in a single cell.
The example below illustrates a DAX formula that employs a "naked" column without any aggregation function, which is incompatible with standard measures, as demonstrated by the accompanying error message.
To ensure Power Pivot aggregates data correctly, you need to specify how to summarize the values from a particular column, resulting in a single value for each cell in the pivot table The aggregation functions discussed in this chapter transform a column of values into one consolidated value.
The correct way to write this measure is shown below.
Did you notice that this example uses the table name and the column name in the formula? Remember that this is best practice.
When writing DAX, it is essential to always specify the table name alongside the column name This practice ensures clarity and precision in your formulas, preventing potential confusion and errors in your calculations Understanding the importance of this approach will become clear as you delve deeper into DAX usage.
In DAX, a key feature is the ability to reuse measures when creating new ones For instance, once you establish a measure called [Total Sales] in the Power Pivot data model, it can be referenced in other measures This allows you to easily calculate additional metrics, such as a new measure for 10% tax on sales, by utilizing the existing [Total Sales] measure in your formula.
Note that the new measure [Total Tax] is a calculation based on the original measure [Total Sales] multiplied by 0.1.
It is good practice to reuse measures inside other measures.
Note: I did not add the table name in front of the measure name above That is, I wrote [Total
When referencing data in your reports, it's essential to differentiate between columns and measures for clarity Always prefix column names with the table name, such as Sales[ExtendedAmount], but omit the table name when referring to measures, like [Total Sales] This practice allows readers to quickly identify that Sales[ExtendedAmount] is a column and [Total Sales] is a measure, enhancing the overall readability of your data presentation.
Now is the perfect opportunity to begin crafting your own DAX expressions for hands-on practice To get started, sit at your computer, open the workbook containing the data from Chapter 1, and immerse yourself in writing DAX code.
Supercharge Excel if you have never written formulas using these functions, you should physically do it now, as you read this section Imagining yourself doing it is not enough.
To begin, ensure that you have loaded the test data as outlined in Chapter 1 Once the data is successfully loaded and prepared, you can proceed to create new measures in the upcoming practice exercises The initial measure you will develop is the same one discussed in the section "Here’s How: Using IntelliSense" from Chapter 3.
Throughout this book, you will encounter practice exercises aimed at enhancing your learning experience through hands-on activities It is recommended that you complete each exercise as you progress For your convenience, answers to all practice exercises can be found in Appendix A.
Practice writing DAX formulas for the specified measures without consulting previous resources If you're unable to complete the task, review Chapter 3 for guidance and attempt it again Keep in mind that this exercise is meant for skill development Solutions to these practice exercises are available in Appendix A.
Write DAX formulas for the following columns, using SUM() for each one.
To calculate the total sales, create a new measure that sums the values in the ExtendedAmount column from the Sales table This measure will provide a comprehensive overview of total sales figures.
To create a measure that sums one of the cost columns in the Sales table, follow the same structure as the [Total Sales] measure from Practice Exercise 1, but focus on adding the product cost instead of the sales amount You can choose any of the cost columns available in the Sales table, as they are identical in this sample database.
Create a new measure for total margin, which is total sales minus total cost Make sure you reuse the two measures you created above in this new measure.
To express the total margin from Practice Exercise 3 as a percentage of total sales, create a new measure You can utilize the previously developed measures for this calculation Although the DIVIDE() function will be covered later in this chapter, feel free to explore its usage with the assistance of IntelliSense.
Create a measure for total sales tax paid Look for a tax column in the Sales table and add up the total for that column.
The [Total Sales] measure from Practice Exercise 1 excludes tax, so you need to add two measures to- gether to get this total.
This is similar to the other measures, but this time you add up the quantities purchased Look for the correct column in the Sales table.
As you worked through the practice exercises, did you do the following?
To optimize your Excel experience, it's recommended to first create a pivot table and place Products[Category] in the Rows section This approach allows for immediate feedback on your measures, enabling you to see the results instantly, which is a best practice for effective data analysis.