Where to look next for BI applications? Try the cloud
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
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
Dig Deeper
-
People who read this also read...
This was first published in October 2010
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, Data.gov 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 MyWebLink.com 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.
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation