SQL BI

SSAS Performance Tools

Chris Webb has recently released a really interesting tool: MDX Script Performance Analyzer. It's a really interesting idea that will save me (and you) a lot of time trying to understand the root of a performance issue into an MDX Script of a cube. Thank you Chris, if I only had this tool three months ago! :-)

Chris has also posted the news of an Analysis Services Load Testing tool that is available on CodePlex. More than the tool, it is the available documentation (which is a draft) that deserves most of your attention, beacuse illustrate you how to setup all the test environment using Visual Studio 2005.

Aggregation Usage issues with a measure group not linked to a dimension

I just posted this bug on the Microsoft Connect site. Please vote it if you think it would be a useful change. This is the issue description.

If you set the"Aggregation Usage" to true for an attribute of a dimension (i.e. Customer), you cannot run the aggregation design wizard on a partition of a measure group that is not linked to that measure group.
You receive an error message, but it would be preferable a Yes/No dialog box that allow the user to continue considering an "Aggregation Usage" set to "Default" for that dimension only for this measure group.
Actually you can workaround this issue in two ways:

1. Changing the Aggregation Usage before/after using the aggregation design wizard (setting the desired value for the measure group you want to optimize)
2. Manually designing the aggregation (not a very comfortable practice)

I understand it is not a real bug, but the message is not coherent with what you can do anyway by changing aggregation usage setting.

I'm working on optimizing aggregations in these days and this limitation has been very annoying today, requiring me to write a set of specification just to explain how to redesign aggregations if in the future someone will make a structural change to the cube...

Strange bug in SSAS 2005

I have been notified of this strange bug in Analysis Services 2005 that is easily reproducable.

******************************************************************
PLEASE DO NOT TRY THIS TEST ON A PRODUCTION SERVER! IT HANGS ANALYSIS SERVICES!!!
******************************************************************

• Open SQL Server Management Studio
• Browse the Adventure Works cube
• Put Promotions/Promotions hierarchy on the filter area (no measures in the data area)
• Expand Promotions and uncheck "Customer"
• Drag Promotions hierarchy from filter to rows
• Drill the "Reseller" member - at this point you may observe a long wait but the worst is that msmdsrv.exe process on the server (it's the Analysis Services service) is consuming all available virtual memory - you need to restart Analysis Services service at this point

I tested it on a SP2 server.

First of all, since it is a "Denial of Service" for Analysis Service, it is best to remove the Adventure Works sample from a production server - but you never put sample databases on production server, right? :-)

I hadn't time to test it more to understand what is the real issue here to understand what is the impact on other databases. The strange thing is that if you do not move the Promotions hierarchy on the filter area before, the problem do not arises.

SQLBI at Microsoft Business Intelligence Conference 2007

I am proud to announce that SQLBI community will be at the Microsoft Business Intelligence Conference 2007 (May 9-11, 2007 – Seattle).

You can meet me and Alberto Ferrari in the User Group Area, booth #532. We will try to make our booth visible with the new logo of SQLBI community that you can see below.

If you will attend the conference, please don’t miss the opportunity to give a face to a name and get a free copy of the printed version of the popular “The Many-to-Many Revolution” paper, which has already been downloaded by almost 2000 different people. We will donate this long paper / small bookto all registered users that will visit our booth.

If you cannot attend the conference, stay tuned and read the blog, I will update it more often during conference days.

See you in Seattle!

The need for a better merge component in SSIS

Alberto Ferrari wrote an interesting post about the need of more properties to control the behavior of a Merge component in Integration Services (SSIS). This apparent simple problem becomes very important in large data warehouses. I hope that someone in Redmond will consider all these possible improvements...

FastLoad option on SSIS with int identity as primary key

Alberto Ferrari posted some test we made working on performance optimization loading data in very large dimension tables. When we discovered that an int identity primary key is not handled by SQL Server just as an ordered set of data in a bulk operation... we had to triple check the whole thing. I would like to know if there is a way to bulk insert data in a table with an identity primary key - after all, this would remove the need for an identity generator inside SSIS...

However, I totally agree with Alberto when he says "the OleDb destination adapter should issue a warning if both ORDER fastload option and Maximum insert commit size parameter are used". It could help many SSIS developers who are not completely aware of all SQL Server bulk insert (fast load) details.