[Mondrian] Re: ommission in Mondrian XMLA MDSCHEMA_LEVELS rowset? (was xmla4js plugin)

James Dixon jdixon at pentaho.com
Wed Jan 5 09:32:10 EST 2011


Thanks Roland.

Its not a huge problem for me. I'm trying to create a tabular dataset from
an MDX query so knowing the data types (string, numeric etc) of the members
would help. But its not a show stopper.


On 1/5/11 5:36 AM, "Roland Bouman" <roland.bouman at gmail.com> wrote:

> Hi Julian, James, all,
> 
> thanks for the swift reply!
> 
> Yesterday, I've amended the Xmla4Js schema rowset example to highlight
> any deviations from the standard.
> This is committed in revision r83.
> 
> For now, this only checks the result sets for MDP providers. I noticed
> mondrian supports a few more, and maybe I'll add checks for those
> later on.
> The results I got are listed below.
> 
> I personally haven't analyzed the missing parts (nor the extra's :) in
> enough detail to justify any feature requests or bug reports.
> I also do not want to suggest Mondrian should follow the XML/A
> standard just for completeness sake (although I can understand it if
> somebody feels different about this matter)
> So, the results are just meant as a FYI to whomever it may concern.
> 
> James, perhaps you can use these results and estimate if this will be
> a hurdle in completing the project you're currently working on.
> (And of course, if you find any, file appropriate Jira cases for those)
> 
> DISCOVER_SCHEMA_ROWSETS:
> ==========================
> This rowset has these non-standard fields:
> * SchemaGuid
> 
> MDSCHEMA_ACTIONS:
> ==================
> This rowset omits these fields:
> * ACTION_TYPE
> * ACTION_CAPTION
> * DESCRIPTION
> * CONTENT
> * APPLICATION
> * INVOCATION
> 
> MDSCHEMA_CUBES
> ================
> This rowset omits these fields:
> * LAST_UPDATED_BY
> * CUBE_CAPTION
> * BASE_CUBE_NAME
> * CUBE_SOURCE
> * PREFERRED_QUERY_PATTERNS
> 
> This rowset has these non-standard fields:
> * DIMENSIONS
> * SETS
> * MEASURES
> 
> MDSCHEMA_DIMENSIONS
> ====================
> This rowset omits these fields:
> * DIMENSION_MASTER_NAME
> 
> This rowset has these non-standard fields:
> * DIMENSION_MASTER_UNIQUE_NAME
> * DIMENSION_IS_VISIBLE
> * HIERARCHIES
> 
> MDSCHEMA_HIERARCHIES
> =====================
> This rowset omits these fields:
> * DIMENSION_MASTER_UNIQUE_NAME
> * HIERARCHY_IS_VISIBLE
> * HIERARCHY_ORIGIN
> * HIERARCHY_DISPLAY_FOLDER
> * INSTANCE_SELECTION
> 
> This rowset has these non-standard fields:
> * PARENT_CHILD
> * LEVELS
> 
> MDSCHEMA_LEVELS
> =================
> This rowset omits these fields:
> * LEVEL_ORDERING_PROPERTY
> * LEVEL_DBTYPE
> * LEVEL_MASTER_UNIQUE_NAME
> * LEVEL_NAME_SQL_COLUMN_NAME
> * LEVEL_KEY_SQL_COLUMN_NAME
> * LEVEL_UNIQUE_NAME_SQL_COLUMN_NAME
> * LEVEL_ATTRIBUTE_HIERARCHY_NAME
> * LEVEL_KEY_CARDINALITY
> * LEVEL_ORIGIN
> 
> This rowset has these non-standard fields:
> * LEVEL_UNIQUE_SETTINGS
> 
> MDSCHEMA_MEASURES
> ====================
> This rowset omits these fields:
> * NUMERIC_PRECISION
> * NUMERIC_SCALE
> * MEASURE_UNITS
> * EXPRESSION
> * MEASURE_NAME_SQL_COLUMN_NAME
> * MEASURE_UNQUALIFIED_CAPTION
> * MEASUREGROUP_NAME
> * MEASURE_DISPLAY_FOLDER
> 
> MDSCHEMA_MEMBERS
> ===================
> This rowset omits these fields:
> * DESCRIPTION
> * EXPRESSOIN
> * MEMBER_KEY
> * IS_MEMBER_KEY
> * IS_PLACEHOLDERMEMBER
> * IS_DATAMEMBER
> * SCOPE
> 
> This rowset has these non-standard fields:
> * TREE_OP
> * DEPTH
> 
> MDSCHEMA_PROPERTIES
> =====================
> This rowset omits these fields:
> * CHARACTER_MAXIMUM_LENGTH
> * CHARACTER_OCTET_LENGTH
> * NUMERIC_PRECISION
> * NUMERIC_SCALE
> * SQL_COLUMN_NAME
> * LANGUAGE
> * PROPERTY_ORIGIN
> * PROPERTY_ATTRIBUTE_HIERARCHY_NAME
> * PROPERTY_CARDINALITY
> * PROPERTY_MIME_TYPE
> * PROPERTY_IS_VISIBLE
> 
> MDSCHEMA_SETS
> ===============
> This rowset omits these fields:
> * DESCRIPTION
> * EXPRESSOIN
> * DIMENSIONS
> * SET_CAPTION
> * SET_DISPLAY_FOLDER
> * SET_EVALUATION_CONTEXT
> 
> 
> On Tue, Jan 4, 2011 at 6:43 PM, Julian Hyde <jhyde at pentaho.com> wrote:
>> Roland, James,
>> 
>> We don't support LEVEL_DBTYPE, but we could. Please log a jira case.
>> 
>> FYI, From the code (RowsetDefinition.java), here is a list of XMLA
>> properties that I listed 'not supported' when I first wrote the XMLA
>> handler:
>> 
>>     * Not supported
>>     *  CUSTOM_ROLLUP_SETTINGS
>>     *  LEVEL_UNIQUE_SETTINGS
>>     *  LEVEL_ORDERING_PROPERTY
>>     *  LEVEL_DBTYPE
>>     *  LEVEL_MASTER_UNIQUE_NAME
>>     *  LEVEL_NAME_SQL_COLUMN_NAME Customers:(All)!NAME
>>     *  LEVEL_KEY_SQL_COLUMN_NAME Customers:(All)!KEY
>>     *  LEVEL_UNIQUE_NAME_SQL_COLUMN_NAME Customers:(All)!UNIQUE_NAME
>>     *  LEVEL_ATTRIBUTE_HIERARCHY_NAME
>>     *  LEVEL_KEY_CARDINALITY
>>     *  LEVEL_ORIGIN
>> 
>> Julian
>> 
>>> -----Original Message-----
>>> From: Roland Bouman [mailto:roland.bouman at gmail.com]
>>> Sent: Monday, January 03, 2011 1:07 PM
>>> To: James Dixon; Julian Hyde
>>> Subject: ommission in Mondrian XMLA MDSCHEMA_LEVELS rowset?
>>> (was xmla4js plugin)
>>> 
>>> Hi James, Julian,
>>> 
>>> Julian - FYI. James hit a bug using Xmla4js. I think there's an
>>> omission in the MDSCHEMA_LEVELS rowset. Read for more details.
>>> 
>>> On Mon, Jan 3, 2011 at 6:10 PM, James Dixon
>>> <jdixon at pentaho.com> wrote:
>>>> Hi Roland,
>>>> 
>>>> I am getting on well.
>>> 
>>> Cool, great to hear that!
>>> 
>>>> I'm seeing an error getting the level DB type. It throws an
>>> INVALID_FIELD
>>>> error
>>>> 
>>>> Here's my code
>>> ...
>>>>            level.type = rowset.fieldVal("LEVEL_DBTYPE");
>>> 
>>> Is it this line? I checked some of MS's resources and there
>>> LEVEL_DBTYPE column seems to appear in the MDSCHEMA_LEVELS rowset in
>>> all versions of MSAS (at least, 2000, 2005 and 2008).
>>> But mondrian doesn't seem to deliver it - here's a fragment of the XML
>>> Schema complextype for rows I get for this rowset (as returned by
>>> Mondrian):
>>> 
>>>   <xsd:complexType name="row">
>>>           <xsd:sequence>
>>>             <xsd:element sql:field="CATALOG_NAME" name="CATALOG_NAME"
>>> type="xsd:string" minOccurs="0"/>
>>>             <xsd:element sql:field="SCHEMA_NAME" name="SCHEMA_NAME"
>>> type="xsd:string" minOccurs="0"/>
>>>             <xsd:element sql:field="CUBE_NAME" name="CUBE_NAME"
>>> type="xsd:string"/>
>>>             <xsd:element sql:field="DIMENSION_UNIQUE_NAME"
>>> name="DIMENSION_UNIQUE_NAME" type="xsd:string"/>
>>>             <xsd:element sql:field="HIERARCHY_UNIQUE_NAME"
>>> name="HIERARCHY_UNIQUE_NAME" type="xsd:string"/>
>>>             <xsd:element sql:field="LEVEL_NAME" name="LEVEL_NAME"
>>> type="xsd:string"/>
>>>             <xsd:element sql:field="LEVEL_UNIQUE_NAME"
>>> name="LEVEL_UNIQUE_NAME" type="xsd:string"/>
>>>             <xsd:element sql:field="LEVEL_GUID" name="LEVEL_GUID"
>>> type="uuid" minOccurs="0"/>
>>>             <xsd:element sql:field="LEVEL_CAPTION"
>>> name="LEVEL_CAPTION" type="xsd:string"/>
>>>             <xsd:element sql:field="LEVEL_NUMBER" name="LEVEL_NUMBER"
>>> type="xsd:unsignedInt"/>
>>>             <xsd:element sql:field="LEVEL_CARDINALITY"
>>> name="LEVEL_CARDINALITY" type="xsd:unsignedInt"/>
>>>             <xsd:element sql:field="LEVEL_TYPE" name="LEVEL_TYPE"
>>> type="xsd:int"/>
>>>             <xsd:element sql:field="CUSTOM_ROLLUP_SETTINGS"
>>> name="CUSTOM_ROLLUP_SETTINGS" type="xsd:int"/>
>>>             <xsd:element sql:field="LEVEL_UNIQUE_SETTINGS"
>>> name="LEVEL_UNIQUE_SETTINGS" type="xsd:int"/>
>>>             <xsd:element sql:field="LEVEL_IS_VISIBLE"
>>> name="LEVEL_IS_VISIBLE" type="xsd:boolean"/>
>>>             <xsd:element sql:field="DESCRIPTION" name="DESCRIPTION"
>>> type="xsd:string" minOccurs="0"/>
>>>           </xsd:sequence>
>>>         </xsd:complexType>
>>> 
>>> And the data too, matches that complexType:
>>> 
>>>       <row>
>>>         <CATALOG_NAME>SampleData</CATALOG_NAME>
>>>         <SCHEMA_NAME>SampleData</SCHEMA_NAME>
>>>         <CUBE_NAME>Quadrant Analysis</CUBE_NAME>
>>>         <DIMENSION_UNIQUE_NAME>[Department]</DIMENSION_UNIQUE_NAME>
>>>         <HIERARCHY_UNIQUE_NAME>[Department]</HIERARCHY_UNIQUE_NAME>
>>>         <LEVEL_NAME>(All)</LEVEL_NAME>
>>>         <LEVEL_UNIQUE_NAME>[Department].[(All)]</LEVEL_UNIQUE_NAME>
>>>         <LEVEL_CAPTION>(All)</LEVEL_CAPTION>
>>>         <LEVEL_NUMBER>0</LEVEL_NUMBER>
>>>         <LEVEL_CARDINALITY>1</LEVEL_CARDINALITY>
>>>         <LEVEL_TYPE>1</LEVEL_TYPE>
>>>         <CUSTOM_ROLLUP_SETTINGS>0</CUSTOM_ROLLUP_SETTINGS>
>>>         <LEVEL_UNIQUE_SETTINGS>3</LEVEL_UNIQUE_SETTINGS>
>>>         <LEVEL_IS_VISIBLE>true</LEVEL_IS_VISIBLE>
>>>         <DESCRIPTION>Quadrant Analysis Cube - Department Hierarchy -
>>> (All) Level</DESCRIPTION>
>>>       </row>
>>> 
>>> So my conclusion for now is that this is an omission in Mondrian or in
>>> the XmlaServlet for Mondrian.
>>> (There are quite a bit ot other columns missing that could be
>>> useful IMO)
>>> 
>>> Here's a link to the MS documentation concerning this rowset:
>>> 
>>> http://msdn.microsoft.com/en-us/library/ee301892.aspx
>>> 
>>>>            level.parent = hierarchy;
>>>>            hierarchy.addChild( level );
>>>>            model.addElement( level );
>>>> 
>>>>            if( dimension.isMeasures ) {
>>>>            // now get the measures
>>>> //                this.discoverMembers( model, dimension,
>>> hierarchy, level
>>>> );
>>>>            }
>>>> 
>>>>            rowset.next();
>>>>        }
>>>> 
>>>> Also I found an issue if any of the restrictions are
>>> numeric values (like
>>>> LEVEL_NUMBER). I fixed that one with this change to
>>> _xmlEncodeListEntry
>>>> 
>>>> function _xmlEncodeListEntry(value){
>>>>    if( typeof value == "string" ) {
>>>>        return
>>>> 
>>> value.replace(/\&/g,"&amp;").replace(/</g,"&lt;").replace(/>/g
>>> ,"&gt;");
>>>>    } else {
>>>>        return ""+value;
>>>>    }
>>>> }
>>>> 
>>>> --
>>>> James
>>>> 
>>>> James Dixon - Chief Geek - Pentaho
>>>> email: jdixon at pentaho.com
>>>> blog: http://jamesdixon.wordpress.com
>>>> twitter: http://twitter.com/jamespentaho
>>>> youtube: http://www.youtube.com/user/jamespentaho
>>>> 
>>>> On 12/30/10 2:21 PM, "Roland Bouman"
>>> <roland.bouman at gmail.com> wrote:
>>>> 
>>>>> James, thanks!
>>>>> 
>>>>> I'll get back to you in a few days, hopefully I can find
>>> the problems
>>>>> and fix them.
>>>>> 
>>>>> On Thu, Dec 30, 2010 at 8:19 PM, James Dixon
>>> <jdixon at pentaho.com> wrote:
>>>>>> 
>>>>>> Here you go.
>>>>>> 
>>>>>> This was trivial to turn into a plug-in. I removed all
>>> the discovery
>>>>>> URL/button stuff since we know the relative URL to the
>>> XMLA servlet in this
>>>>>> environment.
>>>>>> 
>>>>>> --
>>>>>> James
>>>>>> 
>>>>>> James Dixon - Chief Geek - Pentaho
>>>>>> email: jdixon at pentaho.com
>>>>>> blog: http://jamesdixon.wordpress.com
>>>>>> twitter: http://twitter.com/jamespentaho
>>>>>> youtube: http://www.youtube.com/user/jamespentaho
>>>>>> 
>>>>> 
>>>>> 
>>>> 
>>>> 
>>> 
>>> 
>>> 
>>> --
>>> Roland Bouman
>>> blog: http://rpbouman.blogspot.com/
>>> twitter: @rolandbouman
>>> 
>>> Author of "Pentaho Solutions: Business Intelligence and Data
>>> Warehousing with Pentaho and MySQL",
>>> http://tinyurl.com/lvxa88 (Wiley, ISBN: 978-0-470-48432-6)
>>> 
>>> Author of "Pentaho Kettle Solutions: Building Open Source ETL
>>> Solutions with Pentaho Data Integration",
>>> http://tinyurl.com/33r7a8m (Wiley, ISBN: 978-0-470-63517-9)
>>> 
>> 
>> 
> 
> 




More information about the Mondrian mailing list