Chris Webb's BI Blog

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

Archive for July 2007

Panorama: One Year On

with 4 comments

After the Proclarity acquisition last year many people, myself included, thought the writing was on the wall for Panorama: after all the two companies’ product lines were very similar and if Proclarity was going to be supplied by Microsoft then there wasn’t going to be much point in looking at Panorama any more. However, one of the things that surprised me at the Microsoft BI Conference a few months back was the big display that Panorama put on. A big stand in a central location with leggy girls luring the attendees (at least the male ones) to it, a party at the top of the Space Needle – this was a company that was not dead, or at least making a pretty big effort to show that it wasn’t dead.

Of course the real test is whether the software is any good or not. In keeping with their bullish mood Panorama were handing out DVDs with their Novaview suite pre-installed on a virtual machine (you can download it here: http://challenge.panorama.com/forms/default.aspx) and since it was a while since I’d last seen what they’d got to offer (and after some encouragement from their marketing department) I was curious to check it out. After a lot of false starts surrounding licence keys etc and help from Panorama – and I have to say that in my experience their pre-sales support has always been very good – I got it running on my laptop and thought I’d share my impressions here.

I have to admit I was disappointed with the Novaview Desktop tool at first: it didn’t look as if anything had changed in the last few years. The UI looks very outdated in a VB6 way and while it’s easier to use than I remember, I’m still not convinced that it’s as intuitive as it should be. Even worse, within a few minutes of using it I got one of those unhandled (although unfatal) error messages that Panorama was always famous for – which simply isn’t acceptable today. These might be purely cosmetic points but users are more interested in this type of thing than some obscure aspect of MDX generation. After a bit more time though I felt more positive. For the power user it does pretty much everything that you’d ever want it to do, such as enter your own MDX, do writeback, advanced filtering, creating calculations; in fact it clearly does what every demanding customer has ever wanted it to do so it’s more than likely able to meet any obscure querying requirements you have.

I took my misgivings back to Panorama and they told me that the desktop client will be dropped in the next release, due Q1 2008, and from that point there will only be an AJAX-based zero-footprint client and a rich client based on Adobe Flex. It’s interesting that the latter is not based on .NET or even Silverlight; not only a move away from the desktop, which is only to be expected, but a move away from the Microsoft dev platform which I suppose makes sense given Panorama’s repositioning of itself away from being a purely Microsoft partner to working with SAP as well. Presumably it will have much the same capabilities as the current desktop client but look rather better… 

In the meantime, the comparison between Novaview Desktop and ProClarity Desktop is one that has been made a lot in the past and is probably worth making again. In terms of querying functionality they’re neck-and-neck but in my opinion Proclarity is easier to use and looks marginally better, although it is still falls way short of what I’d expect from a modern BI tool. The problem with the Proclarity Desktop tool is that is has it has been declared dead by Microsoft: the last official release was made late last year and it seems there’s no place for a rich client in the bright shiny web-enabled PerformancePoint future. I’ve heard various rumours about it such as that it was going to be turned into an Excel addin or, more recently that it was going to be rewritten in .NET, but that lack of any clear direction from Microsoft on its future is a bit frustrating. I suspect that Microsoft have decided that Excel is the only desktop tool anyone is going to need, but I think there’s still a role for a dedicated client for power users and I know a lot of other Proclarity users feel the same way. If you’re in the market for a full-featured AS desktop tool then there’s little point in going with Proclarity Desktop now, so Novaview wins by default given that there’s a clear roadmap for its future.

As far as the web-based querying and dashboarding functionality goes there is clear competition with what Panorama and PerformancePoint have to offer. A lot of companies will simply go with the Microsoft offering simply because it’s from Microsoft, and that’s a perfectly valid decision; anyway, given the lineage of the product it’s going to be a lot better than a typical Microsoft version 1.0. Why choose Panorama then? Panorama claim to be a lot more scaleable on the web than the old Proclarity equivalents; it’s probably too early to tell whether the same will be true of PerformancePoint though. The cross-platform capabilities will probably be the key: from what I’ve seen in my Analysis Services consultancy work, a lot of companies using AS also use SAP BW and a common front-end for both could be an enticing prospect. I’m also told that Panorama will be building functionality behind PerformancePoint to enable import of data from other platforms and in front of it to enable integration with other applications and business processes – the kind of value-add stuff that Microsoft can’t offer because of its longer release cycles and limitations on who they can partner with.

So, it looks like Panorama have got a future after all. Just as the Microsoft’s entry into the OLAP server market didn’t lead to the immediate bloodbath among rival OLAP vendors that many predicted, so its entry into the client tool market hasn’t (yet) killed off the old third-party client tools market. The delay in getting PerformancePoint released after the Proclarity acquisition and the fact that most companies are a long way from rolling Office 2007 onto their desktops means that companies like Panorama have had a chance to work out a survival strategy - and the choice this means can only be a good thing for us end users.

Written by Chris Webb

July 31, 2007 at 2:48 pm

Posted in Client Tools

Metashare

leave a comment »

Mark Garner has just dropped me a mail to let me know that Metashare, a tool he’s been working on for generating metadata documentation from SQL 2005 data warehouses and and SSIS packages, has just got to the beta 1 stage. Here’s his blog entry announcing this:

http://mgarner.wordpress.com/2007/07/27/metashare-beta-1/

…and you can find out more about it, and download it, from Sourceforge:

http://metashare.sourceforge.net/

Written by Chris Webb

July 27, 2007 at 9:18 pm

Posted in On the internet

PerformancePoint release date

leave a comment »

David Francis has the scoop on the release date for PerformancePoint:
Apparently RTM will be sometime in September.

Written by Chris Webb

July 21, 2007 at 9:46 pm

Posted in PerformancePoint

Oracle 11g materialised views

leave a comment »

You may (or may not) have noticed that Oracle 11g got released recently. One of the new features that caught my eye is discussed in these two posts by Seth Grimes and Doug Henschen on the Intelligent Enterprise blog:
Seems like Oracle have used a new OLAP engine (ie not Express or Essbase) as the basis for managing large numbers of materialised views. When I first read this I thought the kind of feature that only a die-hard relational-database-lovin’ OLAP-denier could ever get excited about, but then I realised that that’s probably the point. It would be cool if SQL Server could use Analysis Services in the same way, and if it could it would open the eyes of a lot of new people to the power of Analysis Services.

Written by Chris Webb

July 16, 2007 at 11:58 am

Posted in On the internet

Resurrect the XMLA Council!

with one comment

A few weeks ago I had an interesting email conversation with Nick Goodman and Julian Hyde after Nick noticed that the XMLA Council’s web site which used to be at www.xmla.org now redirects to a site ‘brought to you by Simba Technologies’. Now I have nothing against Simba – in fact they have a number of interesting products – but the lack of an independent web site highlights the fact that the XMLA Council is in effect dead, having not met (so I understand) for several years now. A few days later I saw Andrew Wiles had blogged on this topic too:
http://andrewwiles.spaces.live.com/blog/cns!43141EE7B38A8A7A!199.entry

As Andrew points out, at present XMLA interoperability is something of a myth. There are a few tools that do manage it such as Rex which, unlike Mosha, I was able to use successfully against AS2005 as well as Mondrian although it’s probably not worth the bother (it’s nowhere near as good as SQL Management Studio for running MDX queries). JPivot is another open source tool that claims to work against AS and Mondrian but although I know other people have got it working against AS2K (see for example here: http://forums.pentaho.org/showthread.php?t=49954) and AS2005 I’ve never been able to do so against AS2005 despite several hours of effort. The only commercial product that works against multiple platforms that I’ve had experience of is Panorama (though I know there are others out there), and as this blog entry suggests it’s been a hard slog for them to work with SAP’s bizarre implementation of MDX.

I guess what happened with the XMLA Council is that like a lot of initiatives like this there was an initial burst of enthusiasm that dissipated once its ideals came into conflict with the demands of real-world product development. I know it’s pie-in-the-sky to expect perfect cross-platform interoperability but I think the present situation could be made a lot better and I think the time has come to resurrect the XMLA Council – and I think it’s up to Microsoft to take the lead on this. I don’t want to suggest that Microsoft have some kind of moral obligation to do this as de facto owners of the spec, rather that while everyone would benefit from increased interoperability Microsoft would benefit most. The first reason why is that the XMLA-compatible client tool market is dominated by tools that work against AS and which are sold by Microsoft partners, and they would be able to expand their potential customer base to support other servers like SAP BW and Essbase. More importantly though, the client tool that everyone really wants to use is Excel and if it were possible to hook Excel 2007 up to other OLAP engines then it would cement its position as the BI client tool of choice. Reporting Services’ support for SAP BW and Essbase shows that Microsoft are interested in supporting competing OLAP tools so is it unrealistic to expect Excel could support the same platforms?

Written by Chris Webb

July 10, 2007 at 9:46 pm

Posted in Client Tools

When are named sets in the WITH clause evaluated?

with 3 comments

Following on from my postings of last week about dynamically-generated sets, I had a really interesting email yesterday from my colleague at Solid Quality Mentors Francesco De Chirico. He sent me a variant of one of my queries which worked when he wasn’t expecting it to work and to be honest when I looked at it, I couldn’t work out why it worked either. However after a bit of testing I realised he’d discovered something quite important that I at least didn’t know; as with my last post on sets in the MDX Script it all makes sense when you think about it though (see also Mosha’s comment on my last post for some explanation).

Consider the following query:

with
set mycheapset as {[Department].[Departments].&[2],[Department].[Departments].&[3]}
select {} on 0,
mycheapset on 1
from [Adventure Works]

It returns in a second on a cold cache on my laptop, as you’d expect. Now consider the same query with an extra, expensive set declaration in it that isn’t referenced in the query:

with
set myexpensiveset as bottomcount(
{[Department].[Departments].&[2],[Department].[Departments].&[3]}
*
{[Scenario].[Scenario].&[1],[Scenario].[Scenario].&[2] }
*
[Account].[Account].[Account].members
*
[Date].[Date].[Date].members
, 10,[Measures].[Amount])
set mycheapset as {[Department].[Departments].&[2],[Department].[Departments].&[3]}
select {} on 0,
mycheapset on 1
from [Adventure Works]

This now executes on a cold cache in 13 seconds on my laptop despite returning exactly the same results. Even a query which doesn’t reference any of these sets executes in a minimum of 13 seconds:

with
set myexpensiveset as bottomcount(
{[Department].[Departments].&[2],[Department].[Departments].&[3]}
*
{[Scenario].[Scenario].&[1],[Scenario].[Scenario].&[2] }
*
[Account].[Account].[Account].members
*
[Date].[Date].[Date].members
, 10,[Measures].[Amount])
set mycheapset as {[Department].[Departments].&[2],[Department].[Departments].&[3]}
select {[Measures].[Internet Sales Amount]} on 0
from [Adventure Works] 

As with named sets in the MDX Script, named sets in the WITH clause are being evaluated when the query runs regardless of whether they’re referenced or not.

This has obvious implications for performance tuning: if you’ve got an MDX query which has a lot of expensive set definitions in the WITH clause that aren’t used, then you really need to delete them! However you might have queries in custom-built applications or something like SSRS where sets are present which may or may not be necessary, depending on certain parameters. Here’s an example of the type of thing I’m talking about:

with
set myexpensiveset as extract(bottomcount(
{[Department].[Departments].&[2],[Department].[Departments].&[3]}
*
{[Scenario].[Scenario].&[1],[Scenario].[Scenario].&[2] }
*
[Account].[Account].[Account].members
*
[Date].[Date].[Date].members
, 10,[Measures].[Amount]), [Department].[Departments])

set mycheapset as {[Department].[Departments].&[2],[Department].[Departments].&[3]}

member measures.test as
iif([Department].[Departments].currentmember is [Department].[Departments].&[2],
count(myexpensiveset), count(mycheapset))

select {[Measures].test} on 0,
[Date].[Calendar Year].members on 1
from [Adventure Works]
where([Department].[Departments].&[3])

In this case the WHERE clause might be parameterised and could contain either Department 2 (which would return the count of the expensive set) or Department 3 (which would return the count of the cheap set). You can rewrite this to be much more efficient by taking advantage of the fact that sets are evaluated after the WHERE clause so you only evaluate the expensive set when you need to:

with
set myiifset as
iif([Department].[Departments].currentmember is [Department].[Departments].&[2],
extract(bottomcount(
{[Department].[Departments].&[2],[Department].[Departments].&[3]}
*
{[Scenario].[Scenario].&[1],[Scenario].[Scenario].&[2] }
*
[Account].[Account].[Account].members
*
[Date].[Date].[Date].members
, 10,[Measures].[Amount]), [Department].[Departments]),
{[Department].[Departments].&[2],[Department].[Departments].&[3]})

member measures.test as count(myiifset)

select {[Measures].test} on 0,
[Date].[Calendar Year].members on 1
from [Adventure Works]
where([Department].[Departments].&[3])

Written by Chris Webb

July 5, 2007 at 8:13 pm

Posted in Uncategorized

When are named sets in the MDX Script evaluated?

with 5 comments

Interesting discovery I made last week: I was tuning a cube and noticed that all my queries, when they were run on a cold cache, were much slower than I was expecting – even the most basic query seemed to take at least 40 seconds. After a lot of head-scratching I looked in Profiler and found the answer to what was going on, and it turned out to be the fact that there were two named sets in the MDX Script that used very complex expressions and which together took 40 seconds to evaluate. Commenting them out reduced all my query times by 40 seconds. The problem was that I wasn’t referencing these sets in any of my queries…!

I had thought that named sets in the MDX Script were evaluated the first time they were actually used in a query but this is demonstrably not the case. Consider the following query:

with set mytest as bottomcount(
{[Department].[Departments].&[2],[Department].[Departments].&[3]}
*
{[Scenario].[Scenario].&[1],[Scenario].[Scenario].&[2] }
*
[Account].[Account].[Account].members
*
[Date].[Date].[Date].members
, 10,[Measures].[Amount])
select {} on 0,
mytest on 1
from [Adventure Works]

Executed on a cold cache on the Adventure Works cube it returns in 13 seconds on my laptop. Consider also the following very basic query:

select {[Measures].[Internet Sales Amount]} on 0,
[Date].[Calendar Year].members on 1
from [Adventure Works]

This returns in under a second on a cold cache on the Adventure Works cube. Now go into Visual Studio to edit the Adventure Works cube and add the set from the first query as a named set at the end of the MDX Script so:

create set myexpensiveset as
bottomcount(
{[Department].[Departments].&[2],[Department].[Departments].&[3]}
*
{[Scenario].[Scenario].&[1],[Scenario].[Scenario].&[2] }
*
[Account].[Account].[Account].members
*
[Date].[Date].[Date].members
, 10,[Measures].[Amount]);

If you then clear the cache and rexecute the second query, which used to complete in only 1 second, it will now take 13 seconds despite the fact it doesn’t reference this set in any way. If you take a look in Profiler you can see that the Execute MDX Script event, which is fired the first time you run a query on a cold cache, is now taking 13 seconds and that’s what’s causing the query as a whole to take so long. So named sets in the MDX Script are evaluated when the MDX Script is executed, and that takes place the first time you run a query after the cache has been cleared – either by running a ClearCache command or by processing your cube.

Written by Chris Webb

July 4, 2007 at 10:22 pm

Posted in MDX

olap4j

with one comment

I’ve just come across olap4j (http://www.olap4j.org/) which might be of interest for anyone out there working with Java to access OLAP cubes – as the website says, it’s like JDBC for OLAP (and so I guess you can also say it’s like ADOMD.Net for Java). It’s designed to work with a range of OLAP servers including AS2005. The interesting thing about it for me isn’t the cross-platform support, though, but that it in addition to support for MDX it will have an API for programmatically generating queries – something that I think would be useful to have in Microsoft’s own data access layer. Implemented correctly it would take away the need for programmers to have to learn about how to write efficient MDX and hopefully bring about a kind of standardisation of queries which would make writing calculations on the cube easier.

Written by Chris Webb

July 3, 2007 at 1:30 pm

Posted in Open Source BI

Follow

Get every new post delivered to your Inbox.

Join 2,866 other followers