Cell Annotations
Introduction
A cell annotation is a note on a cell written by a user explaining something of significance. It’s common in financial applications to allow users to annotate cells during, say, the budgeting process to explain some number. This document explains how cell annotations can be incorporated into an Analysis Services 2005 application.
Approach
Analysis Services 2005 (and all prior versions) support cell writeback, but cell writeback is intended for numeric values to be written back and allocated to leaf cells. So that approach doesn’t help here.
The approach instead is to create a member via dimension writeback in a special dimension for each annotation. Each such member contains the annotation cell coordinates encoded in its key and name, and the annotation text in its value (Why value? This is an optimization explained later.).
The annotation dimension source table has two columns: AnnotationID and Annotation. The AnnotationID is the encoded coordinate cell. In this example, it is the key value for each attribute member separated by semi-colons:
<country key>;<state key>;<city key>…<year key>;<quarter key>;<month key>;<day key>
For example, an annotation at (Customer.State.Oregon, Customer.Country.USA, Time.Year.1997) would be encoded as
USA;OR;0;0;0;0;0;0;0;0;0;0;0;0;1997;0;0;0
The keys of attribute members at the all level is encoded as well – the zeroes represent all members in dimensions not participating in the annotation.
<Insert xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<Database xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">CellAnnotations</Database>
<Cube>Sales</Cube>
<Dimension>Annotations</Dimension>
</Object>
<Attributes>
<Attribute>
<AttributeName>Annotations</AttributeName>
<Keys>
<Key>USA;OR;0;0;0;0;0;0;0;0;0;0;0;0;1997;0;0;0</Key>
</Keys>
<Value>Budget for
</Attribute>
</Attributes>
</Insert>
So far, a mechanism for storing and inserting annotations has been described – now the values have to be displayed. This is done with an MDX script assignment that does a lookup into the Annotation dimension to retrieve the annotation text. Since the Annotation measure group serves no other purpose, its single measure will be re-tasked to include this text. The assignment in a cube with four dimensions (in addition to the measures dimension) is tedious but straightforward:
this = strtomember( "annotations.annotations.[" + Customer.Country.currentmember.properties("key") + ";" + Customer.[State Province].currentmember.properties("key") + ";" + Customer.City.currentmember.properties("key") + ";" + Customer.Customer.currentmember.properties("key") + ";" +
[Product].[Family].currentmember.properties("key") + ";" + [Product].[Department].currentmember.properties("key") + ";" + [Product].[Category].currentmember.properties("key")+";" + [Product].[SubCategory].currentmember.properties("key") + ";" + [Product].[Brand].currentmember.properties("key") + ";" + [Product].[Product].currentmember.properties("key") + ";" +
[Store].[Country].currentmember.properties("key") + ";" + [Store].[State].currentmember.properties("key") + ";" + [Store].[City].currentmember.properties("key") + ";" + [Store].[Store].currentmember.properties("key") + ";" +
[Time By Day].[Year].currentmember.properties("key") + ";" + [Time By Day].[Quarter].currentmember.properties("key") + ";" + [Time By Day].[Month].currentmember.properties("key") + ";" + [Time By Day].[Day].currentmember.properties("key") + "]").membervalue;
end scope;
This assignment does the lookup into the annotation dimension and retrieves that member that matches the current cell coordinates. The MemberValue function retrieves the annotation. If a member not matching the cell coordinates is not found, no annotation is assigned.
The assignment above relies on name resolution. Each member in the Annotation dimension has a name like this:
Annotations.Annotations.[USA;OR;0;0;0;0;0;0;0;0;0;0;0;0;1997;0;0;0]
(It’s a bad idea to rely on how unique names are constructed because that can change. So I’ve refrained from relying on how a unique name is constructed from the key.)
This name resolution is a fast way to get the annotation for each cell. So I’ve burned using the name to store the annotation text. The key can’t be used as the texts of each annotation cannot be guaranteed to be unique. Another attribute could be used – but this complicates the assignment and slows things down. That left using the member value (another new feature in Analysis Services 2005).
Security Considerations
Accepting expressions and passing them on to the server can be risky. Analysis Services does not permit multiple queries to be executed in the same statement, so injection attacks are somewhat mitigated compared to SQL injection, yet a risk remains should a malicious user attempt to execute a stored procedure. For example, what if the user provided the annotation:
An evil annotation” + StoredProcWithNastyConsequences() + “ HaHa you are owned!
Use the Constrained flag with the StrToValue function; eg, Strtovalue(<user supplied annotation>, CONSTRAINED). This flag constrains the argument to a scalar value.
Other notes
This is a sample with some drawbacks that need to be addressed. The most obvious is the string manipulation. Keys containing semi-colons will mess things up. This can be resolved by escaping them beforehand.
Access has a limit of 255 characters for text fields. This can be easily consumed for non-trivial cubes. Use Sql Server which doesn’t have this limit.
Acknowledgements
I had some help with this. Thanks to Mosha Pasumansky, Amir Netz and Akshai Mirchandani.
Tags: mdx