Microsoft SQL Server Analysis Services 2005/2008 articles - MDX, performance, management

Analysis Services Memory Limits

Vidas.Matelis@ssas-info.com (Greg Gonzalez) — Fri, 03 Jul 2009 21:25:58 +0000

One of the least understood areas of Analysis Services performance seems to be that of memory limits – how they work, and how to configure them effectively. I was going to start with an overview of Performance Advisor for Analysis Services (aka, PA for SSAS), but during the beta I ran across such a good example of the software in action in diagnosing a problem with memory, I think it’ll work just as well to go through that issue step-by-step.

First some background. SSAS has two general categories of memory, shrinkable and nonshrinkable, and they work pretty much like it sounds. Shrinkable memory can be easily reduced and returned back to the OS. Nonshrinkable memory, on the other hand, is generally used for more essential system-related stuff such as memory allocators and metadata objects, and is not easily reduced.

SSAS: Creating a Rowset action with the ExecuteSQL .Net stored procedure

Vidas.Matelis@ssas-info.com (Darren Gosbell) — Thu, 02 Jul 2009 21:30:38 +0000

A few weeks ago I did a post introduced the ExecuteSQL .net stored procedure for SSAS. Chris Webb asked if this function can be called from Excel 2007 when it is set this up as a rowset action and I figured that this would make a good topic for a blog post. So the following screen shots show how you would go about setting up such an action. As a quick example I cheated a bit and set up an Rowset action that calls the sp_who2 system stored procedure. This way I did not have a depedancy on any particular database. You don't have to use a stored procedure, you can use any sort of SQL command that returns a set of rows into a rowset action.

Below is how I setup the action

Exploded Pie Chart for Analysis Services Data

Vidas.Matelis@ssas-info.com (William Pearson) — Tue, 30 Jun 2009 10:56:45 +0000

This article focuses upon a variant of the Pie chart, and extends the examination of Reporting Services charts for Analysis Services data sources that we began in an earlier article of my MSSQL Server Reporting Services series, Introducing Reporting Services Charts for Analysis Services. In that article we summarized the many different chart (or chart data region) types that are available, and looked ahead to individual articles surrounding each type, where we would specify details – and real world innovations – involving the use of each in reporting Analysis Services data. We noted that the focus of these related articles, interspersed among other topics within my MSSQL Server Reporting Services series over time, would be the design and creation of Analysis Services chart reports of various types, and the exploitation of the rich and flexible features contained in Reporting Services that enable us to make report data more meaningful, and easier to understand, from the perspective of our information consumer audiences.

Reporting Services Tips for OLAP Cubes

Vidas.Matelis@ssas-info.com (Richard Lees) — Mon, 29 Jun 2009 00:44:14 +0000

Naturally I have an inclination to use Reporting Services on OLAP cubes. My feeling is that reports should by and large be run from OLAP cube where they can be dynamic and fast. SQL based reports are more ideally suited for "atomic data" reports (eg transaction lists) and some special reports requiring real time data.

There are a few funnies when you get into OLAP reports with SSRS. The SSRS wizard does a great job of starting the report and building the parameters you need. However, before long you will need to edit the MDX that the wizard creates, to do things like taylor the parameter queries for cascading filters. Here are a couple of tricks that I commonly use.

DrillThrough Actions and (semi) Security in SSAS OLAP cubes

Vidas.Matelis@ssas-info.com (Alberto Ferrari) — Thu, 25 Jun 2009 02:07:46 +0000

Using SSAS 2005/2008, there is no way to apply security to DRILLTHROUGH actions, so we cannot decide whether a specific user is authorized or not to perform a specific action. This is a “by design” behavior, since DRILLTHROUGH actions are initiated at the client side, we can decide whether a user can perform or not DRILLTHROUGH but, once we let him do it, he can query anything he wants.

Nevertheless, if we are not concerned with security but only with user experience, there is a simple trick to define which users can see which actions.

Attribute Discretization: Using the “Clusters” Method

Vidas.Matelis@ssas-info.com (William Pearson) — Tue, 23 Jun 2009 11:20:17 +0000

This article continues my exploration of attribute discretization, a capability in Analysis Services that allows us to group members of an attribute into a number of member groups. Our concentration here will be to get some exposure to the pre-defined “Clustered” discretization method, one of three such pre-defined methods supported by Analysis Services, through hands-on application of the method to a representative dimension attribute within our sample UDM.

Analysis Services 2005 - Dynamic Default Member

Vidas.Matelis@ssas-info.com (Philip Stephenson) — Tue, 23 Jun 2009 01:28:04 +0000

Whilst migrating a database from AS2000 to AS2005 I came across in interesting issue when trying to replicate the same behaviour as the "legacy" version. Firstly, a little background...

For the sake of expediency I used the migration wizard to convert most of the AS2000 database over to AS2005. I say most because although the validation checker stated that everything was a-okay, when it came to actually do the migration the wizard whinged about a dimension that was joined to different cubes at different levels within the dimension. In order to progress this, I deleted the dimension and migrated the rest.

Sampling data with the Excel 2007 data mining add in

Vidas.Matelis@ssas-info.com (Thomas Ivarsson) — Sun, 21 Jun 2009 02:06:35 +0000

have used the sample tool in the Excel 2007 before but I never thought that I could do this directly on a data source, only on an Excel 2007 table, but you can. I am using SSAS 2008, as the data source, and the Excel 2007 data mining tools for SSAS 2008. This can be useful evenn if you do not want to use the data mining add ins and only collect data for a pivot table. You cannot join tables in this tool, only select single tables or views.

Error messages in MDX SELECT statements and what they mean

Vidas.Matelis@ssas-info.com (Chris Webb) — Fri, 19 Jun 2009 11:39:00 +0000

Anyone that has tried to learn MDX will know that, when you make a mistake somewhere in your code, the error messages that Analysis Services gives you are pretty unhelpful. It was suggested to me recently while I was teaching an MDX course that I should blog about common error messages and what they actually mean; so here’s a list of a few example queries using Adventure Works that return confusing errors, the error messages themselves, and details on how to solve the problems. I’ve deliberately concentrated on query-related errors rather than calculation-related errors (that can be a future blog post); if you can think of any more errors that I should cover please leave a comment.

SSAS: Executing Arbitrary SQL queries

Vidas.Matelis@ssas-info.com (Darren Gosbell) — Fri, 19 Jun 2009 00:20:59 +0000

I had a question a little while ago via my blog about possibly using a rowset action to execute a SQL query against a specified table. Although a rowset action will allow you to enter a SQL query, such a query is still executed against the current cube and only the subset of SQL supported by SSAS can be used. Basically the rowset action just returns a flattened result set.

However what would be possible would be to write a .Net stored procedure and use that to execute your SQL query. The code itself is really simple, the whole procedure only takes a few lines: