![]() (MS SQL 2014 BI edition, Enterprise Gateway, etc)Īs a test, we created a Cube with a couple of dimensions and measures in a multidimensional model, and hooked it to Power BI. We are currently building our datawarehouse to use with Power BI specifically.Īs we would like to keep the data on premise, we made sure we have all the required pre-requisites. The KPI you created should be displayed in the KPIs section.I hope some of you can give me some advise on the following: Once you’ve created the KPI and deployed the Tabular cube, you can connect to the cube in Excel. The icon always displays as a circle regardless of the option selected.) ( Note: there appears to be a bug in the SQL Server 2012 KPI functionality. Once you’ve completed the KPI form, it should look similar to the following. In this section, you can type in a description or definition for the properties of the KPI. The options currently available are shown below:Īt the bottom of the form is a section labeled ‘Descriptions’. ![]() By default, the Status icon is displayed as a circle in either green, yellow or red. For example, if less than 90% is considered positive you can select the spectrum with green on the left side of the range (as opposed to the right side which is the default). When you set the Status Threshold, you have the option to change the definition of the status ranges. To set these values, simply drag and drop the arrows to the appropriate locations on the status bar. In this example, anything less than 90% is negative 90% – 100% is neutral and above 100% is positive. Now you can define the ‘Status Threshold’ for the KPI. In this example, the measure that specifies the Target value is ‘P&A to Bike Purchase Ratio Target’. Next, select a measure in the Measure drop-down that defines the ‘Target Value’ of the KPI (as an alternative, you can also enter a value in the Absolute value field). You should now see the KPI form displayed below: Once you have selected a measure to use as the Base Value for the KPI, right-click and select ‘Create KPI’. This measure defines a ratio of Parts and Accessories to Bike sales. In this example, the measure is ‘P&A to Bike Purchase Ratio’. To create a KPI in SSAS Tabular, we first select the measure that we want to use as the Base Value. Tabular KPIs are simpler in that we select a value or percentage in a GUI that defines the low and high ends of the range. In Multidimensional, the Status Expression is written in MDX and must return a value between 1 and –1. ![]() Another difference between the Multidimensional and Tabular KPI functionality is related to the Status Expression or Status Threshold. This feature is not included in SSAS Tabular KPIs as far as I am aware, although you can implement similar functionality as a separate KPI to evaluate a trend over time. The Multidimensional model includes a few additional properties such as Trend Expression that evaluates the KPI performance over time. An icon is displayed to help users easily determine the status of the base value compared to the target value. Status Threshold: Defines the range between a low and high threshold.Target Value: A measure or absolute value that defines the KPIs goal or target.Base Value: A measure in the tabular model that defines the KPIs actual value. ![]() SSAS Tabular KPIs consist of the following properties: KPI functionality in the Tabular model of SSAS is not quite as extensive as the Multidimensional model. I’m now going to discuss the KPI functionality in the Tabular model that was introduced in SQL Server 2012. For those of you who are unfamiliar with the KPI functionality in the Multidimensional model, the blog post can be found here. A few weeks ago, I wrote a blog post that discusses implementing KPIs in SQL Server Analysis Services Multidimensional. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |