Microsoft SQL Server 2000 Analysis Services Step by Step

Contents Preface ix Part 1 Administration Fundamentals Chapter 1 A Data Analysis Foundation 3 Understanding Data Warehousing 4 The Purpose of a Data Warehouse 5 Dimensions in Data Analysis 5 Hierarchies in Data Analysis 9 The Structure of a Data Warehouse 12 A Fact Table 13 Dimension Tables 14 Alternative Dimension Table Structures 16 Understanding Analysis Services 17 Conceptualizing a Cube 18 Analysis Services Administrator Tools 19 Analysis Services User Tools 20 Chapter Summary 23 Chapter 2 Analysis Manager from 500 Feet 25 Previewing Analysis Manager 26 Explore the console tree 26 Explore menu commands 27 Explore the detail pane 29 Preparing to Create a Cube 31 Review the data warehouse structure 31 Create a new OLAP database 31 Specify a data source 32 Designing a Cube by Using the Cube Wizard 34 Select the fact table and the measures 34 Create a dimension from a star schema table 36 Create a dimension from snowflake schema tables 38 Create a dimension from a parent-child table 41 Create a dimension from a date column 43 Name and save a cube 45 Processing and Browsing a Cube 46 Process a cube 46 Browse cube data 48 Chapter Summary 51 Chapter 3 Dimension and Cube Editors 53 Start the lesson 54 Working with a Standard Star Schema Dimension 54 Use the Dimension Editor 54 Choose how to sort members of a level 56 Add additional levels in the Dimension Editor 58 Create a ragged hierarchy 59 Working with a Standard Snowflake Dimension 60 Create a dimension from multiple tables 60 Allow duplicate names in a dimension 61 Create an expression for a member name 64 Specify a default member 66 Create a detail-level member property 69 Create a summary-level member property 69 Working with Time Dimensions 71 Create a calendar date hierarchy 71 Set Time dimension properties 73 Create a fiscal date hierarchy 76 Working with a Cube 79 Use the Cube Editor 79 Add a shared dimension by using the Cube Editor 81 Working with Measures in a Cube 83 Create a derived measure 83 Specify the aggregation function for a measure 85 Create a calculated measure 87 Create a measure that calculates an average 89 Hide an internal measure 91 Working with a Parent-Child Dimension 92 Create a parent-child dimension 92 Allow data for non-leaf-level members 93 Manage levels within a parent-child dimension 95 Chapter Summary 98 Chapter 4 Advanced Dimensions and Cubes 101 Start the lesson 101 Creating a Finance Cube 102 Create an initial finance cube 102 Refine the formatting of an Account dimension 104 Use custom rollup operators 105 Use a custom member formula 106 Creating an Internet Tracking Cube 108 Create a cube from a measureless fact table 108 Enable drillthrough for a cube 109 Handle a very large, flat dimension 110 Link days to a Time dimension 112 Calculate distinct counts for a dimension 114 Creating a Forecast Cube 116 Create a Scenario dimension 117 Create a cube from an empty fact table 118 Use only the top levels of a shared dimension 119 Enable write-back for a cube 121 Write values back to a cube temporarily 122 Write values back to a cube permanently 123 Dynamically add members to a dimension 126 Creating Virtual Cubes 127 Remove dimensions and measures from a cube 128 Combine measures from multiple cubes 131 Create calculated members in a virtual cube 132 Chapter Summary 135 Chapter 5 Office 2000 Analysis Components 137 Start the lesson 138 Creating an Excel PivotTable Report 138 Establish a link from Excel 138 Browse a cube by using a PivotTable report 142 Drill down to member children in a PivotTable report 144 Add multiple dimensions to a single axis 146 Format a PivotTable report 148 Create a PivotChart report 150 Creating an Office PivotTable List 153 Create a PivotTable list from a PivotTable report 153 Manipulate levels in a PivotTable list 155 Manipulate subtotals in a PivotTable list 159 Design a PivotTable list in FrontPage 162 Create a restricted PivotTable list 166 Creating a Local Cube 168 Create a local cube from a PivotTable report 168 Create a local cube from a relational data source 171 Use the OLAP Cube Wizard to create a cube file 173 Chapter Summary 177 Part 2 Multidimensional Expressions Chapter 6 MDX Values 181 Start the lesson 181 Creating Simple MDX Expressions 182 Create a calculated member using constant values 182 Display the name of the current member 185 Display the ancestor of a current member 188 Test a member name against a string 190 Display a member property for a member 192 Use an external function to convert a string to a number 193 Retrieving Values from Cells 196 Retrieve a value by specifying a single dimension 196 Retrieve a value by specifying two or more dimensions 198 Calculating Relative Contribution 203 Calculate contribution as a percent of the total 203 Calculate contribution as a percent of a parent 205 Check for an empty member 206 Comparing Values over Time 207 Calculate growth from the previous period 207 Calculate growth from a parallel period 209 Chapter Summary 212 Chapter 7 MDX Sets 213 Start the lesson 213 Preparing to Create MDX Queries 214 Use a PivotTable list to understand MDX terms 214 Use the MDX Sample application 218 Creating Basic MDX Queries 222 Create a minimal MDX query 222 Add a set to the column axis 223 Add a set to a second axis 224 Create a set by using explicit member names 226 Put two dimensions on one axis 227 Creating Calculations by Using Aggregation Functions 229 Create a calculated member as part of a query 229 Create a calculated member of a nonmeasure dimension 230 Create a cumulative total 234 Calculate discount dollars by using a member function 238 Chapter Summary 242 Part 3 Advanced Administration Chapter 8 Storage Optimization 245 Start the lesson 246 Specifying Options for Optimizing Storage 246 Understand Analysis server storage modes 246 Choose the correct storage mode 247 Understand Analysis server aggregations 248 Inspect aggregations for a single dimension 249 Inspect aggregations for two dimensions 252 Use the Storage Design Wizard 255 Managing the Pool of Potential Aggregations 257 Preparing for Usage-Based Optimization 257 Populate the usage log 258 View usage analysis reports 259 Use the Usage-Based Optimization Wizard 261 Consider the server cache 263 Manipulate the query log 264 Manage aggregations for a dimension 264 Chapter Summary 266 Chapter 9 Processing Optimization 269 Start the lesson 269 Understanding OLAP Processing 270 How the Analysis Server Processes a Dimension 270 How the Analysis Server Processes a Cube 272 Watch the server process a database 274 Changing Data in a Warehouse 276 Set storage options for sample cubes 277 Browse data before updating the warehouse 278 Change the database data source 279 Browse data after updating the warehouse 280 Managing OLAP Processing 283 Incrementally update a dimension 283 Incrementally update a cube 286 Watch an incremental update double-count values 287 Incrementally update by using a separate fact table 288 Incrementally update by using a filter 290 Working with Partitions 291 Use a data slice when creating a new partition 293 Use a filter when editing an existing partition 297 Merge two partitions into one 299 Automating the Processing of a Database 302 Create a DTS package 303 Create an Analysis Services Processing Task 304 Send an e-mail message if the task fails 305 Save and schedule a DTS package 306 Chapter Summary 308 Chapter 10 Dimension Optimization 309 Start the lesson 309 Optimizing Dimensions in a Cube 310 Process a cube without optimizations 310 Assign unique keys to the leaf levels of a dimension 311 Optimize a cube 314 Creating Virtual Dimensions 318 Create a single-level virtual dimension 318 Creating a Hierarchy in a Virtual Dimension 321 Add aggregations to the Sales cube 321 Create a Fiscal Year member property 323 Create a Fiscal Quarter member property 324 Create a Time.Fiscal virtual dimension 324 Adjust the sort order of virtual dimension members 326 Test the performance with aggregations 328 Chapter Summary 330 Chapter 11 Security 331 Start the lesson 331 Creating Security Roles 332 Create sample users and groups 332 Create a role for all users 335 Create roles at the database level 338 Manage database roles 341 Applying Security to a Dimension 344 Fully restrict a dimension 344 Restrict the members of a dimension 347 Control visual totals for a dimension 349 Create a default member for a role in a dimension 353 Applying Cell-Level Security to a Cube 355 Prevent values in cells from being read 355 Allow users to write to cells 358 Chapter Summary 359 Index 361