As I've mentioned, my recent thoughts on client tools (see here and here) have prompted a lot of interest around the Analysis Services community. One result is that Panorama have asked me to host a webinar for them where I get to sound off about the state of the client tool market before they show you their latest stuff. Yes, I'll be paid for it but I'm not going to be promoting their products directly (I feel like I need to justify myself!), just repeating my standard line that if you want to do anything serious with Analysis Services you should at least check out the range of third-party client tools available rather than stick blindly with what MS gives you - and given that Panorama are the largest vendor of third-party client tools for Analysis Services, they deserve to be on the list of tools to check out. Here's the link to sign up for the webinar:

http://www.panorama.com/webcasts/archives/2008/webinar-with-cwebb-oct-21.html

So the SQL marketing people have put together a website to plug SQL2008 share the SQL Server 2008 experience:
http://www.sqlserverexperience.com/

Actually, there are some quite interesting (in a Channel 9 sort of way) videos up there in the 'For Developers' section, including a few interviews with members of the AS dev team. Worth a look.

While I was at SQLBits I had the pleasure of meeting Tomislav Piasevoli (who has come all the way from Croatia especially), someone who has been very active on the Analysis Services MSDN Forum recently and who knows a lot about MDX. His company, Softpro, sells an Analysis Services client tool called Cubeplayer and he very kindly gave up his lunchtime to gave me a detailed demo. As I said recently the general feeling of frustration surrounding Microsoft's client tool strategy has made me look again at the third-party client tool market and decide to review some of these tools here, and this look at Cubeplayer is the first in the series. Remember, if you've got a client tool you'd like me to look at, please drop me a line...

The first thing to say about Cubeplayer is that it's a tool for power users and consultants, not the average user who might want to browse a cube. As such it's going to appeal to the fans of the old Proclarity desktop client, which it vaguely resembles in that it's a fat client with a lot of very advanced query and analysis functionality. It's not part of a suite - there's no web client etc - but it includes dashboarding functionality that's only available through the tool itself, and also has the ability to publish queries up to Reporting Services.

What can it do? Well, the web site has a good section showing video demos of the main functionality, but here are some main points:

• It can certainly do all the obvious stuff like drag/drop hierarchies to build your query, as well as more advanced selection operations such as the ability to isolate individual members in a query, drill up and down on individual members or all members displayed. It also has a number of innovative features like the ability to click on a cell and drill down on it, which means that you drill down on every member on every axis associated with this cell.
  
• It also supports all the more advanced types of filtering and topcounts that you'd expect from a tool like this; in fact, it seems to do pretty much anything you can do in MDX. This does give you an immense amount of power and flexibility, but sometimes at the expense of ease-of-use. Take the old nested topcount problem, solved in MDX using the Generate function: any advanced client tool will have to handle this scenario and Cubeplayer certainly does in its Generate functionality, but would you even expect a power user to be able to understand what's going on here and remember they have to click some extra buttons to get this to happen?
• There's a nice feature where you can choose to display the data in your grid either as actual values, ranks, percentages or other types of calculation. This makes it really easy to make sense of large tables of data.
  
• It has a whole load of built-in guided analyses such as 'How Many?', 'Show Me', ABC analysis (for segmentation) and Range analysis. This for me is a real selling point - I've been asked several times, for example, about doing ABC analysis with Analysis Services and I'm not sure I've seen another tool that does it.
  
• Another cool thing I've not seen before is the ability to put two queries side-by-side and, if they have selections on the same hierarchy, do operations like unions, intersects and differences on the selections.
• There's an MDX editor (with intellisense and other useful stuff) where you can write your own queries. Again, not something that even a power-user might want to do, but if you're a consultant who knows MDX it's a useful feature for those times when you know you can write the query but you can't get the query builder to do exactly what you want.
• You can generate Reporting Services reports from a query view. You probably already know my opinions on the native support for Analysis Services within Reporting Services, and this certainly does make it much easier to create cube-based reports.

Overall, definitely worth checking out if you're in the market for this type of tool. There are a few criticisms to be made: as I said, I'm not sure it's as easy to use as it could be although this is partly the price you pay for the richness of the functionality; there are some strange lapses in UI design such as the way all dialogs have 'Accept' and 'Cancel' buttons with icons on, instead of the usual plain 'OK' and 'Cancel'; and charting is competent but not up to the standards of the best visualisation tools (I think many vendors would do well to look at their tools and ask themselves "What would Stephen Few think?" - it might not be very complimentary).  In my opinion, though, it's a very strong and mature tool and the positives far outweigh the negatives.

One final point: Tomislav mentioned he was looking for reseller partners outside Croatia. If you're interested in this I can put you in touch with him.

As you may have already seen, Microsoft has just announced it has closed its acquisition of DATAllegro:
http://www.microsoft.com/presspass/press/2008/sep08/09-16DAPR.mspx?rss_fdn=Press%20Releases

More details will be forthcoming at the BI Conference, CTPs will be available in the next year or so and the product is slated for release in the first half of 2010 (for SQL2010 then?). It'll be interesting to see what form it actually takes exactly. As I've said before I suspect it would be easier to get Analysis Services working with this technology than many people think, and I also still wonder whether MS might also be thinking about buying or building a column-oriented database too.

As a counter to this euphoria, here are some interesting links... First, take a look in the discussion in the comments here:
http://www.dbms2.com/2008/07/24/how-will-oracle-save-its-data-warehouse-business/
Plenty of people with an axe to grind, I'm sure, but some points worth considering. Also worth checking out for a dissenting point of view is Kevin Closson's Oracle blog, which discusses DATAllegro a few times, for example:
http://kevinclosson.wordpress.com/2008/07/07/i-know-nothing-about-data-warehouse-appliances-and-now-so-wont-you-part-ii-datallegro-supercharges-fibre-channel-performance/

It's always good to take the blog-equivalent of a cold shower before getting too worked up about a new feature or technology. I'm sure that the guys at MS have done their due diligence on DATAllegro, and that come 2010 we'll have a solution that is way, way more scalable than what we have right now, but let's also set our expectations appropriately - it is going to be a version 1.0, and the competition isn't going to stay still in the next two years either.

Thinking more on the subject of Chrome and web-based BI, my lunchtime browsing today brought me to the topic of site-specific browsers, something I'd not come across before although it's been around for a while. Here's what Wikipedia has to say about them:
http://en.wikipedia.org/wiki/Site_Specific_Browser

There are quite a few site-specific browsers out there but the Bubbles web site does a good job of showing off the kind of thing that's possible:
http://bubbleshq.com/

Here's a thought: wouldn't it be cool to have a site-specific browser for Reporting Services? Just think of the ways you could improve the user experience: launch RS direct from the desktop, always have it available in the system tray, better support for parameter display and selection (this would be a key feature), make it easier to add corporate branding to the interface, the ability to save and then organise local copies of reports and then compare different versions. Maybe you could even have some basic report design capabilities? The list is endless. There's a business idea here for someone with a bit of JavaScript knowledge...

For someone like me without a financial background, the default behaviour of AS when displaying a hierarchy seems natural: when you ask for a set like MyHierarchy.Members, you'd get all the parent members appearing in the list before their child members. But for financial reports you want the opposite, you want to see all child members displayed before the parent members. The other day I was asked how to do this in turn and asked Andrew Wiles (who has a lot more financial OLAP experience than me), and he pointed me to the simple answer - the HIERARCHIZE function.

Here's an example from Adventure Works:

SELECT [Measures].[Amount] ON 0,
DESCENDANTS([Account].[Accounts].&[95])
ON 1
FROM [Adventure Works]

Run the query and you'll see the following returned, with the parent member Statistical Accounts displayed above its children:

However, if you want to display it in the financial style, all you need to do is wrap your set with HIERARCHIZE( <<set>>, POST) as follows:

SELECT [Measures].[Amount] ON 0,
HIERARCHIZE(
DESCENDANTS([Account].[Accounts].&[95])
, POST)
ON 1
FROM [Adventure Works]

I have to admit, I'd always wondered what the point of Hierarchize() was - this is the first time I've actually needed to use it!

Phew, another SQLBits done and dusted. And I think it was a good one, especially looking at the comments so far:
http://sqlblogcasts.com/blogs/drjohn/archive/2008/09/14/busting-for-a-wii-at-sql-bits.aspx
http://andrewwiles.spaces.live.com/blog/cns!43141EE7B38A8A7A!368.entry
http://blogs.technet.com/andrew/archive/2008/09/14/sql-bits-cubed-thanks.aspx

Thanks to everyone that came and especially to everyone that helped out! And if you weren't there, the best thing is that Microsoft brought along a camera crew and filmed the sessions (I think all of them, certainly all of the sessions in the room I was monitor for) which should be made available online somewhere soon. Apologies to everyone who stopped me to say hello - I was running around like the proverbial blue-a*sed fly all day so I couldn't stop to chat with anyone for more than a few minutes, I hope I didn't seem too rude...

Courtesy of Jamie Thomson, here's some sample code showing how to create Analysis Services partitions from within an Integration Services package using AMO:

http://blogs.conchango.com/jamiethomson/archive/2008/09/15/ssis-create-analysis-services-partitions-from-a-ssis-package.aspx

Although I should stress that if you're using this in production, you do want to set the slice property on a partition to ensure you get the best possible performance in all cases.

The blog entry I posted a month or so ago about XLCubed where I had a pop at Microsoft for their client tool strategy certainly seemed to strike a chord with a lot of people (see the comments, and also Marco's blog entry here). It also made me think that it would be worth spending a few blog entries looking at some of the new third party client tools that are out there... I've already lined up a few reviews, but if you've got an interesting, innovative client tool for Analysis Services that I could blog about, why not drop me an email?

So anyway, the big news last week was of course Google's announcement of Chrome. And as several of the more informed bloggers (eg Nick Carr, Tim McCoy) the point of Chrome is to be not so much a browser as a platform for online applications, leading to a world where there is no obvious distinction between online and offline applications. And naturally when I think about applications I think about BI applications, and of course thinking about online BI applications and Google I thought of Panorama - who incidentally this week released the latest version of their gadget for Google Docs:
http://www.panorama.com/newsletter/2008/sept/new-gadget.html

Now, I'll be honest and say that I've had a play with it and it is very slow and there are a few bugs still around. But it's a beta, and I'm told that it's running on a test server and performance will be better once it is released, and anyway it's only part of a wider client tool story (outlined and analysed nicely by Nigel Pendse here) which starts in the full Novaview client and involves the ability to publish views into Google Docs for a wider audience and for collaboration. I guess it's a step towards the long-promised future where the desktop PC will have withered away into nothing more than a machine to run a browser on, and all our BI apps and all our data will be accessible over the web.

This all makes me wonder what BI will be like in the future... What will it be like? Time for some wild, half-formed speculation:

• Starting at the back, the first objection raised to a purely 'BI in the cloud' architecture is that you've got to upload your data to it somehow. Do you fancy trying to push what you load into your data warehouse every day up to some kind of web service? I thought not. So I think 'BI in the cloud' architecture is only going to be feasible when most of your source data lives in the cloud already, possibly in something something like SQL Server Data Services or Amazon Simple DB or Google BigTable; or possibly in a hosted app like Salesforce.com. This requirement puts us a long way into the future already, although for smaller data volumes and one-off analyses perhaps it's not so much an issue.
• You also need your organisation to accept the idea of storing its most valuable data in someone else's data centre. Now I'm not saying this as a kind of "why don't those luddites hurry up and accept this cool new thing"-type comment, because there are some very valid objections to be made to the idea of cloud computing at the moment, like: can I guarantee good service levels? Will the vendor I chose go bust, or get bought, or otherwise disappear in a year or two? What are the legal implications of moving data to the cloud and possibly across borders? It will be a while before there are good answers to these questions and even when there are, there's going to be a lot of inertia that needs to be overcome.
  The analogy most commonly used to describe the brave new world of cloud computing is with the utility industry: you should be able to treat IT like electricity or water and treat it like a service you can plug into whenever you want, and be able to assume it will be there when you need it (see, for example, "The Big Switch"). As far as data goes, though, I think a better analogy is with the development of the banking industry. At the moment we treat data in the same way that a medieval lord treated his money: everyone has their own equivalent of a big strong wooden box in the castle where the gold is kept, in the form of their own data centre. Nowadays the advantages of keeping money in the bank are clear - why worry about thieves breaking in and stealing your gold in the night, why go to the effort of moving all those heavy bags of gold around yourself, when it's much safer and easier to manage and move money about when it's in the bank? We may never physically see the money we possess but we know where it is and we can get at it when we need it. And I think the same attitude will be taken of data in the long run, but it does need a leap of faith to get there (how many people still keep money hidden in a jam jar in a kitchen cupboard?). 
• Once your data's in the cloud, you're going to want to load it into a hosted data warehouse of some kind, and I don't think that's too much to imagine given the cloud databases already mentioned. But how to load and transform it? Not so much of an issue if you're doing ELT, but for ETL you'd need a whole bunch of new hosted ETL services to do this. I see Informatica has one in Informatica On Demand; I'm sure there are others.
• You're also going to want some kind of analytical engine on top - Analysis Services in the cloud anyone? Maybe not quite yet, but companies like Vertica (http://www.vertica.com/company/news_and_events/20080513) and Kognitio (http://www.kognitio.com/services/businessintelligence/daas.php) are pushing into this area already; the architecture this new generation of shared-nothing MPP databases surely lends itself well to the cloud model: if you need better performance you just reach for your credit card and buy a new node.
• You then want to expose it to applications which can consume this data, and in my opinion the best way of doing this is of course through an OLAP/XMLA layer. In the case of Vertica you can already put Mondrian on top of it (http://www.vertica.com/company/news_and_events/20080212) so you can already have this if you want it, but I suspect that you'd have to invest as much time and money to make the OLAP layer scale as you had invested to make the underlying database scale, otherwise it would end up being a bottleneck. What's the use of having a high-performance database if your OLAP tool can't turn an MDX query, especially one with lots of calculations, into an efficient set of SQL queries and perform the calculations as fast as possible? Think of all the work that has gone into AS2008 to improve the performance of MDX calculations - the performance improvements compared to AS2005 are massive in some cases, and the AS team haven't even tackled the problem of parallelism in the formula engine at all yet (and I'm not sure if they even want to, or if it's a good idea). Again there's been a lot of buzz recently about the implementation of MapReduce by Aster and Greenplum to perform parallel processing within the data warehouse, which although it aims to solve a slightly different set of problems, it nonetheless shows that problem is being thought about.
• Then it's onto the client itself. Let's not talk about great improvements in usability and functionality, because I'm sure badly designed software will be as common in the future as it is today. It's going to be delivered over the web via whatever the browser has evolved into, and will certainly use whatever modish technologies are the equivalent of today's Silverlight, Flash, AJAX etc.  But will it be a stand-alone, specialised BI client tool, or will there just be BI features in online spreadsheets(or whatever online spreadsheets have evolved into)? Undoubtedly there will be good examples of both but I think the latter will prevail. It's true even today that users prefer their data in Excel, the place they eventually want to work with their data; the trend would move even faster if MS pulled their finger out and put some serious BI features in Excel...
  In the short-term this raises an interesting question though: do you release a product which, like Panorama's gadget, works with the current generation of clunky online apps in the hope that you can grow with them? Or do you, like Good Data and Birst (which I just heard about yesterday, and will be taking a closer look at soon) create your own complete, self-contained BI environment which gives a much better experience now but which could end up being an online dead-end? It all depends on how quickly the likes of Google and Microsoft (which is supposedly going to be revealing more about its online services platform soon) can deliver usable online apps; they have the deep pockets to be able to finance these apps for a few releases while they grow into something people want to use, but can smaller companies like Panorama survive long enough to reap the rewards? Panorama has a traditional BI business that could certainly keep it afloat, although one wonders whether they are angled to be acquired by Google.

So there we go, just a few thoughts I had. Anyone got any comments? I like a good discussion!

UPDATE: some more details on Panorama's future direction can be found here:
http://www.panorama.com/blog/?p=118

In the months to come, Panorama plans to release more capabilities for its new Software as a Service (SaaS) offering and its solution for Google Apps.  Some of the new functionality will include RSS support, advanced exception and alerting, new visualization capabilities, support for data from Salesforce, SAP and Microsoft Dynamics, as well as new social capabilities.

So, you were wondering whether you should give up your precious weekend to attend SQLBits. OK, so it has some great technical content (the agenda has now been published here: http://www.sqlbits.com/information/mainagenda.aspx) and it will be generally good fun, but you were planning on washing your hair/mowing the lawn/watching the entire Lord of the Rings trilogy on DVD... what could possibly tempt you away from that? How about FREE BEER? Yes, we can now announce that there will be an evening 'do' after the sessions have finished with drinks and pizza. So what are you waiting for? Register today at http://www.sqlbits.com/default.aspx !

OK, ok, I promise this is the last time I use my blog to promote SQLBits before it actually takes place. I need to get down to work on the content for the training day I'm running with Allan; incidentally, if you can't make it to that, Allan will be presenting all the material himself here:
http://sqlknowhow.com/training/am20080929/