by Don Jones

Lots of big words! Actually, in the past 4 or 5 years, so many related terms have been thrown together that it’s tough to sort them out. Microsoft started offering “data warehousing” for SQL Server back in v7, but then switched to “BI features” (BI is insider-speak for business intelligence) and now offers SQL Server Analysis Services. So what’s what?

Properly defined, BI is a set of skills, technologies, and practices that a business uses to gain better understanding of itself and to make better decisions about its future. Data warehousing and analysis are components of business intelligence, but so are more mundane practices such as budgeting and planning. The real goal of BI is to provide methods to analyze historical data in a variety of new ways, allow business leaders to propose scenarios, and then predict how those scenarios will change the data trends in the future. This process helps business leaders make more informed guesses about how specific decisions will play out in the future and to select decisions that will have the best positive impact.

A data warehouse is often a component of a BI effort. Data warehouses are a specialized form of database: A normal database, often called an Online Transaction Processing (OLTP) database is typically used for real-time transactions. Imagine an order-entry application in which orders are constantly being entered, recalled, modified, and so forth. These databases’ structures are tuned to their task, meaning they are optimized for fast retrieval of individual pieces of data, fast acceptance of new pieces of data, and so forth. When it comes to reporting, however, these databases’ structures are often not the ideal form. Pulling a report that helps a manager understand long-term order volume trends correlated with sales and special promotions, for example, can be very time-consuming. A data warehouse—also called an Online Analytical Processing (OLAP) database—copies data from an OLTP database and transforms it into a structure better-suited for that type of reporting.

A data warehouse is often created through an ETL process: Extracting the data from an OLTP database, Transforming the data into an OLAP structure, and Loading the data into a data warehouse. In fact, most data warehouses extract data from multiple other databases; they might pull data from an order-entry database but might also pull data from financial systems, marketing databases, and so forth. Data warehouses often consume enormous amounts of storage space as a result, and may take hours to populate with data.

Designing a data warehouse takes considerable effort, because the design needs to accommodate the type of questions that the data warehouse is meant to help answer. One of the simplest data warehouse structures is the star schema. Consider an OLTP database that tracks retail sales by date, product sold, and store. Moving this into a star schema would involve four database tables: One table apiece listing all the dates, another listing all the products, and another listing all the stores. A fourth “fact table” would combine these three so that each row in the fact table represented the sale of a single product from a single store on a single date. This structure makes it easier to look at sales across the entire company, find popular products, determine popular dates for product sales, and so forth. Reports can be created more quickly from this type of schema, although this schema doesn’t lend itself at all well to continual updates. Data warehouses are never used for “live,” real-time transactions, and most data warehouses are considered read only apart from loading new or updated data from an OLTP database.

Business analytics is the practice of analyzing past data—often from a data warehouse—to drive future actions. For example, most casinos issue player cards to slot machine players and encourage players to insert their cards into slot machines during play. Players accumulate points that can be redeemed for various prizes or “comps,” but the casino accumulates valuable data: when the player plays, how long they play, how much they gamble, how long they sit between spins, and so forth. Business analytics helps extract interesting information from all that data so that casinos can offer room or meal discounts and other promotions. The casino tries to offer different promotions to different groups of players so that each distinct subset of players gets a promotion they are more likely to take advantage of—getting them back into the house for more play time. Casinos add promotion response to the data mix so that they can learn which promotions are more effective at getting players to return.

Taken together, these various techniques all form BI: accumulating data, arranging it in a way that makes it easier to query and develop into information, taking actions based upon that information, and then feeding the results of those actions back into the data.

About the Author

Don Jones has more than a decade of professional experience in the IT industry. He's the author of more than 30 IT books, including Windows PowerShell: TFM; VBScript, WMI, and ADSI Unleashed; Managing Windows with VBScript and WMI; and many more. He's a top-rated and in-demand speaker at conferences such as Microsoft TechEd and TechMentor, and writes the monthly Windows PowerShell column for Microsoft TechNet Magazine. Don is a multiple-year recipient of Microsoft's "Most Valuable Professional" (MVP) Award with a specialization in Windows PowerShell. Don's broad IT experience includes work in the financial, telecommunications, software, manufacturing, consulting, training, and retail industries and he's one of the rare IT professionals who can not only "cross the line" between administration and software development, but also between IT workers and IT management. Don is a co-founder of Concentrated Technologies, and serves as author and series editor for Realtime Publishers.