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.
