For the second year running I'm late celebrating my blog birthday (it was yesterday); my only excuse is that I'm still reeling from the amount I've eaten over the last two weeks. But four years of blogging... wow... it feels like ages.

And to a certain extent I feel that, after all this time, I'm running out of things to say here. The actual writing of blog entries isn't a problem, it's more the problem of having something to write about. Part of the problem is me: I don't want to write about things I don't find interesting so I haven't gone down the route of turning the blog into a MDX tutorial (Bill Pearson does that much better than I ever could, and only Mosha could ever cover the advanced stuff properly), but at the same time I'm not coming across so many MDX/SSAS issues or obscure features as I used to. Part of the problem is, too, that SSAS2008 was so light on new features that it didn't provide me with much new to write about. So I'm hoping that Gemini, Kilimanjaro, Excel 14, Azure etc will give me something to get my teeth into in 2009; I'm sure they will. If not, well, I've always wanted to spend some time getting into Mondrian and other open source BI technologies. And with the economy the way it is I suppose I'll have a lot more spare time for learning new stuff in the coming twelve months...

But anyway, bear with me and keep reading! For those of you who have stuck with me for the last four years, thanks, and best wishes for 2009.

Here's a way-out thought I had over the Xmas break for a new approach to building BI reports....

Have you, when you've asked a typical non-technical business user what they want a report to look like, asked them to draw a quick sketch? I do all the time - I find seeing what the user wants the report layout to look like is much the best way to understand what they want and for the user it's the best way to express their requirements. So on the back of the proverbial envelope you'd get something like this:

...and then go back to your desk and write the query and design the report layout in something like SSRS. So - why can't we cut out a step and go direct from the sketch to the report design? I can see two options for the first step here:

• Using a tablet PC you write some software that works a bit like OneNote, but where the user can draw the outline of a report freehand. Unfortunately tablet PCs just aren't that common and the really tech-phobic end user wouldn't feel comfortable using one.
• The user draws the report on paper and then the drawing gets scanned; definitely something the most computer illiterate manager would be comfortable with.

You would then take the freehand drawing and:

• Interpret the freehand lines into the borders of a table, and
• Interpret the text on columns and rows as either
  • Explicit selections of members, or
  • Set expressions
• Apply a lot of smarts to format the report in ways that conform to the best practices laid down by the likes of Stephen Few et al. Almost no business or IT people (me included) have any idea on how to format reports properly, and while you could argue that this might be intrusive I think users would appreciate a tool that did this for them.
• Apply a standard corporate template, with the appropriate logos etc in place.

Working out what the borders of a table should be from a freehand drawing must be possible (although implementation would be well beyond me). Interpreting what the user has written they want on columns and rows would present more problems:

• Handwriting recognition is notoriously difficult to do well, and usually the software needs to have a bit of practice to get good. On the other hand, in this particular scenario it should be easier because the user won't be writing just any old text. For instance, if we assume that we're working with an Analysis Services data source, we know that any text is either going to be the name of a member or something that will resolve to a set expression; we also know, for instance, if there are what look like multiple member names on the same position on the same axis they will all have to come from the same hierarchy.
• Resolving text to member names is all very well, but turning sentences into set expressions would be trickier. It seems reasonable to think that phrases like "Products where sales is greater than £100" could be interpreted effectively, but whether your average business user can write something as clear as that is debatable. Any tool would certainly have to prompt the user to confirm that the interpretations it has made was correct, and do to that it would have to resort to the kind of techy interface that the tool is trying to get away from.
• Similarly there are going to be ambiguities that need to be resolved when looking at a the design. For example, the drawing might have the years 2006, 2007 and 2008 on columns. But does this mean the report should always have these three years on columns, or the last three years with data, or something else?

So it certainly wouldn't work like magic, but at the same time I think it would offer some advantages over current report design tools, the designers of which have fallen into the trap of building a UI on top of the functionality they've got  available in MDX or SQL, rather than building a UI for what the user actually wants to do. After all, don't you think that it's actually very difficult to lay out anything other than the most simplistic reports in most report design tools, compared to how easy it would be to draw the report?

The next SQLBits will be taking place in Manchester (in the UK) on March 28th 2009, and if you're interested in submitting a session you can now do so here:
http://www.sqlbits.com/information/SessionSubmission.aspx

Remember, we're always looking for new blood so don't be nervous and have a go at speaking! We're also looking for sponsors too:
http://www.sqlbits.com/information/Sponsorship.aspx
It's the ideal venue to promote your SQL Server-related product or service to several hundred serious SQL Server users.

Hope to see you there!

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:
http://www.sisense.com/

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:
http://community.sisense.com/blogs/siblog/archive/2008/11/19/250.aspx
...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.

Interesting article here on Intelligent Enterprise from Seth Grimes on how SQLStream and Mondrian can be used together for real-time OLAP analysis on Twitter feeds:
http://www.intelligententerprise.com/blog/archives/2008/12/bi_on_content_f.html

More on the technical side of this from Julian Hyde's blog earlier this year:
http://julianhyde.blogspot.com/2008/02/streaming-sql-meets-olap.html

I'm still not convinced there's much of a market out there for real-time OLAP (as opposed to real-time BI); after all, how many people out there are actually using pro-active caching with Analysis Services? The kind of analysis you do with an OLAP tool, looking at high-level trends in aggregated data, seems to me to be the complete opposite of BI scenarios where you need to be able to respond instantly to certain events. But maybe I've just not come across the right business scenarios yet.

...or, what to do while you're waiting for Analysis Services to get a proper resource governor (fingers crossed for the next version).

We all know about the query from hell, that one query that the idiot from Finance insists on running to try to pull the entire contents of the cube down into Excel even though you've told him a hundred times not to do it, that one query that brings your server to its knees just at the worst possible moment. What can you do about it? Well, you can set a general timeout on all all queries that are run against Analysis Services using the ServerTimeout property in msmdsrv.ini, but this is something of a blunt instrument - while I don't mind everyone else's queries timing out after two minutes, I certainly don't want mine to do that and I definitely don't want that to happen to the CEO's queries either. On the other hand, you can wait for someone to phone you up and complain that the server's really slow, then take a look at what's running and kill sessions manually using something like the technique described here:
http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!1652.entry

But to me neither option seems satisfactory. I want to be able to kill sessions automatically but at the same time apply some rules of my own: if the query has run for more than 30 seconds and the user is that idiot from Finance then kill it; if the query has run for more than 5 minutes and the user is the CEO send me an email so I can gently remind him not to drag the 6-million-member Products hierarchy onto rows in Excel; if I'm running the query, do nothing. What better way to implement this logic than in an SSIS package? I put together a proof of concept and this is how I did it:

1. First of all, I needed a way of finding the sessions that I want to kill. As we've already seen, you can find a list of currently executing commands by using the following AS2008 DMV:
   select * from $system.discover_commands
   But you may need more information in order to make the decision on whether to kill or not so it could also be useful to run the two following DMV queries to find out more about sessions and connections:
   select * from $system.discover_sessions
   select * from $system.discover_connections
2. I then took each of these three queries and ran them in an OLEDB Source in my SSIS dataflow, and joined the resultsets. I could then implement the logic I wanted to use to decide whether a session should be killed or not in a Conditional Split, for example using an expression something like this:
   (COMMAND_ELAPSED_TIME_MS > 30000)  &&  ([SESSION_USER_NAME] != "MyPC\\ChrisWebb")
   I could then store the SPIDs of the sessions I wanted to kill in a Recordset destination. The dataflow looked like this:
   
3. With the resulting recordset stored in a variable, I could then loop over the recordset in a ForEach task in my control flow and use an Analysis Services Execute DDL task to run the XMLA Cancel command to kill each query:
    
4. The last step is to schedule the package to run frequently, perhaps every 30-60 seconds, using SQL Server Agent.

Very easy. Of course you could add loads more functionality to this basic package - as I said, you may want to send an email to someone after you've killed their session explaining what has happened, or you may only want to kill a session if there are other users running queries at the same time.

You can download the VS2008 from here: