| INTRODUCTION xxxv PART I: INTRODUCTION CHAPTER 1: INTRODUCTION 3 BISM Multidimensional Mode 4 A Closer Look at Data Warehousing 5 Key Elements of a Data Warehouse 8 Fact Tables 8 Dimension Tables 9 Dimensions 10 Cubes 11 The Star Schema 12 The Snowfl ake Schema 13 Inmon Versus Kimball -- Different Approaches 14 Business Intelligence Is Data Analysis 15 BISM Tabular Mode 15 SQL Server Analysis Services 2012 17 The Business Intelligence Semantic Model 18 Data Model 19 Query Language 20 Data Access Technology 20 Summary 21 PART II: DESIGNING MULTIDIMENSIONAL BISM CHAPTER 2: A FIRST LOOK AT MULTIDIMENSIONAL BISM 25 Development, Administrative, and Client Tools 26 Upgrading to Analysis Services 2012 26 Using SQL Server Data Tools to Build Analysis Services Multidimensional Applications 31 Creating a Project Using SQL Server Data Tools 32 The Solution Explorer 34 The Properties Window 35 The Output Window 35 Creating an Analysis Services Database Using SQL Server Data Tools 35 Creating a Data Source 36 Creating a Data Source View (DSV) 40 Creating a Cube Using the Cube Wizard 44 Deploying and Browsing a Cube 53 Using SQL Server Management Studio 56 The Object Explorer Pane 59 Querying Using the MDX Query Editor 61 Summary 63 CHAPTER 3: UNDERSTANDING MDX 65 What Is MDX? 66 MDX Concepts 66 Measures and Measure Groups 66 Hierarchies and Hierarchy Levels 67 Members 68 Cells 70 Tuples 72 Sets 72 MDX Queries 73 SELECT Statement and Axis Specification 74 FROM Clause and Cube Specification 75 Subselect Clauses 75 WHERE Clause and Slicer Specification 76 Slicer Dimension 76 WITH Clause, Named Sets, and Calculated Members 77 Named Sets 77 Calculated Members 79 Ranking and Sorting 81 MDX Expressions 82 MDX Operators 83 Arithmetic Operators 83 Set Operators 83 Comparison Operators 84 Logical Operators 84 Special MDX Operators -- Curly Braces, Commas, and Colons 84 MDX Functions 85 MDX Function Categories 85 Set Functions 85 Crossjoin 86 NONEMPTYCROSSJOIN and NONEMPTY 86 Filter and Having 87 Member Functions 87 Numeric Functions 88 Dimension Functions, Level Functions, and Hierarchy Functions 89 String Manipulation Functions 89 Other Functions 89 MDX Scripts 89 MDX Script Execution 90 CALCULATE Statement 90 Cube Space 92 AUTO EXISTS 93 Cell Calculations and Assignments 94 Recursion 97 Freeze Statement 97 Restricting Cube Space/Slicing Cube Data 98 SCOPE Statement 98 CREATE and DROP SUBCUBE 98 Using EXISTS 99 Using EXISTING 99 Using SUBSELECT 100 Parameterized MDX Queries 101 MDX Comments 102 Summary 102 CHAPTER 4: DATA SOURCES AND DATA SOURCE VIEWS 103 Data Sources 104 Data Sources Supported by Analysis Services 105 .NET Versus OLE DB Data Providers 109 .NET Framework Data Providers 109 OLE DB Data Providers 109 The Trade-Off s 110 Data Source Views 110 DSV Wizard 111 DSV Designer 111 Adding/Removing Tables in a DSV 113 Specifying Primary Keys and Relationships in the DSV 114 Customizing Your Tables in the DSV Designer 115 Data Source Views in Depth 119 Diagrams 119 Data Source View Properties 121 Diff erent Layouts in DSVs 123 Validating Your DSV and Initial Data Analysis 125 Multiple Data Sources Within a DSV 126 Summary 127 CHAPTER 5: DIMENSION DESIGN 129 Working with the Dimension Wizard 130 Working with the Dimension Designer 136 Attributes 137 Attribute Relationships 139 User Hierarchies 144 Browsing the Dimension 148 Sorting Members of a Level 157 Optimizing Attributes 159 Defining Translations in Dimensions 159 Creating a Snowflake Dimension 162 Creating a Time Dimension 165 Creating a Parent-Child Hierarchy 168 Summary 172 CHAPTER 6: CUBE DESIGN 173 The BISM Multidimensional Mode 173 Creating a Cube Using the Cube Wizard 176 Browsing Cubes 180 Cube Dimensions 184 Relationship Types 185 No Relationship 185 Regular Relationships 186 Fact Relationships 186 Many-to-Many Relationships 187 Data Mining Relationships 187 Referenced Relationships 187 Browsing Reference Dimensions in Excel 190 Measures and Measure Groups 192 Calculated Members 198 Calculated Measures 200 Querying Calculated Measures 203 Creating Perspectives 203 Creating Translations 205 Browsing Perspectives and Translations 206 Summary 208 CHAPTER 7: ADMINISTRATION AND MANAGEMENT 209 Administration Using SQL Server 2012 Tools 210 Managing Analysis Servers 210 Managing Analysis Services Objects 214 Database Creation 215 Processing Analysis Services Database Objects 217 Processing a Cube 221 Processing a Dimension 226 Managing Partitions 228 Managing Assemblies 234 Backup and Restore 237 Detach and Attach 241 Synchronization 244 Managing Security 249 Server and Database Roles 249 Enabling or Disabling Features 249 Online Mode 250 Programmatic and Advanced Administration 253 Analysis Management Objects (AMO) 253 Processing Analysis Services Databases 253 Back Up and Restore 258 Adding Assemblies to Analysis Services 259 PowerShell and Analysis Services 261 Resource and Activity Monitoring 261 HTTP Connectivity to Analysis Services 264 Analysis Services and FailOver Clustering 265 Summary 265 PART III: ADVANCED TOPICS IN BISM CHAPTER 8: ADVANCED DIMENSION DESIGN 269 Custom Rollups 270 Enhancements to Parent-Child Hierarchies 280 Unary Operators 280 Specifying Names of Levels in a Parent-Child Hierarchy 286 Using Properties to Customize Dimensions 288 Ordering Dimension Members 289 The All Member, Default Member, and Unknown Member 289 Error Confi gurations for Processing 292 Storage Mode 293 Grouping Members 294 Dimension Intelligence Using the Business Intelligence Wizard 295 Account Intelligence 295 Time Intelligence 301 Dimension Intelligence 305 Server Time Dimension 307 Dimension Writeback 311 Summary 314 CHAPTER 9: ADVANCED CUBE DESIGN 315 Measure Groups and Measures 316 Adding and Enhancing Dimensions 321 Fact Dimensions 322 Many-to-Many Dimensions 323 Data Mining Dimensions 325 Role-Playing Dimensions 328 Adding Calculations to Your Cube 329 Key Performance Indicators (KPIs) 337 KPI Creation 337 KPIs in Depth 344 Using ADOMD.NET to Query KPIs 345 Drillthrough 347 Actions 348 Action Types 348 Action Target Types 348 URL Action 349 Browse URL Action in the Cube Browser 353 Report Actions 354 Drillthrough Action 356 Adding Intelligence to the Cube 362 Semiadditive Measures 363 Currency Conversion 365 Working with Partitions 371 Building a Local Partition 373 Building a Remote Partition 375 Storage Modes and Storage Settings 384 Building Aggregations 386 The Aggregation Design Process 389 Usage-Based Optimization 392 Real-Time Cubes 393 Long Latency Scenario 393 Caching After Data Change 394 Caching Using Timed Updates 396 Average Latency Scenario 397 Caching with MOLAP Storage Option 397 No Latency Scenario 401 Real-Time ROLAP Storage Option 402 Defining Security 403 Cell Writeback 407 Cell Writeback Prerequisites 408 Writeback Statement 409 Update Nonleaf Cell Value Using Allocation 411 Equal Allocation 411 Weighted Allocation 412 Incremental Allocation 413 Cautions 413 AMO Warnings 414 Design Experience 415 Dismissing Warnings 415 Warnings Designer 416 Summary 418 CHAPTER 10: DESIGNING MULITDIMENSIONAL BISM FOR PERFORMANCE 419 Optimizing Multidimensional BISM Design 422 Fine-Tuning Your Dimensions 422 Choosing the Right Key Attribute 422 Avoiding Unnecessary Attributes 423 Turning Off Optimization for Rarely Used Attributes 424 Turning Off Attribute Hierarchy for Member Properties 425 Defining Relationships between Attributes 425 Fine-Tuning Your Cube 427 Fact Table I Measure Groups or Partitions 427 Optimizing Reference Dimensions 429 Many-to-Many Dimensions 429 Partitions 430 Merging Partitions 431 Partition Slices 434 Partition Slices and ROLAP 434 A Partition Slice Example 435 Distinct Count Partitioning 437 Optimizing for Processing 439 Creating Partitions to Speed Up Processing 441 Choosing Small and Appropriate Data Types and Sizes 441 SQL Server and Analysis Services Installations 442 Optimizing a Relational Data Source 442 Avoiding Excessive Aggregation Design 443 Using Incremental Processing When Appropriate 443 Parallelism during Processing 445 Identifying Resource Bottlenecks 449 Designing Aggregations 450 Understanding Aggregations 451 Creating Aggregations 453 Applying Aggregation Design 459 Usage-Based Aggregation Design 462 Aggregation Design Options 468 Designing Efficient Aggregations Using Hints 468 Relationships between Attributes 468 Properties Controlling Attributes and Aggregation Design 471 Managing Aggregation Designs 474 Scalability Optimizations 475 Configuring Server Configuration Properties 476 Scaling Out 477 Scaling Up 477 Handling Large Dimensions 478 Summary 478 CHAPTER 11: OPTIMIZING QUERY PERFORMANCE 479 How OLAP Enhances Performance 480 The Calculation Model 480 MDX Scripts 481 Scope and Assignments 483 Dimension Attribute Calculations 483 Session and Query Calculations 484 Query Execution Architecture 485 Analysis Services Engine Components 485 Stages of Query Execution 486 Query Evaluation Modes 487 Cell-by-Cell Mode 487 Subspace Computation 489 Performance Analysis and Tuning Tools 492 SQL Server Profi ler 492 Analysis Services Trace Events 497 Performance Monitor 498 Task Manager 501 SQL Server Management Studio 502 SQL Server Data Tools 502 Server Properties 502 Analyzing Query Performance Issues 503 Understanding FE and SE Characteristics 504 Common Solutions for Slow Queries 504 Large Storage Engine Requests 504 Several Storage Engine Requests 505 Formula Engine-Intensive Query 505 Query Optimization Techniques 505 Using NON EMPTY on Axes 506 Using Non Empty for Filtering and Sorting 508 Using SCOPE Versus IIF and CASE 509 Auto Exists Versus Properties 509 Member Value Versus Properties 509 Move Simple Calculations to Data Source View 510 Features Versus MDX Scripts 510 Scale Out with Read-Only Database 510 Writeback Query Performance 512 Summary 512 CHAPTER 12: DATA MINING, MULTIDIMENSIONAL BISM, AND ... |
内容加载中，请稍后...
商品评论（0条）