Vidas Matelis Analysis Services Blog

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 »

I got a message from Microsoft that I was nominated for MVP award. I will know on April 1st if I got the award. I am very honored and excited about this. Even if I will not get the award, just being nominated is a big deal for me.

When I initially migrated Analysis Services database from 2000 to 2005 version, in the new database I created single cube with all measure groups in it. At the time I believed that was a right way to do. This is quote from one of my favorite books “The Microsoft Data Warehouse Toolkit with SQL Server 2005 and the Microsoft Business Intelligence Toolset” page 322, chapter 7:

“The best practice in Analysis Services  2005 is to define a single cube for a database”… ” You are still permitted to create multiple cubes in a database, but you shouldn’t”. Instead, create a single cube with multiple measure groups.”

But later I found about Microsoft recommendation “Avoid having more than 15 measure groups in cube”. I posted question on Analysis Services MSDN forum and got reply from Greg Galloway about his experience – about 15% performance boost after splitting and about 10% performance improvement after reducing cubes MDX script code.

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 »

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 »

I just recently started to learn a bit more about Analysis Services data mining. I am very impressed how Excel 2007 add-ins makes data mining user friendly. All technical stuff is hidden, you just select your data in Excel, run add-in and review results. Behind the scene this add-in generates SSAS data mining model and process it with provided data. Of course, for more complicated stuff you will have to learn SSAS data mining to have a better control on how data mining project is build.

Just today in Microsoft webcast newsletter I noticed that there are 7 Analysis Services data mining webcasts scheduled for November. If you are also interested in this technology, make sure you don’t miss them:

I was looking into some issues with using Analysis Services translations and found not related to my problem, but new to me information about SSAS configuration property EnableFast1033Locale. From BOL:

If you use the English (United States) language identifier (0x0409, or 1033) as the default language for the Analysis Services instance, you can get additional performance benefits by setting the EnableFast1033Locale configuration property, an advanced configuration property available only for that language identifier. Setting the value of this property to true enables Analysis Services to use a faster algorithm for string hashing and comparison.

I found this properly in the msmdsrv.ini file (default folder: C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Config):

 <EnableFast1033Locale>0</EnableFast1033Locale>

I did some tests and found that you have to drop all databases from SSAS server before you change property value to 1. Then you have to restart server and re-deploy and re-process all databases. I found that even Adventure Works DW database would not work without redeployment. My other test databases were also giving me different errors until I redeployed and reprocessed them.

I could not see performance differences on my small tests databases. But for a big installations with 1033 locale it could be worth to see if this could give you some performance boost.

As as side effect I noticed, that when EnableFast1033Locale property value is set to 1, in Microsoft SQL Server Management Studio server properties window was always giving me error when clicking on “OK” button:

TITLE: Microsoft SQL Server Management Studio
——————————
Errors in the metadata manager. LOG file extension can be only .LOG.
Errors in the metadata manager. An error occurred while setting the value for the ‘ConfigurationSettings\Log\File’ configuration property.
 (Microsoft.AnalysisServices)

To go around this problem just change required property values directly in msmdsrv.ini file.