Page Comparison

KPI Definition

In the world of business intelligence (BI) and reporting, there is an ever increasing demand to be able to Create/Calculate KPI's (Key Performance Indicators).  The ability to add and maintain on-the-fly KPI's is additionally a vital requirement for all customers. Customers want to be able to apply their own type of performance calculation/logic on top of the integrated/aggregated data sources through a BI platform.

There are some slight differences between KPI and a Calculated Measure. A Calculated Measure is a new (on-the-fly) measure on top of data-source/s pre-existing measures where-by the outcome is a numeric value, whilst a KPI has many angles, such as outlined below:

• Value - this is same as what we are expecting as a Calculated Measure.
• Status - this is a Status logic (value and/or colour) on KPI.
• Trend - this is a Trend logic (side and/or colour) which shows/indicates the trend of the KPI value. The Trend's side/colour can be calculated by same KPI's value or by any other KPI's value.
• Target - this is a desire value that we wish our KPI to achieve.
• Format - KPI normally has a numeric value but that is not limited to a numeric always. It can have some textual values such as "Satisfied", "Below Target" or anything else. With Formating feature, we are able to format and represent our KPI value.

eMite KPI Admin Dashboard

eMite BI platform supports KPI creation/maintenance through its KPI dashboard. Anyone has been given access to KPI dashboard can create/edit/delete/publish KPI(s) and/or use KPI(s) inside other KPI(s). Also within each KPI creation/maintenance, we are able to add as much as we want, with the ability to apply business logic filters on top of existing data-sources. The data-sources are the eMite's RTBI data structures and not the customer's data-sources.

There are two options to access KPI Admin dashboard page:

1. Through Web admin Dashboard Page:
   • On the main menu of eMite Web admin page, hover the mouse over the Edit Page and select Add Content.
   • Select Add Row button on the Add Content page.
   • On the Select Datablock page, select KPI Admin then follow the Add button. You should now see the new KPI Admin page added. The page can be re-sized by click on the right corner of the page. 
2. The first option is the standard method. The eMite web dashboard may refreshes all the pages (including the one you have already opened within KPI Admin page) automatically, or you may need to navigate through other pages. When this happens, you may face a RTBI data-source structure's re-loading experience or even you may loose your page (if didn't save it). Some users want to create/change/publish KPI(s) reports and quickly test the result. These users are likely to experience the re-loading issue more than others, as they need regular page navigation. In this case, you should use the direct KPI Admin URL in another browser or a separate page on the current browser. To find the KPI Admin Dashboard URL, you need to go through first option and then find the URL in header 

Contact your eMite administrator or eMite consultants if the page failed to open, probably the settings in back-end have not been configured correctly.

There are three main components on the KPI Admin dashboard page, which allow you to add/edit/delete/publish KPIs in eMite platform. It is IMPORTANT to note that any creation,deletion and edit will be auto-saved, so be careful when making changes within KPI Admin dashboard. The auto-save does not perform the auto-publish (where users can see changes in report designers), the KPI will only be published when the UpdateKPI button is selected (see below on how to Publish).

The Cube name, KPI Group and the KPI

The Cube is a report representation of your single or multiple data sources. In eMite, this sometimes called a Fact Query. The KPI Group is a group contains one or many KPIs. 

To view/add/delete or modify KPI Groups and KPI:

• On the KPI Admin main menu, select KPI Dashboard tab. Select the top chevron (∨) to collapse or expand the options.  
• The Cube can be selected from the system drop down menu. Contact your eMite administrator or eMite consultants if the Cube (or data structure) drop down is empty. 
• To add a KPI Group, type in a name under KPI Groups and select the Add button. To delete a group, select a group name follow by the Delete button. The system will not allow you to delete the group if there are KPIs in the KPI group. You must delete each individual KPI within the group before the group can be deleted.
• To add a KPI, type in a name under KPIs and then select the Add button. After a KPI has successfully added, the system will automatically populate parts of Admin dashboard. To rename/deleted/copy a KPI, choose a KPI from the drop down list then select the  Rename/Delete/Copy button. 
  
  
  

The Threshold, Template, Format String, Status KPI, Trend KPI and Target KPI part

Predefined Calculations

Select the second chevron (∨) on the KPI Dashboard tab to expand the options below.

Template: allows you to select the type of formula/calculation for the KPI. Templates contain one or two sets (maximum of two, Set A or Set B or both) to represent the KPI logic. Each set carries one or more logic and each logic works as a filter on top of data source. Each logic (filter) contains two parts, the Measure and the Dimension member(s).

For example, the [Measures].[Incident Count] means that we are trying to count the Incidents where "Incident Count" is the measure. The [Priority].[Priority].[P1]  means that we are looking for P1 (Priority dimension member) tickets. The combination of the measure and dimension filter means that we are trying to count the number of P1 Incidents.

eMite currently supports 9 types of calculations. Each type of calculation requires its own requirements (one or two Sets). Here are the types:

SUM(A) = X – This is the sum aggregation of records for a set (Set A) of logic. An example of a KPI for this type could be something like "Number of PI Incidents", which shows the sum of incident records by P1 priority.

SUM(A) / SUM(B) * 100 = X%  – This type is use to create percentage KPIs. Set A represents a set of aggregation of Measures filtered by Dimensions and Set B represents second set of aggregation of Measures and Dimensions. An example of the KPI could be a "% Of Closed Incidents within 24 Hours". This KPI shows a percentage of the "number of Incident have been closed from last 24 hours" versus the "number of total incidents have been created or updated from last 24 hours". The percentage is a value from between 0 to 100, however, eMite % KPI calculates the % depends on Set A and Set B. If Set B has smaller number than Set A, then the percentage value would be bigger than 100 which in some cases is this valid.

SUM(A) + SUM(B) = X – The result of this type of KPIs would be the sum of results from Set A and Set B. Each Set can have one or many logic (filter). If in case, you are looking for Set A + Set B + Set C, you need to first build your first KPI (for example call it MyFirstKPI) as Set A + Set B, then publish it (will explain later in this topic), then reload the KPI Amin (to make sure the new KPI, MyFirstKPI, has come through to the list of measures), then you can build second KPI as Set A + Set B, where Set A (or Set B) can use the new KPI, MyFirstKPI, as your measure.

SUM(A) - SUM(B) = X – Similar to SUM(A) + SUM(B), but here the final result is the Set A - Set B

SUM(A) * SUM(B) = X – Similar to SUM(A) + SUM(B), but here the final result is the Set A multiply Set B

SUM(A) = X (include 0) – This is same as "SUM(A) = X" but has one more feature and that is, if the result is empty (null value), it gets converted to zero

AVG(A) = X – This template automatically uses an Average function. Using this function, you got to have a Set A (where your logic are there) with proper measure included and system will return an Average value of that measure against number of records matched your logic

Advanced – This is an advanced mode of KPI calculation where non of the above templates covered your calculation type and here you are able to put your own MDX query (the query language of Analytic structures, for more clarification, refer to: https://msdn.microsoft.com/en-us/library/ms144785.aspx) and system will try to parse, run and return a numeric value of that. 
In MS Analysis version of eMite, all kinds of valid MDX queries are supported (MS Analysis has its own MDX parser), while in RTBI version, currently, just the "Case Statements" are supported, as an example, you can write: " Case When [Measures].[Incident Count] > 10 Then 1 Else 0 End "

Visible: When set to false and after publishing your changes, nobody will see that KPI in reporting side, so it is like a hidden KPI. This is actually a cool feature, if a KPI doesn't need to be used by users/reports thru. dashboard, but it can be used to build a second level of KPIs. As an example, imagine you want to calculate the employees' low performance cost (where this KPI can be seen and used from dashboard), but that might use two separate KPIs, one is a fixed value KPI (like $1 per unit of low performance), and you approximately prefer to hide it of reporting side, and the second one which is the actual low performance value KPI, calculates the performance.

Submit: Sometimes, you are in a middle of creating a KPI, but that is not ready to be published, but another person may want to publish changes (Publish from any body will publish changes from all people using KPI Admin dashboard), so setting this field to false means, it is in Draft mode and even trying to publishing the changes, the System wouldn't look at that at all.

Format String:Using MS Analysis version of eMite, that feature is to wrap the KPI value to a specific format. eMite's MS Analysis version supports "Standard,Currency,Percent,Short Date,Long Date,0, Long Date, "#,##0.00;-#,##0.00", "$#,##0.00;-$#,##0.00", "#,##0.00 %;-#,##0.00 %", "d/MM/yyyy" and "dddd, d MMMM yyyy". (For more clarification, refer to: https://msdn.microsoft.com/en-AU/library/ms146084.aspx). In RTBI, you can choose "Standard", "Currency" or "Percent" and the KPI will be shown in proper format in all places, like Status block, report and ...

Status KPI: When creating a KPI, you can define what are the expected thresholds boundaries (will explain separately) for that KPI and what color (status) the KPI will represents in dashboard. Example is, you can show a KPI value in a Status block (a Dial) , as well as showing the Status block color (there are different segments can be mapped, like Header, Value color, Content ...) to what threshold boundaries returns. If the KPI value is 6, the Status block color might be Yellow, but value of 20, might change it to Red.

There are also more complex scenarios where the current KPI (call it KPI A) value is not useful for color (status) calculation, instead we need another KPI (call it KPI B) to calculate the value which will be used for threshold boundaries. In this case, the KPI A doesn't need to carry any threshold boundaries (you can delete or leave the default boundaries), instead, KPI B must have the boundaries and KPI A must point to B from StatusKPI field.

Trend KPI:  tempalte SUM(A) / SUM(B) * 100 = X% 

As discussed at the start of the article, a KPI has many angles and Trend is one of them. Even knowing a KPI value and its status, but there are reasons to find how the KPI is performing during time, is that getting better, worse or even not changing!? Knowing the team performance is good, is not even useful than knowing the performance dramatically moving down! To see the KPI's direction, you can use a eMite's Score Board and check the KPI's Trend. 

The "Trend Value" will dictate the direction of the Trend. Any value bigger than 0 dictates an UP ▲,  below than 0 for DOWN ▼ and equal to 0 for no change ▄. The "Trend Value" comes from dividing current value of KPI and its previous value if no second KPI has been selected in TrendKPI, otherwise, the value of the second KPI will represent the current KPI's Trend Value.  But the color of the Trend still comes from a threshold boundary of second KPI (listed in TrendKPI) or there wouldn't be any color! It is important to know that the UP and DOWN can carry just two colors Green and Red, representing GOOD or BAD.

Let's look at two examples: 

Base KPI: "Number of Open Incidents", Trend KPI linked to base KPI: "Growth of Closing Incidents" --> Let's say, yesterday, "Number of Open Incidents" was 120 and now is 118. "Growth of Closing Incidents" for today is -2. As the growth rate is below one, so the Trend is DOWN, but is that a Good down or a BAD down! Well, that depends on threshold boundaries of KPI "Growth of Closing Incidents". Let's say we set threshold boundaries as Red >= -5 & Green < -5. So in this case, even the growth is DOWN (good news) but that is not still satisfying us, as we are expecting to a 5 level growth in each day, so we get Red DOWN. #** Base KPI: "% Of of Closed Incidents", Trend KPI linked to base KPI: "Growth of Closing Incidents" --> Let's say, yesterday, "% of Closed Incidents" was 56% and now is 64%. "Growth of Closing Incidents" for today is 8%. As the growth rate is over one, so the Trend is UP, but is that a Good up or a BAD up! Well, that depends on threshold boundaries of KPI "Growth of Closing Incidents". Let's say we set threshold boundaries as Red <= 10% & Green >10%. So in this case, even the growth is UP (good news) but that is not still satisfying us, as we are expecting to a 10 level growth in each day, so we get Red UP.

Target KPI: Target is a value which can help you to understand how close are your KPI from what you are expecting as your desire value. How much you are expecting to sell in current financial year is a Target for your selling KPI. Same as Trend KPI, you can see that value in Score Board and if no KPI has been linked to TargetKPI of a KPI, the Target would come from the first value of threshold boundaries.

Description: Just put your own description about the KPI and explain the logic, then anybody looks at your KPI, they can understand the reason and logic of your KPI.

Thresholds Boundaries: The threshold boundaries are what you are expecting your KPI to look like. For example, "Number Of Open Incidents" might have conditions to say, if the KPI value is below 5, so the KPI is fine (Status is good) and we can show it (the KPI Name & Value) as Green, if equal above 5 and below 10, so that is in Warning status, show it as Amber and equal above 10 means Critical and show it as Red. You are able to see these Color reflections (means Status) in few places in UI, like Status, Scoreboard, Score List and Grid view of reports.

Although, the three statuses, Green/Warning/Alarm are very popular, but in eMite, there is no limitation on how many expression lines/colors you want to have. You are welcomed to have nothing or even 10 lines. You can pass your own desire expression values and colors as well, all would be automatically and with no delay reflected into UI components.

If the KPI Value can be used for its threshold boundaries, then we can simply add our expressions as boundaries, but if the same value can't be used for threshold boundaries, so we may use one of below options:

Another (secondary) KPI can be created and linked to StatusKPI of the main KPI. In this case, the main KPI doesn't need any boundaries (the defaults can be left or deleted), instead, the second KPI MUST have the right expressions and colors.

When a value of another Dimension can be used in expressions. As an example, imagine, if the "Number Of Open Incidents" (as a main KPI) is 10, then you want to see it as Red, if "Number of P1 Priority Incidents" are over 3 or "Number of P2 Priority Incidents" are over 8 and so on. In this case, you need to first select a member of a dimension (P1 from Priority), then press "Add" button of Threshold Boundaries, and put your own expression later on.

Logic (Filters)

Simple Click Through

The logic is the last part of your KPI creation. Well, depending on what type of Template you used, you may need to fill one (just Set A) or two Sets (Set A & B). Each Set is like a "one to many conditions" where the data need to come from. As an example, you may want to create your Set A as all incident tickets where their priorities are P1 or P2 and the same time, their Updated Date is within Last 24 Hours. As we are looking for "all incident tickets", we need a predefined measure called "Incident Count" and we have two filters: P1 & P2 Priorities and Updated <=Last 24 Hours. To get these filters added into Set A, you need to do it in two steps: 1- select Incident Count measure from Measure parts and P1 and P2 members from Priority dimension 2- select Incident Count measure from Measure parts and Update dimension. There are some special cases as below:
Date Dimension: When you choose a date dimension, you need to actually choose one level of Date, as Date dimensions have many levels like Date, Week, Month, Month-Year ...
Date Dimension, Special Cases: For Date sort of calculations, eMite uses a lot of built-in functions in order to empower the query functionality. When facing with these builtin functions, the commands may need to follow specific rules. In our above example, last 24 hours must be added as @Last24Hours. Full list of these commands and their description can be found in Examples section.
Multiple Members: Sometimes, you want to add more than a member in your criteria, similar to our example, we need incidents where Priority is P1 or P2. In these circumstances, you need to select members one by one and each time you need to "Add" to Set A (or B). Their name will automatically be appeared in the same line.

Publish

Once you created/edited/deleted your KPIs, you need to publish them to RTBI engine. Remember, any change in KPI Admin is auto-save except publishing to RTBI. To do that, simple press "UpdateKPI" button from section one. The publish method will analyse all KPIs and their logic and will notify to you if there is an error and ask you to fix them before publishing anything.

Examples

 In this section, you will see how you can create different types of KPIs and how they look like in MDX format.

• SUM(A) = X --> That just needs a Set A, but Set A itself may have one or many filters. 
  SUM({[Priority].[Priority].[1],[Priority].[Priority].[2]} * {[u_status].[u_status].[Pending],[u_status].[u_status].[Pending 3rd Party],[u_status].[u_status].[Pending Approval]} * [Measures].[incident Count])
  
  
• SUM(A) = X (including 0) --> This is same as SUM(A)=X where when the result is Null or Empty, that gets converted to Zero
• SUM(A) + SUM(B) = X  --> This needs two Sets
  SUM({[Priority].[Priority].[1]} * {[u_status].[u_status].[Pending],[u_status].[u_status].[Pending 3rd Party],[u_status].[u_status].[Pending Approval]} * [Measures].[incident Count])
  +
  SUM({[Priority].[Priority].[2]} * {[u_status].[u_status].[Pending],[u_status].[u_status].[Pending 3rd Party],[u_status].[u_status].[Pending Approval]} * [Measures].[incident Count])
• SUM(A) - SUM(B) = X --> This needs two Sets

SUM({[Priority].[Priority].[1]} * {[u_status].[u_status].[Pending],[u_status].[u_status].[Pending 3rd Party],[u_status].[u_status].[Pending Approval]} * [Measures].[incident Count])
-
SUM({[Priority].[Priority].[2]} * {[u_status].[u_status].[Pending],[u_status].[u_status].[Pending 3rd Party],[u_status].[u_status].[Pending Approval]} * [Measures].[incident Count])

• SUM(A)/SUM(B) = X --> This needs two Sets

SUM({[Priority].[Priority].[1]} * {[u_status].[u_status].[Pending],[u_status].[u_status].[Pending 3rd Party],[u_status].[u_status].[Pending Approval]} * [Measures].[incident Count])
/
SUM({[Priority].[Priority].[2]} * {[u_status].[u_status].[Pending],[u_status].[u_status].[Pending 3rd Party],[u_status].[u_status].[Pending Approval]} * [Measures].[incident Count])

• SUM(A)*SUM(B) = X 

SUM({[Priority].[Priority].[1]} * {[u_status].[u_status].[Pending],[u_status].[u_status].[Pending 3rd Party],[u_status].[u_status].[Pending Approval]} * [Measures].[incident Count])
*
SUM({[Priority].[Priority].[2]} * {[u_status].[u_status].[Pending],[u_status].[u_status].[Pending 3rd Party],[u_status].[u_status].[Pending Approval]} * [Measures].[incident Count])

• SUM(A)/SUM(B) * 100 = X --> This needs two Sets

SUM({[Priority].[Priority].[1]} * {[u_status].[u_status].[Pending],[u_status].[u_status].[Pending 3rd Party],[u_status].[u_status].[Pending Approval]} * [Measures].[incident Count])
/
SUM({[Priority].[Priority].[2]} * {[u_status].[u_status].[Pending],[u_status].[u_status].[Pending 3rd Party],[u_status].[u_status].[Pending Approval]} * [Measures].[incident Count])
*
100

• SUM(A)/SUM(B) *  X --> This needs two Sets and supported by eMite, but doesn't have any Template.

SUM({[Priority].[Priority].[1]} * {[u_status].[u_status].[Pending],[u_status].[u_status].[Pending 3rd Party],[u_status].[u_status].[Pending Approval]} * [Measures].[incident Count])
/
SUM({[Priority].[Priority].[2]} * {[u_status].[u_status].[Pending],[u_status].[u_status].[Pending 3rd Party],[u_status].[u_status].[Pending Approval]} * [Measures].[incident Count])
*
X

• AVG(A) = X --> This needs just one Set , but the result is as : SUM(A)/COUNT(A) which returns the Average
• Advanced -->  The Advanced template is the one without any Set! In fact, it gives you a plain text environment to write your own MDX query. In eMite MS Analysis version, all kinds of MDX queries are supported but in RTBI, only Case statements are supported. The Case statements are doing a lot job, but the result is always a numerical value which as part of KPI value is Ok. Below are few examples on MS Analysis and RTBI MDX queries:
  • MS Analysis:

This returns the total Incident Time Spent (Closed Date - Open Date) for the top 5 (Assigned Group, Priority), means whatever Assigned Group spent more time on whatever Priority.<br />SUM( TopCount ({[AssignGroup].[AssignGroup].Members * [Priority].[Priority].Members} , 5 , [Measures].[Incident Time Spent] ) )

This returns number of Incident records from start of the year to August 2003<br />Count( PeriodsToDate( [Created].[Created].[Year], [Created].[Created].[August 2003] )

• • RTBI:
    • This returns number of Incident rows where Priority is P1 and they have been updated from last 15 days
      Case When [Priority].[Priority].[P1] And [Updated].[Updated].[Date] <= @Last15Dyas Then 1 Else 0 End 
• @Functions: All these kinds of functions need to be aware of how to use Date dimensions, so carefully follow the format. The format must be: "[DateDim].[DateDim].[Hour]   Operator   @FUNCTION", "[DateDim].[DateDim].[Date]   Operator   @FUNCTION", "[DateDim].[DateDim].[MonthAndYear]   Operator   @FUNCTION" or "[DateDim].[DateDim].[Year]   Operator   @FUNCTION", examples are:</span> [Assessmentdate].[Assessmentdate].[Date] , [Assessmentdate].[Assessmentdate].[Hour] , [Assessmentdate].[Assessmentdate].[MonthAndYear] or [Assessmentdate].[Assessmentdate].[Year]
  • @Today: [Assessmentdate].[Assessmentdate].[Date] < @Today
  • @Now : [Assessmentdate].[Assessmentdate].[Hour] <= @Now
  • @Yesterday: [Assessmentdate].[Assessmentdate].[Date] >= @Yesterday
  • @ThisMonthAndYear: [Assessmentdate].[Assessmentdate].[MonthAndYear] = @ThisMonthAndYear
  • @LastMonthAndYear: [Assessmentdate].[Assessmentdate].[MonthAndYear] = @LastMonthAndYear
  • @LastYear: [Assessmentdate].[Assessmentdate].[Year] >= @LastYear
  • @ThisYear: [Assessmentdate].[Assessmentdate].[Year] < @ThisYear
  • @ThisWeek: [Assessmentdate].[Assessmentdate].[Date] >= @ThisWeek
  • @OneWeek: [Assessmentdate].[Assessmentdate].[Date] >= @OneWeek
  • @LastWeek: [Assessmentdate].[Assessmentdate].[Date] > @LastWeek
  • @LastXHours: [Assessmentdate].[Assessmentdate].[Hour] >= @Last72Hours
  • @LastXDays: [Assessmentdate].[Assessmentdate].[Date] >= @Last2Days
  • @TodayAndLastXDays: [Assessmentdate].[Assessmentdate].[Date] <= @TodayAndLast3Days
  • @NextMonthAndYear: [Assessmentdate].[Assessmentdate].[MonthAndYear] <= @NextMonthAndYear
  • @NextYear: [Assessmentdate].[Assessmentdate].[Year] <= @NextYear
  • @NextWeek: [Assessmentdate].[Assessmentdate].[Date] < @NextWeek
  • @NextXHours: [Assessmentdate].[Assessmentdate].[Hour] > @Next48Hours
  • @NextXDays: [Assessmentdate].[Assessmentdate].[Date] <= @Next4Days
  • @TodayAndNextXDays: [Assessmentdate].[Assessmentdate].[Date] <= @TodayAndNext5Days

KPI Template

Use KPI template page to manage KPI templates

Copy Child Query

Click copy to child fact query button to copy KPI from master fact query to child fact query

Multiple Connections

Multi-connection dropdown will show if one user domain is mapped to multiple index groups

Multiple Source

(New function in v6.0.2)
When "enable multiple source" is checked, you can build the data set from more than one cube.