Hi,

My previous message was a bit confusing - I hope this
simplifies our question a bit.

According to our research, we have found several
inconsistencies between the way the MS OLAP provider
handles result cube data within an ADODB recordset and an
ADOMD cellset. For example the following MDX query
generates slightly different data using ADODB versus
ADOMD:

With
MEMBER [Marital Status].[All Members] AS' aggregate
({[Marital Status].members})'
SELECT
{[Gender].[All Gender]} ON COLUMNS,
({[Store Type].[All Store Type]} * {[Time].[1997],[Time].
[1997].[Q1]} * {[Marital Status].allmembers}) ON ROWS
FROM [Sales]

When a user runs the above MDX query using ADOMD (using
the MDX Sample Application), it returns all the
dimensional metadata on the ROW AXIS INCLUDING THE
MEMBERS FROM THE "ALL" LEVEL. A user could retrieve ADOMD
cellset data from the above query in the following manner:

All Gender
All Store Type 1997 All Marital Status 266,773
All Store Type 1997 M 131,796
All Store Type 1997 S 134,977
All Store Type 1997 All Members 533,546
All Store Type Q1 All Marital Status 266,773
All Store Type Q1 M 131,796
All Store Type Q1 S 134,977
All Store Type Q1 All Members 533,546

Yet, when the same query is run using ADODB, the MDX
query fails to return any "ALL" level metadata for real
or calculated members on the ROW AXIS. In the above query
this means that members on the "ALL" level in the Store
Type and Marital Status dimensions are simply not
included. Thus we lose caption information for all
members (real or calculated) on the "ALL" level on the
ROW AXIS (note this does not affect "ALL" level on the
column axis).

Using ADODB the above MDX query returns the following
data (excluding level and column information):

1997 266,773
1997 M 131,796
1997 S 134,977
1997 533,546
Q1 266,773
Q1 M 131,796
Q1 S 134,977
Q1 533,546

As one can see we are missing the "ALL" levels (and any
members on these levels) for the Store Type and Marital
Status dimensions. In addition, from our research, it
appears that an ADODB recordset DOES NOT support any MDX
format properties.

Is there a solution to the above problems? Are we
overlooking any other significant MDX limitations related
to ADODB recordsets? Most importantly, does anybody know
whether Microsoft is committed to fixing these ADODB
versus ADOMD inconsistencies?

ANY HELP WOULD BE GREATLY APPRECIATED!

Thanks,

Raj C.

Irina and Mosha,

THANK YOU so much for replying to our posting. We
certainly appreciate the time you have taken to educate
us.

I now understand that the ADODB flattened rowset behavior
is by design. But it seems to me that this design
principle - from a users' perspective - is fundamentally
flawed.

Not allowing users to retrieve member information at the
(ALL) level from a recordset grossly inconveniences
certain users. Although our internal support team can
(with some advanced programming techniques) derive the
(ALL) level member information, they can only do this
when there are no calculated members present at the (ALL)
level (no other memebers at the (All) level besides
the "All Member").

If there are, then there is no feasible way that we know
of to retrieve (ALL) level calculated member information
other than to prevent users from creating calculated
members at the (All) level and/or requiring them to
define the calculated members as children of the (All) or
lower levels (we call this the "Big Brother" approach).

Also, as I mentioned earlier, ADODB doesn't seem to
support MDX format properties.

3 qustions for either or both of you:

1) Is there any creative way other than banning creation
of calculated members at the (All) level that we can
access (All) level calculated member information from
recordsets.

2) Is there a way we can use an ADODB recordset to access
MDX format properties.

3) In your opinion, are there any other significant
problems with the ADODB recordset that might lead us to
use a cellset rather than a recordset (currently, we use
a recordset because it can and often is significantly
faster than a cellset).

Thanks again for all your help,

Raj C.

Irina and Mosha,

THANK YOU so much for replying to our posting. We
certainly appreciate the time you have taken to educate
us.

I now understand that the ADODB flattened rowset behavior
is by design. But it seems to me that this design
principle - from a users' perspective - is fundamentally
flawed.

Not allowing users to retrieve member information at the
(ALL) level from a recordset grossly inconveniences
certain users. Although our internal support team can
(with some advanced programming techniques) derive the
(ALL) level member information, they can only do this
when there are no calculated members present at the (ALL)
level (no other memebers at the (All) level besides
the "All Member").

If there are, then there is no feasible way that we know
of to retrieve (ALL) level calculated member information
other than to prevent users from creating calculated
members at the (All) level and/or requiring them to
define the calculated members as children of the (All) or
lower levels (we call this the "Big Brother" approach).

Also, as I mentioned earlier, ADODB doesn't seem to
support MDX format properties.

3 qustions for either or both of you:

1) Is there any creative way other than banning creation
of calculated members at the (All) level that we can
access (All) level calculated member information from
recordsets.

2) Is there a way we can use an ADODB recordset to access
MDX format properties.

3) In your opinion, are there any other significant
problems with the ADODB recordset that might lead us to
use a cellset rather than a recordset (currently, we use
a recordset because it can and often is significantly
faster than a cellset).

Thanks again for all your help,

Raj C.

I cannot think of one.

No. But you raise valid points. We will consider your suggestions for
improvements of flattened rowset for the next release (Yukon). Of course we
cannot promise you anything, but you identified real issues which we will
look into.

If the only reason you use recordset is because you beleive it is faster
then cellset - you should use cellset.
From my experience, the performance of two is very close, but cellset gives
you much richer true multidimensional model.

HTH,
Mosha

--
==================================================
Mosha Pasumansky - www.mosha.com/msolap
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================