Microsoft SQL Server 2000 Data Transformation Services- P3

Microsoft SQL Server 2000 Data Transformation Services- P3: Data Transformation Services in Microsoft SQL Server 2000 is a powerful tool for movingdata. It’s easy to use, especially when you’re creating transformation packages with the DTSWizard. But it’s also a flexible tool that you can customize to gain a high degree of controlover the transformation of your data.
Nội dung trích xuất từ tài liệu:
Microsoft SQL Server 2000 Data Transformation Services- P3 Getting Started with DTS76 PART I 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.Using DTS to Move Data CHAPTER 4into 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 Getting Started with DTS78 PART I 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. NOTE 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. 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 Using DTS to Move Data into a Data Mart 79 CHAPTER 4 • The account balance • The number of days the item was on the shelf • The number of units producedFIGURE 4.1The star schema of the Sales cube from the Food Mart 2000 sample database, as shown in the Analysis Manager’sCube Editor.The tables at the poin ...