PowerPivot for Excel and SharePoint
- 4/14/2011
Enhancing and Analyzing the Data
After importing some data, you can start to enhance the data and get it ready for analysis. This section explores a few basic PowerPivot for Excel 2010 features, such as creating relationships and calculations with DAX.
PowerPivot for Excel was designed to look and work as much like Excel as possible, so if you’re familiar with Excel, you will find actions such as sorting and filtering data and moving and renaming columns in the PowerPivot windows both natural and intuitive. You might also notice some differences. For example, the Design tab in the PowerPivot window provides more operations that you can perform on the table, such as refreshing the data in the table (or all tables), and hiding, adding or deleting columns, as shown in the following illustration. You can also create and manage relationships between tables, as discussed in the next section.
Relationships
A relationship is an association or a connection between two tables. When you create a relationship between two tables, you are defining a way to navigate from one table to another by connecting a single record in one table to one or more records in another table. Relationships are a fundamental concept of relational databases—but not a concept available in Excel. This is one of the key differences between PowerPivot workbooks and regular Excel workbooks: Excel does not offer a mechanism to relate different tables.
Depending on the data source used to acquire your data, PowerPivot can automatically get relationships (and related tables) for you. For example, if you connect to a relational database such as a SQL Server, PowerPivot can import related tables for you from that database. When PowerPivot cannot retrieve table relationships automatically (as when importing data from a Data Feed or a text file), PowerPivot for Excel provides ways for you to define them.
You can create a relationship between two tables as follows: Select the column in the table that contains the “many” side, and then right-click and select Create Relationship from the context menu. Also, you can define a relationship by selecting the column you want to use and clicking Create Relationship (see the preceding illustration) in the Relationships group on the Design tab of the PowerPivot window, as shown in the following illustration. This example creates a relationship between tables imported from Azure DataMarket on the web and data imported from a relational database.
Calculations with DAX
DAX formulas are designed to be as similar as possible to Excel formulas. Just as in Excel, all DAX formulas begin with an assignment operator, such as an equals sign (=), but DAX works with tables (as in a database) rather than with cells arranged in a tabular fashion. The main difference between Excel formulas and DAX is that DAX never uses cell coordinates (B2, C3, and so on). Also, DAX does not work with cell ranges. To work with ranges, you can use DAX functions to apply filters to narrow down the data you are interested in.
Here is a simple example of a DAX formula:
=FactSales[SalesAmount]-FactSales[TotalCost]
This calculation defines the Margin column in the sample workbook by subtracting the TotalCost column from the SalesAmount column, which yields a profit-margin value.
PowerPivot evaluates the DAX expression for each row of the FactSales table and populates the FactSales[Margin] column with the result, as shown in the following illustration.
Here’s a slightly more complex DAX formula:
=SUMX(RELATEDTABLE(FactSales),FactSales[SalesAmount])
In the preceding formula, the DAX expression calculates a value for each row from the DimProduct table by scanning the rows in the FactSales for the current row, retrieving the SalesAmount, and performing a summation of the SalesAmount. In other words, this DAX formula filters the FactSales table that corrensponds to the product of the current row at DimProduct table and aggregates the SalesAmount value, as shown in the following illustration.
)
Appendix B, “DAX Function Reference,” provides a list of all available DAX functions along with a short description of each. For more information about DAX, see the “Data Analysis Expressions in PowerPivot for Excel 2010” white paper and sample workbook available for download at http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=1ae63bfb-c303-44e3-ae44-7413d499495d.
PivotTables and PivotCharts with PowerPivot
You can consider PivotTables and PivotCharts as the two features that make Excel an excellent BI client tool. Using these features, you can analyze large amounts of data in a quick and easy way—and PowerPivot takes advantage of both features. PowerPivot provides different ways to insert various combinations of PivotTables and PivotCharts into a workbook. You can access these features from the PowerPivot window or from the PowerPivot tab on the Excel ribbon as shown, respectively, in the following illustrations.
)
)
The options in those two menus provide easy and convenient ways to insert a single PivotTable, a single PivotChart, or a combination of PivotTables and PivotCharts into a PowerPivot workbook.
A single PivotTable is the most basic configuration. The following illustration shows a new PowerPivot PivotTable before adding any fields. Notice that, for the PowerPivot PivotTable and for the PowerPivot PivotCharts (in the succeeding illustration), the PowerPivot field list is shown instead of the standard Excel field list.
)
Selecting PivotChart inserts a single PivotChart in your workbook, as shown in the following illustration. PowerPivot also creates a PivotTable on a separate sheet that contains the data that the PivotChart uses. This additional sheet’s name follows a standard naming convention: “Data for Sheet <x> Chart<y>.” For this example, we inserted a PivotChart named Chart1 in the Sheet2 sheet, which created a new sheet named “Data for Sheet2 Chart1.” (See the following illustration.) This naming scheme can help you browse from the PivotTable that contains the support data back to the PivotChart (as long as you neither rename the PivotChart’s sheet nor move the PivotChart to a different sheet).
)
The following illustration shows a PowerPivot PivotChart displaying Total Sales By Country from the Contoso database.
)