Search This Blog

Loading...

Monday, December 5, 2011

Limitations with the use of attribute qualifications with regards to Multisourcing against MDX data sources in MicroStrategy 9.2.x


Introduction:
When creating an MDX report, it is not possible to create a report filter based on an attribute description (DESC) form. Only a non-DESC form can be used in an attribute qualification, as shown below.



Qualifications against MDX data sources using DESC forms result in an error from the data source. Therefore, in MicroStrategy Desktop 8.x. - 9.x., the use of DESC forms in an attribute qualification is disabled, when creating MDX data source reports.



Limitation with Attribute qualifications in reports using Multisourcing against MDX data sources:

In reports using Multisourcing against MDX datasources, it is not possible to have an attribute qualification based on an attribute's DESC form and using a function that is supported by the MDX datasource. It is only possible to have an attribute qualification using the DESC form when using a function that is NOT SUPPORTED by the MDX datasource. This is demonstrated with an example below based on the MicroStrategy Tutorial project.
  1. Consider a MDX source which brings in data on regions and cost. The Region attribute from the MDX datasource is mapped to the 'region' attribute from the MicroStrategy Tutorial project.
  2. Create a new report (New report > general) and add the attribute 'Region', and the metric 'Cost' from the MDX datasource. Also, add an attribute qualification as: 'Region (DESC) Exactly South', as shown below.
  3. Executing this report will result in the following error:
    Sample Code/Error
    Error: SQL Generation Complete QueryEngine encountered error: ExecAxisInfo failed. MDX Result read failed.
    Error type: JCO layer error. JCOResult.fetchAxisInfo failed. java.lang.Exception: [JCO] Error during fetching the AxisInfo Result Set
    [JCO] Error during Bapi Execution: Invalid MDX command with <internal>
    at com.microstrategy.Database.JCO.JCOResult2.fetchAxisInfo(Unknown Source)
    . Connection String: N/A;.....

  4. Edit the report and modify the attribute qualification to: 'Region (DESC) begins with South', as shown below.


  5. The edited report will run successfully.
Cause for the above behavior:
In normal reports created from MicroStrategy Desktop 9.2.x, there is no restriction on the attribute forms that can be used for attribute qualifications. Therefore it is possible to create attribute qualification based on an attribute DESC form even when if the report is running against a MDX datasource. When such a qualification is created using a function which is supported by the MDX datasource, the report from Step 2 is run only against the MDX datasource (as we have one datasource which meets all the requirements for running this report). However, the MDX datasource does not support such DESC form based qualifications and results in the error.

After editing the report filter to use an attribute qualification based on an attribute DESC form, but using a function that is not supported by MDX, the report is executed against the MDX datasource to get the data and then the filter qualification is applied on the relational database. The SQL view of the report from Step 4 is shown below. The data which is retrieved from the MDX data source is inserted in a temporary table on the relational database and then the attribute qualification is applied.

Sample Code/Error
[DB Instance: SAP BW 7]
with set [dim0_select_members] as '{[SH_EMP                        GEO_HIER].[LEVEL01].members}'
select {[Measures].[SH_REV]} on columns,
non empty [dim0_select_members] dimension properties [SH_EMP].[2SH_EMP] on rows
from [$QE_TUTO]
cell properties CELL_ORDINAL, FORMATTED_VALUE, VALUE, FORMAT_STRING

[DB Instance: Tutorial Data]
create table ZZOP00 (
 REGION_ID SHORT,
 REGION_NAME0 TEXT(50),
 WJXBFS1 TEXT(200))

[DB Instance: Tutorial Data]
insert into ZZOP00 values ([Analytical Engine Results: REGION_ID, REGION_NAME0, WJXBFS1])
[DB Instance: Tutorial Data]
select pa11.[REGION_ID] AS REGION_ID,
 pa11.[REGION_NAME0] AS REGION_NAME0,
 pa11.[WJXBFS1] AS WJXBFS1
from [ZZOP00] pa11
where pa11.[REGION_NAME0] like 'South%'

[DB Instance: Tutorial Data]
drop table ZZOP00

Tn Key: 36863
Source


No comments:

Post a Comment

PDF Man