Tài liệu Microsoft SQL Server 2000 Data Transformation Services- P3 pdf

Multidimensional Database Management Systems (OLAP)

You can create a multidimensional database schema in a relational database system. There are

also database systems that are specifically designed to hold multidimensional data. These sys￾tems are typically called OLAP servers. Microsoft Analysis Server is an example of an OLAP

server.

The primary unit of data storage in a relational database system is a two-dimensional table. In

an OLAP system, the primary unit of storage is a multidimensional cube. Each cell of a cube

holds the data for the intersection of a particular value for each of the cube’s dimensions.

The actual data storage for an OLAP system can be in a relational database system. Microsoft

Analysis Services gives three data storage options:

• MOLAP—Multidimensional OLAP. Data and calculated aggregations stored in a multi￾dimensional format.

• ROLAP—Relational OLAP. Data and calculated aggregations stored in a relational data￾base.

• HOLAP—Hybrid OLAP. Data stored in a relational database and calculated aggregations

stored in multidimensional format.

Conclusion

The importance of data transformation will continue to grow in the coming years as the useful￾ness of data becomes more apparent. DTS is a powerful and flexible tool for meeting your data

transformation needs.

The next chapter, “Using DTS to Move Data into a Data Mart,” describes the particular chal￾lenge of transforming relational data into a multidimensional structure for business analysis

and OLAP. The rest of the book gives you the details of how to use DTS.

Getting Started with DTS

PART I

76

05 0672320118 CH03 11/13/00 4:57 PM Page 76

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

CHAPTER

4

Using DTS to Move Data

into a Data Mart

IN THIS CHAPTER

• Multidimensional Data Modeling 78

• The Fact Table 82

• The Dimension Tables 84

• Loading the Star Schema 88

• Avoiding Updates to Dimension Tables 94

06 0672320118 CH04 11/13/00 5:03 PM Page 77

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Getting Started with DTS

PART I

78

With the introduction of OLAP Services in SQL Server 7.0, Microsoft brought OLAP tools to

a mass audience. This process continued in SQL Server 2000 with the upgraded OLAP func￾tionality and the new data mining tools in Analysis Services.

One of the most important uses for DTS is to prepare data to be used for OLAP and data

mining.

It’s easy to open the Analysis Manager and make a cube from FoodMart 2000, the sample

database that is installed with Analysis Services. It’s easy because FoodMart has a star schema

design, the logical structure for OLAP.

It’s a lot harder when you have to use the Analysis Manager with data from a typical normal￾ized database. The tables in a relational database present data in a two-dimensional view. These

two-dimensional structures must be transformed into multidimensional structures. The star

schema is the logical tool to use for this task.

The goal of this chapter is to give you an introduction to multidimensional modeling so that

you can use DTS to get your data ready for OLAP and data mining.

A full treatment of multidimensional data modeling is beyond the scope of this book.

Most of what I wrote about the topic in Microsoft OLAP Unleashed (Sams, 1999) is

still relevant. I also recommend The Data Warehouse Lifecycle Toolkit by Ralph

Kimball, Laura Reeves, Margy Ross, and Warren Thornthwaite.

NOTE

Multidimensional Data Modeling

The star schema receives its name from its appearance. It has several tables radiating out from

a central core table, as shown in Figure 4.1.

The fact table is at the core of the star schema. This table stores the actual data that is analyzed

in OLAP. Here are the kinds of facts you could put in a fact table:

• The total number of items sold

• The dollar amount of the sale

• The profit on the item sold

• The number of times a user clicked on an Internet ad

• The length of time it took to return a record from the database

• The number of minutes taken for an activity

06 0672320118 CH04 11/13/00 5:03 PM Page 78

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

• The account balance

• The number of days the item was on the shelf

• The number of units produced

Using DTS to Move Data into a Data Mart

CHAPTER 4

4

USING DTS TO

MOVE

DATA INTO

A

DATA

MART

79

FIGURE 4.1

The star schema of the Sales cube from the Food Mart 2000 sample database, as shown in the Analysis Manager’s

Cube Editor.

The tables at the points of the star are called dimension tables. These tables provide all the dif￾ferent perspectives from which the facts are going to be viewed. Each dimension table will

become one or more dimensions in the OLAP cube. Here are some possible dimension tables:

• Time

• Product

• Supplier

• Store Location

• Customer Identity

• Customer Age

• Customer Location

• Customer Demographic

06 0672320118 CH04 11/13/00 5:03 PM Page 79

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

• Household Identity

• Promotion

• Status

• Employee

Differences Between Relational Modeling and

Multidimensional Modeling

There are several differences between data modeling as it’s normally applied in relational data￾bases and the special multidimensional data modeling that prepares data for OLAP analysis.

Figure 4.2 shows a database diagram of the sample Northwind database, which has a typical

relational normalized schema.

Getting Started with DTS

PART I

80

FIGURE 4.2

A typical relational normalized schema—the Northwind sample database.

Figure 4.3 shows a diagram of a database that has a star schema. This star schema database

was created by reorganizing the Northwind database. Both databases contain the same infor￾mation.

06 0672320118 CH04 11/13/00 5:03 PM Page 80

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.