A blog about designing and developing Business Intelligence solutions using Microsoft SQL Server
Header

In some cases we need to bring comments or descriptions into a dimension in SSAS for detail reporting. For example the comment from a maintenance worker against a machine breakdown, or a geological survey description. These fields are typically free text in the source system and as such don’t contain restrictions or constraints on data entry (e.g. upper and lower case, empty values, white-space, uniqueness). When you try and process these attributes you’ll run into a couple of issues.

To demonstrate this I’ve knocked up a few rows with variations in case of the same text, a null value and an empty string.

Free Text Sample Data

Creating a dimension based on this table in SSAS gives the following error when we try to process it – “Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: ‘dbo_FreeText’, Column: ‘Comment’, Value: ”. The attribute is ‘Comment’”.

SSAS Duplicate Key - Empty and NULL Values

This is due to the NullProcessing property on the dimension attribute, for which the default setting is Automatic. This specifies that the Null value is converted to zero (for numeric data items) or in this case a blank string (for string data items). This means that we’ve now got a null value which is converted to an empty string and an empty string, resulting in duplicate key values. If we set NullProcessing to Preserve this will fix this, giving us two distinct key values. If your database or this particular field is case sensitive you’ll then likely run into a second issue with a very similar error message – “Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: ‘dbo_FreeText’, Column: ‘Comment’, Value: ‘Some comment’. The attribute is ‘Comment’”. This comes down to a difference in case sensitivity between the underlying database and Analysis Services, which by default is case insensitive. In SSAS this is set at the attribute level. If you select the Comment attribute and expand they KeyColumns group you’ll find the collation property, under which you’ll find a case sensitive check box.

SSAS Attribute Collation

Once you’ve checked this the dimension should now process successfully. When we browse the comment attribute you’ll notice two “blank” values (null and empty) and multiple values for “some comment”. This example assumes that these are different discrete values. If this isn’t the case this should be handled in ETL i.e. converting null values to empty strings and standardising on case.

SSAS Browser Comment Attribute

Previously I wrote a post about the LocaleIdentifier error when browsing an Analysis Services cube via Management Studio. Whilst it’s annoying, it really only affects developers so quickly slips down the list of priorities to address! However, when it starts to affect users it becomes a serious issue. Working on another project with an Excel component this issue came up in a couple of places – connecting to the cube from Excel (sometimes), and using drill-through in Excel.

After looking into this for a while I came across a solution, although it’s not one I feel completely comfortable with.

1. Go to Windows > Control Panel > Region and Language

2. Update the Format to “English (United States)”

Region and Language Format

3. Click Apply

4. Update the Format back to your original language, in my case “English (Australia)”

5. Click Apply

6. Click OK

Recently I had a requirement to set the colour of a couple of non-calculated measures in Analysis Services to differentiate between an actual and inferred value. It was another one of those things that I’d remembered being as simple as selecting a value from the properties screen… Unfortunately my memory deceives me and I was greeted with a properties window totally devoid of any colour settings!

Conveniently it’s easy (enough) to set the colours using MDX in the cube calculation script. SSAS defines colour values using a somewhat strange system, there’s a great post from Boyan Penev explaining this – Colour Codes in SSAS.

To set the colour of the Amount measure in the Adventure Works cube to a lovely shade of pink, you’d add the following line to the cube calculation script…

1
FORE_COLOR ( [Measures].[Amount] ) = 16711935;

With the resulting err… lovely pink measure when browsed from Management Studio.

Pink Coloured Non-Calculated Measure

I can across an annoying error when using another client computer to connect to an Analysis Services cube via Management Studio. The error or received was – “The query could not be processed: XML for Analysis parser: The LocaleIdentifier property is not overwritable and cannot be assigned a new value.”

SSAS LocaleIdentifier Issue

To resolve this you can simply select your language from the language drop down in the cube browse window.

SSAS LocaleIdentifier Issue Resolution