Welcome to Microsoft SQL Server 7.0 Data Warehousing Training kit. The book provides you with the technical skills required to implement a data warehouse with Microsoft SQL Server client/server database management system version 7.0 and Microsoft SQL Server OLAP Services. The content of this book provides a theoretical and practical discussion of data warehouse design and the important features that SQL Server provides for implementing a data warehouse.
NOTE
For more information on becoming a Microsoft Certified Systems Engineer, see the section titled "The Microsoft Certified Professional Program" later in this chapter.
Each chapter in this book is divided into lessons. Most lessons include hands-on exercises that allow you to practice or demonstrate a particular concept or skill. Each lesson ends with a short summary and each chapter has a set of review questions to test your knowledge of the chapter material.
The "Getting Started" section of this chapter provides important setup instructions that describe the hardware and software requirements to complete the exercises in this course. It also provides information about the networking configuration necessary to complete some of the hands-on exercises. Read through this section thoroughly before you start the lessons.
This book was developed for information system (IS) professionals who need to design, plan, implement, and support data warehouses using Microsoft SQL Server 7.0 and Microsoft SQL Server OLAP Services or who plan to take the related Microsoft Certified Professional exam 70-019: Designing and Implementing Data Warehouses with Microsoft SQL Server 7.0.
Experience using the Microsoft Windows NT Server network operating system:
You should also have passed the Microsoft Certified Professional Exam 70-028 (Administering Microsoft SQL Server 7.0) and the Microsoft Certified Professional Exam 70-029 (Designing and Implementing Databases with Microsoft SQL Server 7.0) or have mastered the Microsoft SQL Server 7.0 System Administration Training Kit and the Microsoft SQL Server 7.0 Database Implementation Training Kit.
You might find the following reference material useful:
The Supplemental Course Materials compact disc contains a variety of informational aids that can be used throughout this book. These include multimedia presentations, sample data, add-on software, and files used in hands-on exercises.
The multimedia presentation supplements some of the concepts covered in the book. You should view this presentation when suggested, and then use it as a review tool while you work through the material. A complete version of this book is also available online with a variety of viewing options available. For information about using the online book, see the section "The Online Book" later in this introduction. (The other CD-ROM contains an evaluation version of Microsoft SQL 7.0 120-day Evaluation Edition.)
The Supplemental Course Material compact disc also contains files required to perform the hands-on exercises, and information designed to supplement the lesson material. These files can be used directly from the CD-ROM or copied onto your hard disk by using the Setup program. The files include demonstrations of key concepts, practice files for the exercises, and additional articles about related concepts.
The demonstrations require an HTML browser. If Microsoft Internet Explorer is installed on your system simply double-click on any of these files to view them.
Each chapter opens with a "Before You Begin" section, which prepares you for completing the chapter.
The "Review" section at the end of each chapter allows you to test what you have learned in the lesson.
Appendix A, "Questions and Answers" contains all of the book s review questions and corresponding answers.
Notes appear throughout the lessons.
Monospacetype represents code samples, examples of screen text, or entries that you might type at a command prompt or in initialization files.
Icon | Represents |
---|---|
A multimedia presentation. You will find the applicable multimedia presentation on the course compact disc. | |
A file contained on the CD-ROM. Some files are needed to complete a hands-on-practice; others contain supplemental information about the topic being discussed. The purpose of the file and its location are described in the accompanying text. | |
A hands-on practice. You should perform the practice to give yourself an opportunity to use the skills being presented in the lesson. | |
Chapter review questions. These questions at the end of each chapter allow you to test what you have learned in the lessons. You will find the answers to the review questions in Appendix A, "Questions and Answers" at the end of the book. |
This self-paced training course combines notes, hands-on exercises, a multimedia presentation, and review questions to teach you SQL Server 7.0 data warehousing. It is designed to be completed from beginning to end, but you can choose a customized track and complete only the sections that interest you. (See the next section, "Finding the Best Starting Point for You" for more information.) If you choose the customized track option, see the "Before You Begin" section in each chapter. Any hands-on exercises that require preliminary work from preceding chapters refer to the appropriate chapters.
The book is divided into the following chapters:
Because this book is self-paced, you can skip some lessons and revisit them later. Note, however, that you must install Microsoft SQL Server 7.0, SQL Server OLAP Services, and some other software before you can perform the exercises in the chapters. (See the "Getting Started" section in this About This Book for instructions on how to install the appropriate software.) The exercises in most of the chapters use the Northwind and the Northwind_Mart SQL Server databases, and the Northwind_DSS OLAP Services database. Batch installation files that create the Northwind_Mart database and the Northwind_DSS database for the later chapters are supplied on the Supplemental Course Material CD-ROM. Use the following table to find the best starting point for you:
If You | Follow this learning path |
---|---|
Are preparing to take the Microsoft Certified Professional exam 70-019, Designing and Implementing Data Warehouses with Microsoft SQL Server 7.0. | Read the "Getting Started" section. Work through the remaining chapters in any order. |
Want to review information about specific topics from the exam. | Use the "Where to Find Specific Skills in This Book" section that follows this table. |
The following tables provide a list of the skills measured on certification exam 70-019: Designing and Implementing Data Warehouses with Microsoft SQL Server 7.0. The tables provide the skill, and where in this book you will find the lesson relating to that skill.
NOTE
Exam skills are subject to change without prior notice and at the sole discretion of Microsoft.
Skill Being Measured | Location in Book |
---|---|
Analyze the scope of the project. | Chapter 3, Lesson 1 and Chapter 4, Lessons 2 & 3 |
Analyze the extent of a business requirement. | Chapter 2, Lesson 3 and Chapter 3, Lesson 1 |
Analyze security requirements. | Chapter 3, Lesson 3 and Chapter 14, Lesson 3 |
Analyze performance and scalability requirements. | Chapter 4, Lesson 5 and Chapter 5, Lesson 3 and Chapter 14, Lesson 4 |
Analyze maintainability requirements. | Chapter 3, Lesson 3 and Chapter 13 |
Analyze human factors requirements, such as target audience, localization, accessibility, roaming users, Help, and special needs. | Chapter 3 |
Skill Being Measured | Location in Book |
---|---|
Identify which technologies are appropriate for implementation of a given business solution. Technologies include design tools, data transformation tools, storage tools, presentation access tools, management tools, and scheduling tools. | Chapter 5 to Chapter 12 |
Choose a data storage architecture. | Chapter 5, Lesson3 and Chapter 9, Lesson 4 |
Skill Being Measured | Location in Book |
---|---|
Identify the sources of data from the operational databases. | Chapter 4, Lessons 1 & 2 |
Identify the encoding structure and key structure for integrating all data. | Chapter 4, Lessons 2, 3 & 4 |
Identify the filtering requirements for operational data. | Chapter 3, Lesson 1 and Chapter 6, Lesson 1 |
Assess whether a data mart schema should be integrated within the enterprise data warehouse schema. | Chapter 1, Lesson 1 |
Assess the level of detail required for data. | Chapter 4, Lesson 3 |
Skill Being Measured | Location in Book |
---|---|
Assess how a given logical design impacts performance, maintainability, extensibility, scalability, availability, and security. | Chapter 4 |
Assess whether data should be queried from a relational database or a multidimensional database. | Chapter 4, Lessons 1 & 2 |
Choose a schema design for a relational database. Design options include normalized, star, or snowflake. | Chapter 4, Lessons 1, 2 & 3 |
Group data into fact tables and dimension tables by applying denormalization rules. | Chapter 4, Lessons 1, 2, 3, & 4 |
Skill Being Measured | Location in Book |
---|---|
Use Microsoft ActiveX Data Objects (ADO), ActiveX Data Objects Multidimensional (ADO MD), multidimensional expressions (MDX), or Microsoft English Query to access or manipulate a data source. | Chapter 11 and Chapter 12 |
Write SQL statements that retrieve and summarize data. SQL statements include SELECT, ROLLUP, CUBE, and HAVING. | Appendixes C, D, E, and F |
Replicate data among data marts. | Chapter 7 |
Skill Being Measured | Location in Book |
---|---|
Implement a data storage architecture by creating and managing files and filegroups. | Chapter 5, Lessons 2 & 3 |
Use visual database tools to create databases and database tables that enforce data integrity and referential integrity. | Chapter 5, Lesson 3 |
Populate the warehouse with data from an external data source by using Data Transformation Services (DTS). External data sources include other SQL Server databases, comma-separated files, delimited files, and OLE DB for ODBC.
| Chapter 6, Lesson 2 and Chapter 8 |
Choose an indexing strategy to optimize performance for relational decision support. | Chapter 5, Lesson 3 and Chapter 14, Lesson 4 |
Create, maintain, and optimize indexes. | Chapter 5, Lesson 3 and Chapter 14, Lesson 4 |
Design the multi-dimensional OLAP model.
| Chapter 9, Lessons 3 & 4 and Chapter 13, Lesson 2 and Chapter 14, Lesson 4 |
Create and maintain OLAP aggregates.
| Chapter 9, Lessons 3 & 4 and Chapter 13, Lesson 2 and Chapter 14, Lesson 4 |
Implement security for databases and cubes | Chapter 9, Lesson 4 and Chapter 14, Lesson 3 |
Configure SQL Server options for optimal performance | Chapter 14, Lesson 2 |
Skill Being Measured | Location in Book |
---|---|
Monitor and optimize the amount of space in the database. | Chapter 5, Lesson 3 and Chapter 14, Lesson 4 |
Perform backup procedures, restore procedures, and roll-off procedures on the data warehouse.
| Chapter 13, Lesson 3 |
Perform disaster recovery procedures on the database. | Chapter 13, Lesson 3 |
Maintain database indexing. | Chapter 13, Lesson 1 |
Verify database consistency. | Chapter 13, Lesson 1 |
Monitor and optimize query performance. | Chapter 14, Lesson 4 |
Automate maintenance tasks by using alerts and agents.
| Chapter 6, Lesson 2 and Chapter 13, Lesson 3 and Chapter 7, Lesson 4 |
NOTE
Some of the skills defined in the previous tables are discussed in greater detail in the Microsoft SQL Server 7.0 System Administration Training Kit and the Microsoft SQL Server 7.0 Database Implementation Training Kit.
This self-paced training course contains hands-on procedures to help you learn about data warehousing with Microsoft SQL Server 7.0 and Microsoft SQL Server OLAP Services.
CAUTION
Several exercises may require you to make changes to your servers. This may have undesirable results if you are connected to a larger network. Check with your Network Administrator before attempting these exercises.
Each computer must have the following minimum configuration. All hardware should be on the Microsoft Windows NT Server 4.0 Hardware Compatibility List.
The following software is required to complete the exercises in this course. A 120-day evaluation copy of Microsoft SQL Server 7.0 is included on a CD-ROM in this kit.
CAUTION
The 120-day Evaluation Edition provided with this training is not the full retail product and is provided only for the purposes of training and evaluation. Microsoft Technical Support does not support this evaluation edition. For additional support information regarding this book and the CD-ROMs (including answers to commonly asked questions about installation and use), visit the Microsoft Press Technical Support Web site at http: //mspress.microsoft.com/mspress/support/. You can also email TKINPUT@MICROSOFT.COM, or send a letter to Microsoft Press, Attn: Microsoft Press Technical Support, One Microsoft Way, Redmond WA 98052-6399.
Set up your computer according to the manufacturer s instructions.
CAUTION
If your computer is part of a larger network, you must verify with your network administrator that the computer name, domain name, and other information used in setting up Microsoft SQL Server 7.0 does not conflict with network operations. If it does conflict, ask your network administrator to provide alternative values and use those values throughout all of the exercises in this book.
You should not perform the exercises in the chapters of this book on a SQL Server that is being used by others.
The Supplemental Course Materials CD-ROM contains a set of exercise files that you will need to install on your hard disk drive to complete many of the exercises in this book.
This will initiate the setup process that will install the exercise files to your hard disk drive.
IMPORTANT
This book operates on the assumption that your hard disk is named C, and the Setup wizard installs the exercise files to a default folder named C:\SQLDW. If you alter the name of this default folder during the setup, the references to exercise files in this book will differ from the true locations of files on your hard disk drive.
The Supplemental Course Materials CD-ROM contains an audio visual demonstration file that you can view by running the file from the CD-ROM. You will find a prompt within the book indicating when the demonstration should be run. You must have installed Media Player and an Internet browser on your computer to view this file. (Internet Explorer and Media Player are included on this CD for this purpose. To install either of these software products, see the installation instructions in the Readme.txt files on the CD.)
This will run the appropriate demonstration in your Internet browser.
In the \SQLDW\Exercise\bldlib folder on the Supplemental Materials CD-ROM you will find the files that create the library database used in the training kit appendix exercises. After you have copied the exercise files to your hard drive, you can use them to create the library database by executing the bldlib.cmd batch file at the command prompt.
The CD-ROM also includes an online version of the book that you can view on screen using Microsoft Internet Explorer 4.01 with SP1.
This will install the online book to your hard disk drive.
The Microsoft Certified Professional (MCP) program provides the best method to prove your command of current Microsoft products and technologies. Microsoft, an industry leader in certification, is on the forefront of testing methodology. Our exams and corresponding certifications are developed to validate your mastery of critical competencies as you design and develop, or implement and support, solutions with Microsoft products and technologies. Computer professionals who become Microsoft certified are recognized as experts and are sought after industry-wide.
The Microsoft Certified Professional program offers six certifications, based on specific areas of technical expertise:
Microsoft certification, one of the most comprehensive certification programs available for assessing and maintaining software-related skills, is a valuable measure of an individual s knowledge and expertise. Microsoft certification is awarded to individuals who have successfully demonstrated their ability to perform specific tasks and implement solutions with Microsoft products. Not only does this provide an objective measure for employers to consider; it also provides guidance for what an individual should know to be proficient. And as with any skills-assessment and benchmarking measure, certification brings a variety of benefits: to the individual, and to employers and organizations.
As a Microsoft Certified Professional, you receive many benefits:
Through certification, computer professionals can maximize the return on investment in Microsoft technology. Research shows that Microsoft certification provides organizations with:
The certification requirements differ for each certification and are specific to the products and job functions addressed by the certification.
To become a Microsoft Certified Professional, you must pass rigorous certification exams that provide a valid and reliable measure of technical proficiency and expertise. These exams are designed to test your expertise and ability to perform a role or task with a product, and are developed with the input of professionals in the industry. Questions in the exams reflect how Microsoft products are used in actual organizations, giving them "real-world" relevance.
Microsoft Certified Product Specialists are required to pass one operating system exam. Candidate may pass additional Microsoft certification exams to further qualify their skills with Microsoft BackOffice products, development tools, or desktop applications.
Microsoft Certified Professional - Specialist: Internet are required to pass the prescribed Microsoft Windows NT Server 4.0, TCP/IP, and Microsoft Internet Information System exam series.
Microsoft Certified Systems Engineers are required to pass a series of core Microsoft Windows operating system and networking exams, and BackOffice technology elective exams.
Microsoft Certified Solution Developers are required to pass two core Microsoft Windows operating system technology exams and two BackOffice technology elective exams.
Microsoft Certified Trainers are required to meet instructional and technical requirements specific to each Microsoft Official Curriculum course they are certified to deliver. In the United States and Canada, call Microsoft at (800) 636-7544 for more information on becoming a Microsoft Certified Trainer. Outside the United States and Canada, contact your local Microsoft subsidiary.
Technical training is available in a variety of ways, with instructor-led classes, online instruction, or self-paced training available at thousands of locations worldwide.
For motivated learners who are ready for the challenge, self-paced instruction is the most flexible, cost-effective way to increase your knowledge and skills.
A full-line of self-paced print and computer-based training materials are available direct from the source Microsoft Press. Microsoft Official Curriculum courseware kits from Microsoft Press are designed for advanced computer system professionals and are available from Microsoft Press and the Microsoft Developer Division. Self-paced training kits from Microsoft Press feature print-based instructional materials, along with CD-ROM based product software, multimedia presentations, lab exercises, and practice files. The Mastering Series provides in-depth, interactive training on CD-ROM for experienced developers. They re both great ways to prepare for Microsoft Certified Professional (MCP) exams.
For a more flexible alternative to instructor-led classes, turn to online instruction. It s as near as the Internet and it s ready whenever you are. Learn at your own pace and on your own schedule in a virtual classroom, often with easy access to an online instructor. Without ever leaving your desk, you can gain the expertise you need. Online instruction covers a variety of Microsoft products and technologies. It includes options ranging from Microsoft Official Curriculum to choices available nowhere else. It s training on demand, with access to learning resources 24 hours a day.
Online training is available through Microsoft Certified Technical Education Centers.
Microsoft Certified Technical Education Centers (CTECs) are the best source for instructor-led training that can help you prepare to become a Microsoft Certified Professional. The Microsoft CTEC program is a worldwide network of qualified technical training organizations that provide authorized delivery of Microsoft Official Curriculum courses by Microsoft Certified Trainers to computer professionals.
For a listing of ATEC locations in the United States and Canada, call the Microsoft fax service at (800) 727-3351. Outside the United States and Canada, call the fax service at (206) 635-2233.
Every effort has been made to ensure the accuracy of this book and the contents of the companion disc. If you have comments, questions, or ideas regarding this book or the companion disc, please send them to Microsoft Press using either of the following methods:
E-mail:
TKINPUT@MICROSOFT.COM
Postal Mail:
Microsoft Press
Attn: Microsoft SQL 7.0 Database Implementation Training kit Editor
One Microsoft Way
Redmond, WA 98052-6399
Microsoft Press provides corrections for books through the World Wide Web at the following address:
http://mspress.microsoft.com/support/
Please note that product support is not offered through the above mail addresses. For further information regarding Microsoft software support options, please connect to http://www.microsoft.com/support/ or call Microsoft Support Network Sales at (800) 936-3500.
The Evaluation Edition of Microsoft SQL Server 7.0 included with this book is unsupported by both Microsoft and Microsoft Press, and should not be used on a primary work computer. For online support information relating to the full version of Microsoft SQL Server 7.0 that might also apply to the Evaluation Edition, you can connect to:
http://support.microsoft.com/
For information about ordering the full version of any Microsoft software, please call Microsoft Sales at (800) 426-9400 or visit www.microsoft.com. Information about any issues relating to the use of this evaluation edition with this training kit are posted to the Support section of the Microsoft Press Web site (http://mspress.microsoft.com/support/).