Impressions of Pentaho

Not Microsoft BI-related, but I guess we all know we need to keep an eye on what’s happening in the open source BI space. To that end I thought I’d link to the Bayon Technologies blog (usually covering Oracle BI) where there’s a good, ongoing series of posts on Pentaho. See:
…for the story so far.
 

Data Mining Amazon Wish Lists

I’ve seen this article linked to by several people today and thought it was worth posting up myself:
Not true data mining as BI folks understand it, but once you’ve got the data just think what you could do with it using AS2005 data mining (something similar to what Amazon are already doing with it, I guess). By the way, if anyone wants to buy me a present…

NON EMPTY limitations

I was putting together a ridiculously large query for my previous post to try to test performance of the HAVING clause, and tried to run the following on Adventure Works:
 
SELECT [Measures].[Internet Order Quantity] ON 0,
NON EMPTY
[Customer].[Customer].MEMBERS
*
[Date].[Date].MEMBERS
*
[Product].[Product].MEMBERS
*
[Geography].[City].MEMBERS
ON 1
FROM [Adventure Works]
 
To my surprise, I got the following error message:
 

Executing the query …

The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.

Execution complete

 
Obviously some kind of internal limit here to do with NON EMPTY: the query runs if you remove it. Now 4,294,967,296 tuples is a lot of tuples but imagine that you had a million customers and ten thousand products, and you wanted to find out which customers had bought which products – you’d want to find the non empty combinations in a set of tuples which is still larger than that limit. OK in any normal cube this would probably bring back far too many rows to be a useful query, but what if you had a very specific slice in the WHERE clause (eg adding WHERE([Ship Date].[Date].&[1])  onto the end of the above query still gives the same message) so that you’d only expect to return a very small number of rows in your results? There are certainly going to be ways to work around this if you are writing your own MDX, but I wonder if any of the commercially available client tools which use NON EMPTY know to do this yet? Hmm…
 
 

The HAVING clause

The fact that there’s now a HAVING clause at all in AS2005 MDX doesn’t seem to be publicly documented anywhere, as far as I know; it’s one of those hidden features we found out about while researching ‘MDX Solutions’. While George has a full write-up on how it behaves in the book (coming soon to a store near you) here’s a quick overview of what it does…
 
Basically, it allows you to filter the contents of an axis without having to use the FILTER function. So, for example, the following Adventure Works query only returns the tuples on the ROWS axis which have Internet Order Quantity greater than 1000:
 

SELECT [Measures].[Internet Order Quantity] ON 0,
NON EMPTY
[Date].[Date].MEMBERS
*
[Product].[Subcategory].MEMBERS
*
[Geography].[Country].MEMBERS
*
[Customer].[Gender].MEMBERS
HAVING
[Measures].[Internet Order Quantity]>1000
ON 1
FROM
[Adventure Works]

 
Of course this isn’t anything you couldn’t do before in AS2K, so what’s the point of it? Well, first of all, if you know how you’d do this using a FILTER then you’ve probably already realised that using the HAVING clause makes your MDX much easier to read; as someone who in the past wrote the MDX generation component for a client tool, I can also say that it will also make generating MDX in code easier.
 
There also seem to be some performance benefits to using it too. Since I was told the HAVING condition is applied after the NON EMPTY, I rewrote the above query to use a combination of NONEMPTY and FILTER as follows:
 

SELECT

[Measures].[Internet Order Quantity] ON 0,
FILTER(
NONEMPTY(
[Date].[Date].MEMBERS
*
[Product].[Subcategory].MEMBERS
*
[Geography].[Country].MEMBERS
*
[Customer].[Gender].MEMBERS
,[Measures].[Internet Order Quantity])
,[Measures].[Internet Order Quantity]>1000)
ON 1
FROM
[Adventure Works]

 
I was expecting this query to perform exactly the same as the first one; however the version which uses HAVING seems to consistently perform better on my machine, although only slightly so. I’d be interested to know whether this is reproducible and why this is. 

Blog Birthday

It’s my blog’s first birthday today! Seems like I’ve been writing it forever… Anyway, I’m quite pleased with how the first year of blogging has gone – I’ve managed to keep my resolution and posted something just about every week, and hopefully it’s been interesting and useful. I’ve learnt a lot too, made a lot of new friends and even managed to become the top-ranked "Chris Webb" on Google (for the moment)!
 
Next year will see a lot of changes for me professionally and with a bit of luck they will leave me more time for blogging as well as provide me with some new Microsoft-BI-related topics to blog about. As always, I welcome any kind of feedback/suggestions/tip-offs for news, either in public via comments or by email (check my profile for how to contact me).
 
Happy New Year!

New version of the Microsoft Excel Addin

Marco Russo notes that a new version of the Microsoft Excel Addin has been released which supports AS2005:
(Teo Lachev also comments on this here; I agree with what he says about OWC, and I’m trying to find out whether there will be an OWC in Office 12)
 
It’s a pleasant surprise that Microsoft have actually released another version of this addin, given that they’re now hard at work at integrating its features into Excel 12. I didn’t notice much that was different apart from the AS2005 support though.
 
However, if I can be permitted one small moan, I did spot a problem that I’ve seen in other AS2005-enabled clients and which I hope won’t turn into a trend. I found it by creating a report using Adventure Works, putting the measure [Internet Sales Amount] on columns and trying to put something from the Geography dimension on pages, which resulted in a dialog box informing me that the Geography dimension was unrelated to the [Internet Sales] measure group and stopping me from completing the operation. Now in 99% of cubes this would be a good thing to do, but I’ve already built a few cubes where I have dimensions that have no relation to any measure group but where selections on them do impact calculations (think solving the start/end date problem, where you might want to create an end date dimension with no relation to a measure group); of course, this client feature stops you from being able to design cubes in this way. It’s a case of the designers of the tool being a little bit too helpful… and it would be good to be able to turn this behaviour off in the Options dialog.
 

Visio 12 and BI

And the third link for today comes from Nick Barclay’s excellent blog, which I’m sure many of you read already – it’s on Visio 12’s BI capabilities:
 
Check out the PPT that he links to at the bottom of the post – some of those screenshots look very cool.

SPSS Statistical Services for SQL2005

The second potentially interesting set of BI-related tools today I saw mentioned on Jamie MacLennan’s blog: a beta of SPSS Statistical Services for SQL2005, which you can find out more about here:
 

A quick look at the user guide, which is also downloadable, shows what it’s all about. For AS2005 users there’s an addin for the Business Intelligence Development Studio which allows you to do two things: create calculated columns in your dsv which bin/band/bucketise values (for example if you had a Customer Age column you could use this to create a column which grouped ages into a number of age range categories), and explore the data in SPSS. For SSIS users there are a bunch of data flow and transformation components to do things like interact with SPSS data sources, do anomaly detection, and use various SPSS statistical functions.

SQL2005 BI Metadata Samples Toolkit

Courtesy of Karen Watterson, I’ve just found the ‘SQL2005 BI Metadata Samples Toolkit’, downloadable from here:
 
The demo video that you can see on the page above gives you a good idea of what this set of tools and samples does: it allows you to analyse the dependencies between objects in your AS databases and/or SSIS packages. So for example in an AS database you can click on a cube dimension and it will tell you which database dimension, data source view, data source and relational tables it has a dependency on. Might be quite useful on a large project where you have many developers working on the same cube.
 
Note that the sample tools need to be edited so that they point to the SQL Server/AS instances you are interested in and then compiled before they will work.
 
UPDATE: there’s a white paper to go along with these tools which can be downloaded here: