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

SQL Server Day Belgium

with one comment

I’m pleased to announce I’ll be speaking at the Belgian SQL Server User Group’s one-day conference, SQL Server Day 2009, which will be taking place in Mechelen on December 3rd 2009. You can find out more about the agenda and register here:

http://sqlserverday.be/

It looks like it’s going to be a great day, so if you’re in Belgium (or nearby) why not come along?

Written by Chris Webb

October 13, 2009 at 1:30 pm

Posted in Events

Using Dimensions as Parameters to Calculations and Sets

with 3 comments

One of the advantages of using SSRS as a client for SSAS is the control you have over your MDX, and one of the advantages of control over your MDX is the ability to parameterise not only your queries but also any calculations defined in those queries. It would be great if we could parameterise calculations defined in the MDX Script (I have a Connect open on this, in fact – please vote!) but until we can, we can do something almost as good: we can use a dimension hierarchy to store a pre-defined range of parameter values and then use the CurrentMember on this hierarchy to pass one of these values to a calculation or dynamic named set. Here’s how.

First of all, we need to create a dimension to hold these parameter values, something like a Time Utility or Shell dimension. We can create the source data for this easily in the DSV using a named query and a SQL SELECT statement as follows:

SELECT        1 AS ParamID, ‘5%’ AS ParamDesc, 1.05 AS ParamValue
UNION ALL
SELECT        2 AS ParamID, ‘10%’ AS ParamDesc, 1.1 AS ParamValue
UNION ALL
SELECT        3 AS ParamID, ‘15%’ AS ParamDesc, 1.15 AS ParamValue
UNION ALL
SELECT        4 AS ParamID, ‘20%’ AS ParamDesc, 1.20 AS ParamValue

We can then build an SSAS dimension – I called it [Parameter Values] – with a single attribute, its KeyColumn property using the ParamID column above, its NameColumn property using the ParamDesc column, and its ValueColumn property using the ParamValue column. This means we have a simple hierarchy with four members on it. We then add the new dimension to the Adventure Works cube; it doesn’t need to have any relationship with any measure group.

Now let’s use it. Imagine we want to see what the value of Internet Sales Amount would be if it grew by 5%, 10%, 15% or 20%, how would we do it? Well, what we could do is say that when the All Member on the Parameter Values is selected we see the real value of Internet Sales Amount, but when one of the other members is selected we increase the value of Internet Sales Amount by the percentage associated with the selected member. The MDX required would look like this:

SCOPE([Measures].[Internet Sales Amount]);
    SCOPE([Parameter Values].[Parameter Values].[Parameter Values].MEMBERS);
        THIS =
            ([Measures].[Internet Sales Amount], [Parameter Values].[Parameter Values].[All])
            *
            [Parameter Values].[Parameter Values].CURRENTMEMBER.MEMBERVALUE;
        FORMAT_STRING(THIS) = ‘CURRENCY';
    END SCOPE;
END SCOPE;

What I’m doing here is scoping on Internet Sales Amount and all of the members except the All Member on my new dimension, so that I’m only doing my calculation when a selection is made on the Parameter Values hierarchy. In this scope I’m then multiplying the value of Internet Sales Amount at the All Member with the value returned by the MemberValue function for the CurrentMember on [Parameter Values].[Parameter Values] – which is the value from the column I used in the ValueColumn property of the attribute. Even though I’m scoping on a real measure, the calculation doesn’t aggregate up to the All Member because this dimension has no relationship with the measure group that Internet Sales Amount is from (or indeed any other measure group).

Here’s a query that shows the results:

SELECT [Measures].[Internet Sales Amount] ON 0,
[Parameter Values].[Parameter Values].MEMBERS ON 1
FROM [Adventure Works]

And here’s the output, showing Internet Sales Amount and underneath it the value increased by 5%, 10%, 15% and 20%:

image

So we’ve got a (sort of) parameterised calculation. We can also use a similar approach with dynamic named sets too – here’s an example dynamic named set definition that uses the same dimension to control the percentage passed into TOPPERCENT function:

CREATE DYNAMIC SET MYSET AS
    IIF([Parameter Values].[Parameter Values].CURRENTMEMBER IS
        [Parameter Values].[Parameter Values].[All],
            [Customer].[Customer].[Customer].MEMBERS,
            TOPPERCENT(
                [Customer].[Customer].[Customer].MEMBERS
                ,([Parameter Values].[Parameter Values].CURRENTMEMBER.MEMBERVALUE – 1) * 100
            , [Measures].[Internet Sales Amount])
        );

The set returns all Customers if no selection is made on Parameter Values. However if a selection is made on Parameter Values then the selection drives the number of Customers that the set returns. So the query:

SELECT [Measures].[Internet Sales Amount] ON 0,
MYSET ON 1
FROM [Adventure Works]

…returns all 18485 Customers, whereas:

SELECT [Measures].[Internet Sales Amount] ON 0,
MYSET ON 1
FROM [Adventure Works]
WHERE([Parameter Values].[Parameter Values].&[1])

…returns the top 5% of Customers (I’m using the MemberValue function minus 1, multiplied by 100, here so I can use the value 5 rather than the original MemberValue of 1.05) by Internet Sales Amount, which results in the top 164 Customers being returned. Slicing by the other members on Parameter Values will give me the top 10%, 15% and 20% of Customers by Internet Sales Amount.

Written by Chris Webb

October 9, 2009 at 10:14 pm

Posted in MDX

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 http://blogs.technet.com/trm/archive/2009/10/01/october-2009-technology-rollup-mail-sql-server.aspx) 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:
http://www.microsoft.com/downloads/details.aspx?displaylang=en&familyid=dae82128-9f21-475d-88a4-4b6e6c069ff0
http://www.microsoft.com/downloads/details.aspx?displaylang=en&familyid=a370fbc9-98b1-4f5c-b09b-6f1bf08e9292

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

Follow

Get every new post delivered to your Inbox.

Join 3,113 other followers