MDSCHEMA_DIMENSIONS and MDSCHEMA_HIERARCHIES

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

MDSCHEMA_DIMENSIONS and MDSCHEMA_HIERARCHIES

Author

Topic

IFarafontov
Starting Member

1 Post

Posted - 2011-04-12 : 06:47:33

Hello.
I am using Adventure Works DW 2008 database, cube - [Finance].
When executing this query (in order to obtain dimensions list)
<?xml version="1.0" encoding="utf-8"?>
<SOAP-ENV:Envelope
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<RequestType>MDSCHEMA_DIMENSIONS</RequestType>
<Restrictions>
<RestrictionList>
<CATALOG_NAME>Adventure Works DW 2008</CATALOG_NAME>
<CUBE_NAME>Finance</CUBE_NAME>
</RestrictionList>
</Restrictions>
<Properties>
<PropertyList>
<DataSourceInfo>XXX</DataSourceInfo>
<Catalog>Adventure Works DW 2008</Catalog>
<Format>Tabular</Format>
<Content>SchemaData</Content>
</PropertyList>
</Properties>
</Discover>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
i got this in response (i am only interested in [Organization] dimension, so only this row is presented here)

<row>
<CATALOG_NAME>Adventure Works DW 2008</CATALOG_NAME>
<CUBE_NAME>Finance</CUBE_NAME>
<DIMENSION_NAME>Organization</DIMENSION_NAME>
<DIMENSION_UNIQUE_NAME>[Organization]</DIMENSION_UNIQUE_NAME>
<DIMENSION_CAPTION>Organization</DIMENSION_CAPTION>
<DIMENSION_ORDINAL>19</DIMENSION_ORDINAL>
<DIMENSION_TYPE>15</DIMENSION_TYPE>
<DIMENSION_CARDINALITY>15</DIMENSION_CARDINALITY>
<DEFAULT_HIERARCHY>[Organization].[Currency Code]</DEFAULT_HIERARCHY>
<DESCRIPTION />
<IS_VIRTUAL>false</IS_VIRTUAL>
<IS_READWRITE>false</IS_READWRITE>
<DIMENSION_UNIQUE_SETTINGS>1</DIMENSION_UNIQUE_SETTINGS>
<DIMENSION_MASTER_NAME>Organization</DIMENSION_MASTER_NAME>
<DIMENSION_IS_VISIBLE>true</DIMENSION_IS_VISIBLE>
</row>
as you can see - i have [Organization].[Currency Code] under DEFAULT_HIERARCHY. And when i fire MDSCHEMA_HIERARCHIES request, restricted to [Organization] dimension -
<?xml version="1.0" encoding="utf-8"?>
<SOAP-ENV:Envelope
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis"
SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<RequestType>MDSCHEMA_HIERARCHIES</RequestType>
<Restrictions>
<RestrictionList>
<CATALOG_NAME>Adventure Works DW 2008</CATALOG_NAME>
<CUBE_NAME>Finance</CUBE_NAME>
<DIMENSION_UNIQUE_NAME>[Organization]</DIMENSION_UNIQUE_NAME>
</RestrictionList>
</Restrictions>
<Properties>
<PropertyList>
<DataSourceInfo>XXX</DataSourceInfo>
<Catalog>Adventure Works DW 2008</Catalog>
<Format>Tabular</Format>
<Content>SchemaData</Content>
</PropertyList>
</Properties>
</Discover>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
i got this only row in response-
<row>
<CATALOG_NAME>Adventure Works DW 2008</CATALOG_NAME>
<CUBE_NAME>Finance</CUBE_NAME>
<DIMENSION_UNIQUE_NAME>[Organization]</DIMENSION_UNIQUE_NAME>
<HIERARCHY_NAME>Organizations</HIERARCHY_NAME>
<HIERARCHY_UNIQUE_NAME>[Organization].[Organizations]</HIERARCHY_UNIQUE_NAME>
<HIERARCHY_CAPTION>Organizations</HIERARCHY_CAPTION>
<DIMENSION_TYPE>15</DIMENSION_TYPE>
<HIERARCHY_CARDINALITY>20</HIERARCHY_CARDINALITY>
<DEFAULT_MEMBER>[Organization].[Organizations].&[1]</DEFAULT_MEMBER>
<DESCRIPTION />
<STRUCTURE>2</STRUCTURE>
<IS_VIRTUAL>false</IS_VIRTUAL>
<IS_READWRITE>false</IS_READWRITE>
<DIMENSION_UNIQUE_SETTINGS>1</DIMENSION_UNIQUE_SETTINGS>
<DIMENSION_IS_VISIBLE>true</DIMENSION_IS_VISIBLE>
<HIERARCHY_ORDINAL>1</HIERARCHY_ORDINAL>
<DIMENSION_IS_SHARED>true</DIMENSION_IS_SHARED>
<HIERARCHY_IS_VISIBLE>true</HIERARCHY_IS_VISIBLE>
<HIERARCHY_ORIGIN>3</HIERARCHY_ORIGIN>
<HIERARCHY_DISPLAY_FOLDER />
<INSTANCE_SELECTION>1</INSTANCE_SELECTION>
<GROUPING_BEHAVIOR>2</GROUPING_BEHAVIOR>
</row>
So, i have [Organization].[Currency Code] hierarchy in the first case for [Organization] dimension while there is no such thing in the second case (while, to my mind, it should be there, because the dimension is the same as in the first request).

I am new to SSAS and OLAP in general, i have spent three days investigating this case, any help will be greatly appreciated.
p.s i think that the reason is somehow connected with parent-child
attribute hierarchy properties in [Organization] dimension.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources