Latest Articles: SSAS

In Analysis Services 2008 there are 13 MDSCHEMA Dynamic Management Views (DMVs).

These DMVs describe cubes, dimensions, functions, measures, etc in Analysis Services database.Here is a diagram that I got after exporting structure of above DMVs into SQL Server database.

One of the tasks that we developers hate to do is... generation documentation :-)

SSAS provides you with DMV, you can create a linked server and via openqueries can extract the info that you need (you can use that queries on SSRS to get a nice layout)

In this post you will get a tables diagram that will tell you which info can you extract from that DMV.

New SSAS 2008 DMVs allows you to easily access Microsoft SQL Server Analysis Services (SSAS) metadata – information about cubes, dimensions, measure groups, measures, etc. As with DMVs metadata information is returned in the data set format, it is very easy to build Reporting Services reports to generate documentation about your database.

Quite useful, autogenerate cube documentation, dimensiones, hierarchies...

This article describes how to identify your current Microsoft SQL Server version number and the corresponding product or service pack level. It also describes how to identify the specific edition of SQL Server you are using.

Determine which version or which service pack is installed on a given SQL Server instance is not something straight forward.

In this doc you will find how to check which version of SQL Server is installed just be execution a single SELECT statement.

This article lists known issues that may occur when you use Microsoft SQL Server 2005 Analysis Services cube-based report models in SQL Server 2005 Report Builder.

Report builder is not the best tool for cube navigation / browsing, rather evaluate PPS Analytics, Excel Services... or at least upgrade to 2008 version.

Here you will find some limitations of the product applied to SSAS.

The fact that there's now a HAVING clause at all in AS2005 MDX doesn't seem to be publicly documented anywhere, as far as I know; it's one of those hidden features we found out about while researching 'MDX Solutions'. While George has a full write-up on how it behaves in the book (coming soon to a store near you) here's a quick overview of what it does...

Basically, it allows you to filter the contents of an axis without having to use the FILTER function.

SSAS MDX sometimes seems to be like black magic, or too hard for a SQL Developer :-).

Interesting thing to take a look: HAVING clasule on an MDX statement.

This post explains how you can build a dynamic MDX query in Reportins Services, customised to the users requirements. This can often bring some quite major performance benefits.

Defining dynamic MDX queries on SSAS is complex but can bring you in some cases good performance benefits (watch out, don't use this approach for every query).

Designing effective aggregations are a well known way to improve query performance (for the sake of AS processing time). As you may have noticed in Tip #2 of the of the Analysis Services Query Performance Top 10 Best Practices whitepaper, the best way to define effective aggregations is to use the Aggregation Manager sample application. Here we discuss one of the strategies for adding effective aggregations using this tool.

How to create effective aggregations based on logging MDX queries.

From the early days of Microsoft® SQL Server® Analysis Services, one of the query performance optimization techniques was for developers to use the usage-based optimization feature. Developers would create their OLAP cube and cube partitions and then set the aggregations for the cube at a low percentage. It was common to see developers set the aggregations for their partitions at 0% - 5%. After the OLAP database was deployed to the production servers, there would be a beta test period for users to work with the cube and to store the components of the queries executed against the cube. Afterwards developers could build aggregations based on what users had queried, typically optimizing for the slowest queries. After this was done, these aggregations could significantly improve the performance of these initially slow queries. With the release of SQL Server 2008 Analysis Services, an improved usage-based optimization algorithm has been included that enhances Analysis Services’ ability to create better aggregations.

Desiging optimal Aggregations looks like a black magic, this article gives you good tips about to implement usage base optimizations

This document describes how to build Create Cache commands. Create Cache for Analysis Services (AS) was introduced in SP2 of SQL Server 2005. It can be used to make one or more queries run faster by populating the OLAP storage engine cache first.

Warming up cube cache can be an important tasks to achive after cube processing, a good approach is to create an SSIS package for that.

I've been involved recently in a quest to optimize an SSAS 2008 long-running query that would return the 12-month history of a KPI. Since the KPI was displayed on a dashboard page alongside other KPIs and charts, it was important to optimize the query as much possible.

Interesting real case study, comparing filtering on an MDX subselect (select from select) versus setting the filter in the where clausule.

One of the interesting conclusions of this test is that results may change depending on the cube size... more fun to our MDX fine tunning job.

Q: How do I clear Analysis Services (SSAS) database cache?

A: To clear a cache you should use XMLA command ClearCache. You can choose to clear cache for database, cube or measure group. Note: you should specify object IDs and not names. In many cases this might not be the same. To see ID of the object you can open database in the BIDS, then select object (database, cube or measure group) and check value for property ID.

Clearing the cache is quite an important thing to do to check how well performs a given MDX query, how to make it using an XMLA script.