If you look through the the properties collection of a KPI in ADOMD, the last item is called "Annotations". Does anyone know how to set this property through BIDS?
We need to pass additional data to our client app for each KPI in order to make up for incomplete functionality and bugs in Analysis Services in regards to formatting, and we would like to use this field if possible. "Annotations" is not the same as "Description" by the way. We may have to prepend XML data to the description field if there is not an alternative.
Any help would be appreciated.
Thanks,
Terry
and bugs in Analysis Services in regards to formatting
Can you please provide more details about bugs in formatting of KPIs ? I beleive any formatting issue can be solved without going to annotations.
|||
Terry and I work together and I'm replying on his behalf...
The issue we have happens when the KPI value and goal are evaluated in the status or trend expression. For example, if both are formatted (using the Format function) as percent, the KPI status expression shows an error message of "type mismatch for the / operator." We've been getting around this by formatting them as "standard" in the KPI and formatting them in the client app by checking for values that don't start with a "$".
Recently, we came across another issue where the value returned was null. The KPI expression was checking for divide by zero errors but not for null numerators. The status expression blew up with the same error, presumably because the goal was still a formatted value. Removing the format from the Value and Goal or returning a formatted 0 instead of a null both resolve the problem.
It seems that the KPI framework is very sensitive to how values are formatted. This is why we've been thinking about moving the formatting to the client side by passing a format string in Current Time Member or by using Annotations. It seems it would be better to format the end result instead of worrying about how AS handles formats in Status and Trend. Following all the examples we've seen, we're using KPIValue and KPIGoal in the status expression instead of repeating the Value calculation multiple times.
Any advice would be greatly appreciated.
|||Can you please explain a little more about "if both are formatted (using the Format function)". Do you mean that you use VBA's Format function in the expression of KPI ? If so - this is really bad practice, it has all kinds of problems associated with it. Why wouldn't you want to rely on standard MDX formatting capabilities - I cannot think of scenario where MDX won't be able to do what Format function can do.
It would be best if you will provide some examples of KPI expressions that you are using, and expected formatting.
Thanks,
Mosha (http://www.mosha.com/msolap)
|||Yes, we are using VBA's Format in KPIs. We're using Format_String in calculations. It may be bad practice, but AdventureWorks and all of the books we have show examples of using it for KPIs. It would be great if there's a better way to do this. Here's sample code for one of the KPIs:
VALUE:
Case
When (Not IsEmpty( ([Measures].[Average Customers]) ))
And (Not IsEmpty( [Measures].[Annualized Contribution]))
Then Format(
([Measures].[Annualized Contribution])
/
([Measures].[Average Customers])
,"Currency")
Else
Format(0,"Currency")
End
If the Status is Null and the Goal is a formatted value this would have caused the type mismatch error. This is why we're returning a formatted 0 instead.
GOAL:
Format(300,"Currency") //Temporary goal
STATUS:
Case
When IsEmpty( KpiValue( "Average Customer Contribution" ) )
Then Null
When KpiValue( "Average Customer Contribution" ) /
KpiGoal ( "Average Customer Contribution" ) >= .90
Then 1
When KpiValue( "Average Customer Contribution" ) /
KpiGoal ( "Average Customer Contribution" ) < .85
And
KpiValue( "Average Customer Contribution" ) /
KpiGoal ( "Average Customer Contribution" ) >= .80
Then 0
Else -1
End
TREND:
Case
When [Date].[Calendar Year Hierarchy].CURRENTMEMBER.LEVEL Is
[Date].[Calendar Year Hierarchy].[(All)]
Then 0
When
VBA!ABS(
KpiValue( "Average Customer Contribution" ) -
(KpiValue ( "Average Customer Contribution" ),
[Date].[Month].CurrentMember.PrevMember)
/
(KpiValue ( "Average Customer Contribution" ),
[Date].[Month].CurrentMember.PrevMember)
) <=.05
Then 0
When
KpiValue( "Average Customer Contribution" ) -
(KpiValue ( "Average Customer Contribution" ),[Date].[Month].CurrentMember.PrevMember)
/
(KpiValue ( "Average Customer Contribution" ),[Date].[Month].CurrentMember.PrevMember)
>.05
Then 1
Else -1
End
We're currently developing on SP2 because of the LastNonEmpty performance problems in SP1. In several cases, our KPIs are just a calculated measures that have Format_String="Percent". To use them in the KPI and have the resulting KPI formatted as a percent, we have to multiple the calculated measure by 100 before the second format is applied. Analysis Services doesn't seem to know the underlying measure's format.
Thanks for your help.
|||It may be bad practice, but AdventureWorks and all of the books we have show examples of using it for KPIs.
I went over all the KPIs in AdventureWorks cube, and I didn't see any one using Format function. Could you please point out which KPI uses it, and I will file a bug for it to be fixed in the next release of the sample. Or perhaps it is already fixed and we are using different versions of AdventureWorks ? I also would like to know which book shows such examples. You can contact me by mail if you don't want this to be published in the forum.
Now to your scenario. It seems like you want 'Currency' to be formatting of KPI's Value property. Using VBA!Format in the Value expression makes it return a string. Therefore all other properties which try to do arithmetic operations with KPIValue operate with strings instead. It is almost a miracle, but AS actually does support arithmetics on strings to some degree by converting them to numbers when possible. But this is a slippery road. For example, you will find that "300" - "100" = 200, but "300" + "100" = "300100", and not 400 !!! And there are, of course, all other problems when you work with strings that you have noticed already.
Here is how I would've done it. In the MDX Script you can add the following snippet (it also fixes couple of other minor problems with your expression))
CREATE HIDDEN MyKPIValue = IIF ( [Measures].[Average Customers] <> 0, [Measures].[Annualized Contribution]/[Measures].[Average Customers], NULL );
FORMAT_STRING(MyKPIValue) = 'Currency';
This calculated measure will be created as hidden, and then inside the Value expression of your KPI, you simply reference [Measures].[MyKPIValue].
HTH,
Mosha (http://www.mosha.com/msolap)
|||I went through our current copy of AdventureWorks and didn't find it either. I may have seen it in an earlier version, or I may just be remembering incorrectly. I know I've seen it in other online articles. There's also a sample showing the use of VBA!ABS at DataBaseJournal.
Thanks for you explanation of strings in the KPIs. The type mismatch error makes sense now.
I think what we were missing here is that the value and goal really need to be created in the script and not in the KPI designer. We create a number of percentage ratios as KPIs where we may divide a currency amount by a count (just like ROA in AdventureWorks.) Using Format() seemed to be the only way to show this as a percent. We should be doing this calculation in the script and use Format_String. I just read another post of yours on solve order that suggests creating all the calculations in the script and only referring to them in the KPI designer is the right way to go.
It would be great to have documented best practices from Microsoft that keep us newbies away from problems like this. KPIs seem to be one of the least documented features.
Getting back to Terry's original question, is there a way of using annotations to pass additional info to the client for custom features? We plan on implementing spark lines and bullet graphs and it would be great if we could pass additional data to the client.
Thanks again for your help!
|||> There's also a sample showing the use of VBA!ABS at DataBaseJournal.
VBA!ABS - is kosher to use, since it's a math function, and its return data type is number. And, BTW, the MDX articles in DataBaseJournal by William Pearson are usually very good - so this is a source I would trust.
> Getting back to Terry's original question, is there a way of using annotations to pass additional info to the client for custom features? We plan on implementing spark lines and bullet graphs and it would be great if we could pass additional data to the client.
Yes - it is certainly possible. Assuming you create your cubes and KPIs using AMO - here is the link to AMO documentation about how to add annotations to any AS object.
http://msdn2.microsoft.com/en-us/library/microsoft.analysisservices.modelcomponent.annotations.aspx
HTH,
Mosha (http://www.mosha.com/msolap)
|||There's an open source project called BIDS Helper which is a Visual Studio Add-in. One feature lets you edit annotations on Analysis Services objects within BIDS:
http://www.codeplex.com/bidshelper/Wiki/View.aspx?title=Show%20Extra%20Properties&referringTitle=Home
|||I just looked at BIDs Helper. That is exactly what we were looking for. The other features will be very helpful as well. Thanks!!!