次の方法で共有

PowerPivot for the DBA: Part 2

  • [アーティクル]

[This article was contributed by the SQL Azure team.]

In this article I am going to continue to tie some simple terminology and methodology in business intelligence with Transact-SQL – bring it down to earth for the DBA. This is a continuation in a series of blog posts that I started that equates Transact-SQL to PowerPivot.

Measure

If you’re not an OLAP guru, you might be baffled by the frequent references to measures in PowerPivot. Basically, a measure in PowerPivot is a predefined calculation that understands the scope of the cell that it is being evaluated in. This is much like a function with Table-Value parameters in Transact-SQL. Conceptually it is like a function in any language, a predefined calculation that takes input and produces output based on that input. The language in a measure is Data Analysis Expressions (DAX).

The dialog in which the measure is defined has multiple entry points:

  • “New Measure” button in the PowerPivot ribbon
  • Right-click menu item for each table in the field list that says “Add New Measure...”
  • Right-click menu item for each measure in the field list that says “Edit Formula”
  • Right-click menu item for each measure in the Values well below the field list that says “Edit Measure...”

Choosing any of these entry points will bring up the Measure Settings dialog that allows you to specify the name and formula for your measure, and that dialog looks like this:

clip_image001

This measure is then associated with the table that you created it in (Table name drop down above).

DAX is very similar to an expression in Excel; however it has additional attributes for dealing with scope. In fact, a measure can only be used in a PivotTable, because only a PivotTable has the concept of scope.

Scope

Understanding scope is the key to understanding measures. When I talk about scope, I am taking about the number of rows sent to the measure by PowerPivot. For example, in my previous blog post I was using the Adventure Works database to create a PowerPivot example that looked like this:

clip_image003

Which is the same data as this Transact-SQL Statement containing a GROUP BY statement with both OrderDate and ProductCategory.Name:

 SELECT    ProductCategory.Name, SalesOrderHeader.OrderDate, SUM(LineTotal)
FROM    Sales.SalesOrderHeader
    INNER JOIN Sales.SalesOrderDetail ON 
        SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID
    INNER JOIN Production.Product ON 
        Product.ProductID = SalesOrderDetail.ProductID
    INNER JOIN Production.ProductSubcategory ON 
        Product.ProductSubcategoryID = ProductSubcategory.ProductSubcategoryID
    INNER JOIN Production.ProductCategory ON 
        ProductSubcategory.ProductCategoryID = ProductCategory.ProductCategoryID
GROUP BY ProductCategory.Name, SalesOrderHeader.OrderDate
ORDER BY SalesOrderHeader.OrderDate

If I was using measures, the scope sent to the measure in this case is all the rows that match the GROUP BY and all the columns of the inner join that a SELECT * would return. The measure would then be evaluated for every cell in the PivotTable. So for July 1, 2001 and the category Accessories in the example above that would be 37 rows that looked like this:

clip_image005

All 37 rows would be sent to the measure to evaluate for this cell. My example Sales measure just sums the LineTotal column, which is the same result as letting PowerPivot sum the column in the first example.

If I used the Sales measure I created above the PowerPivot table would look like this:

clip_image007

Just like other languages, the measure (or function) can be changed and all the cells will update automatically. The measure can be thought of as a naming abstraction between PowerPivot and the calculation. In fact you can use the Sales measure in multiple PivotTables, or PivotCharts.

Changing Scope

The DAX language has built in functions to change the scope of the results within the measure. You can filter the results or expand the scope to include more rows. This really is the power of a measure, the ability to take the cells scope and compare it to an expanded or reduced scope.

Summary

In my next blog post in this series I will show how to take and expanded scope in PowerPivot and create a measure that computes a ratio between the cells scope and the product category the cell is in. Plus, I will give you the same results in Transact-SQL.

Do you have questions, concerns, comments? Post them below and we will try to address them.