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)
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:
Get Microsoft SQL Server 2012 Bible now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.