Consuming SSRS data in Excel 2007

In a recent post I sketched out an idea I had about consuming data from SSRS data within Excel: rather than have SSRS push data to a new Excel spreadsheet by rendering a report to Excel, what I wanted to do was have an Excel spreadsheet that pulled data from an SSRS report. Why do this? Well, we all know that users always want their data in Excel rather than in any other format. If we take an SSRS report and render it to Excel, though, we have two problems:

  1. Getting an SSRS report to render to Excel in exactly the way you want, at best, involves a lot of trial-and-error. Even then when you render to Excel there’s a whole load of Excel functionality that SSRS doesn’t support – for example, you can’t get native SSRS to render a workbook containing a macro.
  2. Ever time we run the report, we are creating a new workbook. So once we’ve got the workbook there isn’t much point in doing any extra work in it, for example adding new formulas or charts, because it’s going to be superseded by a newer workbook the next time the report is run.

A tool like Softartisans Officewriter (which MS licensed a long time ago and possibly will appear with SSRS 2008 R2) will solve the first problem, because it allows you to upload an Excel workbook to SSRS which has data injected into it when the report is rendered, but not the second.

However, it is possible to pull data into Excel from SSRS and avoid these problems. Excel allows you to import data from an XML document into a worksheet; since you can get an SSRS report to render to an XML document, all you need to do is hook Excel directly up to the XML file generated by SSRS. Here’s how:

  • The key to getting this to work is URL access to SSRS reports. Excel needs to know where the XML file it’s importing is – and you can give it the URL of an SSRS report, and in that URL specify that the report should be rendered to XML. Let’s take the Sales Order Detail report from the Adventure Works sample reports as an example:
    On my machine, the URL for rendering this report into XML is as follows:
    Paste this into your browser and you’ll automatically get an XML file downloaded; you can find more details on URL addressability of SSRS reports here.
  • Now, open Excel 2007, click on the big round Office button in the top left corner, click the Excel Options button and on the Popular tab check the box “Show Developer tab in the Ribbon”. This will ensure you can see the functionality within Excel we’re going to be working with.
  • Open the Developer tab and click on the Source button to open up the Source pane, then the XML Maps button in the Source pane, then Add on the XML Maps dialog, and then enter the URL of the SSRS report in the File Name box on the Select XML Source dialog and click Open.
  • The XML Source dialog will now be populated. Click on a cell in the worksheet, and then right-click on a node in the XML Source pane and choose Map Element to map an element into a cell; click the Refresh Data button in the ribbon to actually bring the data into the worksheet. Here’s what the data from the report above looks like when mapped into Excel:

The point is that every time you hit the Refresh Data button in Excel the report is rendered, so you’re able to build your worksheet around live data from SSRS. You can of course pull data directly from data sources like SQL Server in Excel, but the benefit of doing it this way is that you can take advantage of SSRS features like caching and snapshots, and of course as an end user you may not have direct access to the source database anyway.

There are some obvious drawbacks to this approach:

  • It’s a bit too technical to set up for end users, except perhaps for the most technical of Excel power-users.
  • There isn’t an easy way to pass parameters to reports. You can of course pass parameters through the URL, but it would be great if it could be done from a dropdown box in the worksheet. I think with a little bit of coding you could create an Excel addin that would do this though.
    UPDATE: actually, I think some of the techniques discussed in this post on the Excel blog could be useful here
  • Rendering to XML isn’t the ideal format for this task – although I’m not sure there is an ideal format (the RPL format used by Report Viewer might be a good candidate but it’s not documented). Books Online has details of how reports behave when rendered to XML; one obvious drawback is that there’s no pagination of data, so if you have a lot of data in your report spread across multiple pages, you’ll get all the data from every page in Excel.

That said, I think this this approach might be useful when you have a large number of existing Excel reports that currently have data copied-and-pasted into them manually and which can’t (for whatever reason) be turned into full SSRS reports.

SQLBits V Session Submission now open

Session submission is now open for SQLBits V, which, as I said a week or two ago, is taking place near Newport in Wales on November 19th-21st. We’re looking for sessions on any SQL Server-related subject and we actively encourage submissions from people with little or no conference speaking experience – we always make room on the agenda for new faces. And it’s not an exclusively-UK event either: we’ve had quite a few European and US speakers at previous events, so if you’re looking for an excuse to come to the UK for a holiday then this is it!

You can submit sessions by completing a speaker profile:

and then going to:

Announcing “Expert Cube Development with Microsoft SQL Server 2008 Analysis Services”

Expert Cube Development with Microsoft SQL Server 2008 Analysis Services

I’m pleased to announce that, after a lot of effort and late nights, the book that Marco Russo, Alberto Ferrari and I have been working on has finally been published! It’s called “Expert Cube Development with Microsoft SQL Server 2008 Analysis Services” and, basically, it’s a book about building cubes with Analysis Services 2008. So no surprises there then…

Why should you buy this book? There are a lot of other, really good SSAS books out there on the market, but we’d like to think ours is a bit different. For a start, it’s not a book for beginners and we assume you’ve already built a few cubes and know what a cube is; that’s not to say we ignore the basics, but we don’t spend too much time on them and as a result we can get onto the more interesting problems you’ll face when building cubes. Secondly this is a book with an opinion: we tell you which features work well and which don’t, how to work around any limitations in SSAS, and what the best practices are for building cubes; we make a lot of references to using BIDS Helper and MDX Studio for example, and not just the out-of-the-box features, and we reference a lot of useful material that’s on the net in white papers and on blogs. Thirdly, it’s meant to be a book you can read from cover-to-cover rather than a reference book: it’s relatively short, it follows the lifecycle of an SSAS project, and tries to tell a story; it doesn’t cover every possible piece of functionality in exhaustive detail. It’s not the only SSAS book you’ll ever need, but if you’ve already got a beginner-level book this will be a useful addition.

You can buy the book direct from the publishers here, from Amazon UK here, or Amazon US here. The table of contents is here, and there’s a sample chapter here.

Oh, and I should point out that this is a real book and not a hoax like last time…!

What’s new for Analysis Services users in Excel 2010?

I downloaded the Technical Preview for Office 2010 a few days ago, and was intending to blog about it as soon as possible but a few things made me wait a bit. First of all, there was the problem of whether I was in fact allowed to blog about it at all (which a lot of other people also seem confused about – which explains the strange silence on the web about it, perhaps); then there was the problem of actually finding what the new functionality was, since the Office team had neglected to provide any kind of detailed list of what has been added. Hmm. I think they’re planning to blog about everything that’s new soon though.

But anyway, now I’ve been given the go-ahead to blog I thought I’d list all of the new features I’ve found that are relevant to Analysis Services users. This does not include anything to do with Gemini, because Gemini isn’t part of the Technical Preview and I don’t have it yet unfortunately. I’m also not going to comment on bugs or things that don’t work in the way I’d want because, after all, this is not released software and things can and hopefully will change before RTM.

  • Slicers. If you’ve seen any of the Excel 2010 or Gemini demos you’ve probably seen that pivot tables can now have large slicer windows which make it a lot easier to select members (and see what has been selected) on the slice axis of a query. In terms of query functionality, as far as I can see they do exactly the same thing as the old single-cell dropdown-box-based slicers, created when you dragged a hierarchy into the Report Filter box, do. You can control their position, size and shape and almost every other aspect of their appearance.
  • Named sets. Ideally I’d have liked to be able to specify any MDX query I wanted for use in a pivot table, but this is the next best thing: it allows you to create your own named sets either using a simple UI or by (hooray!) entering your own MDX set expression; you can then use these sets wherever you want in your pivot table, for example on the rows or columns axis. This then means you can set up much more advanced selections than you ever could before, and is going to be incredibly useful for BI developers when creating dashboards in Excel. It might also allow for integration between Excel and other SSAS client tools.
    Here’s the simple set UI:
    Here’s the MDX set UI:
  • Show as. A lot more calculation options have been added to the ‘Show as’ feature in pivot tables; Thomas Ivarsson has already blogged about this here
  • Writeback support. According to the Excel blog we’ll get support for writeback in Excel, at last. Not tested this yet though.  
  • Dynamic named sets. Again, according to the Excel blog dynamic named sets will work with 2010 (they didn’t with Excel 2007).
  • Search. There’s now a Search feature that allows you to find specific members quickly (although again I’ve not tested this on a really big hierarchy) in the slice dialog. It’s not available in the Slicers mentioned above yet, but I assume it will be.
  • Sparklines. Again, if you’ve seen any of the Excel 2010 demos you’ll have seen that at last they’ve implemented sparklines. This isn’t of course an SSAS-specific feature but anyone who’s creating BI dashboards in Excel will want to use them. Here’s a screenshot of what they look like:

On a related note, I see Panorama have put out a press release saying how closely they’ll be working with MS, Office 2010 and SQL 2008 R2. It doesn’t say anything meaningful directly, but it’s nice to see that MS and Panorama are friends again and that MS sees value in what Panorama have to offer beyond the pure-MS BI stack; I wonder if MS are coming to regret what they did to Proclarity?

Excel 2007 Web Data Addin, and some thoughts on SSRS and Excel

Following on from my blog entry on Kapow the other week (and Jamie’s post on the same subject), I’ve just stumbled on something called the Excel 2007 Web Data Addin, something that Microsoft Research put together. It’s basically an addin that improves on Excel’s built-in functionality for importing data from web pages; it falls a long, long way short of what can be done with Kapow – and it’s a bit buggy – but it’s still interesting. There’s not much to it, or indeed much information out there about it, but here’s the link to download it and a pair of blog entries announcing its release from 2007:

There’s also a short demo video:

I wonder if this kind of functionality will be built into Excel 2010? It would be useful from a Gemini point of view if it was.

Another thought I had when looking at this was that SSRS reports would be the obvious source of data for this kind of functionality (although the addin refuses to work with Report Viewer, I guess it would work if the report was addressed directly via its URL). It should be pretty straightforward to suck data out of an HTML report with a tool like this, and indeed we’ve been told that SSRS 2008 R2 will be able to expose report data as a feed, but thinking about this it struck me that that’s not how I’d really want to work with SSRS data at all.

Rather than Excel linking to a table in an SSRS report, or SSRS rendering a report to Excel, or even what the OfficeWriter functionality we may get in Excel 2010 does, what I’d really want is an Excel addin that works in the same way as Report Viewer: as a sophisticated client to SSRS, pulling data into a worksheet rather than having data pushed to it. I’d want to be able to connect to a report from a worksheet, then be able to enter parameters from within the worksheet (either using dropdown boxes, like filters in a pivot table, or by entering values directly into cells, with me being able to choose which cells held the parameter values), and then when I clicked Refresh have the data from the report brought straight into one or more Excel tables or graphs, with pagination working too. All in all it would work in a similar way to the Excel Cube functions and make it much easier to build applications in Excel based on SSRS data. SSRS would be reduced to the role of running queries, handling some calculations, and doing caching; the actual layout of the report would be controlled from within Excel. Maybe it’s something that could be built by the community, or by MS as a sample app? It wouldn’t be much work to develop, I think, although the problem would be that you’d want to be able to use the RPL rendering format that Report Viewer users (mentioned by Robert Bruckner here) and that’s not publicly documented.


Analysis Services Connect digest

It’s a fairly popular thing for SQL Server bloggers and MVPs to put together lists of items on Connect (bugs that need fixing, new bits of functionality that should be in a future version, etc) and ask readers to vote on them so they get more visibility. Jamie Thomson did a post for SSIS the other day, for instance:

While I’ve done something similar in the past, I thought it would be a good idea to put together a more comprehensive list of Connect items for SSAS in the hope that some of the more important issues will be addressed in the next major release. So, please look down the list below and vote on anything that you think is important!

  1. MDX needs a special division operator that returns null instead of infinity when dividing by zero or null. Why do we always have to trap this in code ourselves? I have never, ever wanted to return infinity from a calculation!
  2. BI Development Studio is ridiculously slow doing Deployment or Saving (when in Online mode) sometimes. This isn’t a BIDS issue, of course, more of a reflection on how long the XMLA it’s generating takes to run, but still it wastes so much time…
  3. It would be cool if, after every time the server crashed, SSAS could make a copy of the current Flight Recorder trace file so we can see what was happening on the server. I know PSS can get this information from the mdmp files that get generated, but sometimes I want to do some detective work of my own before opening a support call:
  4. Attribute relationships serve a second purpose as member properties, but sometimes you want to display an attribute as a member property of another attribute when there isn’t a direct relationship between them. This leads people to creating either redundant attribute relationships or duplicate attributes in the dimension, both of which are equally bad:
  5. We have role-playing dimensions, so why not role-playing measure groups? There are a few times when I’ve wanted to reuse the same measure group with dimensions joining onto different columns (eg when working with many-to-many relationships), and not have to create and process multiple different physical measure groups:
  6. Apart from the Slowly-Changing Dimension support that we already have, it would be useful to be able to create different versions of the same dimension and allow users to choose which version they want to see. This would allow us to expose snapshots of what a dimension looked like at any given point in time:
  7. Richard Tkachuk’s approach for handling ‘events in progress’ highlights the fact that SSAS isn’t very good at dealing with what is a reasonably common BI problem. Let’s have a new ‘Range’ dimension relationship type to do this out of the box:
  8. Let calculated members in MDX return sets. This would allow us to optimise calculations where expensive set operations currently have to be duplicated:
  9. At the moment, the IgnoreUnrelatedDimensions property controls what happens for measures when you query them with dimensions that have no relationship with the measure group, and when you query them with attributes below the granularity of the measure group. Very often, though, I do not want the same behaviour for these two scenarios, so can we have two different properties please? Or even be able to set this on a per-dimension basis?
  10. At the moment, when using connection string properties in SQLMS, after you’ve set them the properties get persisted for all future connections without being visible in the connection dialog. Either don’t persist the properties or show us which ones are being set!
  11. No more arbitrary-shaped set errors! When writing scoped assignments, I want to be able to scope on whatever area in the cube I want; at the moment, I often find I have to repeat the same assignment several times to get around the requirement that I can’t assign to an arbitrary-shaped set.
  12. We can’t currently deny access to an entire dimension, rather than individual members on it, yet this is another common requirement:
  13. It would be cool to be able to parameterise server-side MDX calculated members and named sets:
  14. Here’s an old one: we really, really need to be able to rename hierarchies on each instance of a role-playing dimension. Not being able to do so seriously limits the usability of role-playing dimensions.
  15. Similarly, I’d like to be able to set individual All Member captions for all of the attributes on a dimension:
  16. One mistake I make all too often is change something in the cube, redeploy it, process and then at the very end of processing have it fail because of an MDX Script error. I know it’s possible to use the ScriptErrorHandling mode to do something similar, but that’s a bit dangerous to set in production. What I have is an option for processing in a dev environment that is set by default and allows you to ignore MDX Script errors if any occur.
  17. Support for cross-dimensional user hierarchies would be useful for two reasons: first it would help us create (and possibly optimise) complex drillpaths; second, it would help when you have a ROLAP attribute that you want to expose only as the lowest level of a user hierarchy, to encourage people to cut down the scope of their query before they used it. Another approach to solving this second problem would be the ability to have attributes on the same dimension with different storage modes (which Greg Galloway and Harsh suggested on my older post).
  18. Dimensions are getting bigger and bigger, and it would be useful to be able to partition them for manageability and performance reasons. For example, doing this would allow you to delete some members from the dimension quickly by deleting the partition; you could also speed up Process Updates by only processing the partitions where dimension members have changed.
  19. I find it frustrating that dimension calculations (ie custom member formulas, unary operators etc) can’t be edited from the MDX Script. I want all my MDX calculations in one place!
  20. It would be really cool if we could declare parameterised MDX Select statements on the server, and then allow client tools to expose them as pre-canned queries, similar to what can be done with stored procedures in the relational world. This would mean that as developers we could write complex queries that most end users would never be able to create themselves (even if their client tools were capable of building them). Similarly, it would be useful if we could declare custom MDX functions in the MDX Script: for example, you could declare a complex set operation as a new function, then use it in your own calculations and also expose it to users so that they could use it in their own client tools.
  21. Fix parent/child hierarchies! They’re so commonly used, but there are so many ‘known issues’… I want to:
    1. Them to perform better, perhaps by building aggregations within them
    2. To be able to scope calculations on them properly
    3. Have more flexibility building them, by creating more than one per dimension and not having to build them from the key attribute
  22. I’d like calculated members to do everything, and have all of the same properties, as real members. This means I want to be able to
    1. Control the order they appear on a level, and mix them in with real members
    2. Allow them to have other calculated members as children
    3. Allow them to have member properties
    4. Allow all properties (member properties as well as properties such as Name) to be dynamically generated using an MDX expression
    5. Be able to dynamically generate whole sets of calculated members with a single expression; this would, for example, be useful for doing pareto analysis or dynamically generating bandings.
    6. Be able to expose the MDX definition as a property visible in MDX
    7. Be able to secure calculated members with dimension security
  23. From Jamie Thomson (from a long time ago) – automating partition generation when new data appears in the cube is a pain – this is something SSAS should be able to do for us:
  24. One from Thomas Ivarsson here – it would be good to have a flag for the BottomCount function to get it to automatically remove empty tuples from the set we’re passing in:
  25. From Darren Gosbell – we really need an official, built-in way of making calculations work properly when the current context is a set (for example when doing a multiselect results in a set in the Where clause):
  26. Also from Darren – at present, if you rename a database in SQLMS you then can’t deploy to this database from BIDS:
  27. From Marco Russo – at the moment, the DSV still has problems with handling TinyInt columns, casting them to different types in different scenarios:
  28. From Marco Russo – fix drillthrough! It’s almost unusably slow at the moment on large cubes and it doesn’t work with semi-additive measures, let alone calculated measures. This is such an important bit of functionality it needs to work properly:
  29. Following on from this, we need better control over the SQL that Analysis Services generates full stop. I’d like more influence over how SQL is generated for supported relational data sources and also official support (and help) for creating cartridges for new relational data sources.
  30. A tool that displayed MDX query plans would make performance tuning queries much easier:
  31. From David Clem – you can’t currently control the order in which calculated measures are displayed. It would be useful if there was a ‘display order’ property that allowed you to do this:

Of course, if you’ve got your own issues open on Connect that you’d like to publicise, please leave a comment below…

Announcing SQLBits V: SQLBits Goes West, 19th-21st November 2009

Yes, it’s time to announce the next SQLBits, and this time we’re going to Newport in South Wales. It’s going to be a three day event, taking place from the 19th to the 21st of November: the first day will be pre-conference seminars, the second day(a Friday) will be a day of SQL2008 and R2 content for which we’ll be charging a modest sum, and the third day will be the usual free Saturday event. We’ll be holding it at the Celtic Manor Resort, where the 2010 Ryder Cup will be held, so make sure you pack your gold clubs…

The Friday is a bit of a departure for us, but don’t worry, we won’t be turning SQLBits into just another paid-for conference. The formula for Saturday will be the same as ever but we have always been aware that there are a large number of people who simply can’t attend a conference on a Saturday. Since we don’t have the cash to run a free two-day conference, we reasoned that the people who would be interested in attending on a Friday are also likely to work for companies who can pay for training; in return for the cash, we’ll be providing a more structured set of sessions presented by well-known speakers.

Anyway, all the details we have at the moment are at:

Conference registration isn’t open yet but put the dates in your diary! If you register yourself on the site we’ll be able to send you regular updates. And of course, if you’re interested in sponsoring SQLBits then please let us know.

PS as a tribute to our location, Martin Bell has the announcement in Welsh on his blog:

Disk Partition Alignment white paper

Via Greg Lowe, I see there’s a new white paper out on disk partition alignment for SQL Server:

Of course its main focus is the SQL Server relational engine, but it’s all equally relevant to Analysis Services. If you’re running Analysis Services on Windows 2003 or before (or even if you’re running Windows 2008 but your partitions were created on an earlier OS) then this is a must-read.

%d bloggers like this: