ADODB Recordset Limitations

[RajChinna]

Hi,

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 inconsistencies?

ANY HELP WOULD BE GREATLY APPRECIATED!

Thanks,

Raj C.

 

[MDXer]

The data is right the only issue you are having is that it fails to include the ancestors of the level being reported?

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[RajChinna]

No actaully the ADODB recordset DOES NOT return the correct results. In fact, the recordset DOES NOT return any data from the "ALL" level on any dimension in the row axis.

Thus, if one were to construct the following MDX query:

SELECT
{[Measures].[Unit Sales]} ON COLUMNS,
({[Gender].[All Gender]} * {[Time].[1997]}) ON ROWS
FROM [Sales]

The ADODB recordset would not return any metatdata for the entire Gender dimension which in this case includes the "All Gender" member on the "ALL" level of the Gender dimension. ADOMD DOES return this information.

The recordset would look as follows (excluding column and level data):

1997 266,773

rather than:

All Gender 1997 266,773

Thus we are missing critical member caption information in the ADODB recordset.

Hope this makes sense,

RAJ