Chris Webb's BI Blog

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

Archive for October 2009

“SQLBits 2009” written in Task Manager

leave a comment »

Here’s something that is seriously impressive in a geeky way! To promote SQLBits, Henk van der Valk of Unisys put together this video:

Yes, that is “SQLBits 2009” written in Task Manager…

Written by Chris Webb

October 31, 2009 at 9:36 pm

Posted in Events

BI Survey 9 – Invitation to Participate

leave a comment »

I’ve just been told that fieldwork has begun on the BI Survey 9; if you’d like to participate you can find all the details below.

Full disclosure: by posting this here I’ve been promised a free copy of the research when it’s published – and I promise to blog the juicy details (as I have done in the past) when I get it.

The BI Survey 9: The Customer Verdict

We would very much welcome your participation in ‘The BI Survey 9: The Customer Verdict’, the world’s largest survey of business intelligence (BI) and performance management (PM) users (formerly known as The OLAP Survey).

As a participant, you will:

- Receive a summary of the results from the full survey

- Be entered into a draw to win one of ten $50 Amazon vouchers

- Ensure that your experiences are included in the final analyses.

To take part in the survey on-line, visit:

http://digiumenterprise.com/answer?link=249-KP9DYABR

BARC’s annual survey obtains input from a large number of organizations in order to better understand their buying decisions, the implementation cycle and the business benefits achieved.

Both business and technical users, as well as vendors and consultants, are welcome to participate. If you are answering as a consultant, please answer the questions (including the demographic questions) from your client’s perspective; we will ask you separately about your own firm.

The BI Survey has always adopted a vendor-independent stance. While vendors assist by inviting users to participate in the Survey, Business Application Research Center (BARC) – the publisher – does not accept vendor sponsorship of the Survey, and the results are analyzed and published without any vendor involvement.

You will be able to answer questions on your usage of a BI product from any vendor. Your answers will only be used anonymously, and your personal details will never be passed on to vendors or other third parties.

* BARC (Business Application Research Center) is a leading independent software industry analyst specializing in Data Management and Business Intelligence. For more information on BARC please visit The BARC website and www.BI-Verdict.com.

Written by Chris Webb

October 27, 2009 at 8:54 pm

Posted in On the internet

Actions and Multiselect

with 4 comments

At the beginning of this week a customer asked me why, in a certain third-party client tool that shall remain nameless, they could no longer do a drillthrough when they did a multiselect on a filter axis. It seemed a bit weird to me, and it got weirder when I asked around for ideas and Greg Galloway pointed out that Excel 2007 didn’t show any actions at all when there was a multiselect, and Marco Russo noted that the current beta of Excel 2010 didn’t either. This made me wonder whether the problem was in fact with Analysis Services rather than the client tools…

I didn’t actually know how a client tool worked out what actions were available when, so I did some research and found out that the MDSCHEMA_ACTIONS schema rowset was how it was done. Here’s the documentation on MSDN:
http://msdn.microsoft.com/en-us/library/ms126032.aspx

For example, if a client tool needs to know which actions can be called when a user clicks on a cell in a resultset, then it will execute an XMLA command something like this one on Adventure Works:

   1: <Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
   2:     <RequestType>MDSCHEMA_ACTIONS</RequestType>
   3:     <Restrictions>
   4:       <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
   5:         <CUBE_NAME>Adventure Works</CUBE_NAME>
   6:         <ACTION_TYPE>401</ACTION_TYPE>
   7:         <COORDINATE>([Customer].[Country].&amp;[Australia],[Measures].[Internet Order Quantity])</COORDINATE>
   8:         <COORDINATE_TYPE>6</COORDINATE_TYPE>
   9:       </RestrictionList>
  10:     </Restrictions>
  11:   <Properties>
  12:   </Properties>
  13: </Discover>

You can see that a tuple is being passed into the COORDINATE to indicate which cell we’re interested in. But when there’s a multiselect, which cell in the cube are we actually clicking on? Good question… Different client tools handle multiselect in different ways, and it turns out there’s no way of telling SSAS you’re doing a multiselect in this situation. If you try to pass a set of tuples to the COORDINATE you get no actions returned, for instance.

Having talked this over with Akshai Mirchandani from the dev team, what the client tool needs to do is to make multiple calls to MDSCHEMA_ACTIONS, one for each member selected in the multiselect. It then needs to work out from each of the rowsets returned which actions should be available in the current context – and of course, in this case, there’s a good chance that different client tools will do different things (if they do anything at all). Not ideal.

To be honest, this really needs to be something that is solved in SSAS rather than on the client and the key to solving it properly would be to have a standard way of handling and detecting multiselect in MDX. As Mosha hinted here, it’s something that’s been on the dev team’s radar for a while but it’s still not made it into the product unfortunately. In the meantime, if there are any client tool developers from the Excel team or third parties out there reading this, it would be great if you could at least do something rather than nothing here!

Written by Chris Webb

October 26, 2009 at 2:50 pm

Posted in Analysis Services

Access 2010 and Access Services

with 3 comments

I’ve not used Access for, oh, years now I think… but here’s an interesting video on Channel 9 showing new features in Access 2010 and Access Services:

http://channel9.msdn.com/shows/Access/Microsoft-Access-2010-Demo/

Not only can you publish Access databases, including any UI, up to Sharepoint in Office 2010, but you can create reports in your Access database and once they get published they become SSRS reports (the last few minutes of the video talks about this). Yet another way to create dashboards and BI reports then…

Written by Chris Webb

October 21, 2009 at 10:50 am

Posted in Access

Gemini is now PowerPivot, and other news

with 11 comments

Gemini is of course only a codename, and it was announced today that it’s real name will be ‘PowerPivot’. Given that there have been some pretty awful Microsoft branding decisions over the years I think PowerPivot is actually a very good name (let’s be glad it’s not called something like “Microsoft Office 2010 SQL Server Analysis Services R2 Desktop Edition”), certainly one that will stick in the minds of its target users. There’s a new website, albeit with no new information I can see, here:
http://www.powerpivot.com/

and there’s a data sheet here:
http://download.microsoft.com/download/8/C/F/8CF3C7AD-E252-44F1-B3A0-CB26CD0AC902/PowerPivot%20datasheet%20%28TDM%29.docx

Also, here’s a blog entry summarising the new features that are coming in Sharepoint 2010:
http://blogs.msdn.com/sharepoint/archive/2009/10/19/sharepoint-2010.aspx

Here’s an excerpt highlighting the BI-relevant features:

Insights

Historically, business intelligence has been a specialized toolset used by a small set of users with little ad-hoc interactivity. Our approach is to unlock data and enable collaboration on the analysis to help everyone in the organization get richer insights. Excel Services is one of the popular features of SharePoint 2007 as people like the ease of creating models in Excel and publishing them to server for broad access while maintaining central control and one version of the truth. We are expanding on this SharePoint 2010 with new visualization, navigation and BI features. The top five investment areas:

1. Excel Services – Excel rendering and interactivity in SharePoint gets better with richer pivoting, slicing and visualizations like heatmaps and sparklines. New REST support makes it easier to add server-based calculations and charts to web pages and mash-ups.

2. Performance Point Services – We enhanced scorecards, dashboard, key performance indicator and navigation features such as decomposition trees in SharePoint Server 2010 for the most sophisticated BI portals.

3. SQL Server – The SharePoint and SQL Server teams have worked together so SQL Server capabilities like Analysis Services and Reporting Services are easier to access from within SharePoint and Excel. We are exposing these interfaces and working with other BI vendors so they can plug in their solutions as well.

4. “Gemini” – “Gemini” is the name for a powerful new in memory database technology that lets Excel and Excel Services users navigate massive amounts of information without having to create or edit an OLAP cube. Imagine an Excel spreadsheet rendered (in the client or browser) with 100 million rows and you get the idea. Today at the SharePoint Conference, we announced the official name for “Gemini” is SQL Server PowerPivot for Excel and SharePoint.

5. Visio Services – As with Excel, users love the flexibility of creating rich diagrams in Visio. In 2010, we have added web rendering with interactivity and data binding including mashups from SharePoint with support for rendering Visio diagrams in a browser. We also added SharePoint workflow design support in Visio.

Now, I’ve not been following Sharepoint 2010, but two things strike me here. First of all, Excel Services does heatmaps? It’s the end of a long day, but I don’t remember seeing heatmaps in my Excel 2010 CTP. I wonder if this is a new charting feature…? Secondly, Visio Services – ok, a quick Google shows that this has been public knowledge for over a year now, but I think this is very interesting from a BI point of view. Remember that Visio can already consume data from SSAS (see here on how to do this); assuming that Visio Services will be able to do the same thing, I think we have here yet another way of creating BI dashboards.

UPDATE: before you leave a comment, it’s just struck me that what Excel means by a heatmap is that colour-scale cell formatting that’s been possible since Excel 2007. Hmm, so probably nothing to get excited about.

Written by Chris Webb

October 19, 2009 at 8:34 pm

Posted in Gemini

Building an Ad-Hoc SSAS Cube Browser in SSRS

leave a comment »

The post I did on implementing SSAS drilldown in SSRS back in February has been one of the most popular I’ve written this year. Its success immediately made me think of taking the idea one step further and building a full, ad-hoc cube browser in SSRS – something I’ve had a crack at doing several times in the past, but which has proved to be pretty much impossible with SSRS out of the box. However I knew that with the extra flexibility that Intelligencia Query (full disclosure – I have a financial stake in this product; see here for some background on my involvement with it) gives it was going to be feasible… the next problem was that I didn’t have the time to do the work.

Luckily, my friend Andrew Wiles (who owns the company that develops and sells Intelligencia Query) did have the time to do this and blogged about it here:
http://andrewwiles.spaces.live.com/blog/cns!43141EE7B38A8A7A!566.entry

He also uploaded a video demo of it to YouTube:

It’s now available as a sample app for the SQL 2008 version of IQ (it relies on DMVs, so it won’t work with 2005), and he’s made several improvements to it since. I thought I’d mention it here because it’s not only a fun demo, it shows just how flexible the combination of SSRS and IQ is: we traditionally think of SSRS as being suitable only for static or semi-static reports, but here it is working as a basic ad-hoc query tool. OK it’s not exactly elegant the way you have to rerun a report every time you click something, and of course the only form of interaction possible is clicking on links, but hopefully you get the point!

Written by Chris Webb

October 16, 2009 at 2:05 pm

Posted in Reporting Services

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:

http://social.technet.microsoft.com/Forums/en-US/ppsmonitoringandanalytics/thread/b4e9bd35-62ce-4ca5-bd1f-05133b30bcc9

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

Follow

Get every new post delivered to your Inbox.

Join 3,311 other followers