Microsoft SQL Server 2012 Bible

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 ...