Vidas Matelis Analysis Services Blog

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 »

For the past few years I was working on data warehouse project that is customized and installed for different clients. As part of customization, sometimes clients might request to use different names for dimensions, attributes or measures. For example in Geography dimension for US customers that do business just in US you would want to have hierarchy Country-State-City, but for Canadian customer the same hierarchy would be Country-Province-City. For customers that do business in US and Canada, the same hierarchy could be called Country-State|Province-City.

Analysis Services 2005 provides easy interface to rename dimensions, attributes or measures. But things become more complicated when these object names are used in cube MDX script – these renames can break script. Instead of renaming actual object, you might consider using “Translation” feature of Analysis Services 2005. I usually create translation for language that matches clients PCs language. This way Excel 2007 uses translated names without any additional steps. For other SSAS clients you might have to specify locale ID in connection string parameter, example: “Language Identifier=1033;”. With translations you can choose to rename just a few objects. If no translations exists, Analysis Services uses default object name assigned during object creation. Read the rest of this entry »