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. 

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.