Chris Webb's BI Blog

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

Archive for May 2005

More Maestro

with one comment

While I was away in Munich more information on Maestro appeared. As before, Barbara Darrow on www.bizintelligencepipeline.com has the best coverage – see this article for instance – and for an insider’s perspective see Mat Stephen’s blog entry here. Interesting that it’s going to be a standalone product rather than a component of SQL2005: I think Mat’s argument about freebie products having no credibility also applies somewhat to products that get bundled on the same CD as SQL Server. In a way I wish that Analysis Services was a separate sku from SQL Server, it would make it easier to explain to customers that it’s not the same as the relational database and not dependent on it so much easier.

This thought, and the quote from a MS partner in Barbara Darrow’s article that "the impact on Teradata, Cognos, Business Objects, maybe even SAS, could be huge" leads me onto the question of how much impact it really will have. Will it kill the competition? They said that of Analysis Services when it was released, and although various other OLAP vendors panicked at the time and while it’s true that AS is now the leading OLAP server according to the OLAP Report, Hyperion, Cognos, Business Objects et al are still there and still selling. So even though I have no doubt that Maestro is going to be a very cool product I’m not sure that it’s going to have a massive impact.

Why? It’s nothing to do with the technology, but with the way that Microsoft is set up to sell this kind of thing. The Microsoft model is to work as far as possible through partner companies and maintain a minimal sales force of its own. This is great for keeping costs down but it does mean that when a customer puts out an RFP, very often Microsoft doesn’t turn up in person when its competitors do. A Microsoft BI partner may be extremely skilled and knowledgeable in technical terms but they are usually relatively small companies with limited resources, and customers who like to see ‘skin in the game’ take a dim view of Microsoft’s non-appearance. Even if Microsoft do turn up they may only send one or two people, and these people may or may not know much about BI. Compare this with a dedicated BI software company like Cognos or Business Objects that will have a much larger, much more dedicated sales force and can throw many more people at an opportunity. The same goes for marketing spend. When I went through Heathrow airport the other day I saw a massive Cognos advert above passport control; marketing for Microsoft BI tends to get lost in the overall SQL Server message (see Donald Farmer’s comments here). Oh well. Despite all this the products do very well and I’m sure if Microsoft did spend more on marketing and kill off the competition it would only lead to more anti-trust problems.

Anyway, to go back to Maestro, Mat Stephen has details on how you can nominate yourself for the beta program here. I filled in the survey today but it looks like they’re going to be very selective on who gets accepted (sample question: are you an MVP?). So I’m not sure I will get my hands on it, and since it’s still under strict NDA I won’t be able to blog about it even if I do…

Written by Chris Webb

May 24, 2005 at 10:44 pm

Posted in On the internet

BI Webcasts

leave a comment »

These have been pretty heavily promoted already, but in case you’ve not noticed there are a lot of SQL2005 BI webcasts coming up:
http://www.microsoft.com/events/series/sqlserverbi.mspx

Free t-shirts, as ever, only available to US residents. Hohum. Good thing I’ve already got mine…

If you only have time to go to a few I can recommend the ones on Project REAL (I think they’re the same content as I saw in Munich) which have some useful practical content. The first one is here:
http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032273451&EventCategory=4&culture=en-US&CountryCode=US

There’ll be a Project REAL page appearing on the Microsoft website soon, I’m told, with lots of interesting white papers etc.

Written by Chris Webb

May 24, 2005 at 9:59 am

Posted in On the internet

PASS European Conference

leave a comment »

Like several other SQL Server bloggers out there (eg Christian Wade, Marco Russo, Steve McHugh to name a few) I was at the PASS European conference in Munich the other week. It was the first PASS conference I’ve been to and I had a really good time. The standard of the presentations was very high – in-depth content rather than the high-level introductory stuff that is the norm when a product like SQL2005 is in beta – and meeting people and being able to discuss shared problems was very valuable too. The entry on Marco Russo’s blog about many-to-many dimensions is a case in point: if we hadn’t had that conversation I might not have found out that chaining m:m dimensions together was in fact going to be supported.

Unfortunately my training budget won’t stretch to a trip to the much bigger US PASS conference in September, but a look at the session schedule makes me think it will be even better than the European version. I can recommend Christian Wade’s SQL CLR presentation and the series on Project Real from Len Wyatt & team; even though he’s doing a different presentation from the one he did in Munich I’m sure Kurt Allebach will be worth seeing too.

Written by Chris Webb

May 23, 2005 at 2:38 pm

Posted in Events

New white papers on Richard Tkachuk’s site

leave a comment »

See:

http://www.sqlserveranalysisservices.com/default.htm

The one on many-to-many dimensions is very good!

Written by Chris Webb

May 14, 2005 at 10:18 am

Posted in On the internet

AS2KSP4 unwelcome changes

leave a comment »

As I’m sure you all now by now, AS2KSP4 has now been released and is available here:
http://www.microsoft.com/sql/downloads/2000/sp4.asp

One very unwelcome change is to the DSN Requested and DSN Used Perfmon counters, which are now practically useless. The values are now in scientific form (eg 1.3112e+012) which means that you can only see what levels your queries are hitting for the first five dimensions in your cube – which makes building aggregations manually (one of my favourite tricks for performance optimisation, detailed here) impossible. Grr! I noticed this a while ago and logged a bug in betaplace, but got no feedback. Double Grr!

Written by Chris Webb

May 9, 2005 at 1:09 pm

Posted in Analysis Services

Subcubes in the FROM clause

with 4 comments

Don’t know how I’ve not managed to talk about this one, as it’s one of the biggest new additions to MDX. Basically what this functionality means is that instead of putting a cube or a perspective name in the FROM clause you can put a SELECT statement which defines a subcube instead, similar to a SQL subselect. So what is a subcube? It’s a subset of the cube you’re eventually going to query, sliced by members on one or more dimensions. Think of the effect as being the same as if you had dimension security on your cube, only allowing you to see certain members on some dimensions.

For example with the Adventure Works cube, say you were interested in analysing the combined sales of the Accessories and Clothing product categories, you could run the following query:

select [Measures].[Internet Sales Amount] on 0,
[Date].[Day Name].members
on 1
from (
select ({[Product].[Category].[Category].&[4], [Product].[Category].[Category].&[3]})
on 0 from [Adventure Works])

In this example, what we’re saying in this query is: find me the Internet Sales Amount for each day of the week, from a version of the Adventure Works cube which only has the Accessories and Clothing members on the Product Category hierarchy. Note that for this example we could also use a set in the WHERE clause to get the same values, as follows:

select [Measures].[Internet Sales Amount] on 0,
[Date].[Day Name].members
on 1
from [Adventure Works]
where({[Product].[Category].[Category].&[4], [Product].[Category].[Category].&[3]})

However, there is a big difference between the two approaches. When you put the Product Category hierarchy in the WHERE clause it can’t appear on any other axis, but this isn’t true of the subcube approach as the following example shows:

select [Measures].[Internet Sales Amount] on 0,
[Date].[Day Name].members
*
[Product].[Category].[Category].members
on 1
from (
select ({[Product].[Category].[Category].&[4], [Product].[Category].[Category].&[3]})
on 0 from [Adventure Works])

The thing to notice is that [Product].[Category].[Category].members now only returns the Accessories and Clothing members, and not the Bikes or Components members which are present in the real Adventure Works cubes. It’s the structure of the cube itself which has been altered.

You’re not restricted to one dimension in your subcube definition, of course, as the following example shows:

select [Measures].[Internet Sales Amount] on 0,
[Date].[Day Name].members
on 1
from (
select (
{[Product].[Category].[Category].&[4], [Product].[Category].[Category].&[3]}
,{[Date].[Calendar Year].[Calendar Year].&[2004], [Date].[Calendar Year].[Calendar Year].&[2003]}
)
on 0 from [Adventure Works])

and you can even do quite sophisticated things like:

select [Measures].[Internet Sales Amount] on 0,
[Product].[Category].[Category].members
*
[Date].[Calendar Year].[Calendar Year].members
on 1
from (
select (
{([Product].[Category].[Category].&[4],[Date].[Calendar Year].[Calendar Year].&[2004])
, ([Product].[Category].[Category].&[3], [Date].[Calendar Year].[Calendar Year].&[2003])
})
on 0 from [Adventure Works])

…which shows that you’re not restricted to a simple product of sets of members from various hierarchies. You can also do nested subselects, which make it easy to express otherwise complex multi-step filtering operations. For example:

select [Measures].[Internet Sales Amount] on 0,
[Date].[Day Name].members
on 1
from (
select (topcount([Customer].[Country].[Country].members, 2, [Measures].[Internet Sales Amount]))
on 0 from (
select(
{[Product].[Category].[Category].&[4], [Product].[Category].[Category].&[3]})
on 0 from [Adventure Works]))

…gives me the internet sales amount by day of week for the top two countries for the Accessories and Clothing product categories. This example doesn’t do anything you couldn’t do in a single subselect, but it’s easier to understand; I assume that there would be a performance overhead with doing this kind of nesting though.

Finally, one other point of interest is that calculated members have the ability to look ‘outside’ the subcube. For example:

with member measures.test as [Product].[Category].currentmember.prevmember.name
select [Measures].test on 0,
[Product].[Category].[Category].members
on 1
from (
select ({[Product].[Category].[Category].&[4], [Product].[Category].[Category].&[3]})
on 0 from [Adventure Works])

…shows how measures.test, for Clothing, returns the string ‘Bikes’ even though the Bikes member isn’t in the subcube. This is necessary I suppose for calcs like previous period growth, which would be pretty useless if you put non-contiguous time periods in your subcube.

So, finally, what is all this useful for? Lots of things I’m sure, but the most obvious use to me is as a better way of handling VisualTotals type queries – if you think about how Excel pivot tables work and the MDX they generate, this seems to be a much better way of achieving the same result. I definitely need to research this in much more detail…

Written by Chris Webb

May 6, 2005 at 2:00 pm

Posted in MDX

Amazon data mining applications

leave a comment »

Interesting article on Wired this morning about new features on Amazon which represent a new and imaginative application of data mining (although this probably isn’t data mining in the sense that I understand it, but even if it isn’t it’s still fascinating). Using ‘Statistically Improbably Phrases’ to link to other books with similar content seems to work pretty well but I’m not sure about the usefulness of some of the other stats… or at least, when I compared ‘MDX Solutions’ with James Joyce’s ‘Ulysses‘ and found that the latter was much less complex and much more readable than the former, I needed to convince myself something was wrong!

Written by Chris Webb

May 6, 2005 at 11:01 am

Posted in On the internet

Follow

Get every new post delivered to your Inbox.

Join 3,146 other followers