Author Topic: SSAS and Cafe and long member names  (Read 1035 times)

Offline dhandler

  • Full Member
  • ***
  • Join Date: Jun 2013
  • Posts: 5
  • Forum Citizenship: +0/-0
SSAS and Cafe and long member names
« on: 28 Jan 2015 04:13:14 pm »
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?

 


           
Twittear