Problem solve Get help with specific problems with your technologies, process and projects.

Where to look next for BI applications? Try the cloud

PowerPivot, SQL Azure and ‘Dallas’ combine to create BI applications in the cloud. Is that integration seamless and is it even for you?

In the last year, cloud computing has gained significant traction. Currently, there are two options for database and business intelligence (BI) applications like the PowerPivot product for accessing and consuming data in the Microsoft cloud -- SQL Azure and a new technology code-named “Dallas.” Each of these products has a particular sweet spot for deployment.

Here’s a look at how they can benefit you based on your line of business.

PowerPivot: BI applications for Excel Users

PowerPivot is a free add-on for Microsoft Excel 2010 that provides BI capabilities such as data viewing, reporting, and slicing and dicing from within Excel. Before releasing PowerPivot, Microsoft did an extensive research of BI users, learning which tools they were using and which ones they needed.

One of the main findings was that the vast majority of BI users didn’t have any tools, mainly because of their cost and complexity. PowerPivot allows BI users to leverage their knowledge of Excel to create BI applications. It puts the power of BI into the hands of millions of Excel users who probably wouldn’t have purchased third-party BI tools.

Excel lets DBAs quickly work with millions of rows of compressed data on the desktop and create visually rich applications using built-in graphs and slicers. For collaborative capabilities, admins can upload the PowerPivot application to SharePoint and give users access to them in a browser. (See Figure 1.)

Figure 1

SQL Azure and data in the cloud

With SQL Azure, Microsoft gives you tools to create tables, upload your data and query it from the new SQL Server Management Studio as well as from your own applications. Having Microsoft host your data is convenient and means that the company handles backups, high availability and scalability. It also gives you access to all of your data via the Internet. But like all new technologies, SQL Azure has its flaws.

SQL Azure needs more architectural and development work before it provides the same features you get when using your own SQL Servers. For example, SQL Azure throttles CPU and disk I/O if your application process uses them extensively. Therefore, it may not be suitable for some resource-intensive applications.

When it comes to BI, SQL Azure lacks a few features. On the plus side, you can import raw data from SQL Azure cloud into PowerPivot and analyze it there. You can also refresh the cloud data periodically, manually or automatically, if you uploaded your PowerPivot application to SharePoint. But there’s nothing special about data coming to PowerPivot from the cloud. Importing it works the same as importing from SQL Server, Oracle or any other supported database.

The biggest benefit is the ability to import and refresh data from SQL Server Azure in locations where you cannot connect to your company servers over a VPN. For example, suppose a sales rep is giving a presentation and wants to refresh PowerPivot to present the most recent data to a client. This usually means logging onto the company VPN, but even with Internet access, there’s no guarantee that the VPN will work. With SQL Azure, it will.

Pay-Per-View data with ‘Dallas’

A new option for BI and .NET applications is to access data stored in a cloud using a new Windows Azure technology Dallas, which is still in community technical preview (CTP) stage. Consider Dallas as a catalogue of various datasets that are accessible via paid subscription. Currently, you can get a free trial subscription of the application to evaluate the service with certain limitations, such as only having access to 100 rows in the result set.

Dallas has APIs that let you access cloud data from custom applications such as Web pages, through Web service calls or by logging onto the Dallas website. Dallas also integrates with PowerPivot. Once you set up your parameters and run the query to get data you’re interested in, you can launch PowerPivot and import the dataset using the familiar import table wizard. Once the data is in PowerPivot, you use it the same way, just as if it came from SQL Server or any other data source.

PowerPivot, SQL Server Reporting Services 2008 and Microsoft SharePoint capabilities can be combined with data from Microsoft’s data clouds -- SQL Azure or Dallas. This provides new opportunities for creating BI applications for personal use as well as possible resale.

Figure 2

Figure 2 shows a map in SQL Server Reporting Services 2008 that uses data from an AdventureWorks sample database. Importing the database content into PowerPivot in SharePoint would create the same map using PowerPivot as your data source. 

Several Microsoft partners provide datasets for Dallas, including the Associated Press with stories and news briefs, with crime data, and Infogroup, which serves up data about businesses throughout the U.S., Canada and the U.K. Data that Dallas takes in can be useful on many levels: Combine it with your own data in PowerPivot and create applications that combine reporting and analytics.

It will be extremely valuable for companies that specialize in data analytics as well as repackaging and reselling data because they can combine data from multiple sources, build a BI solution and sell the output as a PowerPivot application or make it available through a subscription-based website using SharePoint 2010 on the back end.

Mapping and BI in PowerPivot

The visualization components of PowerPivot and SQL Server 2010 Reporting Services provide even more value as well as new reporting options within BI applications that include either reports or PowerPivot apps. One of the most impressive features is the integration of Bing Maps with Microsoft’s BI. In SQL Server Reporting Services 2008 R2 you can use the map control to import a U.S. map and break down the data visually by regions or states.

A lot of the data that’s provided in the Dallas cloud comes with geographic breakdown, which can be especially useful for data originating from Dallas dataset subscriptions.

Integrating mapping capabilities takes just two steps.

1.    Import data from Dallas or SQL Azure into PowerPivot and upload the finished PowerPivot file into SharePoint.

2.    Create a report in Reporting Services and use the PowerPivot data in SharePoint as a data source for your report. Once the data source is defined, you can use map control and configure the map to display data according to region.

About the author:
Roman Rehak, is a senior database architect at in Colchester, Vt. He specializes in SQL Server development, database performance tuning, ADO.NET and writing database tools. He regularly contributes SQL Server articles to Visual Studio Magazine, SQL Server Magazine and other technical publications and presents at user groups and conferences in the U.S. and Canada. Roman is an active member and volunteer for the Professional Association for SQL Server and serves as Tech Chair for the SQL Server track at the annual DevTeach conferences in Canada. He is president of the Vermont SQL Server User Group.

Dig Deeper on SQL Server Business Intelligence Strategies