Search This Blog

Saturday, August 24, 2013

Migrating TM1/Alea formulas in Excel to Analysis Services

SQL Server 2005-2012

In scenarios where TM1 (Applix, then Cognos, now IBM) or MIS Alea is used, you often find workbooks using specific TM1/Alea functions like DBR(), DBRW() or DBGET() to access cube values. Since those workbooks are easy to create you may find a lot of such workbook reports in existing TM1/Alea installations. Assuming you have successfully migrated the cube to Analysis Services (Multidimensional or tabular) there might still be a lot of work left in order to migrate the workbooks.

One easy method in order to reuse the old workbooks and to check, if the new cube matches the results from the old one is to open the Excel workbook without the TM1/Alea addin being installed, add an SSAS connection to your cube and provide the VBA code for the functions (DBR, DBRW, DBGET etc.) by yourself. This is relatively easy to do and makes it easy to reuse the old workbooks without much work.

To illustrate this process, let’s assume you’re connected to a TM1 cube “Sales” with only two dimensions: Date and Product Category. In order to call the cubevalue-function from VBA I’m using a function that is very similar to the code I posted back in 2008 to provide a “CubeValue”-function for Excel 2002/2003:


Function CubeValueVBA(connstr As String, ParamArray axes()) As Variant
    On Error GoTo CubeValueVBA_error
    Dim conn As WorkbookConnection
    Dim adoconn As ADODB.connection
    Dim cs As ADOMD.Cellset
    Dim tuple As String
    Dim mdx As String
    Dim result As Variant    
    Set conn = ActiveWorkbook.Connections(connstr)    
    If Not conn.OLEDBConnection.IsConnected Then
        conn.OLEDBConnection.Reconnect
    End If
    Set adoconn = conn.OLEDBConnection.ADOConnection    
    Set cs = New ADOMD.Cellset    
    For Each s In axes()
        If tuple <> "" Then tuple = tuple + ","
        tuple = tuple + s
    Next
    mdx = "SELECT (" & tuple & ") on Columns from [" & conn.OLEDBConnection.CommandText & "]"
    cs.Open mdx, adoconn
    result = cs.Item(0)
    cs.Close
    If IsNull(result) Then result = ""
    CubeValueVBA = result
    Exit Function   
CubeValueVBA_error:
    CubeValueVBA = Error$
    On Error GoTo 0
    Exit Function
End Function

Please remember to add references to “Microsoft Activex Data Objects Library” and “Microsoft Activex Data Objects (Multi-Dimensional)” in order to use the code above.

Now you can create for example a DBR(…) function like this:

Public Function dbr(conn As String, ParamArray axes()) As Variant
    dbr = CubeValueVBA("AdventureWorks", _
        "[Date].[Calendar].[Calendar Year]." & chkstr(axes(0)), _
        "[Product].[Product Categories].[Category]." & chkstr(axes(1)), _
        "[Measures].[Internet Sales Amount]" _
        )
End Function

Of course you need to adjust this function to the type and order of the dimensions in the TM1/Alea cube of your installation. Within the DBR-function (as well as with DBRA, DBRW, DBGET etc.) all dimensions have to be addressed and the order is important. For the CubeValue function usually we only specify the axis which are not on their default element. Also, if you have more than one cube being queried from your workbook and if the dimension list differs, you need to switch to right connection as shown below:

Public Function dbr(conn As String, ParamArray axes()) As Variant
    Select Case conn
        Case "Sales":
            dbr = ...
        Case "Sales2":
            dbr = ...
        Case Else
            ... do some error handling
    End Select
End Function

For dealing with the square brackets, I used a helper-function:

Function chkstr(ByVal s As String) As String
    s = Trim(s)
    If Left(s, 1) = "[" And Right(s, 1) = "]" Then
        chkstr = s
    Else
        chkstr = "[" + s + "]"
    End If
End Function

So, with only a little bit of code, many calls in the workbook can be migrated to Analysis Services. However, I would consider this a temporary solution, as the connection handling and the need of having macros in your Excel workbook is not optimal. But since the solution from above is very easy to implement a lot of the workbooks can be reused immediately while you have time to properly migrate workbook by workbook. Also, having the macros from above makes migration a lot easier since you already know the connections and the order of the axes to be addresses. You can also write a macro to replace the functions in your workbook with the proper CubeValue function calls.

Saturday, August 3, 2013

A duplicate attribute key has been found during processing (revisited)

SQL Server 2005-2012

This blog post is my 100th post here and I’d like to say a big thanks to all the readers and supporters of this blog. Reason enough to concentrate on a very popular topic from my past blog posts.

Back in 2009 I wrote about the typical cases for the duplicate attribute key error in SSAS. From the response I got to this post this is still a very common error message causing confusion. Reason enough to revisit this topic and complement my last post with three new variations. Thanks for all the comments, as two of the cases are taken directly from the comments on my previous post.

 

Reason 4 (likely): This is a variation of reason 1 from my previous post. Reason 1 mentioned a NULL value in the key column being converted to a blank string which might conflict with another row that really has a blank string value. The same situation happens (as one of the comments mentioned) with a NULL value being converted to 0 for a numeric column.

Solution (identical as in reason 1): Try avoiding those NULL values in your data source (for example by using a DSV query and the T-SQL coalesce-function). When your source data is a data warehouse it’s also a good practice to avoid null values as they complicate the queries to the data warehouse.

Reason 5 (not very likely): The collation of the SQL Server relational database is “more detailed” than the collation on the SSAS side for an attribute’s key column. For example, SQL Server uses a case sensitive collation (like SQL_Latin1_General_CP1250_CS_AS) while SSAS uses a case in-sensitive collation. This causes SQL Server to treat a and A as two different values, while SSAS treats them as the same value (giving the duplicate key error). The same thing happens with an accent sensitive collation.

Solution: If you can, use the same collation type on both sides. If not, you could access the database table using a view that changes the collation or change the collation for the SSAS key column(s).

Reason 6 (not very likely): Your using the translation feature of the Enterprise Edition and for a certain attribute key you’re getting more than one translation.

Solution: Make sure that the translations are consistent (from the view-point of the attribute key)

 

Long explanation for reason 4

In order to produce this error, I used a table with a numeric (int) column. The following rows are present in the table:

image

As you can see, there is one row with a value of 0 and one row with a value of NULL. I created an attribute that simply uses this column as attribute key (no definition for attribute name or value).

image

When you try to process the dimension, you get the duplicate key error:

image

The error message reads:

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_DimTestNull', Column: 'Key', Value: '0'. The attribute is 'Key'.

If you check the details for the key column you see that NullProcessing is set to Automatic (which is identical to ZeroOrBlank in this case, see long explanation here).

image

When reading the dimension, SSAS runs a SQL query similar to this one:

SELECT DISTINCT    [dbo_DimTestNull].[Key] AS [dbo_DimTestNullKey0_0] FROM [dbo].[DimTestNull] AS [dbo_DimTestNull]

image

Now, with NULL being converted to the number 0 this results in two rows with the same key for a column that is expected to contain distinct values. This causes the error.

As with reason 1, having NULL values in the data warehouse is not a best practice as they cause problems not only with SSAS processing. So, it’s best to replace the NULL values in ETL and to define all colums as NOT NULL.

A tricky thing with this error is, that it might not occur on a process update. So, let’s assume your dimension contains only a row with the value 0 and processes fine. Then, sometimes later, a row with a NULL value appears. Since you’re still doing a process update, the error will not occur! After a while you try to full process the dimension and now you see the error message from above. This can be very confusion especially if the reason for the full process is a major change to the dimension (so you suspect this change to be the reason for the error).

 

Long explanation for reason 5

Let’s assume we have a very simple source table for our dimension:

image

I’d like to build a dimension based on this table. I could use the column Key as the dimension Key. Next I use the column AttributeKey as the source for my new attribute.

image

SSAS uses this query to get the values for the attribute’s key:

SELECT DISTINCT    [dbo_DimTestCase].[AttributeKey] AS [dbo_DimTestCaseAttributeKey0_0] FROM [dbo].[DimTestCase] AS [dbo_DimTestCase]

If you have a case insensitive collation (like SQL_Latin1_General_Cp1_CI_AS, CI=case insensitive) on this column in the relational database, the result contains only one row (in my case with the lower a). However, if you have a case-sensitive collation (like SQL_Latin1_General_Cp1_CS_AS, CS=case sensitive), you’ll get two rows like this:

image

You can see the collation for the column in the column properties in SQL Server Management Studio:

image

Since the CS in the collation means case sensitive, SQL Server treats the entries a and A as two different values.

Now, let’s have a look at the SSAS collation. You can find this setting in the attribute’s key column:

image

A blank entry (“none”) means the default collation. If you click on the ellipsis, you can see the details of the collation and make modifications for this single column:

image

In my case, the default collation of the SSAS database is set to Latin1_General, case-insensitive.

So, if the collation on the SQL Server side is case sensitive while the collation on the SSAS side is not, we’re in a similar situation as for reason 4: We’re getting two rows a, A while SSAS only sees one value (a), thus resulting in a duplicate key error.

Basically, to fix this error you have two options:

  1. make the collation on the SQL Server side case insensitive
  2. make the collation on the SSAS side case sensitive

In detail, you may go for one of the following changes:

  • change the collation for the column in the relational database to a case insensitive one, for example like this:
    ALTER TABLE dbo.DimTestCase
    ALTER COLUMN AttributeKey NVARCHAR(150)
    COLLATE SQL_Latin1_General_CP1_CI_AS


    or
  • Create a view on the SQL Server side to change the collation to case insensitive using a statement similar to the following and replace the table in the data source view with this view:
    create view vDimTestCase as
    select [Key], AttributeKey COLLATE SQL_Latin1_General_CP1_CI_AS  AttributeKey, AttributeName
    from dbo.DimTestCase

    or
  • change the collation for this key column (check case sensitive in the dialog above)

    or
  • leave the collation of this column to none (choose “none” in the combo box from the dialog above, if you already changed the collation) and set the collation on the dimension properties itself (this then defines the collation of all column binding in the dimension that don’t have an explicit collation-override)
    image

    or
  • leave the collation of this column and for the dimension set to “none” and set the collation on the SSAS server to a case sensitive collation (server restart required). Be careful as has an effect on all the SSAS databases sitting on this server, unless the databases are not defining their own collation (see above).
    image

As mentioned above, the same thing may happen with a accent sensitive collation on the SQL Server side and a accent insensitive collation on the SSAS side, for example with source data like this:

image

 

Long explanation for reason 6

This is a variation of case 3 of my previous post. If you’re using translations, they are handled like the Name property of your attribute and therefore they also have to be consistent. Take a look at the following table:

image 

I’ve created an attribute that uses EnglishName as the key.

image

This works fine. By default, the key column is also used as the name column.

Now, we’re setting up a translation that uses the column GermanName as the translation for this attribute. Now, EnglishName is still the key, but GermanName gives two different results for the key value “Summer”: “Sommer” and “Fällt aus”. This causes the error. If you replace the German translation in line 5 with “Sommer” the dimension processes correctly again. In general, this error is very unlikely, because usually there will be some kind of translation table providing exactly one translation per original row. It still can happen, since I recently saw this in a project of mine.