Live Blogging @PASS – SSAS Consolidation and Virtualisation

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.

Quest add support for SSAS monitoring

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.

Live Blogging @PASS – SQL Server BI in the Cloud

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. 

Live Blogging @PASS – Master Data Services

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

PASS Summit 2009

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…!

“SQLBits 2009” written in Task Manager

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…

BI Survey 9 – Invitation to Participate

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.

Actions and Multiselect

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!

Access 2010 and Access Services

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 now PowerPivot, and other news

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.