Make the data count in your data warehouse
To get the most accurate information from its business intelligence efforts, IT must develop best practices for managing its repository.
In Summary:
| • | Work with the business decision makers to understand which metrics are most important to them. |
| • | When it comes to a data warehouse, start small and think big. |
| • | Data consistency enforces data integrity and a single version of the truth. |
The first thing you might have to do when you advocate for a data warehouse in your company is figure out how to counter the look of horror on the boss's face. "When you talk about data warehouses, the first thing people think of is throwing millions of dollars down the drain," says David Loshin, president of Knowledge Integrity, a Silver Spring, Maryland-based consulting firm that specializes in business intelligence.
 | The absolute wrong way to create a data warehouse is for the IT people to build it and then show the business side what they have. |  | | Caron Mooney
Director,
IS Partners | |
|
Perhaps it's time to present a more positive view of the technology.
"A data warehouse is nothing more than a method of consolidating different information in order to learn something that you couldn't have derived from the data sources in isolation," says Loshin. Unfortunately, he believes, companies too often focus on the technology aspects of the data warehouse rather than on the sort of information it should contain.
IT managers must insist on clear insight from business decision makers on their reporting objectives and the information they need to achieve those objectives before they embark on a data warehouse project. Beyond that, IT must ask the business what it needs to measure and then do whatever it can to ensure data consistency.
Put the business in the driver's seat
BI solutions pull data from a variety of sources for analysis. Those departments might not have a relationship to each other, but they all have a relationship with IT. The IT department has to take a leadership role in data warehousing because it holds the overarching view of all the information in the organization. However, proceed with caution.
"The absolute wrong way to create a data warehouse is for the IT people to build it and then show the business side what they have," says Caron Mooney, director of Johannesburg, South Africa-based IS Partners, a Microsoft Gold Certified Partner specializing in business intelligence.
Interviewing executives and users, she says, helps IT assess both frustrations and needs. The next step is to figure out how much of that information already exists and how much it will cost to collect what doesn't . "Don't say to the managing director or general manager that you don't have the information," insists Mooney. "If you do, you shut down all sorts of exciting opportunities for BI before you've got off the ground."
Instead, strive to do more than just deliver data. Give the business side information that is easily accessible and usable. (One way to do that is to use the familiar Microsoft Excel application as the front end for the BI capabilities of Microsoft SQL Server; for more on SQL Server, see the sidebar.)
As well, aggregate the data into a hierarchy that reflects the way you need to ask questions, advises Dan Hooper, vice president of sales and marketing at Dallas-based Integrated Services Inc., a Microsoft Gold Certified Partner. "That way you can drill down from a consolidated view to a granular level," he says.
Start small, think big
The concept of taking small, incremental steps toward a data warehouse and then expanding your vision makes sense for midsize companies. "Pick a single area that will give you clear short-term business benefits," Mooney says. "Make sure it's one area which you have data to analyze." For instance, you might want to analyze your customer rewards program or how different models of your product fare in different markets.
Then you need to take a holistic look at the data. "Define what you mean by a customer on a company-wide basis," she says. You need consistency in how sales, marketing, manufacturing, and customer support all refer to customers, so that information from those departments' systems can be aggregated easily and clearly.
Experts can't stress this effort enough. "Without a doubt, data integrity is critical," Hooper says. "That's where BI efforts tend to fail. If there is still more than one version of the truth, and no one trusts the data, you've failed."
How SQL Server 2005 eases common BI woes
The growing adoption of business intelligence tools among midsize companies derives from one factor: economics. "Prices have been coming down," says Dan Hooper, vice-president of sales and marketing for Dallas-based Microsoft Gold Certified Partner Integrated Services Inc. "What would have cost you US$2,000 per seat two years ago is now available for between $600 and $1,200 per seat today." He also describes Microsoft's integration of BI tools in SQL Server 2005 as a way for midsize companies to get BI capabilities for as little as $100 per seat.
SQL Server offers five core capabilities for companies that want to aggregate information from a variety of databases.
| • | Integration: You can easily integrate data from a wide range of operational and enterprise data sources (such as enterprise resource planning [ERP] and customer relationship management [CRM] systems), and gain competitive advantage through a holistic view of your business. |
| • | Analysis: With an integrated view of all your business data as the foundation, you can perform traditional reporting, online analytical processing (OLAP) analysis, scorecards, and data mining. |
| • | Reporting: Deliver the information employees need to make better business decisions using familiar tools such as Microsoft Office Excel for access. |
| • | Data mining: Explore data, discover patterns, and apply them to business operations. |
| • | Data warehousing: You can leverage enhancements in partitioning, manageability, and query optimizations to streamline operations and increase performance. |
SQL Server 2005 also includes an integrated development environment that specifically addresses the needs of the BI developer. Built on Visual Studio 2005, Business Intelligence Development Studio offers debugging, source control, and script and code development for all components of the BI platform.
 | Silicon Valley-based freelancer Howard Baldwin writes regularly for the Microsoft Midsize Business Center. His work has also appeared on AllBusiness.com and in CIO. |
