A Practical Guide to Microsoft® OLAP Server

John Shumate is currently a Decision Support Consultant at Anteon Corporation in Fairfax, Virginia. His 16 years of experience in the development of business intelligence systems include work on large data warehouse projects for the United States Department of Housing and Urban Development, Capital Blue Cross, and the United States Postal Service. He taught courses in systems analysis and design and Visual Basic programming at George Washington University from 1993 to 1996 and is a member of the Data Warehousing Institute. John is also a Contributing Editor to PC Week, and has published more than 25 articles on OLAP, business intelligence, and data warehousing. He has used Microsoft SQL Server extensively on numerous projects and has been working with OLAP Services since its initial beta release in 1998. For this book, he used OLAP Services to develop and test the included examples and he interviewed the pioneers and early adopters of the OLAP industry. 0201485575AB04062001

Preface Part I Basics Chapter 1 OLAP Concepts Five Characteristics of OLAP OLAP Applications Decision Support Decision Support versus Transaction Processing Decision Support and the Data Warehouse Datamarts and Data Warehouses Key OLAP Concepts Dimensions Hierarchies and Aggregation Multidimensional Cubes Components of an OLAP Application Data Acquisition Data Structuring and Storage Multidimensional Analysis Chapter 2 OLAP History and Trends The OLAP Story The First Generation The Second Generation The Third Generation The Little Engine That Could Plato OLAP Standards The OLAP Council OLE DB for OLAP and MDX OLAP Solutions Intended Function and Audience Data Acquisition and Storage End-user Client Model Visualization Time Intelligence Open Environment Automation OLAP Trends Analysis at Large Integration Packaged Applications Cutting Data Clutter Chapter 3 Introduction to Microsoft SQL Server OLAP Services The Enterprise Picture OLAP Services Functionality OLAP Services Architecture System Components Open and Extensible Environment Data Acquisition Read/Write Data Access Multidimensional Storage Options Time Intelligence OLAP Services Security OLAP Manager Architecture SQL Server 7 Software Versions Benefits of OLAP Services Part II Implementation: Planning, Analysis, and Design Chapter 4 Planning the OLAP Project Project Planning Steps Defining Project Goals Decomposing the Task List Identifying Resources Staff Resources Nonstaff Resources Estimating the Schedule Estimating the Budget Assessing the Risks Money Time Unknowns Dependencies Assumptions Conducting a Final Review The Plan in Action Project Planning Tools OLAP in the Self-actualized Enterprise Assessing the State of Your Enterprise Chapter 5 OLAP Systems Analysis The Analytical Process The Analytical Spirit Perspectives of Information Providers and Users Information-Collecting Interviews Strategic and Planning Executive Interviews Business Perspective Interviews Technical Perspective Interviews Reviewing Current Systems Source Data Analysis Host Platform and Data Accessibility Scope and Variance of Source Data Analysis of Data Quality Measures Analysis Dimensional Analysis Relational Analysis Physical Examination Recording Metadata Conducting a Final Review Roadblocks Bottlenecks Black Holes Miracles Redundancies Refocusing on Goals Chapter 6 Designing Your OLAP Services Solution The Solution Proposal Database Design The Subject of Analysis The Measures The Dimensions The Star and Snowflake Schemas Schema Complexities Source Data Transformations Data Transport Architecture Data Element Mappings and Transformation Rules Extraction and Transformation Processing Tools Quality Control Cubes and Cube Processing System Security A Basic User Security Scheme Beyond Basic Security Physical Security End-user Access Custom Components Design Review Part III Implementation: Construction, Release, and Support Chapter 7 Installing OLAP Services Minimum Requirements User Accounts Final Prep for Installation Performing the Installation Verifying the Setup Client and Database Drivers Administrator Accounts Registering OLAP Servers Migrating the Repository to SQL Server OLAP Manager Add-ins and Function Libraries Chapter 8 Building the OLAP Database Quick Guide to OLAP Manager Creating the Database Creating Data Sources Creating Dimensions Standard Dimension Time Dimension Creating Cubes Calculated Members Design Storage Options Database Roles and Read/Write Permissions Aggregation Functions Processing Cubes Chapter 9 Using Excel as an OLAP Client How Excel Works with OLAP Data Data Drivers and MS Query Data Sources PivotTables Introduction to PivotTables Creating OLAP PivotTable Reports Working with PivotTables PivotCharts Basics of PivotCharts Working with PivotCharts Offline Cube Files Creating OLAP Cubes from Relational Data Overview of Creating Cubes Creating an MS Query on Relational Data Running the OLAP Cube Wizard OLAP and Excel on the Web Overview of Office and Excel Web Functionality Noninteractive Web Reports Interactive PivotLists Interactive PivotCharts Preparing to Implement Excel as Your OLAP Client Minimum Requirements Creating a Shared Folder for Data Sources Chapter 10 Managing the OLAP Application Administrative Duties Scheduled Administrative Functions Unscheduled Administrative Functions Documentation and Metadata Administrators Guide Users Guide Metadata Training Administrative and Technical Training End-user Training User Support Refreshing Cube Data Incremental Update Processing Refresh Cube Processing Cube Usage Analysis and Optimization The Query Log Usage Analysis Wizard Usage-Based Optimization Wizard Write-back Data Maintenance Refinements and Additions Chapter 11 Third-Party OLAP Clients Overview Excel Add-ins Dedicated Fat Clients Internet Solutions Managed Query and Reporting Tools Vertical-Market and Value-Added Solutions Software Developer Kits Client Selection Process Establishing Selection Criteria Selecting Product Category Conducting Product Search Hands-on Product Evaluation Part IV Beyond the Basics Chapter 12 Multidimensional Expressions Overview of MDX How MDX Is Applied MDX Sample Application Learn Your Way Around the Cube Dimensional Structure and Family Relationships Member and Tuple References Set References Basic Selects Cube Specifications Axes Specifications Slicer Specifications MDX Functions Member Functions Set Functions Advanced Selects Nested Dimensions: Tuples and Crossjoins Multiple Measures Calculated Members Numeric Functions in Parameters Handling Empty Values Filtering Datasets Where to Go from Here Chapter 13 Advanced Application Techniques Member Properties and Virtual Dimensions Managing Member Properties Managing Virtual Dimensions Creating a Dual-axes Dimension View Virtual Cubes Managing Virtual Cubes Joining Fact Tables Data Partitioning Creating Partitions Merging Partitions Cell-level Security Permission Rules Setting Cell-level Permission Rules Time as an Analysis Factor Period Comparisons Rolling Period Ratios Moving Averages Alternate Time Dimension Hierarchies Breaking the Time Dimension in Two Multiple Time Dimensions in a Cube Contribution Percentages Real-time Data Feeds Appendix Bibliography Glossary Index