Subject: OLAP MDX: SetToStr function: limitations reached.

Hi all,

Products:
Server:Microsoft SQLServer 2000 SP3a Analysis Services.
Client: OLE-DB for OLAP driver as included in the MDAC V2.8 package.

We have some questions about the MDX function SetToStr():
1. It seems that the SetToStr function in MDX has some limitations
which are unfamiliair to me,
nor can we find technical documentation that overrules/confirms this.
The SetToStr function output seems to be limited to ***32767***
characters.

1.a. Can someone confirm / deny this statement?

1.b. Where can we obtain official technical documentation which
reveals more technical details of the interface
to this function (the MSDN page is rather generic)?

1.c. Can you advise alternative solutions for the problem?

2. Alternatively, maybe can we use the SetToArray function to return
an array containing the **UniqueNames** of the members in my set?
2.a. Is this possible?

3. Upgrading to Yukon?
3.a. Does Yukon have a higher SetToStr() capacity than SQL2K?


Your advise if greatly appreciated.


An in-depth explanation can be found below:
I have a rather complex "Geography" dimension. Actually, it is (and
business-wise, it HAS to be) a mix of team-structure
and geography.
The Levels are:
- Country
- Business Unit
- Sales Force
- Region
- Territory
- Geographic Sales Area (GSA).

In this setup, each GSA can occur multiple times under a Business Unit
or
Sales Force, each time with another UniqueName.

If I want to display aggregated sales data for a Business Unit, this
results
in double-countings.

We solved this by creating a calculated member, passing all
Descendants of
the Business Unit to an External Function that makes the GSA-unique.

MDX:
IIF(
[Geography].CurrentMember.Level.Ordinal <
[Geography].[Region].Ordinal,
SUM({
StrToSet(
MakeUnique(
SetToStr(
DESCENDANTS(
[GEOGRAPHY].CurrentMember, [Geography].[GSA]
)
)
)
)},
[Measures].[External Sales Quantity Base]
),
[Measures].[External Sales Quantity Base]
)


Logic of the External Function MakeUnique :
Incoming String:
{[Geography].[BU01].&[SF0101].&[RegionA].&[TerrA01].&[001],
[Geography].[BU01].&[SF0101].&[RegionA].&[TerrA01].&[002],
[Geography].[BU01].&[SF0102].&[RegionA].&[TerrA01].&[002]}
will return:
{[Geography].&[001], [Geography].&[002]}


This construction works fine for a "small" country like Monaco,
but... it seems the SetToStr function is limited to 32767 characters,
which gives
me a problem for larger countries like United Kingdom.

Therefore, I created a second MakeUnique UDF that takes an Array as
input and basically
does the same thing. But I couldn't get the SetToArray function to put
the UniqueNames of the
members in my set into the array.


P.S.: Georges Spofford's book on MDX Solutions says "SetToArray (...)
will in fact
create sets of strings as well, which makes it useful for scanning
sets of
member names and property values as well as for processing a set of
numbers." (p.166).


Your advise if greatly appreciated.


Please advise.
Season's Greetings
Rolf H.