Hi,

I've encountered a situation whereby Microsofts' concept of 'Role Playing
Dimensions' in AS2005 appears to be rendered useless by lack of naming
control from client applications:

In AS2005 I have a dimension called Date. It has 2 hierarchies called 'yqmd'
and 'ywd' respectively. I have 2 cubes:

In 'cube 1' I attach the Date dimension twice, to different key fields
(called Maturity Date and Valuation Date). Under the 'Dimension Usage' tab
in visual studio I have renamed each instance of the Date dimension to its
correct logical name (Maturity Date and Valuation Date). When I look at this
in Excel 2003 I see 4 hierarchies appearing labelled 'Maturity
Date.yqmd','Maturity Date.ywd','Valuation Date.yqmd', 'Valuation Date.ywd'.
That's fine.

In 'cube 2' I only have Maturity Date, so I attach the Date dimension once
and rename it to 'Maturity Date' under the 'Dimension Usage' tab in visual
studio. However, when I open the cube Excel 2003 I see 2 hierarchies labelled
'yqmd' and 'ywd'. This is useless because the user doesn't know what
dimension is being referred to. Excel (and ProClarity does the same) has
decided only to show the hierarchy names if the dimension appears only once
in the cube.

I want to force Excel to append the dimension name to the hierarchy even
when the dimension only appears once in a cube, but no amount of tweaking the
HierarchyUniqueNameStyle property makes any difference. I can't rename the
hierarchy to something specific to one dimension instance or it won't make
sense when it appears more than once in the cube. It seems ridiculous that I
should have to create a second dimension to achieve what I want, since role
playing dimensions were designed (I believe) to avoid this.

Any ideas ?

You could try the Excel 2002/2003 Add-in for SQL Server Analysis
Services - version 1.5 has support for AS 2005-style hierarchies, as
does the latest version of Proclarity.

http://www.microsoft.com/downloads/d...AE82128-9F21-4
75D-88A4-4B6E6C069FF0&displaylang=en


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Deepak,

That didn't really help. Both Excel 2003 (using OLE DB for Analysis Services
9.0) and ProClarity 6.1 (the latest version) both 'handle' hierarchies. My
problem is how to get them to use dimension name with the hierarchy name even
when the dimension only occurs once in the cube, instead of ignoring the
dimension name. I'd like to find out whether I can change this behaviour in
the AS db or as a connection string parameter etc.


Steve Green




"Deepak Puri" wrote:

Steve,

Maybe there's some confusion, but I was actually alluding to "Excel
2002/2003 Add-in for SQL Server Analysis
Services Version 1.5", not base Excel 2003 functionality. I have the
Add-In open now, pointing to the Adventure Works cube, and Date (the
original dimension) is listed with its hierarchies, in addition to
role-playing dimensions like Delivery Date and Ship Date.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Deepak,

I'm aware of the tool you mention and that it does correctly display
dimension and hierarchy names. This confirms that the problem is with the
client tool (Excel 2003) rather than any server configuration. However, the
add-in is not a practical solution because it would need to be distributed to
many of our customers internationally, which is a problem in itself. Also,
this add-in tool is not as easy to use as the standard functionality in Excel
2003, which my current user base are used-to.

I'm after a solution for Excel 2003 (because it will be a long time before
the 2007 product arrives and gets adopted in many companies) and it seems
that the only option is to create 2 dimensions, instead of using MS's idea of
'Role Playing' dimensions which are not named correctly when seen in Excel.

Therefore I maintain that Excel 2003 does not offer complete support for
AS2005 OLAP databases.

If you've any other information as to how to handle the issue within Excel
2003, I'd be pleased to hear about it.

Steve Green




"Deepak Puri" wrote: