Analysis Services Best Practise Analyser

Introduction

Download

You can download this tool here. It is a clickonce application, so the download will automatically install the tool. The tool also regularly checks for updates. Currently, the tool is available for free. However, notice that this software is licensed “as-is.” You bare the risk of using it. U2U gives no express warranties, guarantees or conditions.

Prerequisits

Prerequisits for installing the tool are .Net 2.0 runtime, the AMO (Analysis Management Objects) library and the ReportViewer, all freely available from the Microsoft website.

Detailed Description

After the tool starts, you select the Analysis Services server to connect to. Once connected, you select the database you want to analyse. A list appears with the violated best practises, and some detailed information about the objetcs involved. One can save these results in csv or xml format (handy for importing in e.g. Excel), or render reports (table, crosstab as well as charts) which can be saved in PDF or in Excel.

It is also possible to mark a violation as 'ignored', and provide annotations with each violation. Earlier saved analysis can also be uploaded again. Based on our personal experience with Analysis Services 2005, we assigned a weight to each best practise, which makes it easy to distinguish the less important best practises (which for instance create a more user friendly end-user experience) from the very important best practises, which improve performance or avoid wrong results.

This list of checked Best practises is long (28 best practises for version 1.1). There are best practises on cube and dimension design, partitioning and aggregations. Some best practises strongly influence processing or querying performance, others improve the usability of the resulting databases.

Below, we discuss three of these best practises to give you an idea of the type of best practises which are being checked.

Non Empty Behavior

Every calculated member has a property Non-Empty Behavior. With this, you can specify that the calculated member will be empty in all scenaros where a real (non-calculated) measure (or collection of measures) will be empty. Since analysis Services has a very effecient mechanism to find out if a real measure is empty for a particular tuple, it can avoid calculating the calculated member in many cases where it would be null (or empty) anyhow. And given the fact that OLAP cubes are usualy sparse, this can give a big performance improvements on all queries which refer to these calculated members.

Aggregations

Aggregations are for OLAP cubes what indexes are for relational databases: They take storage and time to compute, but they significantly speed-up your queries. Just like the indexes on relational databases, they are not created automatically: you have to initiated explicitly the design of aggregations. This tool checks all partitions in the selected database, and reports those partitions that do not have aggregations defined upon them.

Attribute Relationships

Attribute relationships define the physical storage of the dimensional data, but they also influence the calculation of aggregated data. Because of this, they have a huge impact on performance. With attribute relationships between e.g. products, subcategories and categories, to compute a category total, Analysis Services just adds up all the subtotals from the subcategories underneath this category. Without attribute relationships, it would need to retrieve all the products (assuming this is the key attribute of the dimension) in this category and add up their aggregated data. So instead of adding up e.g. 4 subcategories, it now needs to add up e,g, 450 products (which takes 112.5 times as long). So in this example, setting the attribute relationships right would have given us as 11250% speed improvement. Nice!

The tool checks all the cube dimensions in the selected database to see if there is at least one non-trivial attribute relationship set (a trivial attribute relationships is the relationship between a non-key attribute and the key-attribute). It reports all the dimensions in which no non-trivial attribute-relationships do exist. Notice that in some dimensions indeed no reslationships should exist, but in most dimensions, missing attribute relationships are a strong indication of failing best practises.

Questions?

Any remarks or questions on this product are welcome at info@u2u.net.