Chris Webb's BI Blog

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

Announcing Intelligencia Query for Reporting Services

with 12 comments

Can I be utterly shameless and use my blog to promote products I have a commercial interest in? Of course I can! But first, let me tell you a story…

Last December I spent a lot of time thinking about the problems of building Reporting Services reports on top of Analysis Services. To be honest, this issue has been a constant irritation for me ever since I first saw Reporting Services about five or six years ago; this blog entry, one of the most popular I’ve ever posted, sums up my thoughts on the matter:
http://cwebbbi.spaces.live.com/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!163.entry
and I’ve blogged about specific workarounds and performance issues on a regular basis ever since. But anyway, as I was saying, last December I was working with a customer new to the MS BI stack who had announced their intention of using SSRS and SSAS together in the naive assumption that because these tools were produced by the same company they would work well together, and for the n-hundredth time I found myself having to manage their expectations and teach them the various tips and tricks necessary to get their reports working. I also wrote an article on this topic for SQL Server magazine that came out in December and I expanded the same material into a presentation for the last SQLBits (you can download the slide deck here); it was also around that time that I realised that nothing was going to change in Reporting Services 2008 (see this thread on the MSDN Forum for other people’s reactions to this). All this got me thinking.

The way you’d want SSRS to work is fairly clear: you’d want a decent query builder and the option of using any MDX query you’d care to write, and you’d want SSRS to render the resulting cellset in a matrix-like control automatically. It should work like pretty much like every SSAS client tool on the market only the output should be a SSRS report. The reason you can’t do this is also clear: SSRS needs to know the name and number of columns your query returns in advance, and you can’t do that with MDX queries. So how can you get around this limitation?

Then it hit me. Instead of having to unpivot your MDX query manually so that it’s in the format that SSRS likes, why couldn’t you have a custom data extension that did the hard work for you? So I wrote my first attempt in an afternoon and after a lot more polish it now works really well. Here’s an example of what it does:

Take the following query:

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

This returns the following SSRS-unfriendly results:

originaldata

But when run through my custom data extension the data is returned as follows:
pivotdata

You’ll see that all of the columns have become rows, and there are two columns containing the Value and Formatted_Value properties of the cells. The cool thing is now that you can put a matrix control in your report, map the ‘column’ fields onto the matrix column groups, the ‘row’ fields onto matrix row groups and one of the cell properties onto the data area like this:
matrixlayout

And when you run your report, the matrix control recreates the original structure of your MDX query:
matrixrender 

This is pretty simplistic but even more complex queries with multiple levels of crossjoining on rows and columns are handled just as easily; it gets around all the limitations with the built-in Analysis Services data source/query builder as well as those of the OLEDB data source. Some of the key advantages are:

  • You can use any MDX query and display the results (see here for some of the problems associated with the built-in Analysis Services data source needing measures on columns)
  • You can parameterise your reports by measures (see here)
  • There’s no messing around with ‘aggregate rows’, all rows are treated the same
  • Because the field names (optionally) do not refer to the hierarchy names used in the query, you can write one report that displays the results of very different queries – the only requirement is that you have the same number of rows and columns in your query. For example you can create one report that displays either Years, Quarters or Months on rows and control which one gets displayed using a parameter. It also means that the report design is much more tolerant of changes to the query during development.
  • There’s full support for parameterisation, something you don’t have with the OLEDB data source.
  • It (optionally) indents member captions based on their level depths, so the captions of members on lower levels appear further to the right of captions of members on higher levels.

I was feeling quite pleased with myself at this point and was about to publish the code up to Codeplex when it occurred to me that I could make some money out of this – after all, I do have a wife, two kids and a mortgage to feed. There was also one big missing feature that I knew I didn’t have the time to implement on my own and that was an MDX query builder: whereas I’d be quite happy to write my own MDX queries, I know most people aren’t that comfortable doing so and need some kind of tool to do it for them. So I got in touch with Andrew Wiles of iT Workplace who I knew had an excellent MDX query control, signed a partnership agreement with him and over the last few months we (well, mostly him really) have worked to integrate his query builder with my custom data extension with BIDS to come up with what we think is a product that anyone using Reporting Services and Analysis Services will want to use, Intelligencia Query for Reporting Services. Yes, it’s something you’re going to have to pay for but in my opinion it’s very modestly priced and more importantly it has been saved my work from the fate of being a cool idea implemented by an occasional C# coder and stuck in perpetual beta on Codeplex, and turned into a commercial-quality product that is safe to install on your servers and which will grow and improve over time.

The main selling point of the whole solution is that it gives you, for the first time with Analysis Services, the ability to build in a query builder exactly the resultset you want to see displayed in your Reporting Services report – it’s truly WYSIWYG. Certainly in a lot of cases you can achieve what you want with the native query builder but as I said, with it you always have to try to imagine the end result you want and write a query that can be pivoted into that end result, and that’s not easy for the majority of people. Combine this with the fact that the Intelligencia query builder allows you to build much more sophisticated queries than the native query builder and that, as mentioned above, the custom data extension solves many of the problems you run into with the built-in Analysis Services and OLEDB data sources, and I think you’ve got something that finally makes it easy to build Reporting Services reports using Analysis Services data.

I think Andrew’s query builder is the equal of anything else on the market in terms of the kind of query it can create. It does all the stuff you’d expect like hierarchical selections, it does stuff that few other tools do such as allow for the creation of asymmetrical sets on an axis, and it has at least one outstanding feature that no-one else has in its ability to let users create MDX calculations with an Excel formula-like interface. It even supports SAP BW as a data source too. Here’s a screenshot:

IQScreenshot

Anyway, enough of me going on. You can find out more, download an eval version, watch a video and buy some licences here:
http://www.it-workplace.co.uk/IQ.aspx

One last thing: we want to hear your feedback. This is a version 1.0 and while we’ve got a lot of ideas on new features that could be added, we want to be sure they are the features that our users actually want. So even if you only download the eval please drop me a mail to let me know what you like and what you think is missing. It’s likely we’ll be able to add new features pretty quickly.

Written by Chris Webb

April 13, 2008 at 12:44 am

Posted in Reporting Services

12 Responses

Subscribe to comments with RSS.

  1. Nice work! Look forward to trying it out.
     
    Cheers,
    Nick

    Nick Barclay

    April 13, 2008 at 3:54 am

  2. Chris, you mentioned your SQLBits presentation in your post. But I could not find it on the SQLBits web site. Can you make it available? I am sure it will be of interest for many readers of your blog. Thanks!
     
    — Patrice.

    Patrice

    April 13, 2008 at 11:06 am

  3. Hi Patrice,
     
    Sorry, I\’ll get that fixed asap.
     
    Chris

    Chris

    April 14, 2008 at 8:46 am

  4. The SQLBits site says there are no items to download, so no deck is available :-(

    http://www.sqlbits.com/Agenda/Using_Analysis_Services_as_a_data_source_for_Reporting_Services_reports/default.aspx

    Or is there somewhere else I should be looking?
    Thanks – Marianne

    marianne_collins

    June 13, 2008 at 6:34 pm

  5. For some reason it never got put up there. Drop me a mail (you can find contact details at http://www.crossjoin.co.uk ) and I\’ll send it to you.

    Chris

    June 14, 2008 at 7:14 am

  6. Hi Chris,

    I know this is a rather old post, but hopefully this reply will find it’s way to you. I’ve used this add in really sucessfully in the past and was trying to encourage it’s use in a new workplace, but the link (http://www.it-workplace.co.uk/IQ.aspx) seems dead… what became of the very useful iq-datasource / where could I find or buy it?

    many thanks,

    Andrew

    Andrew Justice

    October 10, 2012 at 11:51 am

    • Hmm, good question. The site has been up and down over the last year, but it looks like it has finally gone now and the url redirects somewhere else. I’ll try to contact Andrew Wiles to see what he’s doing and what the status of the product is. I’m pleased to hear you ended up using IQ though – presumably I recommended it on that course you were on a few years ago?

      Chris Webb

      October 10, 2012 at 1:31 pm

      • Thanks Chris,

        that’s a good memory you have – yes LAS were using IQ to sucessfully take some of the fight/workaround out of the mdx ssrs interface – and without meaning to be sycophantic – once you’ve used it, it feels like a large step backwards to use the native method. So please do let me know what you find out.

        cheers,

        Andrerw

        Andrew Justice

        October 10, 2012 at 2:07 pm

  7. Just like Andrew I know that this post is pretty old, but I have been trying to make a reporting services report work with a dynamic column (and group) together with the aggregate function of SSRS. Unfortunately I have not managed to get it to work so far. I stumbled into this post and wanted to see if IQ could solve my issue.

    Have you been able to find out why it is no longer available or is there any other alternative?

    Thanks,
    Bas

    Bas

    April 26, 2013 at 10:34 am

    • No, sorry, I don’t know why it’s gone and I’m not aware of any other similar products I’m afraid.

      Chris Webb

      April 26, 2013 at 1:23 pm

      • Ok, thanks Chris

        Bas

        April 29, 2013 at 6:53 am

  8. We bought this product a while back to use when needing measures on rows….can’t believe they are no longer around!

    Scott Barrett

    May 30, 2013 at 4:40 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,309 other followers

%d bloggers like this: