BISM – Creating KPI’s

The tabular model and power pivot (CTP) allows the quick creation of KPI’s. This quick post looks at how KPI’s are created and viewed in Excel.


Once a measure is defined in the measure grid, a KPI can be created from it. This is done by selecting the cell that contains the base measure (ie the value cell) and selecting ‘Create KPI’ from the menu bar (or ‘Create KPI’ from the fast menu (ie, right click à create KPI)). This invokes the KPI manager dialog as below. Note that the base measure is defined and although this is selectable, it is not editable.

To create the KPI, we only need to define the target measure (assuming that we are basing the base value against some other measure) and (perhaps) edit the thresholds for which the KPI value is determined.

For example, to create the KPI against budget we would select the [Sales Bgt] calculation from the target value drop down (as below). [Sales Bgt] has already been defined as a measure (not a column in a table). Setting the thresholds is done by sliding the marker arrows (see below) or entering the value directly into the threshold box (as highlighted).

The threshold determines the upper and lower bound of the KPI severity based on the relationship of the [base measure]/[target measure]. For this example, thresholds at 95% and 100% will display an amber icon when the value of [Sales Act]/[Sales Bgt] is between 95% and 100%. The direction of severity (red, amber, green) or (green, amber, red) is controlled by selecting one of the four types (identified below). When a value does not appear in the base value, the KPI is not shown.


Descriptions can be added to each component of the KPI by Expanding the descriptions area

(click ).

Descriptions are shown as tooltips when the item is hovered over in excels pivot field list.

View in Excel

The Creation of the KPI appends three measures in the pivot field list (see right). When these are added as to the pivot, the base measure name is suffixed with the function that the measure (and derived measure) plays in the KPI. For example, the value of the KPI base measure of [Sales Actual] becomes [Sales Actual Value].

The original measure is lost (that is there is no measure [Sales Actual]).


Overall, the tabular model is a very simple and intuitive technology and the creation of KPI are no exception to this. Further reading can be found at books on line;

Other Notes

Once the KPI has been created (and the base measure has been suffixed), the model does not support a new calculation with the same name as the base measure.  For example, there could be no measure titled ‘Sales Act’.

BISM – Tabular Security Gotcha! (Denied Permissions)

And as the marketing saying goes “Now that I’ve got your attention”. In my last post, (BISM – Tabular Security Model) I commented that role based tabular model security was similar to traditional SSAS. While this is true, the tabular model has no concepts of denied permissions, and I think this is worthy of a small but separate post.

I’ve raised this on the Denali (Pre-Release) forums (see here). Cathy Dumas from the product team (her blog is here) comments that there is no denied permission and that permissions are additive (as one would expect).

This subtle difference is important to identify. The statement ‘table‘[column]<>’value‘ is not a denied permission on the columns value. Rather, it is an allowed permission on everything else other than value. If the value row data is permitted through some other role, the user will see the data that was previously excluded (under the role ‘table‘[column]<>’value‘).

This is a change from the denied permissions of the UDM. In this model, denied permissions took authority over the accumulative allowed permissions so that restrictions could be actively applied. In situations where the same account is covered by many roles, the tabular model lack of denied permissions will require more stringent security controls.

BISM – Tabular Security Model

The tabular model uses role based security in the same manner as traditional SSAS. This post looks at the creation of roles and the use of restriction of data through (roles).

In this example, the model is very simple only showing only sales by [ship date] and [Sales Territory] (below). We create a model and restrict what data a user can see through security roles.

Roles are administered via the Role Manager (accessed via Model > Roles… or the roles icon () in the menu bar) and apply to the entire model.

The role must have a name and permission before any restriction can be applied.

Permissions include (None, Read, Read and Refresh, Refresh and Administrator). When the role is initially created, it has ‘None’ permissions and therefore no filter can be applied (since it has no visibility of the data within model). The row filters dialog (below) is greyed (disallowing entry) as is the case with the Refresh permission (again no data is available to the role) and the Administrator Role (since security is not applicable). The only permissions that can restrict data views are the Read and ‘Read and Refresh’ roles.

When the role permits restriction, a DAX predicate can be applied to the table that the filter should be applied to. The predicate (in this example) takes the form of column (boolean operator) value (see below). For example, visibility of the united states is [Country]=”United States” whereas permission to everything other than the united states would be [Country]<>”United States”.

As is the case with SSAS, members are added through the members tab and allow ad groups and users (inc security principals).

The Effect of Table.Column Predicate

Role permissions are accumulative to accounts. For example, when an account is a member of the country_usa role (filter: [Country]=”United States”) and country_canada (filter: [Country]=”Canada”), the account will have access to both countries.

However, I have not found the expected inverse to be true. For example, when the account is permitted access to data in one role and denied it in another, they are allowed access to the data.