XPath and XSLT for the metadata analysis of Analysis Services objects

Technical Value


Analysis Services (starting from version 2005) has its object definitions and communication in XML, thus making reasonable using the XML processing methods at the application side. 

You can consider XML parsing and transformation in situations where you are:

- sending information to AS for:

   - generating batches of CREATE commands for partitions

   - generating batches of PROCESS commands for any objects

   - assembling parameters for MDX-queries

- receiving information from AS for:

   - parsing the results of MDX queries

   - parsing/extracting/analyzing the metadata of AS

Here we consider the analysis of the AS metadata using basic XML methods in situations where it would be impossible to use the text search and could be very tedious doing it by browsing the project in Visual Studio. Such an analysis can be a part of your Quality Assurance infrastructure.

Let’s see how we can implement three following tasks:

1. We want to list all flexible attribute relations in our project. The problem is you cannot just search for something like “FLEXIBLE” in sources, because flexible definition is implemented as the absence (!) of the relation type.

2. We want to find all database dimensions that are not used in any of cubes (“the trash finder”).

3. We want to detect partitions that have no associated aggregation design while having at least one defined in the measure group (someone has forgotten to assign a design while creating a new partition).

As an AS developer you can surely extend this list with problems you got in your practice!

The idea is that such problems can be easily solved by using XPath – a query language for XML. Practically it can be implemented in the form of XSL transformation.

The great source of information about XML technologies for beginners is http://www.w3schools.com/ . You need a day or even less for browsing through samples and references to be ready to implement basic tasks like this!

The AS project consists of several definition files: dimensions, cubes etc. But to be able to query all the objects definitions in one we need them as a single XML document. For this purpose we can use the result of the Build process in Visual Studio – the file .asdatabase (generated in /bin folder of your AS project). Another possibility could be the use of the document delivered by the DISCOVER command for getting the metadata of the AS database.

To run manually the XSLT transformation over AS database definition we use the nice free utility “XML Notepad” (http://xmlnotepad.codeplex.com/ ). While using XML Notepad you can keep your XML and XSLT completely separated – without need to edit the .asdatabase (actually without putting in the reference to your XSLT).

For automated processes you can use “XML Task” in SSIS or directly use XML API in our code.

So let’s write the XPath queries for our problems from above. Here is the implementation for querying the .asdatabase file (you have to change them a little bit for the response of DISCOVER):

1. Flexible attribute relationships:


  1. as:Database/as:Dimensions/as:Dimension/as:Attributes/as:Attribute/as:AttributeRelationships/as:AttributeRelationship[not(as:RelationshipType)]

2. Dimensions not used:


  1. <?xml version="1.0"?><stylesheet version="1.0" xmlns:xsl="
  2. xmlns:as="
  4. http://schemas.microsoft.com/analysisservices/2003/engine"
  5. >   
  7.     <template match="/">      
  13.                 <h2>Dimensions not used in cubes</h2>      
  15.                 <table border="1">      
  17.                     <tr bgcolor="#9acd32">      
  19.                         <th>Name</th>      
  21.                         <th>ID</th>                       
  23.                     </tr>      
  25.                     <for-each select="as:Database/as:Dimensions/as:Dimension[not(ancestor::as:Database/as:Cubes/as:Cube/as:Dimensions/as:Dimension/as:DimensionID/text()=as:ID/text())]">      
  27.                         <tr>      
  29.                             <td>      
  31.                                 <value-of select="as:Name"></value-of>      
  33.                             </td>      
  35.                             <td>      
  37.                                 <value-of select="as:ID"></value-of>      
  39.                             </td>                           
  41.                         </tr>      
  43.                     </for-each>      
  45.                 </table>      
  51.     </template>

3. Partitions without aggregation design:


  1. as:Database/as:Cubes/as:Cube/as:MeasureGroups/as:MeasureGroup/as:Partitions/as:Partition[not(as:AggregationDesignID) and ancestor::as:MeasureGroup/as:AggregationDesigns/as:AggregationDesign]

…where xmlns:as=http://schemas.microsoft.com/analysisservices/2003/engine .

Not much, isn’t it?

Now let’s see how simple XSLT can be implemented:

1. Flexible attribute relationships.


  1. <?xml version="1.0"?>      
  2. <xsl:stylesheet version="1.0"      
  3. xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  4. xmlns:as="http://schemas.microsoft.com/analysisservices/2003/engine">   
  5.     <xsl:template match="/">      
  6.         <html>      
  7.             <body>      
  8.                 <h2>Flexibe Attribute Relationships</h2>      
  9.                 <table border="1">      
  10.                     <tr bgcolor="#9acd32">      
  11.                         <th>Dim</th>      
  12.                         <th>Attribute</th>      
  13.                         <th>Relationship</th>      
  14.                     </tr>      
  15.                     <xsl:for-each select="as:Database/as:Dimensions/as:Dimension/as:Attributes/as:Attribute/as:AttributeRelationships/as:AttributeRelationship[not(as:RelationshipType)]">      
  16.                         <tr>      
  17.                             <td>      
  18.                                 <xsl:value-of select="ancestor::as:Dimension/as:Name"/>      
  19.                             </td>      
  20.                             <td>      
  21.                                 <xsl:value-of select="ancestor::as:Attribute/as:Name"/>      
  22.                             </td>      
  23.                             <td>      
  24.                                 <xsl:value-of select="as:Name"/>      
  25.                             </td>      
  26.                         </tr>      
  27.                     </xsl:for-each>      
  28.                 </table>      
  29.             </body>      
  30.         </html>      
  31.     </xsl:template>      
  32. </xsl:stylesheet>


2. Dimensions not used.

  1. <?xml version="1.0"?><stylesheet version="1.0" xmlns:xsl="
  2. xmlns:as="
  3. http://schemas.microsoft.com/analysisservices/2003/engine"
  4. >  
  5.     <template match="/">      
  8.                 <h2>Dimensions not used in cubes</h2>      
  9.                 <table border="1">      
  10.                     <tr bgcolor="#9acd32">      
  11.                         <th>Name</th>      
  12.                         <th>ID</th>                      
  13.                     </tr>      
  14.                     <for-each select="as:Database/as:Dimensions/as:Dimension[not(ancestor::as:Database/as:Cubes/as:Cube/as:Dimensions/as:Dimension/as:DimensionID/text()=as:ID/text())]">      
  15.                         <tr>      
  16.                             <td>      
  17.                                 <value-of select="as:Name"></value-of>      
  18.                             </td>      
  19.                             <td>      
  20.                                 <value-of select="as:ID"></value-of>      
  21.                             </td>                          
  22.                         </tr>      
  23.                     </for-each>      
  24.                 </table>      
  27.     </template>

3. Partitions without aggregation design.

  1. <?xml version="1.0"?><stylesheet version="1.0" xmlns:xsl="
  2. xmlns:as="
  3. http://schemas.microsoft.com/analysisservices/2003/engine"
  4. >  
  5.     <template match="/">      
  8.                 <h2>Partitions with not assigned aggregation designs</h2>      
  9.                 <table border="1">      
  10.                     <tr bgcolor="#9acd32">      
  11.                         <th>Cube</th>      
  12.                         <th>MeasureGroup</th>      
  13.                         <th>Partition</th>      
  14.                     </tr>      
  15.                     <for-each select="as:Database/as:Cubes/as:Cube/as:MeasureGroups/as:MeasureGroup/as:Partitions/as:Partition[not(as:AggregationDesignID) and ancestor::as:MeasureGroup/as:AggregationDesigns/as:AggregationDesign]">      
  16.                         <tr>      
  17.                             <td>      
  18.                                 <value-of select="ancestor::as:Cube/as:Name"></value-of>      
  19.                             </td>      
  20.                             <td>      
  21.                                 <value-of select="ancestor::as:MeasureGroup/as:Name"></value-of>      
  22.                             </td>      
  23.                             <td>      
  24.                                 <value-of select="as:Name"></value-of>      
  25.                             </td>      
  26.                         </tr>      
  27.                     </for-each>      
  28.                 </table>      
  31.     </template>

Now to get it running let’s open the “Adventure Works DW 2008 SE.asdatabase” with the “XML Notepad”:


Then on the second page (“XSL Output”) select the transformation you want (for example “Flexible_relationships.xslt”) and click “Transform”:


Here is our html page with flexible attribute relationships!

One could put all the tests in one XSLT file named like “AS_project_problems.xslt” and expect an empty result file if everything is ok.

We hope you can get a practical use of it or at least consider as a “take home message” the idea of using XPath and XSLT methods for your AS infrastructure!


Mike Honey
Fr, 01.06.2012 - 06:43

Thanks Michel for sharing these techniques. I was just able to use your "Dimensions not used" method to find and eliminate two redundant dimensions. BTW my .asdatabase file weighs in at 12.5GB (it's a rich cube that's been maturing for almost 5 years now) and XML Notepad basically choked on it - my PC almost died. But after slapping together a single-task SSIS package it was all good.

Di, 11.12.2012 - 22:09

[...] XPath and XSLT for the metadata analysis of Analysis Services objects. var a2a_config = a2a_config || {}; a2a_localize = { Share: &quot;Share&quot;, Save: &quot;Save&quot;, Subscribe: [...]

Neuen Kommentar schreiben

Der Inhalt dieses Feldes wird nicht öffentlich zugänglich angezeigt.


  • Keine HTML-Tags erlaubt.
  • HTML - Zeilenumbrüche und Absätze werden automatisch erzeugt.
  • Web page addresses and email addresses turn into links automatically.
Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden