Yet another new white paper, this time on scaling out Analysis Services:

Interesting and good that they don’t shy away from the things that don’t work so well. I’ve heard a few people complain that the built-in sychronisation doesn’t work well with complex (ie lots of dimensions/attributes/partitions as opposed to large) databases, and although I’ve not had any practical experience myself that reflects this the paper does refer to the synchronise functionality as being "quite robust" just before it describes how to do the same thing in a SSIS package using Robocopy to copy the AS data folder.

Two excellent new papers published today, on best practices for AS processing and query performance:

There’s a lot of useful information in both which isn’t present in the AS Performance Guide or in other sources.

Following on from the Teradata post, I was just wondering how well the newly announced Dataupia (their website is rubbish at explaining what the product is – and I can’t even pronounce the name – but see this article for background: http://www.channelregister.co.uk/2007/05/15/dataupia_databases/) would work with Analysis Services, if you were using Dataupia underneath SQL Server and using AS in ROLAP or HOLAP mode. Could make for an interesting story for scalability if it does what it says on the box.

(Via BI/BPM – The SeeQuel) Here’s the first fruit of the partnership between Microsoft and Teradata that was announced earlier this year: a paper describing how to use Analysis Services in ROLAP mode with Teradata:

 

I’ve heard of people trying to do this for years, usually experiencing a lot of pain along the way, and by all accounts the situation still isn’t ideal but it sounds like it’s getting there.

Via Darren Gosbell, news that the beta of BIDSHelper has been released:

 

You can download BIDSHelper from Codeplex here:

 

BIDSHelper contains so many cool features that make your life as an AS developer easier it’s untrue. The MDX Script updating (bypassing the sometimes 30 minutes waits I’ve sometimes seen) feature is worth the download alone. Kudos to Darren and Greg Galloway for their work on this.

Via Teo Lachev, here’s news of a new tool for doing basic monitoring and admin for Analysis Services called ‘Companion for MS Analysis Server’ from SQLMinds:

 

I’ve long thought that there’s a big gap in the market for a tool like this, and I’ve signed up to the beta so hopefully I’ll be taking a look at it soon.

Here’s a new, and extremely detailed, white paper on how to troubleshoot connectivity problems for AS2005:

I can’t imagine there’s a scenario this doesn’t miss, and it’s good to have the error messages generated from different clients too.

It’s all happening on Codeplex at the moment: via Russell Christopher and Patrice Truong I’ve just seen that the dev team have released their Analysis Services load testing tool to the community:

 

I like this new-found enthusiasm on the part of Redmond for sharing code like this with the rest of us….

Great post here from Erik Veerman on a way of optimising some many-to-many relationships:

I blogged about something similar, but applicable in slightly different scenarios, here:

In general the idea is that if you can reduce the size of any dimensions and measure groups involved in a m2m relationship then you’ll get better performance. I believe partitioning your intermediate measure group appropriately can also help.

 

One topic that has come up occasionally over the years is whether there is any merit in using a RAM Disk with Analysis Services to improve performance. Certainly in the SQL Server world they seem to have some benefits (see for example Tony Rogerson’s post here: http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/24/958.aspx) but as far as I know no-one has ever done any testing for Analysis Services so when I found myself with a bit of free time and a customer who might potentially benefit I thought I’d do so myself.

 

The RAM Disk I used was RamDisk from Superspeed, which as far as I can tell is the same product that Tony used in his blog entry above. You can download a trial version here:
http://www.superspeed.com/ramdisk.php

 

I then created a new RAM drive with 512Mb space on it, created a new directory and then made that new directory my local AS instance’s data directory. Incidentally it didn’t work if I tried to point AS to the root of the new drive – I don’t know whether there’s some kind of reason for this but I certainly wasted a long time finding this out. After that I was able to restore a (pretty small) backed up AS database so that the data was held on the RAM drive and compare performance with the same database on my local drive. And… I found that even on quite long-running queries(>20 seconds, where complex calculations forced multiple reads of the same partition) there was little or no difference between the two; perhaps the RAM drive was slightly faster but it wasn’t consistently so. Even though I was clearing the cache before each query I suspect that what I was really doing was comparing the performance of the RAM drive with the Windows file system cache, and in any case IO only represents a fraction of the amount of time taken by a query for AS on a properly tuned database.

 

That said, I could imagine that there would be scenarios where a RAM disk would have more of an impact: for example where you’re doing regular processing of a partition as with pro-active caching, or where you have a lot of memory available and you’re querying much larger partitions than I was. Some of Superspeed’s other products such as SuperCache and SuperVolume might be interesting to test out for these scenarios, and indeed they make some big claims for performance improvements on Cognos and SAS BI systems here:
http://www.superspeed.com/servers/business.php

…which makes me think further investigation could be worthwhile.