O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Extending a BI Semantic Model with PowerPivot

The SQL Server 2012 PowerPivot Excel add-in introduces new features to extend a basic BI Semantic Model created in a PowerPivot workbook. These new features include:

  • Key Performance Indicators (KPI)
  • Hierarchies
  • Perspectives

Creating KPIs in PowerPivot

KPIs provide a quick and effective way to evaluate success of an organization's activity. For example, you can create a KPI that evaluates Reseller Sales Gross Profit from the AdventureWorksDW2012 database. The Reseller Sales Gross Profit formula is as follows:

Reseller Sales Gross Profit = Sales Amount – Total Product Cost

In this example, Sales Amount should be greater than the Total Product Cost to earn a profit. If Sales Amount is less than Total Product Cost than, the business loses money. You want to create a KPI that displays a green flag if there is a profit or a red flag if there is a loss on the reseller sales transactions. To create a KPI to track Reseller Sales Gross Profit, first create a calculated column named Gross Profit as follows:

1. Follow steps 1 to 10 from the section “Creating BI Semantic Models Using PowerPivot.”
2. Click the FactResellerSales tab.
3. Scroll all the way to the right until you see a blank column with Add Column as header name.
4. Click on the Add Column header and position the mouse cursor inside the formula bar.
5. Type the following formula inside the formular bar
=[Sales Amount]-[Total Product Cost] and press enter.
6. Right-click the ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required