Another SQLBits – the fifth! – has come and gone, and I wanted to say thanks to everyone that helped to make it such a success. It was a three-day event for the first time this time, which meant that there was even more organisation work needed, but looking back I think it all went extremely smoothly and was by far the slickest conference we’ve put on so far. My colleagues on the organising committee, Simon Sabin, Martin Bell, Allan Mitchell, Darren Green and James Rowland-Jones, are a great bunch of people and it’s always a pleasure to work with them on SQLBits even if it does take a big chunk out of our spare time. Thanks are also due to the speakers and sponsors, without whom the event would not be possible, and I’d also like to highlight the people who volunteered to help out doing unglamorous things like stuffing the attendee bags and room monitoring – Rachel Clements, Jon Reade,  Gary Short, Rachel Hawley, Richard Douglas, Luke Hayler and many others.

If you came to SQLBits I hope you enjoyed it, and if you did enjoy it please let everyone know by blogging and twittering about it. Please also join the SQLBits groups on LinkedIn and Facebook, and if you’ve got any pictures of the event why not post them on the latter?

Anyway, it’s time to crack on with some real work and deal with the big pile of emails that has built up over the last few weeks. I’ve missed a whole bunch of big announcements I would otherwise have blogged about but I’m sure you’ve caught them elsewhere… I must get round to downloading and installing the latest Powerpivot CTP etc.

See you at the next SQLBits!

Interesting news from PDC: Microsoft has announced two new services – Pinpoint and Dallas.

You can find Pinpoint here: http://pinpoint.microsoft.com

Here’s the blurb from the site:

Pinpoint is the fast, easy way for business customers to find experts, applications, and professional services to meet their specific business needs—and build on the software they already have.

At the same time, Pinpoint helps developers and technology service providers quickly and easily get software applications and professional services to market—and engage customers who need what they offer.

Pinpoint is the largest directory of qualified IT companies and their software solutions built on Microsoft technologies.

• More than 7,000 software application offerings.
• More than 30,000 Microsoft-technology experts.
• The largest, most diverse set of Microsoft business platform offerings in the industry in a central location.
• Direct links between applications and the services that support them.

Whether you’re searching for expert help or offering it, Pinpoint helps you easily find and engage the right people and technologies to get the job done.

Much, much more interesting from a BI point of view is Dallas, which is part of Pinpoint: http://pinpoint.microsoft.com/en-US/Dallas

It’s Microsoft’s marketplace for data, all built on Azure. Again from the blurb:
Microsoft Codename “Dallas” is Microsoft’s Information Services business, enabling developers and information workers to instantly find, purchase, and manage Web services and datasets to power the next set of killer applications on any platform.

The Register has the best write-up of what this is here: http://www.theregister.co.uk/2009/11/17/microsoft_dallas_data_service/

From that article:
Dave Campbell, a Microsoft technical fellow, demonstrated Dallas at PDC. He showed a list of data provides from the partners such as infoUSA, subscriptions, the ability to store structured and unstructured data, and to explore the data without needing to parse it, to preview the data in ATOM, invoke the data as a Rest service and analyze the data using PowerPivot in Microsoft's Excel spreadsheet program.

Note my emphasis on the last sentence! Here at last is the ability to buy that third party data that’s been a part of every Powerpivot demo. I’ve worked with a lot of companies that sell data in my career, and this looks like it could be a very significant development for them. I’d even heard vague rumours that MS were interested in buying commercial data providers at one point, several years ago – if they were prepared to go this extreme then it would certainly go a long way to making this strategy a success.

Now just think how cool it would be if SSAS or PowerPivot could be hosted on the cloud, so all you needed was Excel to analyse this data. Maybe one day…

At long last, the SQLBits agenda has been published:
http://www.sqlbits.com/information/newagenda.aspx

It’s not too late to register, even though SQLBits is only next week. It’s looking like it will be the largest event yet in terms of attendance… If you’re coming and you see me around, say hello!

UPDATE: car sharing is live now too - http://sqlbits.com/CarSharing.aspx

Here’s something that I suspect a few people out there will be surprised I didn’t know – but as far as I’m concerned, if I didn’t know it then it’s worth blogging about.

Anyway, it regards ragged hierarchies, the HideMemberIf property and the MDX Compatibility connection string property. Now you probably know that if you want to turn a user hierarchy into a ragged hierarchy (perhaps to avoid using a parent child hierarchy) you need to use the HideMemberIf property on the user hierarchy. For example, imagine you were using the following SQL query as the basis of your customer dimension:

SELECT        'UK' AS Country, 'Bucks' AS State, 'Amersham' AS City, 'Chris Webb' AS Customer
UNION ALL
SELECT        'Italy' AS Country, 'BI' AS State, 'Biella' AS City, 'Alberto Ferrari' AS customer
UNION ALL
SELECT        'Vatican' AS Country, 'Vatican' AS State, 'Vatican' AS City, 'The Pope' AS customer

We could build a dimension off this with attributes for Country, State, City and Customer, and for two out of our three customers that would be fine. However the Pope lives in the Vatican, which is (at least for the purposes of this exercise) a Country with no concept of City or State; and in the case of customers who live in the Vatican, we just want to be able to drill down on the Country ‘Vatican’ and see all of the Customers who live there without drilling down through a meaningless State and a City.

So what we can do is build a user hierarchy on our dimension with levels Country, State, City and Customer, and on the lower three levels set the HideMemberIf property to OnlyChildWithParentName:

Then, with any sensible client tool, we can connect to the cube and browse the dimension as we want:

I saw ‘sensible’ client tool, because of course this only works if you set:
MDX Compatibility=2
…in the connection string. And of course Excel 2007 hard-codes MDX Compatibility=1 in the connection string and doesn’t allow you to change it, so you can’t use ragged hierarchies properly.

This much I knew.

However, what I didn’t realise until last week when I was moaning about this to TK Anand from the SSAS dev team at PASS, is that for some ragged hierarchies you don’t need to set the MDX Compatibility connection string property at all.

For example, if in our case we duplicate the Customer upwards rather than the Country downwards, like so:

SELECT        'UK' AS Country, 'Bucks' AS State, 'Amersham' AS City, 'Chris Webb' AS Customer
UNION ALL
SELECT        'Italy' AS Country, 'BI' AS State, 'Biella' AS City, 'Alberto Ferrari' AS customer
UNION ALL
SELECT        'Vatican' AS Country, 'The Pope' AS State, 'The Pope' AS City, 'The Pope' AS customer

…and then build the dimension, setting HideMemberIf on our user hierarchy to OnlyChildWithParentName, we can get the result we want without setting the MDX Compatibility property. Here’s a screenshot of this new dimension in Excel just to prove it:

The difference here is that we’re hiding all members below the State level right down to the bottom of the hierarchy, rather than hiding members somewhere in the middle of the hierarchy. Truly, this is one of those “Doh, if only I’d known!” moments… this at least means that in some of the scenarios where you’d use ragged hierarchies you can get them to work with Excel, even if it means that we have to hack the data (‘The Pope’ is a Customer, not a State or a City). On the other hand there are plenty of scenarios where you do need to hide members in the middle of a hierarchy, and frankly I don’t see why Excel 2007 can’t set MDX Compatibility=2 in its connection string so they work properly.

The PASS Summit is over for another year and I’m just starting out on the long trip back home, so there’s plenty of time to get my thoughts together on what’s happened over the past week. In fact there’s not much to say about the event itself: it was, as ever, a lot of fun and totally worthwhile. Hey, within 30 minutes of arriving at the conference I learned I’d won an award for the best BI-related blog entry, for my post on implementing real SSAS drilldown in SSRS!

Attendance was up from last year although probably the recession still took its toll: remember that there was no BI Conference this year and I would have thought that a lot of people who would have gone to it would have gone to PASS instead. To be honest I think not having a BI Conference is a good thing, actually. I don’t like having to choose which conference to attend, and part of the benefit of a conference is to get as many members of a tech community together in one place. And this was certainly the largest gathering of Analysis Services people I’ve ever seen: all the usual crowd were there, I met a lot of people who I’d only met a few times before, and I finally got to meet Darren Gosbell in person after having known him by email for at least five years. One complaint I would make about the event was that the sessions weren’t scheduled particularly well. I know everyone always complains about this but in this case it did seem worse than usual: my session, for example, was up against two other SSAS-specific sessions, but in other cases there were time slots with no SSAS content at all.

The other benefit of PASS is that you get to talk at length about what’s going on in the world of SQL Server with other like-minded people. As a result you get to crystallise your thoughts on a lot of matters and - guess what – I’m going to share mine here.

First of all, the topic that was on everyone’s lips was PowerPivot. In fact everyone at the conference must have seen the standard demo at least five times and there were also a lot of advanced sessions on it too. Don’t get me wrong, I really think PowerPivot it cool from a technology point of view, I am going to take the time to learn it, and I also think from a make-money-by-getting-people-to-upgrade-to-Office-2010 point of view it is a very clever move for Microsoft. But my feelings about it remain ambiguous. Quite apart from the arguments about it discouraging ‘one version of the truth’ and encouraging spreadmarts that have already been discussed ad nauseam, I have another problem with it: I don’t honestly know whether I, as a consultant, will be able to make any money from it. The very nature of it, as a self-service tool, means no expensive outside consultancy is necessary. I don’t think it will take business away from me though; it will be widely used and it will be used instead of regular SSAS for more basic projects, but the more serious stuff will stay with SSAS I hope. I think the need for sophisticated security and more complex calculations will be the deciding factor when people choose between SSAS and PowerPivot; I’m not sure I see many people upselling from PowerPivot to SSAS either. We’ll see.

Something that worries me more about PowerPivot is the fact that it seems to have diverted the attention of the SSAS dev team. For SSAS 2008 we had few new features, although the performance improvements were very welcome. For 2008 R2 I can only think of one new feature in SSAS, and that’s the ability to use calculated members in subselects that will allow Excel 2010 to use time utility dimensions properly (I’ll blog about that at some point). Even though work on good old server-side SSAS will resume for the next major release of SQL Server I worry that PowerPivot will take priority in the future. If this happened it would be bad for me and other BI partners from a business point of view, and seems crazy given that SSAS has been such a successful product in the enterprise sector; it’s not like there aren’t a lot of new features and fixes that could be done. Shades of IE6 and Microsoft getting complacent once it’s cornered a market, I think.

Last of all on PowerPivot, I suspect that there is something new relating to it in the roadmap that hasn’t been announced yet. David DeWitt devoted his keynote on Thursday to it, the specifics of column-store databases and the Vertipaq engine (which is the new in-memory storage engine that PowerPivot uses), and at the end hinted at this saying that although he couldn’t make any announcements, those people who had been paying attention might have some ideas on what the future held for it. Of course I hadn’t been paying attention properly, but the obvious thing would be to integrate it with the relational database somehow. Given that PowerPivot is now being hosted inside Sharepoint, why not host it in SQL Server too? It’s already very table and join friendly, and I could imagine a scenario where it was used inside SQL, pointed at a schema, some kind of proactive caching kept the data in SQL in synch with the data in the Vertipaq store, difficult BI calculations could be expressed in DAX, but the whole thing was transparent to TSQL. Imagine integrating that with Madison too!

Moving on, the other thing that has become clear to me is that I really have to sit down and learn Sharepoint (or at least the relevant bits of it) properly. It’s at the heart of Microsoft’s BI strategy and there’s no avoiding it. I have to admit to some mixed feelings about this move though, and I know other people I talked to at the conference share them. Partly it’s because, in the past, there were BI specialists and there were Sharepoint specialists and we didn’t necessarily have much to do with each other; now,  though, the two worlds are colliding and I’m outside my comfort zone. You might say that Sharepoint has been part of the MS BI strategy for ages now, what with PerformancePoint etc, but I see an awful lot of MS BI customers in my work and I very rarely seem to see any Sharepoint, although it could be because I’m not looking out for it. A more valid objection is that the need for Sharepoint Enterprise Edition CALs adds a lot of extra cost to a project; and from a technical standpoint Sharepoint itself carries a very big overhead – its installation and maintenance may put a lot of customers off if they don’t already have a company-wide Sharepoint strategy, and if they do have one they may not be willing to go to 2010 for some time. Sharepoint might be just too big for some customers to swallow, and be a difficult sell for BI partners.

I’d like to stress though, once again, that I see the considerable technical benefits for using Sharepoint for BI, and even if the reception of the latest wave of PerformancePoint has been somewhat muted (eg the realisation that the decomposition tree has been tacked on at the last minute and isn’t properly integrated) I am impressed with what’s coming with Excel 2010 and Excel Services too; for example I think the Excel Services REST API is very cool indeed, and as a SSAS client Excel 2010 is a big improvement on 2007 (which wasn’t all that bad either). I’ve decided I also need to learn Excel properly now as well – get to know all those advanced Excel functions, use Solver and all that. Once again two worlds are colliding: the Excel guys and the SSAS guys are going to have to learn a lot more about each others’ technologies for truly effective BI applications to get built.

Anyway, I think this post has gone on quite long enough now. As always, your comments on everything I’ve written here would be much appreciated.

Here are some notes from the SQLCat team’s session on SSAS consolidation and virtualisation; they’re a bit fragmentary since I’m too busy paying attention to what’s being said! I get asked about these issues by my customers all the time.

• Use Windows System Resource Manager to control how many resources SSAS can use. For more on WSRM see http://technet.microsoft.com/en-us/library/cc755056.aspx
• If SSAS and SQL are on the same server, use the Shared Memory protocol to improve processing performance
• Also since resource usage requirements for SSAS and SQL will be different when processing and when querying, if they’re on the same box you can use WSRM to dynamically change resource allocations at different times.
• Consolidating multiple SSAS databases on the same machine, it can be good to use multiple instances (maybe one per database) on the same machine to give fine control over resource usage, service packs etc.
• Someone asked the question of whether there is an overhead to using multiple instances each with one database rather than using a single instance with multiple databases. Answer: multiple instances would perform better but use slightly more resources; better to start with a single instance and only move to multiple instances when you have a good reason to do so.
• Tests run comparing SSAS running on bare metal and Hyper V – no difference in performance between the two for querying, but for the Storage Engine (processing and SE activity when querying) you use 1.5 times more threads on Hyper V (can modify the default number of threads available for processing, may therefore need to change this for Hyper V).
• Description of a custom-built system for load balancing SSAS developed by the MSSales team inside Microsoft. Code and white paper will be available in a few months.
• There were various issues with Synchronization in SSAS that have been fixed in the late CUs.
• IIS7 performs much better for HTTP access to SSAS – performs as well as a direct connection. I’m sure I also heard somewhere that there were some performance issues for HTTP access that were noticeable over a slow network that have also been fixed in the latest CUs.

The most useful session so far at this conference for me – I learned a lot.

Something I saw yesterday at PASS: Quest now have support for monitoring SSAS from their “Spotlight on SQL Server Enterprise” product. See

http://www.quest.com/newsroom/news-releases-show.aspx?contentid=10602
http://www.quest.com/spotlight-on-SQL-Server-enterprise/features-benefits.aspx

It’s pretty basic at the moment – they capture some Perfmon counters and data from schema rowsets, but no trace data – and nowhere near as sophisticated as what SQLSentry have, but it’s good to see another vendor entering this market.

Some notes/thoughts while I’m listening to John Welch’s session here at PASS on “SQL Server BI in the Cloud”. The room is packed… full marks to John for picking such a hot topic to speak on!

• Summary of reasons why the cloud is interesting for BI – easy scaling, setup, sizing etc.
• Distinction between ‘virtualised’ and ‘hosted’ services.
• Virtualised = pay on usage, instant scale, reduced scaling concerns
• Hosted = buy a set capacity
• Azure – making the point that, unlike most other cloud offerings, you can leverage your existing (SQL Server) skills
• Notes that other parts of the BI stack, apart from the relational engine, have been promised for the future. My feeling is that when/if SSAS in the cloud appears, it’s more likely to be PowerPivot in the cloud; note also that SSRS in the cloud has kind of already appeared with Access Services.
• BI scenarios not really considered so far by the Azure team. I echo John’s response of “Why???”
• Description of the Azure architecture. I was talking to someone last night about the way Azure requires use of SQL authentication (which MS have discouraged us from using for years!); SSAS of course only supports Windows authentication, which would be a problem for SSAS in the cloud, so I wonder if in the future we’ll get username/password authentication for SSAS?
• Limitations of Azure: 10Gb max data, query limit of 5 minutes, insert/update slow. Though for some, smaller, short-lived BI solutions Azure is a perfectly good solution; sharding is an option too.
• Shows SSRS (locally) working against data from Azure. Works better in CTP2 but still occasional bug.
• Before the presentation started I asked John if he’d tried using SSAS in ROLAP mode against Azure; he said he had and it worked, but it was v. slow (as you’d expect).
• Using SSAS in MOLAP mode, since processing queries are v. slow and there’s a query timeout of 5 mins, you need to create lots of small partitions  to ensure processing queries finish as quickly as possible. Proactive caching can’t use automatic notifications.
• SSIS out of the box support coming in R2. At the moment, SSIS doesn’t support bulk insert operations to ADO.Net destinations. 

I’m currently in John McAllister’s session on Master Data Services at the PASS Summit, and here are some notes…

• The first public beta is due next week
• MDS is will be packaged with 2008 R2 (rather than Sharepoint), be on the DVD but not part of the main install
• Will have an API – everything you can do in the UI, you can do in the API
• Although it’s part of SQL Server it will still rely on Sharepoint for workflow; the main, web-based UI is not Sharepoint-based though
• Has simple business rule validation capabilities, eg make sure that the list price of a product is greater than its cost
• Includes basic documentation features
• Also has auditing features – you can see every transaction ever made in the system, reverse changes made and so on
• Models are containers for different types of data (products, customers); every model can have a version, and versions can be locked, open for editing etc; models can also be secured
• Also has basic notification features, so users/groups can get emails when something changes
• No direct SSAS integration at the moment, but they hope to have some in the future

I’m currently waiting to board my flight to Seattle for this year’s PASS Summit, so I thought I’d give a quick plug for my session “Designing Effective Aggregations in Analysis Services 2008”, just after lunch on Wednesday. Unfortunately I’ve been scheduled against several other interesting SSAS sessions, so all I can say is go to mine and watch the videos of the others later!

I’ll also be hosting a table on ‘Performance Tuning MDX’ at the Birds of a Feather Lunch on Tuesday, so if you’d like to come and have a chat then please join me. And if you see me wandering around anywhere, however stressed/tired/drunk I might look, by all means say hello…!

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…

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.

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!

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…

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.

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!

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…

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?

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%:

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.

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.