Michael Mukovskiy

 

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:

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

2. Dimensions not used:

as:Database/as:Dimensions/as:Dimension[not(ancestor::as:Database/as:Cubes/as:Cube/as:Dimensions/as:Dimension/as:DimensionID/text()=as:ID/text())]

3. Partitions without aggregation design:

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.

<?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:template match="/">
        <html>
            <body>
                <h2>Flexibe Attribute Relationships</h2>
                <table border="1">
                    <tr bgcolor="#9acd32">
                        <th>Dim</th>
                        <th>Attribute</th>
                        <th>Relationship</th>
                    </tr>
                    <xsl:for-each select="as:Database/as:Dimensions/as:Dimension/as:Attributes/as:Attribute/as:AttributeRelationships/as:AttributeRelationship[not(as:RelationshipType)]">
                        <tr>
                            <td>
                                <xsl:value-of select="ancestor::as:Dimension/as:Name"/>
                            </td>
                            <td>
                                <xsl:value-of select="ancestor::as:Attribute/as:Name"/>
                            </td>
                            <td>
                                <xsl:value-of select="as:Name"/>
                            </td>
                        </tr>
                    </xsl:for-each>
                </table>
            </body>
        </html>
    </xsl:template>
</xsl:stylesheet>

2. Dimensions not used.

<?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:template match="/">
        <html>
            <body>
                <h2>Dimensions not used in cubes</h2>
                <table border="1">
                    <tr bgcolor="#9acd32">
                        <th>Name</th>
                        <th>ID</th>                       
                    </tr>
                    <xsl: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())]">
                        <tr>
                            <td>
                                <xsl:value-of select="as:Name"/>
                            </td>
                            <td>
                                <xsl:value-of select="as:ID"/>
                            </td>                           
                        </tr>
                    </xsl:for-each>
                </table>
            </body>
        </html>
    </xsl:template>
</xsl:stylesheet>

3. Partitions without aggregation design.

<?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:template match="/">
        <html>
            <body>
                <h2>Partitions with not assigned aggregation designs</h2>
                <table border="1">
                    <tr bgcolor="#9acd32">
                        <th>Cube</th>
                        <th>MeasureGroup</th>
                        <th>Partition</th>
                    </tr>
                    <xsl: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]">
                        <tr>
                            <td>
                                <xsl:value-of select="ancestor::as:Cube/as:Name"/>
                            </td>
                            <td>
                                <xsl:value-of select="ancestor::as:MeasureGroup/as:Name"/>
                            </td>
                            <td>
                                <xsl:value-of select="as:Name"/>
                            </td>
                        </tr>
                    </xsl:for-each>
                </table>
            </body>
        </html>
    </xsl:template>
</xsl:stylesheet>

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

clip_image002

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

clip_image004

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!