Vítejte na blog.vyvojar.cz Přihlásit | Registrovat | Pomoc

Radim.NET

Radim Hampel : Microsoft business intelligence, performance management and data warehousing
Analysis Services partition slice

When you have more than one partition in your cube, you should consider using Slice property(highlight your partition and you will find Slice in its Properties window). Slice information is used at query time, when queried members are sequentially compared against all partition for possible inclusion. This helps to skip partitions which don't keep data for current query.

Slice can be either set by developer or (in case of MOLAP storage) it is recognized by Analysis Services automatically, during partition processing. So it is not necessary to explicitly set Slice partition, you can rely on AS engine, but you have to be aware of consequences. Partition slice is created automatically during processing of each partition for each attribute of related dimensions. For ROLAP partitions you have to specify Slice manually. But how is the automatic slice information retrieved?

Each attribute has internal surrogate identifier, called DataID, which is assigned during dimension processing, it is increasing integer that is assigned in processing order. When Full process of a dimension is executed, DataIDs are reassigned and that's the reason why related partitions/cubes must be re-processed also, because they would contain invalid referencing DataIDs. Because DataIDs are sequential, system can work with ranges of members.  After partition is processed, engine will store information about minimum and maximum DataID. This information is stored in \OLAP\Data\<DBID>\<CubeID>\<MeasureGroupID>\<PartitionID>\Info.<Version>.xml. More about slice storage location can be found in post How to find slice values that Analysis Services assigned to the partition dimensions? On my laptop I choose Adventure Work file "c:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Adventure Works DW.0.db\Adventure Works DW.20.cub\Sales Summary.20.det\Total_Orders_2003.20.prt\info.21.xml" and here are two examples:

<MapDataIndex>
    <DimensionID>Order Date Key - Dim Time</DimensionID>
    <PropertyID>CalendarYear</PropertyID>
    <m_cbOffsetIndex>0</m_cbOffsetIndex>
    <m_MinIndex>4</m_MinIndex>
    <m_MaxIndex>4</m_MaxIndex>
</MapDataIndex>

<MapDataIndex>
    <DimensionID>Order Date Key - Dim Time</DimensionID>
    <PropertyID>CalendarQuarterDesc</PropertyID>
    <m_cbOffsetIndex>0</m_cbOffsetIndex>
    <m_MinIndex>8</m_MinIndex>
    <m_MaxIndex>11</m_MaxIndex>
</MapDataIndex>

These examples show that for partition "Total Orders 2003" there is just one member from CalendarYear (minIndex = maxIndex = 4) with DataID 4, same for CalendarQuarter - four members 8-11.

Based on this information you can rely on AS in case when the attribute members are processed in natural order. For example, Year attribute should be contain members 2000, 2001, 2002, 2003, ... Processing order is not the same as member order and even when you set OrderBy property to Key, DataIDs will be assigned in same sequence as they are supplied from data source. When years would come as "2003, 2002, 2001, 2004" and you would have 2 partitions, 1st for years 2001 and 2002 and 2nd for years 2003 and 2004 second partition would have minIndex=1 and maxIndex=4 ! This would result in unnecessary reads (Chris Webb created a feedback about it, you can vote here). So when your attributes might restrict number of related partitions, but processing order doesn't fit this schema, you should explicitly state partition slice property and don't rely on the engine.

You can use PartitionHealtCheck tool to check distribution of DataIDs in your partitions, it will show you when members are overlapping and potentially requiring more reads than necessary.

Other articles on partition slice:
Partition Slice Impact on Query Performance - SSAS2005
SSAS Partition Slicing
Viewing Partition Slice Information

Zveřejněno Wednesday, April 16, 2008 1:21 PM by radim

Komentář

Žádné komentáře

Nejsou povoleny nové komentáře k tomuto příspěvku