Business Intelligence: Built-in BI

  • Article

Business intelligence tools can indeed help you make the right business decisions, but you already have a lot of BI firepower in a familiar application.

Brien Posey

Business intelligence (BI) is one of those corporate buzzwords that means different things to different people. One definition of BI that seems to make sense is the process of organizing and analyzing existing data in a way that helps you make better-informed business decisions.

With that in mind, true BI has long been something of a pipe dream. Until recently, it was all but impossible for the average user to pull data from multiple sources and analyze that data in a meaningful way. True BI has traditionally involved expensive applications that use complex queries to display data in elaborate dashboard views.

Over the last few years, however, Microsoft has been trying to make BI more accessible to the mainstream. In fact, there are numerous BI features built-in to the latest versions of SQL Server, SharePoint and Microsoft Office. Microsoft Excel is found on almost every user’s desktop, and it’s one of the primary tools you can use to easily analyze data.

One Version of the Truth

If BI is all about data analysis, then it stands to reason that the information presented by the BI software will only be as reliable as the data analyzed. Unfortunately, many organizations have a problem with inconsistent data, although they may not even be aware of the problem.

Many companies use multiple databases, each with its own copy of customer contact information—and some of which may be out of date. Any time that redundant data resides in multiple standalone (non-replicated) locations, there’s a good chance the data will eventually become inconsistent. Those inaccuracies may not even be limited to a single database. There may be inaccurate data sprinkled throughout an organization’s data sources.

So how do you manage this problem? Before an organization can make reliable use of BI, it must ensure data accuracy. The best way to accomplish this is to have a “single version of the truth.” In other words, it’s best to use an authoritative data source for all of your BI analysis.

One way to accomplish this is with SQL Server 2008 R2 Master Data Services. Using these services, you can designate certain data as master data. Master data usually consists of relatively static data previously stored in multiple sources. Rather than maintaining separate copies of the same data in multiple databases, all database-driven applications pull data from the single master source. For example, rather than having a separate copy of customer contact information in each database, applications that use customer information could be configured to pull the information from that one authoritative master source.

BI with Excel

Many business users already use Excel as their main tool for analyzing data. At first, that may seem contrary to efforts to ensure data consistency. After all, if an organization’s data is scattered across hundreds of spreadsheets, there will most likely be inconsistencies.

There are, however, mechanisms for importing data from Excel into SQL databases, at which point some of the data that previously existed in Excel can be treated as master data if necessary. Just as importantly, you can populate an Excel spreadsheet with data from a SQL Server. That way, users can analyze up-to-date data from a single authoritative source.

Previously, linking an Excel spreadsheet to a SQL database was a big job. The process often involved building Visual Basic applications that would open the database, pull the required data and populate a spreadsheet. There are some good examples of such applications on the Microsoft Support page. Today you can pull SQL Server data into Excel without having to write any code. Excel 2010 has a built-in mechanism for connecting to a SQL database (see Figure 1).


Figure 1 Excel 2010 has native capabilities for importing SQL data

In order to pull data from SQL Server into Excel 2010, you have to know the name of the SQL Server, the username and the password for the SQL Server instance (if required). You also have to know the name of the database containing the required data, as well as the specific tables where the data is stored, to properly configure that connection. There’s a specific checkbox that ensures the spreadsheet is automatically kept up-to-date with the latest SQL data. This keeps it from becoming a static copy.

Consequently, your business users probably won’t be able to set up the Excel/SQL Server linkage by themselves. Although they may lack the knowledge or security credentials to create a spreadsheet that interfaces directly with SQL Server data, it’s important to remember that the end goal is to provide BI. They’ll need that link, so the best course of action is to build a spreadsheet or a spreadsheet template your users will have for long-term data analysis.

Naturally, Excel has its limits with BI. It’s limited to a million rows of data; databases occasionally contain much more. Thankfully, Microsoft offers a free add-on for Excel to boost its BI capabilities. PowerPivot lets Excel analyze hundreds of millions of rows of data from numerous disparate data sources.

Excel BI Tools

Now that you’re better equipped to ensure data consistency, let’s look into a few of the Excel tools for analyzing data and providing true BI:

Sparklines

One of the more notable BI features in Excel is Sparklines. These are essentially Excel charts that fit into a single cell on a spreadsheet. Sparklines are handy because they can convey a lot of important information in a small amount of space (see Figure 2).

Figure 2 shows an Excel spreadsheet displaying current sales for various regions. By themselves, the sales numbers don’t really convey much information. However, check out the Sparklines just to the right of the sales figures. These reflect current sales trends, so you can see at a glance how the current sales numbers compare to previous months.


Figure 2 Sparklines are charts that fit within a single cell

You can create a Sparkline by clicking on the cell where you want to place it. Then go to the Insert tab and click on the type of Sparkline you want to create (Line, Column or Win/Loss). After that, simply tell Excel what data range to use and it will automatically create the Sparkline.

Slicers

PivotTables have always been one of the primary mechanisms for analyzing Excel data. As handy as those are, though, there’s no denying some users have a bit of trouble grasping the concept. Thankfully, Microsoft has introduced a new BI tool called a slicer that’s designed to make PivotTable data easier to follow. Slicers are essentially a point-and-click interface for PivotTables.

There are several ways to determine who’s been selling what to Tailspin Toys, but none as easy and informative as slicers (see Figure 3). There’s a PivotTable based on the sales data and two slicers. The slicers were created by selecting the PivotTable, then choosing the Slicers option from the Insert tab. One of the slicers is based on “Customer” data and the other on “Sold By” data.


Figure 3 Slicers make it easy to analyze business data

Clicking on Tailspin Toys within the Customer Slicer will reveal at a glance that Jane Doe and John Doe both made sales to Tailspin Toys. Anyone who didn’t make a sale (in this case Bob Smith) is grayed out. The PivotTable is displaying Jane Doe’s sales, but it would be just as easy to look at Jane and John’s sales collectively by selecting both names.

You could also view Bob’s collective sales by selecting Bob’s name and all of the customer names (see Figure 4). In this case, Tailspin Toys is grayed out because Bob did not make any sales to this customer.


Figure 4 Slicers can sort data based on a number of criteria

As you can see, slicers are powerful sorting tools that provide important business information at a glance. The best part is that it would only take about 10 seconds to set up the slicers shown in Figures 3 and 4, and yet they provide an easy way for interacting with business data in a meaningful way.

Having accurate, consistent data and properly analyzing that data is essential to ensuring effective BI. Your most convenient BI analysis tool may already be on your desktop.

Brien Posey

Brien Posey*, MVP, is a freelance technical author with thousands of articles and dozens of books to his credit. You can visit Posey’s Web site at brienposey.com.*

Related Content