Just What Are Cubes = Anyway? (A Painless Introduction to OLAP Technology)

Microsoft Excel 2002 Technical Art= icles

Just What Are Cubes Anyway? (A Painless Introduction = to OLAP=20 Technology)

Carl Dubler and Colin Wilcox
Microsoft Corporation

April 2002

Applies to:
Microsoft=C2=AE Excel 2002

Summary: This quick and easy introduction to OLAP databases = shows you=20 the differences between traditional Online Transaction Processing (OLTP) = databases and Online Analytic Processing (OLAP) databases and how to = access OLAP=20 databases from your Office solutions. (8 printed pages)

Understanding=20 OLTP Databases
Understanding=20 OLAP Databases
Putting=20 the Databases Together
Understanding=20 Data Cubes
Building=20 Your Own Cubes

Chances are your business uses at least one database, and probably = more. The=20 databases store information about business transactions, plus other data = such as=20 employee records. Those types of systems are called online transaction=20 processing (OLTP) databases.

You may not know it, but your OLTP data contains a wealth of = information that=20 can help you make informed decisions about your business. For example, = you can=20 calculate your net profits for last quarter and compare them with the = same=20 quarter of the previous year. It can also provide other types of = valuable=20 information such as which employees are the most and least productive, = and the=20 optimum levels of goods to keep in stock.

The process of analyzing your data for that type of information, and = the data=20 that results, are collectively called business = intelligence.=20 Typically, business intelligence tries to answer the following types of=20 questions:

What were the total sales of all products last year?=20
How does our profitability for the first quarter of this year = compare to=20 the same time period during the past five years?=20
How much money did customers over age 35 spend last year, and how = has that=20 behavior changed over time?

However, you can spend a lot of time and money trying to extract = business=20 intelligence information from your database. Some organizations use a = small army=20 of data professionals and perhaps a dozen different software packages to = produce=20 simple reports. Also, if the report doesn't have the proper information, = its=20 creators have to start over.

The time and expense involved in retrieving answers from databases = means that=20 a lot of business intelligence information often goes unused. The = reason: most=20 operational databases are designed to store your data, not to help you = analyze=20 it. The solution: an online analytical processing (OLAP) database, a = specialized=20 database designed to help you extract business intelligence information = from=20 your data.

The following sections provide an overview of both types of = databases. From=20 there, we will look at OLAP databases in more detail.

Understanding OLTP=20 Databases

Most businesses use (OLTP) databases to gather and store the records=20 generated by their daily operations. Typically, (OLTP) databases execute = transactions, meaning that they add, update, or delete groups of = records=20 at the same time. For example, the database for a grocery store inserts = and=20 updates information about prices, purchases, and costs of goods and = freight, and=20 it usually does so at lightning speed. After all, you don't want your = customers=20 to wait in line while your inventory system updates its stock and = pricing=20 tables.

However, the design that allows OLTP databases to record transactions = quickly=20 and accurately also makes it hard to analyze their data for several = reasons.=20 First, OLTP databases contain a large number of tables, sometimes = hundreds.=20 Those tables often have multiple relationships with other tables in the=20 database. That complexity can make it hard to understand the database = and know=20 where to look for data.

The following figure depicts some of the tables and relations that = exist in=20 the Northwind sample database provided by Microsoft=C2=AE SQL = Server=E2=84=A2 2000:

3D""=20

Figure 1. Part of the Northwind database = schema

Second, if you try to extract (OLAP) data from an OLTP database, you = usually=20 need to create and run stored procedures=E2=80=94groups of SQL = statements compiled into=20 a single execution plan. Stored procedures can take hours to run, and = they can=20 slow the down the production database, something you don't want to do = with a=20 live system. (Remember the whole "customers waiting in line" thing? You = don't=20 want that to happen.)

Third, during normal operations, OLTP databases constantly update = their data.=20 Trying to analyze changing data is, well, like trying to analyze = changing data.=20 You will always have a hard time obtaining an accurate result, assuming = you can=20 obtain one at all.

Finally, OLTP databases usually store individual records. For = example:

On April 2, John Smith bought a case of = apples from=20 Jane Doe for $5.00.

That type of storage poses a problem for analysts because they use = summarized=20 data=E2=80=94totals and subtotals=E2=80=94to help answer business = intelligence questions.=20 Individual records don't help them at all.

In other words, you need a system that extracts data from your OLTP = database,=20 aggregates it into totals and subtotals, and then displays the resulting = data in=20 a way that allows you to spot past successes and failures, and to = identify=20 potential future successes and failures.

The solution to that problem is called an Online Analytical = Processing (OLAP)=20 database.

Understanding OLAP=20 Databases

OLAP and OLTP databases differ in several respects. First, IT = departments=20 usually keep OLAP databases isolated from OLTP databases. Doing so = ensures that=20 the transaction database performs well, and that the OLAP database only = receives=20 historical business data. In addition, while the data in an OLTP = database=20 constantly changes, the data in an OLAP system never changes. = Users never=20 perform data-entry or editing tasks on OLAP data. All they can do is run = mathematical operations against the data.

Second, OLAP databases use fewer tables and a different type of = schema. For=20 example, an OLAP database typically uses between five and 20 tables. In=20 addition, they usually keep the number of joins to a minimum by = arranging tables=20 a star schema. The following figure depicts how part of the Northwind = database=20 could look when converted to a star schema:

3D""=20

Figure 2. A star schema

The central table in the schema is the fact table. Fact tables = contain=20 numeric data, such as zip codes, and additive data such as the total = costs of=20 freight for all beverages.

By themselves, numeric facts do not have much meaning. For instance, = the=20 number 206 by itself does not mean much. However, it takes on more = meaning if=20 you know that it represents an area code or the number of dishwashers = sold=20 yesterday. In a star schema, dimension tables contain the descriptive = text that=20 gives meaning to the numbers. Keep in mind that most analyses involve = time,=20 which makes time itself a key dimension.

The facts in a dimension are called members. By design, OLAP = databases=20 group the related facts in a member into hierarchies whenever the = underlying data supports that type of structure. For example, the Time = dimension=20 in the preceding figure contains the following hierarchy:

Year=20
Quarter=20
Month=20
Order Date

Hierarchies use traditional parent/child relationships. For instance, = Quarter=20 is a child of Year, Month is a child of Quarter, and so on. If a child = contains=20 data that your OLAP system can aggregate, its parent level contains = those=20 aggregated sums. Some systems call those aggregated sums rollups. = Whenever you drill up or down through your data, you navigate through = those=20 hierarchies.

The joins between the dimension and fact tables allow you to browse = through=20 the facts across any number of dimensions, as well as up and down any = number of=20 hierarchies. For example, you might query for:

Total sales and total costs for all = beverages=20 purchased in 1999 by customers in Colorado.

=E2=80=94or=E2=80=94

Total sales and total costs for beer = purchased in=20 2000 by customers in Colorado.

The second query, of course, takes data from different hierarchy = levels in=20 the Time and Product dimensions.

The simple design of the star schema makes it easier to write = queries, and=20 they run faster. For example, running the total sales and costs query = against an=20 OLTP database could involve dozens of tables, making query design = complicated.=20 In addition, the resulting query could take hours to run.

Third, OLAP databases make heavy use of indexes because they help = find=20 records in less time. In contrast, OLTP databases avoid them because = they=20 lengthen the process of inserting data.

Putting = the Databases=20 Together

Now that you have an OLAP server and a star schema, you're ready to = go,=20 right? Well, no. Remember, IT departments deliberately isolate OLAP and = OLTP=20 databases. You need a way to move the data to the OLAP database, combine = that=20 data into useful aggregations, and then populate the tables. That = process is=20 often called Extract, Transform, and Load (ETL). SQL Server has a = built-in=20 utility called Data Transformation Services (DTS) that performs the ETL=20 tasks.

You typically use DTS to populate your OLAP schemas, and then = automatically=20 update your data. The update interval depends on your business, and the = types of=20 answers you want from your data.

OK, now you're ready to produce killer reports, right? Sorry! Even = though=20 they use a simplified data structure, star schemas are sometimes too = complicated=20 for some analysts to understand. In addition, OLAP databases can contain = the=20 same type of information found in your OLTP databases:

On April 2, John Smith bought a case of = apples from=20 Jane Doe for $5.00.

In other words, you still don't have the aggregated data you need to = answer=20 your questions. Can you ever win?

Understanding Data=20 Cubes

Data cubes provide the final piece of the puzzle. A cube aggregates = the facts=20 in each level of each dimension in a given OLAP schema. The business=20 intelligence industry uses the word "cube" because it best describes the = resulting data. For example, let's consider our star schema. When you = create a=20 cube from that schema, you take the freight, quantity, discount, and = other facts=20 and add them up by city, by year, by city and year, and by every other = possible=20 combination of dimension and hierarchy level. Those calculations produce = the=20 following type of data structure:

3D""=20

Figure 3. In other words, a cube

Note Data = cubes are=20 not "cubes" in the strictly mathematical sense because they do not = have equal=20 sides. However, virtually all analysts use the term, and it is an = industry=20 standard.

Here is where things get really exciting. Because the cube contains = all of=20 your data in an aggregated form, it seems to know the answers in = advance. For=20 example, if a user asks for total sales by year and city, those numbers = are=20 already available. If the user asks for total sales by quarter, = category, zip=20 code, and employee, those numbers and names are already available. If it = helps=20 you to understand them, think of cubes as specialized small databases = that know=20 the answers before you even ask the questions.

That is the big advantage of a cube. You can ask any pertinent = question and=20 get an answer, usually at warp speed. For instance, the largest cube in = the=20 world is currently 1.4 terabytes and its average response time to any = query is=20 1.2 seconds! In addition, you can view cube data with any valid tool, = including=20 spreadsheets, Web pages, the Cube Browser in Analysis Services 2000, or = graphic=20 data browsers such as Microsoft Data Analyzer.

Building = Your Own=20 Cubes

You can use a variety of tools to build cubes, including Microsoft = Excel,=20 Analysis Services 2000 (which comes with Microsoft SQL Server 2000), or = OLAP=20 Services 7.0, the predecessor of Analysis Services.

The steps in this section explain how to use Excel to create a = connection to=20 a data source and build a cube. The steps create a local cube using data = from=20 the FoodMart 2000 Microsoft Access database (.mdb), which comes with SQL = Server=20 2000 with Analysis Services.

The process of creating a cube takes place in several discrete = phases:

Chose a data source=20
Create the query that extracts data from the database=20
Create the cube from the extracted data

To select a data source

Open Excel 2002.=20
On the Data menu, point to Import External Data, and = then=20 click New Database Query.=20
In the Choose Data Source dialog box, click the = Databases=20 tab, select New Data Source and then click OK.=20
In the Create New Data Source dialog box, enter a name for = the data=20 source in the first text box, and then select a driver for the data = source=20 from the list, and then click Connect.=20
In the ODBC Microsoft Access Setup dialog box, click = Select.=20
In the Select Database dialog box, navigate to the foodmart = 2000.mdb file, and then click OK.=20
Note By=20 default, SQL Server places the foodmart 2000.mdb file at C:\Program=20 Files\Microsoft Analysis Services\Samples\foodmart = 2000.mdb.
Click OK twice more to return to the Choose Data = Source=20 dialog box.

To create the query

In the Choose Data Source dialog box, select the data = source you=20 created in the previous set of steps. Make sure Use the Query = Wizard to=20 create/edit queries is selected and then click OK.=20

In the Query Wizard=E2=80=94Choose Columns dialog = box, select the=20 columns of data you want in your cube. Typically, you include the data = from at=20 least one fact table to provide measures for your cube, and data from = one or=20 more dimension tables, including a time dimension. In the Available = tables=20 and columns list, click the plus sign (+) to expand the = table,=20 select a column, and then click the 'greater than' angle bracket = (>)=20 to move the column into the Columns pane in your query list = box. For=20 this example, select the following tables and columns:

Table	Columns
sales_fact_1998	store_sales, store_cost, unit_sales
time_by_day	the_date
Product	brand_name
Product_class	product_category, product_subcategory
customer	country, state_province, city, lname
Store	store_country, store_state, store_city,=20 store_name

Click Next and then click Next in the next two = screens.=20
In the Query Wizard=E2=80=94Finish screen, select = Create an OLAP=20 Cube from this query and click Finish. This launches the = OLAP=20 Cube Wizard, and you use the wizard to build your cube.

To create the cube

Click Next in the Welcome to the OLAP Cube Wizard = screen. In=20 step 1 of the wizard, select store_sales, store_cost, = and=20 unit_sales in the Source field column. In the = Summarize=20 by column, select Sum for each field, and then click = Next.=20
In step 2 of the wizard, drag the_date from the = Source=20 fields box to the Dimensions box. Right-click = the_date,=20 click Rename, and enter Time as the name of the = dimension. Clear=20 the Week and Day check boxes under the dimension name.=20
Drag product_category, product_subcategory, and=20 brand_name so that they appear in that order, in the next = available=20 dimension. Rename the dimension to Product.=20
Drag country, state_province, city, and = lname=20 so that they appear in that order, in the next available dimension. = Rename the=20 dimension to Customer.=20
Drag store_country, store_state, store_city, = and=20 store_name so that they appear in that order in the next = dimension.=20 Rename the dimension to Store. Click Next.=20
Select the option that best fits the type of cube you want to = create. For=20 our example, select Save a cube file containing all data for the = cube.=20 Enter a path and filename for the cube, and then click Finish.=20
In the Save As dialog box, enter a filename for the query=20 definition that you just created and click Save. Saving the = query=20 definition allows you to reuse it later. The file is saved with an = .iqy=20 filename extension.=20
The Cube Wizard creates the cube file. This may take several = minutes. Once=20 the Cube Wizard creates the cube, the PivotTable and PivotChart=20 Wizard=E2=80=94Step 3 of 3 screen appears. Use the screen = to create a=20 Microsoft PivotTable=C2=AE report from the data in the cube you just = created. Use=20 the Options and Layout buttons to configure the report, = or click=20 Cancel to exit the wizard.

Finally, stay tuned. We will have a lot more to say about business=20 intelligence and OLAP in future articles.

Carl Dubler is a Technical Specialist in the Microsoft Denver, = Colorado=20 office. He specializes in helping customers understand Microsoft = database and=20 analysis technologies.

Colin Wilcox is a Technical Writer for the Microsoft Office team. He=20 specializes in helping end users understand Data Analyzer and related = computer=20 technologies.

Contents

Understanding OLTP=20 Databases

Understanding OLAP=20 Databases

Putting = the Databases=20 Together

Understanding Data=20 Cubes

Building = Your Own=20 Cubes