Archive for November 2009
Something I saw yesterday at PASS: Quest now have support for monitoring SSAS from their “Spotlight on SQL Server Enterprise” product. See
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…!