If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

SSAS and Cafe and long member names

Started by dhandler, 28 Jan 2015 04:13:14 PM

Previous topic - Next topic

dhandler

When we are using IBM's CAFE, we are running into an issue regarding the length of the dimension member's path from our SSAS cubes.  Any argument passed into a function in Excel cannot exceed 255 characters.

So, with PowerCubes, IBM's internal cube structure assigns a "Category code" to every member which, is generally somewhat short.  The way that SSAS presents the member's path has the full, standardized, MDX-style description.

This is how Cognos sees the members in the dimensions:

Member Unique Name from our SSAS Cube (269 chars):
[Operational Summary Cube].[OP SUM Chart Of Accounts].[OP SUM Chart Of Accounts].[Opsum Rollup]->:[M12].[[OP SUM Chart Of Accounts]].[OP SUM Chart Of Accounts]].[Income Stmt-Stats Rollup]].&[Income Statement]].&[Operating Revenues:]].&[Gross Patient Service Revenue]]]

The same item from an old IBM PowerCube Member Unique Name (165 chars):
[ds_Operational Summary - Ad Hoc Cube -Proj_201302_AttributeDashboard].[OP SUM CHART OF ACCOUNTS].[OP SUM CHART OF ACCOUNTS].[Opsum Rollup]->:[PC].[@MEMBER].[10000]

You can see that the equivalent path with SSAS as the data source is longer than 255 characters.  So trying to use the "Convert to Formulas" function breaks.

I tried using the Name and Value properties for the various members used in the SSAS dimension with the hopes of getting the above path to look something like this:

[Operational Summary Cube].[OP SUM Chart Of Accounts].[OP SUM Chart Of Accounts].[Opsum Rollup]->:[M12].[[OP SUM Chart Of Accounts]].[OP SUM Chart Of Accounts]].[10]].&[100]].&[120]].&[10000]]]

I tired using our other key columns for "Name" and then for the "Value" properties. I was able to get the visual display of the member to change from the nice description to the numeric value (which we don't want), but it in both cases, Cognos still referenced the full path.

Is there some other property we can leverage to shorten how the path to the member is represented?  Has anybody else dealt with this?