Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Archive for April 2007

Last Night’s SQL Event in London

with 2 comments

Just a quick note to say thanks to everyone who turned up to the combined SQL Server and SQL BI events in London last night. We had some great presentations from Reed Jacobson, Allan Mitchell and David Francis, beer, pizza, freebies… what more could you want? Thanks are also due to Simon Sabin and Tony Rogerson for doing the organising, Conchango for providing the rooms and Red-Gate for sponsorship. We’re thinking about doing another one in June, also in London (we’ll probably end up alternating between London and TVP) so if you’d like to present then let me know. Simon was also doing some experiments with Live Meeting so maybe we can start broadcasting these events to the world…

Written by Chris Webb

April 27, 2007 at 11:34 am

Posted in Events

Using the Create Cache statement

with one comment

Another interesting article from the SQLCat team on using the Create Cache statement:
I’m not sure why they say that it was introduced in SP2 since this has been around at least since AS2K and possibly before. Interestingly someone asked me only two days about this functionality and I’d completely forgotten about it despite all the work I’ve done on cache warming recently; I assumed it had been dropped in AS2005 (perhaps it had and maybe it’s only been reintroduced in SP2?). I played around with it a lot a few years ago on AS2K and never found it had any benefit but perhaps the architectural changes have rendered it more useful… I must update my cache-warming package to make use of this. There’s also a WITH clause variant too that isn’t mentioned in the article (although it’s in AS2K BOL) which needs further investigation too.
Lastly this article also mentions a new connection string property I’ve heard about which again was introduced recently, Disable Prefetch Facts.

Written by Chris Webb

April 26, 2007 at 9:04 am

Posted in MDX

The future of Essbase

leave a comment »

Interesting thoughts on which way the whole Hyperion/Oracle BI overlap might be resolved, from Cubegeek:

Written by Chris Webb

April 23, 2007 at 8:54 pm

Posted in On the internet

Business Objects to buy Cartesis

leave a comment »

Industry consolidation continues – at this rate there’ll be about three vendors left. Andy Hayler says pretty much everything that needs to be said here:

Written by Chris Webb

April 23, 2007 at 8:48 pm

Posted in On the internet

Cubulus – an open source OLAP engine

leave a comment »

I’ve had an email from Alexandru Toth about an open source OLAP project he’s working on. Here’s what he had to say:
I am developing an Open Source OLAP project  called "Cubulus". There is a presentation material at   , and an online demo at  .The source code is at .

In brief, Cubulus is an analytic engine + slice&dice web interface on top of relational database (MySQL at the moment) . It caches calculated cells, and is able to parse basic MDX queries. Project is in early alpha, and runs on  Mac OS X, on Windows .. and on Linux too :-)

Perhaps not ready for the enterprise just yet, but still a worthy effort and any new OLAP that supports MDX is ok with me.

Written by Chris Webb

April 21, 2007 at 10:45 pm

Posted in Open Source BI

Analysis Services Load Testing Tool

leave a comment »

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….

Written by Chris Webb

April 20, 2007 at 10:23 pm

Posted in Analysis Services

MDX Script Performance Analyser

with 3 comments

I do a lot of performance tuning as part of my consultancy work, and quite often when I start looking at a customer’s cube I find that for any given query that needs to be tuned there are several (sometimes hundreds) of calculations which affect the cells in the query and which could be the cause of performance problems. To help me work out which calculations are the ones that need to be looked at I put together a tool – the MDX Script Performance Analyser – which I’ve just got round to putting up on Codeplex so it can be shared:
Basically what it does is this:
  • First of all, you connect to the cube that your query runs against
  • Then you enter your query in the text box at the top of the screen and hit ‘Run Query’
  • This then starts the following process:
    • The tool reads the cube’s MDX Script and splits it up into its constituent statements, storing them in an array
    • It executes a Clear Cache command to ensure that all queries are run on a cold cache
    • It executes a Clear Calculations command so that for the current session the cube appears as though its MDX Script contains no commands
    • For each statement in the array of statements from the MDX Script, it then:
      1. Executes the first statement in the MDX Script within the session, so that the cube now acts as though its MDX Script contains only this statement and all previously executed statements
      2. Runs the query you entered in the textbox
      3. Stores how long the query took to run, plus other interesting metrics
    • Once the query has run on the equivalent of the entire MDX Script in the cube, a report is generated which contains graphs and charts illustrating the data captured earlier

As a result you get to see how much each calculation has contributed to the overall time taken by the query; when you see a big jump upwards in the query time graph (that isn’t followed by a big fall subsequently – which could happen with scoped assignments) then at that point in the MDX Script there’s an expensive calculation.

As you’ll see if you download the source code it is in a very basic state at the moment, but it works most of the time for me and has come in very handy on a number of occasions. There are a lot of changes and improvements that I’d like to make (such as recording the total number of cells returned by the query, the total number of non-empty cells and the number of Query Subcube events at each step) and if anybody out there has other suggestions or would like to sign up as a developer I’d be only too happy to hear from you.

I also need to thank Greg Galloway for making some invaluable contributions to the code and for fixing at least one critical bug for me. This is probably also a good point to mention another project on Codeplex that Greg and Darren Gosbell have been working on over the last few months, BIDS Helper:

… which contains some really useful extra functionality for people working with AS in BI Development Studio.

Written by Chris Webb

April 20, 2007 at 12:20 pm

Posted in MDX


Get every new post delivered to your Inbox.

Join 4,002 other followers