Chris Webb's BI Blog

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

Archive for the ‘Client Tools’ Category

Platforms For Building Richer BI Applications

with 4 comments

One of the mysteries of the MS BI third-party ecosystem is how slow it has been to make use of technologies like WPF and Silverlight. Marco Russo has a plausible explanation of why this is here; it’s really only in the last six months that things have begun to change. A few products I’ve seen or heard of include Clearway GeoAnalyzer, Radarsoft’s RIA Grid and Intelligencia for Silverlight; there’s also increased interest in building your own specialised BI apps in Silverlight – for example I’ve seen Sascha Lorenz do presentations on this subject at various conferences, and of course Bart Czernicki’s book “Next Generation Business Intelligence Software with Silverlight 3” was released a few months back. I still don’t think we’re anyway near reaching the potential of the technology though.

I think one way to increase uptake would be to provide some kind of toolkit or additional layer to help developers or even power users build BI applications. Maybe something like a Microsoft version of SAP’s XCelsius would be a good idea? I know there would be a lot of overlap with what PerformancePoint is meant to do, but I think there is sometimes a need for highly visual presentation of data rather than plain old dashboards, beyond what’s possible with PerformancePoint, Excel or Visio even in Office 2010. I’m not advocating the abandonment of Stephen-Few-ish design principles for serious business dashboards in favour of fancy gauges and animations – but sometimes, for example in presentations or newspaper articles, a bit of ‘wow’ in the way the data is presented can be as important for the overall purpose as the meaning of the data; the kind of visualisations you can find on, for example, are what I’ve got in mind here.

Here’s two examples of what could be done. When I saw Microsoft Semblio I thought something like it for BI developers for creating dashboards or presentations would be cool: it’s an SDK for creating rich, multimedia content for educational purposes. In a similar vein, I recently met up with an ex-customer of mine, Steven Peters, who is now the owner of a startup called Munglefish that develops a platform for developing closed-loop sales and marketing presentation applications. Munglefish’s EpicX platform is something like an interactive PowerPoint, and among other things each ‘slide’ can display BI data as an aid to the sales process (eg if you’re selling Widgets to an IT consultant in his mid-30s in SE England, you’d be able to display just how much money other IT consultants in their mid-30s in SE England had saved buying your brand of widgets) as well as capturing information about the flow of the sales process and sending it back to a data warehouse to be analysed; I think it is one of the best examples of BI being integrated in what is not primarily a BI application that I’ve ever seen, and its success is completely due to the kind of high-quality graphics that are possible with WPF and Silverlight. These platforms don’t remove the need for a developer but they do reduce the overall amount of development work needed. They are also targeted at scenarios where slick visualisations are very important for engaging the audience – we know it’s just as important to hold the CEO’s attention in a dull meeting where you’re presenting your financial data as it is to hold a 12-year-old’s attention in a science lesson.

Finally, last week I also saw the announcement of Vedea, a new, experimental data visualisation language from Microsoft Research. You can find full details of it on Martin Calsyn’s blog here:
It’s basically a new .NET language for “creating interactive infographics, data visualizations and computational art” – pretty much what I’ve been asking for so far in this post, and although I still think it would be too technical for the average business user I can see it would have a lot of interesting uses for BI professionals. With a bit of luck, like F#, it will make the transition to being a full member of the .NET family one day and maybe then we’ll have a tool that will allow us to make the most of the power of Silverlight and WPF for BI with the minimum of effort.

Written by Chris Webb

December 7, 2009 at 12:59 pm

Posted in Client Tools

Free version of Microstrategy Reporting Suite for SSAS

with 4 comments

Here’s a cheeky move by Microstrategy: they’ve made the free version of their Reporting Suite work for Analysis Services. More details and a download link here:

I’ve not tried it so I don’t know whether it’s any good or not, but it’s free and you can have up to 100 users, so it will be worth checking out. Of course this is Microstrategy trying to hurt Microsoft and its partners, but, well, it’s free…

Written by Chris Webb

November 25, 2009 at 8:23 pm

Posted in Client Tools

Proclarity Migration Roadmap (or lack thereof)

with 27 comments

For those of you who commented on my recent post asking what the future held for existing Proclarity users, some interesting news. My fellow SQL BI MVP Thomas Ivarsson asked whether there were any plans for helping Proclarity users migrate to PerformancePoint and got this reply from Alyson Powell Erwin:

Here’s the text:

There will not yet be a migration from ProClarity 6.3 to PerformancePoint Services for SharePoint 2010.  Customers can continue to use ProClarity throughout its current supported lifecycle date of July 2012 for mainstream and July 2017 for extended.  We are still working on the roadmap for ProClarity but it is likely that you will not see a migration path until the O15 timeframe. 

So, in effect, three and a half years after Microsoft first announced they were buying Proclarity, they still have no roadmap for migrating existing Proclarity customers onto a new platform. I’m sorry, but this is just not good enough; I don’t think they could have come up with a strategy that would be more damaging to Microsoft BI if they had called up Larry Ellison and asked him to contribute some ideas. Development on Proclarity finished three years ago, almost, and they’re saying that there probably won’t be a migration story until Office 15 – which is likely to be about three or four years in the future! That’s effectively telling some of the most serious, committed Microsoft BI customers to bin their existing solutions and start again from scratch, and I can’t tell you how angry that makes me feel. It seems to me that Microsoft don’t have a BI strategy any more, they have a sell-more-Office (and especially MOSS) strategy. That’s fair enough, Microsoft have to make money somehow, but in there’s no point expecting SQL Server BI to drive sales of Office in the future if they’re busily driving away the existing customer and partner base. It’s a classic case of killing the goose that laid the golden egg.

Here’s what Microsoft should do:

  • Round up whatever members of the Proclarity dev team that are still in Microsoft and get them to work on a new stopgap release of Proclarity. It doesn’t need to add much new functionality, but it does need to update the UI and make it look a bit less like a VB6 app circa 1998.
  • Either stop pretending that Excel will meet the needs of power users and let the Proclarity fat client live for a few years longer, or add functionality to Excel that will bring it up to the required standard. Richard Lees has just published a good list of what needs to be done here (I can think of a few more myself, such as support for ragged hierarchies that use HideMemberIf), and while some of these issues are addressed in Excel 2010 not all are. Excel 2010 is just bringing Excel up to the levels of functionality that most third party SSAS clients had in 2005. And again, I can’t wait until Office 15.
  • Publish – and commit to – a clear roadmap showing how existing Proclarity customers can be migrated to the new Office BI platform. At the moment most Proclarity customers feel completely abandoned and have no idea what to do (as the comments in my recent blog post demonstrate).

In the meantime, if I was one of the remaining third party SSAS tools vendors I would be wondering if it was possible to create a wizard that would migrate existing Proclarity briefing books onto their own platform. I would imagine it might generate a few leads…

Written by Chris Webb

October 13, 2009 at 2:07 pm

Posted in Client Tools

Farewell to the Excel 2003 addin and the BI Accelerator

with 9 comments

Reading the SQL Server technical rollup mail I get sent as an MVP (the same information’s also available at I noticed that two old products have just been retired: the Excel 2003 Analysis Services addin, and the BI Accelerator. A little more information on this is available on the download pages here:

I quote from the Excel addin page:
”The Excel Add-in for SQL Server Analysis Services has been removed to avoid customer confusion about support for this component. As noted in the details that accompanied the release of this product, Microsoft does not provide any support for this add-in and has no plans to release future versions. Newer versions of Excel include most of the functionality that is provided by this add-in; these newer versions are supported according to the Microsoft Product Lifecycle.

To be honest I’ve not even looked at either of these products for years, but at least in the case of the Excel addin I wonder how many people are still using it? If you have no choice but to use Excel 2003 (and I’m sure a fair proportion of Excel users still are) then it was an invaluable upgrade for Excel 2003’s built-in SSAS support. More to the point, the BI Survey 8 (which collected data from mid 2008) had 21.8% of Analysis Services users claiming to use it, more than double the number that were using Panorama Novaview and only 5% less than were using Proclarity. At first that seemed an improbably high number to me, but on reflection I think it could be more or less accurate: as BI consultants and developers we tend only to see ‘new’ BI projects, but what about all those projects we delivered 4+ years ago and haven’t seen since? They’re chugging along happily, ‘just working’ with no obvious need to upgrade, and their users are the people who are likely to be using the Excel addin. They won’t stop using it because of this announcement, but it might start them thinking about what they should upgrade to – probably Excel 2007, but maybe something else.

And Proclarity users are in the same situation: they have an ageing tool that is no longer supported, and need to think about upgrading to something. But what? At least with the Excel addin there’s Excel 2007 but in the case of Proclarity there’s no obvious answer – it’s not just that PerformancePoint/Excel Services/SSRS don’t have the same functionality, but if you’ve got several hundred briefing books your users aren’t going to be happy about rebuilding them in some new tool. I don’t want to go off on yet another rant about Microsoft’s idiotic client tools strategy, but I’m worried that we’ll start to see a series of migrations away from the Microsoft BI platform as a result of this issue.

Written by Chris Webb

October 4, 2009 at 10:26 pm

Posted in Client Tools

DataWarehouse Explorer

with 5 comments

Continuing my occasional series of SSAS client tool reviews, here’s another contender in the post-Proclarity power-user market: DataWarehouse Explorer, from Dutch company CNS International.

DWE is a standalone, ‘rich client’ application that gives you a lot more functionality than you get in Excel pivot tables and as such is competing in the same market that Proclarity Desktop Professional used to dominate and which is still pretty crowded. There’s also a web-based portal that you can publish reports to (see here for full details on the architecture) but if you want to build queries you need to do it on your desktop.

So what’s it like? I liked it: it’s not got any flashy features that mark it out particularly, but it does everything it needs to do and it does so well. Probably the best thing is the UI – a nice Office 2007 look-and-feel and most importantly very clear and easy to use. As someone who has spent plenty of time working with Analysis Services over the last ten years or so, when I start using a new client tool I expect to be able to do what I want to do very easily: I know all the basic concepts of cubes, I know the Adventure Works cube, and I know the queries I want to run, so if I can’t work out how to do something then I lay the blame on the UI design. And if I can’t do something there’s not point expecting an end user to do it. In the case of DWE I had no problems at all and in many respects it’s much easier to use than something like Proclarity or Excel. Here’s a screenshot:


The filter dialog provides a good example of how they’ve got the UI right. Filtering is something that every worthwhile client tool needs to do, but it’s easy to make it confusing for the user especially when you’re applying multiple conditions. The DWE filter dialog is uncluttered, shows all the filters you’ve already set up, makes it easy to add new ones or delete existing ones, and has a number of nice touches like the way it automatically formats any numeric conditions you enter to match the format string of the measure you’re filtering on.

DWE Filter

Other features worth mentioning include:

  • It mimics Excel 2007’s in-cell data bars and conditional formatting very closely. I like those features in Excel and things like this make DWE very easy to pick up for Excel users.
  • There’s a ‘Notes’ pane where you can add text commenting on the query you’ve built.
  • In the slicer pane, you can search for hierarchies by name – useful when you’ve got a lot of hierarchies and dimensions:
  • Similarly, the slicer pane can organise the hierarchies on slice according to which ones you’ve explicitly selected something on, ones where there is an implicit selection (for example because there’s no All Member or a specific Default Member has been set), and ones where there is no selection:
  • There’s a ‘Cube Dictionary’ feature that allows you to look at the metadata of objects on the server, for example to check the aggregation method that a measure uses:
  • The UI can be switched between English, Dutch, Portuguese and Spanish.
  • You can hide more difficult functionality by setting the ‘User Level’ option to ‘Basic’ or ‘Intermediate’ rather than the default of ‘Advanced’. Fewer buttons and options improves ease-of-use for new or less competent users.

Overall, then, a good product and one worth evaluating if you’re looking for a desktop-based SSAS client tool.

Written by Chris Webb

September 1, 2009 at 11:14 am

Posted in Client Tools

Intelligencia Desktop Client

with 3 comments

DISCLAIMER: since I licensed my SSRS custom data extension for SSAS to iT-Workplace, and since this technology is used in Intelligencia Desktop Client, I benefit financially from sales of this tool!

If you’re a regular reader of this blog, you’re no doubt aware that about a year ago I came up with an idea for a custom data extension for SSRS that makes it much easier to work with SSAS data sources, which subsequently became part of the Intelligencia Query product (which I blogged about here and has since gone through several releases). iT-Workplace, the company that sells Intelligencia Query, also produces a .NET MDX query-generator component suite called Intelligencia OLAP Controls (used in Intelligencia Query) which is aimed at third parties who want to add MDX query capabilities to their own apps, and midway last year I suggested to Andrew Wiles of iT-Workplace that he wrap these components in an exe and create his own standalone desktop client tool – and this became Intelligencia Desktop Client (IDC hereon), which I thought I’d review here in my continuing series on SSAS client tools.

IDC is distinctive because it deliberately doesn’t compete with most other Analysis Services ad hoc query tools – it’s aimed very much at the planning and budgeting market. At present the only version available is the Standard Edition which gives you query building and reporting functionality; at first impressions it does very much what other advanced ad hoc query tools like Proclarity do, but it has a lot of functionality important to financial users such as the ability to construct complex asymmetric sets on axes that many such tools lack. In fact it’s as much about creating forms for budget data entry via writeback as it is for querying and reporting; the closest comparison to make is with the PerformancePoint Excel addin although for it’s people who have built their own financial applications from scratch in Analysis Services rather than used PerformancePoint. The Enterprise Edition, which is still a CTP, will I believe offer yet more data entry and modelling functionality – I think Andrew wants to move towards incorporating cube building capabilities too.

Some features to note:

  • Creating query-based calculations is very easy, and it has an innovative spreadsheet-formula-like approach to doing so that financial users will feel very at home with:
    Unfortunately you can’t copy a calculation from a single cell to a whole range, yet, but I’ve asked for that for a future release…
  • It has a lot options for formatting the resulting table for printing or inclusion in a document:
    This ties in with its more mature sister product Intelligencia for Office 2007 which takes the form of Word and Excel addins, and is aimed at producing print-quality documents which incorporate live links to OLAP data.
  • This formatting functionality is also useful because IDC can publish queries to Reporting Services:
    Depending on what your requirements are this could be a very easy way of generating SSRS reports based on SSAS data. I wouldn’t go as far as to say that it makes IDC a proper SSRS report design tool since it doesn’t support the creation of any of the more advanced SSRS features; in fact IDC doesn’t have any charting capabilities (although I know this might be in the pipeline) so you can’t create reports with charts.
  • It has an ‘MDX Mode’ where you can turn off the navigation pane and enter whatever MDX you want, with the query results being displayed in the grid; very useful for those times when you have to write the MDX for a query yourself. It even has Intellisense!

Written by Chris Webb

January 9, 2009 at 10:18 pm

Posted in Client Tools

SiSense Prism

with one comment

A few months ago I announced I was going to do a major series of reviews of client tools here… well, that fell flat (probably because it takes a bit too much effort to install and test one), but at least here’s one more review: Prism, from SiSense. Here’s their website:

Strictly speaking it’s not just an Analysis Services client tool because it can work with data from a number of different sources such as relational tables, Excel and even Google spreadsheets and Amazon S3. I don’t know too much about their internal architecture but it seems to be based on storing the data retrieved from all these different sources in some kind of in-memory store, so I suppose in that way it’s similar to what will be coming in Excel with Gemini. They do treat Analysis Services as a data source seriously, though, and in fact one of the guys behind the company is Elad Israeli, who was behind a tool called MDXBuilder that those of you with very long memories might recall; so for the rest of this review I’ll concentrate on the AS client tool side of things.

First impressions are very good: the UI is very modern, uncluttered and easy to use. There are a few wrinkles in that there is no explicit support for AS2008 yet, and I had to go through a few hoops to get it to connect on my laptop which only had AS2008 installed; also they don’t show hierarchies grouped into dimensions, just a flat list of hierarchies from all dimensions, which is a pain when you have a lot of dimensions and hierarchies – they really should support folders etc. Since I’ve already mentioned this to SiSense hopefully this will be changed soon.

The tool itself is focused on creating dashboards and the starting point is a blank sheet on which you can drag ‘widgets’, which in turn can be hooked up to various data sources to display data. Examples of widgets are pivot controls, various different types of charts, images and textboxes, gauges, calendar controls, dropdown boxes and so on; it’s reminiscent of Reporting Services (but concentrates more on application building rather than pixel-perfect formatting) and PerformancePoint in this respect. I have to say that I found that I found the process of building a dashboard to be exceptionally easy and intuitive, and I was very impressed – I was able to put together something that worked very quickly, and it handled layout and formatting in such a way that even someone who is generally rubbish at report design like me could create a dashboard that looked professional. Here’s a screenshot of one I put together quite quickly:


One other very cool feature is the way that complex selections can be generated using a visual workflow, called ‘Questions’ in the product. You can read more about it on their blog here:
…but the easiest way of thinking about it is as something like the SSIS dataflow for MDX sets (similar to something I blogged about a while ago). Here’s an example of that returns the top 10 Dates by Internet Sales Amount unioned with the bottom 10 Dates by Internet Sales Amount where Internet Sales Amount is greater than $500:


I think this is the best way I’ve seen of letting users set up complex filters, although it probably is still only something a power user could understand.

At the moment Prism is just a fat client, so with no web-based version (yet) sharing dashboards is a matter of emailing .psm files or putting them on a network share; this will be a deal-breaker for some people. SiSense have, though, in my opinion made the right decision in implementing the functionality they have got very well before rushing off to tick all the boxes on potential buyers’ checklists and doing so badly. Overall, if you’re in the market for a desktop BI tool that supports Analysis Services as well as other data sources I can recommend taking a look at Prism.

Written by Chris Webb

December 10, 2008 at 11:53 am

Posted in Client Tools


Get every new post delivered to your Inbox.

Join 3,892 other followers