Vidas Matelis Analysis Services Blog

Few years ago on my blog I posted scripts “SSIS Package to drop/create partitions based on partition list in the SQL Server table” and “SSIS package that process all partitions/measure groups/cubes in one database“. These posts contained partial scripts that I developed for company “Insight Decision Solutions Inc.” to maintain partitions in the Microsoft SQL Server Analysis Services. This company sells, customizes and implements pre-packaged data warehouse solution (using SQL Server, SSAS, SSIS, SSRS, SharePoint and Excel 2007) for “Life” and “Health” insurance companies. Recently “Insight Decision Solutions Inc.” owners let me post full script on how to automate SSAS partition management . Here are step by step instructions that I adjusted and tested on Adventure Works database. Most of the code comes from my earlier published posts, here I just added information how everything works together.

Read more and download code here…

I recently posted a new PowerShell script to process all dimensions and cubes in the specified Analysis Services database. This script has a few additional parameters that give you more control for your SSAS DB processing. First of all, you can specify the MaxParallel processing parameter to control the level of parallelism. You can also specify how many processing commands you want to execute per batch.  Read the rest of this entry »

Microsoft BI Conference 2008 will start in just 11 days, and today I finally finished building my session list. This was really hard to do, and in most of the time slots I still have 2-3 sessions that I would like to attend. Last year Microsoft recorded Breakout sessions, so if they will do the same this year, I’ll try to attend more Chalk Talk sessions and will view sessions that I missed from a recorded DVD.

After last years BI conference I complained about a lack of technical content. But this years session list looks exceptionally good. There are plenty of sessions on SSAS, SSIS, SSRS, Performance Point, Excel Services and Master Data Management. Marco Russo already blogged about the expected announcement during this BI conference regarding “Self-service BI”. I am very interested to hear what Microsoft will say about this. I’ll blog about my conference experience as soon as I’ll come back.

By the way, does anyone want to join us for a beer after the Sunday Evening Welcome Reception? We started to talk about this in this forum post and if you have no other plans then let’s meet.

In one of my previous blog posts I described SQL Server 2008 Management Data Warehouse (MDW) and how it can be used to collect performance information. This post will describe how you can create new MDW data collection sets to capture SQL Server Analysis Services (SSAS) 2008 performance counters.

SQL Server 2008 does not have a graphical interface to create MDW data collection sets, so the best way to do that is by scripting an existing system data collection set and then changing it. To script an existing data collector, start SQL Server Management Studio, expand “Management”->”Data Collection”->”System Data Collection Sets” folder then right mouse click-> “Script Data Collection As..”->”Create To”->”New Query Editor Window”. For my starting point I scripted the data collection set ”Server Activity” and got this script.

Read the rest of this entry »

If you are like me and prefer a visual learning approach, then you should check out the website http://www.learnmicrosoftbi.com. The website owner Craig Utley has recorded a list of videos that are a good way of learning about Microsoft BI technologies and specifically Microsoft SQL Server Analysis Services 2005 and 2008. At the time of writing this post there were 14 videos ranging from 6min to 57min each.

His latest video ”SSAS 109 – Attribute Relationships” should be watched by all SSAS developers who want to understand why attribute relationships are so important. This video shows plenty of examples and compares SSAS 2005 and 2008 versions. This is one of the best tutorials on this subject and I highly recommend it.

The best part - all of the videos are high quality and free, all you need to do is register and start downloading.

As you probably all already know Microsoft released SQL Server 2008 Release Candidate 0 (RC0).  You can download RC0 from here. You can also download SQL Server 2008 Books Online Release Candidate (RC0) and Microsoft SQL Server 2008 Feature Pack RC0, June 2008.

Just today Microsoft also released another related download: Sample Databases for Microsoft SQL Server 2008 RC0. New sample set contains 8 BI related files that lets you install Adventure Works DW and Analysis Services databases:

• SQL2008.AdventureWorks_DW_BI_v2005.ia64.msi
• SQL2008.AdventureWorks_DW_BI_v2005.x64.msi
• SQL2008.AdventureWorks_DW_BI_v2005.x86.msi
• SQL2008.AdventureWorks_DW_BI_v2005.zip
• SQL2008.AdventureWorks_DW_BI_v2008.ia64.msi
• SQL2008.AdventureWorks_DW_BI_v2008.x64.msi
• SQL2008.AdventureWorks_DW_BI_v2008.x86.msi
• SQL2008.AdventureWorks_DW_BI_v2008.zip

As you can see, installation files are split by CPU type (x86, x64 and ia64) and also by related SQL Server version. Files with _v2008 suffix can only be used with SQL Server 2008 and demonstrates some of the new features. If you want to do manual file copy, you can use 2005 or 2008 version zip file.

Read the rest of this entry »

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 »

Our company is using Microsoft Excel 2007 with Microsoft Office SharePoint Server 2007 and Excel Services as the primary interface to access Analysis Services cube data. This works very well on Windows XP PC, but was almost unusable on Windows Vista PC. On Vista PC you would get just too many “Enter UserName/Password” dialogs.

Here is what would happen on Vista PC when you try to open Excel report with Pivot Table from the SharePoint: Read the rest of this entry »

I just came back from 2008 MVP summit. I had a great time, especially meeting so many new people.

Here are few pictures from this summit.

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 »

I just found an great website with a live running demo of report examples with Analysis Services, SharePoint, PerformancePoint, Excel Services, Reporting Services and other Microsoft BI Technologies. This is Richard Lee’s website:

http://richardlees.com.au/sites/Demonstrations

From the site:

Site has a variety of data mining, OLAP and business intelligence tools on several real (census, web logs, Perfmon and libraries) and synthetic (foodmart) databases of over 100 Million records.  The demonstrations are platformed on Microsoft SQL Server 2005 and Microsoft SharePoint Server, Microsoft PerformancePoint Server 2007 and Excel Services.

Well worth visiting and exploring all these online examples.

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 (0×0409, 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.

Years ago I was using Windows Scripting Host (WSH) for majority of my scripting tasks. It is installed by default on all Windows PCs, VBScript language is quite easy to learn/use and you can do a lot with just a few lines of code. But since DTS was introduced, I started to use DTS more and more and now SSIS is default environment where I do all my scripting. This is because SSIS has very good error loging system and is so much more powerful. But I still use a few WSH scripts that gives me quick status of my SSAS databases. To test them in your environment just copy script to text file, name it with extension *.vbs and then run it with command “CScript YourScript.vbs” with required parameters.

I have no problems running these scripts on any of my workstation PCs. But when I tested these cscripts on my server machines I was getting error message “Microsoft VBScript runtime error: ActiveX component can’t create object ‘Microsoft.AnalysisServices.Server’”. Maybe I am getting this error message because my servers are 64bit, or I might require to manually register some dll(s). If anyone knows how to make it work on server, I would appreciate if you’ll leave your comments here.
Updated: Few hours after I posted this blog I got solution from Andreau to this problem. On 64 bit environment I have to execute script using command: “c:\windows\syswow64\cscript.exe YourScript.vbs”. Thanks Andreau!

Here are 2 scripts that you might find useful: Read the rest of this entry »

Microsoft just released new best practice paper “Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services” written by Carl Rabeler and Eric Jacobsen. I just started to read it, but I am very impressed. It describes how to use SQL Server Profiler, System Monitor, Windows Task Manager, Kernrate, Process Monitor and MDX Script Performance Analyzer to analyze MDX queries. For SQL Server Profiler this document lists what events to track and what values actually mean. For System Monitor it describes counters that could be useful for MDX query analysis. Document also describes how to establish if bottlenecks are in storage or formula engine.

Then document shows examples on how actually perform analysis on sample queries, explaining all results and provides recommendations on how to improve MDX query performance.

Amongst other examples there are suggestions on how to do Ranking over sets and demonstrates performance difference between CASE and IIF statements.

I just started to read this document, but already saw a lot of very useful information. You can find this paper here.

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 »