Michael Mukovskiy

In our recent post “MSAS: XSLT for cube management” we presented a template for the cube management infrastructure based on XSL transformations.

In the first feedback people were asking about modifications that allow producing several <Parallel> blocks in single <Batch> or combining <Parallel> blocks with single commands staying alone. For example you could want to have your fact processing in <Parallel> followed by <ClearCache> in one <Batch>:

<Batch>
   <Parallel>
     <Process>…</Process>
     <Process>…</Process>
   </Parallel>
   <ClearCache>…</ClearCache>
</Batch>

Please note that <ClearCache> must stay directly under <Batch> and cannot be under <Parallel>.

You need <ClearCache> after you process a many-to-many measure group, because of the old AS bug.

Such a modification is not a problem at all! Let’s add a column [Order] into the control table and define the following transformation rules:

  1. Commands having the same [Order] value should be grouped in a separate <Parallel> block
  2. A single command with unique [Order] value should stay without <Parallel>
  3. The [Order] should define the real order in <Batch> 

Now we have a choice of how exactly to implement this feature:

  1. Pass [Order] as an additional element (or attribute) for <ASObject> in XML and do the grouping in XSLT
    or
  2. Do the grouping already in XML (by SQL FOR XML) and translate groups to <Parallel> in XSLT

Let’s say we choose the second option because the first one requires using some tricks with KEY elements and functions in XSLT and we want to keep XSLT as simple as possible.

Now our SQL FOR XML will look like following:

SELECT
[Order] as ‚@Order‘,
CAST((
SELECT distinct
   [ObjectType] ‚@ObjectType‘
  ,[NeedCreate] ‚@NeedCreate‘
  ,[DatabaseID]
  ,[DimensionID]
  ,[CubeID]
  ,[MeasureGroupID]
  ,[PartitionID]
  ,[Parameter_1]
  ,[ActionType]
FROM [dbo].[OLAP_ToDo] t2
WHERE t2.JobType=t1.JobType
       AND (t2.[Order]=t1.[Order] OR (t2.[Order] is null AND t1.[Order] is null))
FOR XML PATH(‚ASObject‘)
) AS XML)
FROM [dbo].[OLAP_ToDo] t1
WHERE JobType=’Regular‘
GROUP BY [Order],[JobType]
ORDER BY [Order]
FOR XML PATH(‚Group‘), ROOT(‚ASObjects‘)

…and will deliver something like this:

<ASObjects>
  <Group Order="1">
    <ASObject ObjectType=…>
      …
    </ASObject>
    <ASObject ObjectType=…>
      …
    </ASObject>
  </Group>
  <Group Order="2">

    <ASObject ObjectType=…>
      …
    </ASObject>
  </Group>
</ASObjects>

The last thing to do is to adjust how we interpret it in XSLTs. Here is for example how the beginning of ProcessFacts.xslt should look like:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output omit-xml-declaration="yes"/>

  <xsl:template match="ASObjects">
    <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
      <xsl:apply-templates/>
    </Batch>
  </xsl:template>

  <xsl:template match="Group">
    <xsl:if test="count(ASObject) &gt; 1">
      <Parallel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
        <xsl:apply-templates/>
      </Parallel>
    </xsl:if>
    <xsl:if test="count(ASObject) = 1">
      <xsl:apply-templates/>
    </xsl:if>
  </xsl:template>
  …
</xsl:stylesheet>

Hope it helps!

P.S. Here is what you need in your ProcessFacts.xslt to produce <ClearCache> (an example for the level of database):


<xsl:template match="ASObject[@ObjectType=’Database‘ and ActionType=’ClearCache‘]">
  <ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Oabject>
      <DatabaseID>
        <xsl:value-of select="DatabaseID"/>
      </DatabaseID>
    </Oabject>
  </ClearCache>
</xsl:template>