Vidas Matelis Analysis Services Blog

In my previous post Katmai Analysis Services 2008 November CTP5 – tests on metadata rowsets I listed examples of new SSAS DMVs. But there were few DMVs that you could not query. For example if you would try to execute following query:

SELECT * FROM $SYSTEM.DISCOVER_PARTITION_DIMENSION_STAT

You would get this error message: 

The ‘DATABASE_NAME’ restriction is required but is missing from the request.

Read the rest of this entry »

In SQL Server 2008 Microsoft introduced “Management Data Warehouse” (MDW). This blog post should give you a good idea on what to expect from this new feature. Note: All tests and screenshots for this post were done in SQL Server 2008 February CTP (CTP6). 

Before starting MDW configuration please make sure that your SQL Server Agent is running. You can configure Management Data Warehouse by going into SQL Server Management studio, then expanding Management folder and right mouse clicking on Data Collection folder and choosing “Configure Management Data Warehouse”. This will start wizard that will ask you MDW database connection information (server, database and authentication). As MDW database puts some overhead for the server, in production environment you probably will want to have this database on a separate server. After wizard completes you should see green arrows for Data collection items:

Read the rest of this entry »

A few weeks ago I asked a question on Analysis Services 2008 forum if there will be PowerShell support for SSAS 2008. I got an answer from Microsoft, that no native support is planned. I know that Darren Gosbell released PowerShell Provider for SSAS 2005, but for the different reasons I cannot use it with all clients. But just this week Carl Rabeler (Microsoft) posted a new updated set of Analysis Services 2005/2008 Samples. One folder in these samples contained 4 PowerShell script examples that shows how to use it with Analysis Services. I tested these samples and they worked on a standard SSAS 2008 installation. Today Carl Rabeler actually updated these samples – fixed some minor issues and added one more sample – to get a size of Analysis Services database.

Read the rest of this entry »

Today I was reading Chris Webb post “Visualising Analysis Services Trace Information in Reporting Services” where at the end he talks about tools to monitor SSAS, that made me thinking about SSAS 2008 DMVs again. So I went back to my SSAS 2008 installation to see what do we have new in CTP6. Just after release Darren Gosbell already blogged about new DMVs in his post “SSAS 2008: What’s new in the Schema Rowsets?“. But just after I started to run queries I realized how cool these new DMVs are. After quick test on them, I decided to blog today about my favorite DMV so far: $SYSTEM.DISCOVER_OBJECT_ACTIVITY.

Read the rest of this entry »

SQL Server 2008 February CTP6 is out and I spent last few evenings installing it and going through new features. Here is my report. 

Attaching and detaching SSAS databases 

In new CTP Microsoft introduced option to attach and detach Analysis Services databases. This works in a very similar way like with SQL Server. When detaching SSAS database you have an option to specify Password. When attaching SSAS database you have an option to specify Folder, Password and specify if you want to make new database Read-only.

Read the rest of this entry »

In SSAS 2005 to access SSAS metadata you had to use object model.  In SSAS 2008 November CTP Microsoft introduced schema rowsets as an alternative way to access metadata. I did some tests last few days and here I’ll post examples of what could be done. Generally it is quite easy to write these queries. BOL already have description for most of the tables and fields you can query. What was not so easy is to get metadata on structure that is hierarchical. That is there is an easy way to get a list of hierarchies in one dimension. But to get list of levels of hierarchies of dimensions becomes not an easy task.

SELECT statements you can write on these rowsets appear to be quite limited. For example:

• SELECT DISTINCT does not return DISTINCT values
• ORDER BY clause accepts just one field to order by. Adding second field raises error: “Error (Data mining): Only one order expression is allowed for TOP expression at line 1, column 1”
• COUNT, SUM does not work
• WHERE clause works
• ORDER BY <number> does not ORDER, but no error
• JOINS appear not to work
• LIKE does not work
• string functions like LEFT do not work

As I did not find restriction list in documentation, list above is from my experience. It could be that I just did not do my tests properly, but I am sure sooner or latter there will be official list of what is supported.

Bellow are examples of  queries that are very simple, but enough for anyone to get an idea of what it is possible. All these examples where run in Adventure Works DW database.

Read the rest of this entry »

As Microsoft released today November CTP 5 for SQL Server 2008 and this time there are quite a few changes in Analysis Services area. Documentation lists these SSAS changes in November CTP:

• MOLAP Performance (FITS): New MOLAP-enabled write-back capabilities in SQL Server 2008 Analysis Services remove the need to query ROLAP partitions. This provides users with enhanced writeback scenarios from within analytical applications without sacrificing the traditional OLAP performance.
• Resource Monitoring: This concept is similar to DMV found in the relational engine. Analysis Services DMV addresses immediate needs of DBAs to get answers to the questions like: Who is connected to my server? What are the active sessions and what commands/queries they are running? What CPU consumed by particular query/command? What are the objects being referenced by particular command?
• MDX Query Optimizer – Block Computation: Block computations provide a significant improvement in processing performance, enabling users to increase the depth of their hierarchies and complexity of the computations.
• Aggregation Design:  SQL Server 2008 drives broader analysis with enhanced analytical capabilities and with more complex computations and aggregations. The AS Aggregation Design improvement exposes Aggregation Design objects in SQL Server BI Dev Studio and SQL Server Management Studio and provides tools for users to better work with these aggregation designs. In addition, an advanced view in the new Aggregation Design tab of the cube editor provides the ability for an advanced user to view and manually edit individual aggregations within an aggregation design.
• Analysis Services Cube Design:  New cube design tools help users streamline the development of the analysis infrastructure, enabling them to build solutions for optimized performance. The AS Cube Design improvement introduces a new Cube Wizard which helps users create better cubes in fewer steps.  The new wizard focuses on having the user answer a few questions to create leaner cubes that better targets their needs.  It also unblocks the previously difficult scenarios of creating a cube a cube based on a single, de-normalized table and creating a cube containing only linked dimensions.

Read the rest of this entry »

Mosha Pasumansky just posted blog entry about dynamic named sets in SSAS 2008. In his post Mosha listed 4 SSAS changes that were included in June CTP release:

• Dynamic named sets
• CREATE MEMBER statement extension to allow specify display folder and associated measure group
• New CREATE KPI statement
• Ability to change calculated member “on the fly”

As Mosha focused on changes with Dynamic named sets, I decided to do a quick test on other 3 changes. To be honest, just from Mosha’s blog entry I realised that these MDX changes are already in CTP. I knew that some of these changes were planned, but from webcasts and chat sessions I was under impression that these changes will be available just in the future releases. Is it possible that these changes were included just in July CTP?

So here are my tests on other changes. 

Read the rest of this entry »