Michael Mukovskiy

Sometimes you need to document your cube structure or pass it to another person for the review or comments. It is not always convenient to use a native AS definition in the form of Visual Studio Project or creation script. Other people can not have Visual Studio installed or may want to add their comments into it.

Fortunately the AS definitions are XML based so we can use XSL transformations to generate documents that can be digested by your favorite XML-speaking tools.

Here we demonstrate the transformation of the AS definition of a multidimensional database (XML/A) into MindMap format (.mm) that can be used for example by FreeMind tool.

The input document containing the AS database can be obtained by building the project in Visual Studio (.asdatabase) of by scripting out the live database in Management Studio (Script Database as->CREATE To->File).

To execute the XSLT you can use XML Notepad, XML tools in Management Studio or even the SSIS XML task or PowerShell for automation.

The simplest XSLT (xmla2mm.xslt) can look like following:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="
http://www.w3.org/1999/XSL/Transform"
xmlns:as="
http://schemas.microsoft.com/analysisservices/2003/engine">
  <xsl:output omit-xml-declaration="yes"/>

  <xsl:template match="/">
    <map version="0.9.0">
      <xsl:apply-templates select="//as:Database"/>
    </map>
  </xsl:template>

  <xsl:template match="as:Database |
                      as:Cubes | as:Cube |
                      as:DataSources | as:DataSource |
                      as:DataSourceViews | as:DataSourceView |
                      as:Roles | as:Role |
                      as:Members | as:Member |
                      as:MeasureGroups | as:MeasureGroup |
                      as:Dimensions | as:Dimension |
                      as:Attributes | as:Attribute |
                      as:Hierarchies | as:Hierarchy |
                      as:Levels | as:Level |
                      as:Measures | as:Measure |
                      as:Partitions | as:Partition">
    <node CREATED="1" ID="ID1" MODIFIED="1">
      <xsl:attribute name="TEXT">
        <xsl:if test="as:Name">
          <xsl:value-of select="as:Name"/>
        </xsl:if>
        <xsl:if test="not(as:Name)">
          <xsl:value-of select="name()"/>
        </xsl:if>
      </xsl:attribute>
      <xsl:if test="name()!=’Database’">
        <xsl:attribute name="FOLDED">true</xsl:attribute>
      </xsl:if>
      <xsl:apply-templates />
    </node>
  </xsl:template>

  <xsl:template match="*">
  </xsl:template>

</xsl:stylesheet>

Here is what the transformation does:

  • produces a .mm tree structure which exactly mimics the object structure of the AS database for the certain subset of node types (see enumeration under …match=”…”)
  • the Name-elements of AS objects are taken as MM node names unless Name is not found (in this case the object type is used, for example “Dimensions”, see xsl:attribute name="TEXT"…)
  • only the root node is unfolded (see “xsl:if test="name()!=’Database’"”) 

 

The beginning of the result document looks like following (Adventure Works):

<map version="0.9.0" xmlns:as="http://schemas.microsoft.com/analysisservices/2003/engine">
  <node CREATED="1" ID="ID1" MODIFIED="1" TEXT="Adventure Works DW 2008R2">
    <node CREATED="1" ID="ID1" MODIFIED="1" TEXT="Dimensions" FOLDED="true">
      <node CREATED="1" ID="ID1" MODIFIED="1" TEXT="Promotion" FOLDED="true">
        <node CREATED="1" ID="ID1" MODIFIED="1" TEXT="Attributes" FOLDED="true">
          <node CREATED="1" ID="ID1" MODIFIED="1" TEXT="Promotion" FOLDED="true" />
          <node CREATED="1" ID="ID1" MODIFIED="1" TEXT="Discount Percent" FOLDED="true" />
          <node CREATED="1" ID="ID1" MODIFIED="1" TEXT="Max Quantity" FOLDED="true" />
          <node CREATED="1" ID="ID1" MODIFIED="1" TEXT="Promotion Type" FOLDED="true" />
          <node CREATED="1" ID="ID1" MODIFIED="1" TEXT="Min Quantity" FOLDED="true" />
          <node CREATED="1" ID="ID1" MODIFIED="1" TEXT="Promotion Category" FOLDED="true" />
          <node CREATED="1" ID="ID1" MODIFIED="1" TEXT="End Date" FOLDED="true" />
          <node CREATED="1" ID="ID1" MODIFIED="1" TEXT="Start Date" FOLDED="true" />

Here is how it looks in FreeMind:

image 

We intentionally put only the minimal logic into transformation so you can spice it up with your own features like:

  • custom logic for different AS object by using more specific elements of xsl:template
  • icons related to AS object types (example: <icon BUILTIN="gohome"/>)
  • node colors or font types
  • more AS object types (add to enumeration or place as another xsl:template)
  • AS features as MM attributes (additional text info)

To see how the MM document should look like just browse the file having the features you are interested in or have a look at the description for example here.

The basic info how the XSL transformations work can be found here.

See also other topics of using XSL transformation on AS definitions: