News Stay informed about the latest enterprise technology news and product updates.

Q&A: SQL Server business intelligence meets the cloud

Microsoft’s Mark Kromer offers his thoughts on the current state of cloud-based BI and what technologies like SQL Azure and PowerPivot can do for organizations.

In the first edition of our SQL in Five series, Microsoft data platform technology specialist Mark Kromer answers five questions on the state of cloud-based business intelligence. Read on for Mark’s thoughts on what “BI in the cloud” really means, the potential of SQL Azure and how Microsoft’s PowerPivot technology fits into the mix.

Gartner recently noted a general lack of understanding amongst IT professionals when it comes to “BI in the cloud.” How would you define it?

Mark Kromer Mark Kromer

Mark Kromer: Interestingly, Gartner’s definition states that only one portion of an analytical application needs to be in the cloud to be classified as “BI in the cloud.” To me, this means that the basic value proposition of BI is the same, which I typically define as allowing knowledge workers in your business access to knowledge from data that enables them to make better business decisions. The only difference is that perhaps it is the data warehouse, or a data source, or the analytical engine that is in the cloud.

A present state Microsoft example would be for an analyst in your business to open Excel 2010 with the PowerPivot add-in and connect to a cloud-based SQL Azure database. The analyst would then use the PowerPivot in-memory analysis services engine to perform mining and analytics on that data set from within Excel.

Where would you say the most potential lies in terms of the benefits of cloud-based BI? It has to be scalability right?

Kromer: When I read the question as “potential” for cloud-based BI, I think of the idea that Microsoft has spoken about for several years now, which is making BI pervasive and available to your entire organization through self-service BI.

In terms of scaling to a larger number of users -- yes, I agree. But in terms of back-end processing and data warehousing, I think there are still a number of performance concerns to address in the cloud. Most of the data sets that I can think of for cloud-based BI at this time would probably entail providing answers to business questions that are focused on departmental-sized data sets as opposed to multi-terabyte data warehouses and multi-terabyte hosted OLAP cubes.

Microsoft recently upped the database storage capacity for SQL Azure Business Edition from 10 GB to 50 GB. How important is this increase in regards to BI applications and data warehouses?

Kromer: Well 50 GB is still pretty small for a data warehouse. But in terms of using that as a data source for PowerPivot -- that is very important to enable cloud-based SQL Server for many more workloads. SQL Azure databases are not exact replicas of the SQL Server database engine, and it can be used with an on-premises version of SQL Server Integration Services. But from my perspective, 50 GB is a bit small for most data warehouses that I see today.

PowerPivot has SQL Azure functionality built-in, and Microsoft demoed some of those cloud capabilities at TechEd this year. What are some of the things folks can do with PowerPivot and Azure right now, and what are the benefits?

Kromer: Since SQL Azure does not currently facilitate SQL Server Analysis Services, using PowerPivot is a great option for analysis on a SQL Azure database. PowerPivot uses a local in-memory version of analysis services, so you can load up your Excel 2010 spreadsheet with data from Azure and run analyses and pivots right from Excel with PowerPivot.

My first attempt at SQL Azure was to create a small database that I used to build a sample ASP.NET webpage as my data source. Since you can connect to SQL Azure as a data source, that allows you programmatic access from technologies like ASP.NET as a SqlDataSource. There are a number of companies that I am talking to who are looking at database consolidation projects and are running trials with SQL Azure by migrating smaller database workflows over to SQL Azure databases to see if that will provide the cost savings they are looking for.

These features are being pegged as having business value right now, but cloud deployments have started slowly – particularly with smaller organizations. What are your thoughts on the current state of cloud computing in regards to BI and SQL Server in general?

Kromer: The adoption rates for cloud computing customers of Microsoft, Amazon, Google, and so on continue to grow. The rate of adoption that we have seen was very much the expectation, where smaller pockets of business workloads from teams that did not have dedicated IT staff and budget, were the vanguards in their companies. That resourcefulness demonstrated an agility and flexibility that CIOs and IT departments are taking notice of and are beginning to build business cases that take into account the cost savings of cloud computing, while factoring in the culture shift that comes from giving up complete data center control.

In terms of SQL Server and Microsoft BI, the SQL Azure product team at Microsoft is benefiting from large R&D investments. The cloud business model at Microsoft is going to continue to grow and cloud versions of other areas of the Microsoft data platform, including the BI components, will adopt more and more of the cloud computing paradigm.

Editor’s note: For more from Mark, check out his blog at MSSQLDUDE

Mark Kromer has over 16 years experience in IT and software engineering and is a leader in the business intelligence, data warehouse and database communities. Currently, he is the Microsoft data platform technology specialist for the mid-Atlantic region, having previously been the Microsoft Services senior product manager for BI solutions and the Oracle principal product manager for project performance analytics.

Dig Deeper on SQL Server Business Intelligence Strategies

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.