How to find slice values that Analysis Services assigned to the partition dimensions?
User Rating: / 2
PoorBest 
Written by Vidas Matelis   
Monday, 04 February 2008 18:32

Q: How to find slice values that Analysis Services assigned to the partition dimensions?

A: Analysis Services allows you to specify partition slice information, but it also calculates slice values automatically. After partition is processed, Analysis Services creates file with information about that partition. File name is Info.<versionNo>.xml (example: info.2.xml). This file is located in the partition folder. Default location for this folder is:

<drive>:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\<DBID>\<CubeID>\<MeasureGroupID>\<PartitionID>\

In this file for each dimension attribute Analysis Services specifies minimum and maximum DataID value found in that partition. Bellow is example of what you can find in this info file. In this example you can see just info related to Date dimension and partition in sliced on one single month.

 <MapDataIndex>
    <DimensionID>Date</DimensionID>
    <PropertyID>(All)</PropertyID>
    <m_cbOffsetIndex>2305843009213693952</m_cbOffsetIndex>
    <m_MinIndex>1</m_MinIndex>
    <m_MaxIndex>1</m_MaxIndex>
  </MapDataIndex>
  <MapDataIndex>
    <DimensionID>Date</DimensionID>
    <PropertyID>Year Key</PropertyID>
    <m_cbOffsetIndex>0</m_cbOffsetIndex>
    <m_MinIndex>5</m_MinIndex>
    <m_MaxIndex>5</m_MaxIndex>
  </MapDataIndex>
  <MapDataIndex>
    <DimensionID>Date</DimensionID>
    <PropertyID>Quarter Key</PropertyID>
    <m_cbOffsetIndex>0</m_cbOffsetIndex>
    <m_MinIndex>13</m_MinIndex>
    <m_MaxIndex>13</m_MaxIndex>
  </MapDataIndex>
  <MapDataIndex>
    <DimensionID>Date</DimensionID>
    <PropertyID>Month Key</PropertyID>
    <m_cbOffsetIndex>0</m_cbOffsetIndex>
    <m_MinIndex>35</m_MinIndex>
    <m_MaxIndex>35</m_MaxIndex>
  </MapDataIndex>

You can read more about DataID in "Microsoft SQL Server 2005 Analysis Services book", page 350. It kind of represents ordinal attribute key position in the attribute key store. First member based on the same book is allways "Undefined".
In example above Date dimension started with member 2004-12 and partition was for month 2007-09. So we assume following members for each Date attribute:
Year 2004, 2005, 2006, 2007. It is 4th year, because first member is "Undefined", so DataID for 2007 is 5.
Quarter: 2004-4, 2005-1, ... 2007-3, It is 12th quarter, DataID value is 13.
Month: 2004-12, 2005-01... 2007-09. It is 34th month, DataID value is 35.

 

 

 

Pyramid Analytics