MDX_Solutions_With_MS_SQL_Server_Analysis_Services

文件名称: MDX_Solutions_With_MS_SQL_Server_Analysis_Services_2005_And_Hyperion_Essbase

所属分类: SQLServer

开发工具:

文件大小: 10mb

下载次数: 0

上传时间: 2011-10-11

提供者: lr***

下载 (10mb)

不能下载？报告错误

详细说明：这是一本MDX非常好的书籍！ Acknowledgments xxi Introduction xxiii Chapter 1 A First Introduction to MDX 1 What Is MDX? 1 Query Basics 2 Axis Framework: Names and Numbering 5 Case Sensitivity and Layout 6 Simple MDX Construction 7 Comma (,) and Colon (:) 7 .Members 9 Getting the Children of a Member with .Children 10 Getting the Descendants of a Member with Descendants() 11 Removing Empty Slices from Query Results 14 Comments in MDX 16 The MDX Data Model: Tuples and Sets 17 Tuples 18 Sets 20 Queries 21 Queries with Zero Axes 22 Axis-Only Que ries 23 More Basic Vocabulary 23 CrossJoin() 23 Filter() 25 Order() 28 Querying for Member Properties 30 Querying Cell Properties 32 Client Result Data Layout 34 Summary 35 Contents ix Chapter 2 Introduction to MDX Calculated Members and Named Sets 37 Dimensional Calculations As Calculated Members 38 Calculated Member Scopes 39 Calculated Members and WITH Sections in Queries 39 Formula Precedence (Solve Order) 42 Basic Calculation Functions 48 Arithmetic Operators 48 Summary Statistical Operators 49 Avg() 50 Count(), .Count 50 DistinctCount() (Microsoft extension) 51 Sum() 52 Max() 52 Median() 52 Min() 53 NonEmptyCount() (Hyperion extension) 53 Stdev(), Stddev() 54 StdevP(), StddevP() (Microsoft Extension) 54 Var(), Variance() 54 VarP(), VarianceP() (Microsoft Extension) 55 Additional Functions 55 Introduction to Named Sets 57 Named Set Scopes 58 Summary 60 Chapter 3 Common Calculations and Selections in MDX 61 Metadata Referencing Functions in MDX 64 Many Kinds of Ratios, Averages, Percentages, and Allocations 65 Percent Contribution (Simple Ratios between Levels in a Hierarchy) 65 Percent Contribution to Total 66 Using the .CurrentMember function 66 Using the .Parent function 66 Taking the Share-of-Parent Using .CurrentMember and .Parent 67 Using the Ancestor() function 67 Calculating the Share-of-Ancestor using .CurrentMember and Ancestor() 67 Handling Division by Zero 69 Basic Allocations 70 Proportional Allocation of One Quantity Based on Ratios of Another 70 Unweighted Allocations down the Hierarchy 71 Averages 71 Simple Averages 72 Weighted Averages 73 Time-Based References and Time-Series Calculations 74 Period-to-Period References and Calculations 75 Same-Period-Last-Year References and Calculations 76 x Contents Year-to-Date (Period-to-Date) Aggregations 76 Rolling Averages and 52-week High/Low 79 Using LastPeriods() to Select Time Ranges Based on a Target Member 81 Different Aggregations along Different Dimensions (Semi-Additive Measures Using MDX) 82 Mixing Aggregations: Sum across Non-Time, Average/ Min/Max along Time 82 Mixing Aggregations: Sum across Non-time, Opening/ Closing Balance along Time 83 Carryover of Balances for Slowly Changing Values and Reporting of Last Entered Balance 84 Finding the Last Child/Descendant with Data 87 Finding the Last Time Member for Which Any Data Has Been Entered 88 Using Member Properties in MDX Expressions (Calculations and Sorting) 89 Handling Boundary Conditions (Members out of Range, Division by Zero, and More) 92 Handling Insufficient Range Size 93 Handling Insufficient Hierarchical Depth 93 Handling a Wrong-Level Reference 94 Handling Division by Zero 95 Summary 95 Chapter 4 MDX Query Context and Execution 97 Cell Context and Resolution Order in Queries 98 The Execution Stages of a Query 99 The .DefaultMember Function 100 Default Context and Slicers 101 The Simplest Query: All Context, Nothing Else 101 The WHERE Clause: Default Context and Slicers 102 Adding Axes to a Query 103 Cell Context When Resolving Axes 104 Overriding Slicer Context 106 Cell Evaluation (For Any Cell) 107 Drilling in on Solve Order and Recursive Evaluation 108 Resolving NON EMPTY Axes 110 Resolving the HAVING Clause in AS2005 111 Looping Context and .CurrentMember 114 Interdependence of Members in AS2005: Strong Hierarchies, Autoexists, and Attribute Relationships 116 Strong Hierarchies 116 Autoexists 118 Modifying the Cube Context in AS2005 119 CREATE SUBCUBE Described 120 Subcube Restrictions and Attribute Relations 123 Further Details of Specifying a Subcube 125 Tuple Specifications for Subcubes 125 Contents xi Subcubes Based on Data Values 127 Subcubes for Iterative Query Refinement 127 Points to Consider When Using Subcubes 128 Using SELECT in the FROM Clause in AS2005 128 Infinite Recursion: A “Gotcha” Related to Calculation Context 131 Product-Specific Solve Order Use 132 Use of Solve Order between Global, Session, and Query Calculations in Analysis Services 2005 132 Use of Solve Orders in Essbase 134 Use of Solve Orders in Analysis Services 2000 135 Nondata: Invalid Numbers, NULLs, and Invalid Members 135 Invalid Calculations: Division by Zero and Numerical Errors 136 Semantics of Empty Cells 136 NULLs in Comparisons and Calculations 138 Invalid Locations 140 Precedence of Cell Properties in Calculations 143 Precedence of Display Formatting 143 Data Types from Calculated Cells 144 Cube Context in Actions 146 Cube Context in KPIs 146 Visibility of Definitions between Global, Session, and Query-Specific Calculations in Analysis Services 2005 146 Summary 148 Chapter 5 Named Sets and Set Aliases 149 Named Sets: Scopes and Context 149 Common Uses for Named Sets 150 Set Aliases 152 An Example of a Set Alias 153 Set Aliases in More Detail 155 When Set Aliases Are Required 157 Summary 160 Chapter 6 Sorting and Ranking in MDX 161 The Function Building Blocks 161 Classic Top-N Selections 162 Adding Ranking Numbers (Using the Rank() function) 165 Handling Tied Ranks: Analysis Services 168 Taking the Top-N Descendants or Other Related Members across a Set 169 Getting the Fewest/Most Tuples to Reach a Threshold 172 Retrieving the Top N Percent of Tuples 174 Retrieving the Top N Percent of the Top N Percent 174 Putting Members/Tuples in Dimension Order (Ancestors First or Last) 175 Reversing a Set 176 Summary 177 xii Contents Chapter 7 Advanced MDX Application Topics 179 Arranging Parents/Ancestors after Children, Not Before 181 Returning the Subtree under a Member and the Ancestors of That Member Along with the Member 181 Using Generate() to Turn Tuple Operations into Set Operations 182 Calculating Dates/Date Arithmetic 183 Defining Ratios against the Members Selected on Rows/ Columns/Axes, Instead of against a Specific Dimension 187 Report-Based Totals-to-Parent, Percentage Contribution to Report Totals 190 Technique 1: Only Standard MDX Techniques 191 Technique 2: Considering Using VisualTotals() in Analysis Services 197 Using VisualTotals in Analysis Services 2000 197 Using VisualTotals in AS2005 198 Technique 3: Using AS2005 Subcubes 199 Hierarchical Sorting That Skips Levels in the Hierarchy 200 Sorting a Single Set on Multiple Criteria 202 Multiple Layers or Dimensions of Sorting 202 Sort Nested Dimensions with the Same Sorting Criterion for Each Dimension 203 Sort Nested Dimensions by Different Criteria 204 Pareto Analysis and Cumulative Sums 207 Returning the Top-Selling Product (or Top-Selling Month or Other Most-Significant Name) As a Measure 211 Most Recent Event for a Set of Selected Members 212 How Long Did It Take to Accumulate This Many ? (Building a Set That Sums Backward or Forward in Time) 216 Aggregating by Multiplication (Product Instead of Sum) 219 One Member Formula Calculating Different Things in Different Places 220 Including All Tuples with Tied Ranking in Sets 225 Time Analysis Utility Dimensions 227 A Sample Analysis 229 Summary 237 Chapter 8 Using the Attribute Data Model of Microsoft Analysis Services 239 The Unified Dimensional Model (UDM) 240 Dimensions 242 Attributes, Hierarchies, and Relationships 244 Attributes 245 Hierarchies and Levels 247 Relationships 249 Querying Dimensions 249 Member Properties 252 Parent-Child Hierarchies 254 Time Dimension 257 Contents xiii Cubes 257 Dimension Relationships 260 Role-Playing Dimensions 264 Perspectives 265 Drill-Through 266 The Calculation Model in UDM 266 Defining Security on UDM 267 Summary 272 Chapter 9 Using Attribute Dimensions and Member Properties in Hyperion Essbase 273 UDAs and Attributes 273 Retrieving UDAs and Attribute Values on Query Axes 274 Predefined Attributes 275 Using UDA and Attribute Values in Calculations 275 Selecting Base Dimension Members Based on UDA and Attribute Values 276 Using Attribute() to Select Members Based on Shared Attribute Values 276 Using WithAttr() to Select Members Based on Attribute Values 278 Using UDA() to Select Members Sharing a UDA Value 279 Connecting Base Members to the Attribute Hierarchy with IN 280 Connecting Base Members to Their Actual Attribute Member 280 Connecting Attribute Members to Their Attribute Values 281 Summary 281 Chapter 10 Extending MDX through External Functions 283 Using Stored Procedures with MDX 285 .NET Stored Procedures 286 .NET Stored Procedure Parameters and Return Values 287 ADOMD Server objects 289 Expression 291 TupleBuilder 291 SetBuilder 292 MDX 292 Context 293 Server Metadata Objects 294 AMO .NET Management Stored Procedures 295 Performance Considerations of Static Functions and Nonstatic Functions 297 Debugging .NET Stored Procedures 299 Additional Programming Aspects NULL, ERROR(), and Exception 300 NULL Value As an Input Parameter 300 NULL Value As an Output Parameter 301 Exceptions during Execution 301 Error() Function 302 Using Stored Procedures for Dynamic Security 303 COM DLL Stored Procedures 305 xiv Contents Argument and Return-Type Details 306 Passing Arrays of Values to COM Stored Procedures 307 MDX Functions for Use with COM Stored Procedures 312 SetToStr(), TupleToStr() 312 Members(), StrToSet(), StrToTuple() 313 External Function Example: Time Span until Sum 315 Loading and Using Stored Procedures 316 Security of Stored Procedures 317 Stored Procedure Name Resolution 318 Invoke Stored Procedures in MDX 319 Additional Considerations for Stored Procedures 320 Summary 321 Chapter 11 Changing the Cube and Dimension Environment through MDX 323 Altering the Default Member for a Dimension in Your Session 324 Dimension Writeback Operations 325 Creating a New Member 325 Moving a Member within Its Dimension 326 Dropping a Member 327 Updating a Member’s Definition 327 Refresh Cell Data and Dimension Members 328 Writing Data Back to the Cube 329 Standard Cell Writeback 329 Commit and Rollback 330 Using UPDATE CUBE 330 Summary 334 Chapter 12 The Many Ways to Calculate in Microsoft Analysis Services 335 Overview of Calculation Mechanisms 336 Intrinsic Aggregation for a Measure 336 Rollup by Unary Operator 338 Custom Member Formula 339 Calculated Member 341 Defining a Calculated Member 342 Dropping a Calculated Member 345 Cell Calculation 346 Defining a Cell Calculation 346 Dropping a Cell Calculation 350 Conditional Formatting 351 How Types of Calculations Interact 351 Interaction without Any Cell Calculations 352 Precedence of Custom Member Formulas on Multiple Dimensions 352 Precedence of Unary Operators on Multiple Dimensions 352 Cell Calculation Passes 353 Equation Solving and Financial Modeling 356 Using Solve Order to Determine the Formula in a Pass 358 Contents xv Calculated Members Not Themselves Aggregated 360 Intrinsic Aggregation of Custom Rollups, Custom Members, and Calculated Cell Results 360 Tips on Using the Different Calculation Techniques 362 Summary 362 Chapter 13 MDX Scripting in Analysis Services 2005 365 MDX Scripting Basics 366 What Is an MDX Script? 366 The Calculate Statement 367 Subcubes 368 Assignments and Aggregation 371 Assignments and Calculated Members 376 Assignments and Named Sets 377 MDX Scripting and More Complex Cubes 379 Multiple Attribute Hierarchies 379 User Hierarchies 386 Parent/Child Attribute Hierarchies 387 Many-to-Many Dimensions 388 Fact Dimensions and Reference Dimensions 390 Semi-additive and Nonadditive Measures 390 Unary Operators and Custom Member Formulas 393 Advanced MDX Scripting 395 Defining Subcubes with SCOPE 395 Assignments That Are MDX Expressions 398 Assigning Error Values to Subcubes 402 Assigning Cell Property Values to Subcubes 402 Conditional Assignments 404 Real-World MDX Scripts 405 The Time Intelligence Wizard 405 Basic Allocations Revisited 408 Summary 410 Chapter 14 Enriching the Client Interaction 411 Using Drill-Through 412 Improvements and Changes in Microsoft Analysis Services 2005 for Drill-Through 413 MDX for Drill-Through I 413 Programmatic Aspects of Drill-Through 415 MDX for Drill-Through II 417 Drill-Through Security 418 Using Actions 419 What Can You Do with an Action? 419 Targets for Actions 424 Defining an Action 425 Programmatic Aspects of Actions 428 Dropping an Action 432 xvi Contents Using KPIs 432 Creating KPI 433 MDX KPI Function 436 Using KPI 437 Summary 439 Chapter 15 Client Programming Basics 441 ADOMD.NET Basics 442 Prerequisites 443 Making a Connection 443 Working with Metadata 444 Retrieving Schema Rowsets 445 Interoperability Considerations When Using Schema Rowsets 446 Working with the Metadata Object Model 446 Interoperability Considerations When Working with the Metadata Object Model 447 Dimension Particularities 448 Handling ADOMD.NET Metadata Caching 449 Executing a Query 450 Executing Commands 450 Parameterized Commands 451 Working with the CellSet Object 452 OlapInfo Holds Metadata 452 Axes Hold Axis Information 454 Cells Hold Cell Information 455 Further Details on Retrieving Information from a Query 457 Retrieving Member Property Information 457 Retrieving Additional Member Information 460 Further Details about Retrieving Cell Data 460 Retrieving Drill-Through Data As a Recordset 462 Key Performance Indicators 463 Executing Actions 464 Handling “Flattened” MDX Results 466 DataReader and Tabular Results for MDX Queries 466 Axis 0 467 Other Axes 468 Summary 470 Chapter 16 Optimizing MDX 471 Architecture Change from Analysis Services 2000 to 2005 472 Optimizing Set Operations 473 Sums along Cross-Joined Sets 474 Filtering across Cross-Joined Sets 475 Optimizing TopCount() and BottomCount() 477 NonEmpty function In Analysis Services 2005 478 Optimizing Sorting: Order() 480 UnOrder Function for a Query with a Large Dataset 480 Contents xvii xviii Contents Optimizing Summation 480 Designing Calculations into Your Database (Putting Member Properties into Measures and the New MDX function MemberValue) 482 MDX Script Optimization 483 Scope the Calculation in Detail 484 Avoid Leaf-Level Calculations 485 Cube Design to Avoid Leaf-Level Calculation 486 Measure Expression to Optimize Leaf-Level Calculation 487 MDX Script Optimization for Leaf-Level Calculation 488 Avoid Unnecessary Leaf-Level Calculation 489 Using NONEMPTY for Higher-Level Calculations 490 Using NonemptyBehavior to Provide a Hint for Server Calculations 491 Analysis Service 2005: Use Attribute Hierarchy Instead of Member Property 491 Analysis Service 2005: Use Scope Instead of IIF 492 Avoid Slow Functions in MDX Scripts 495 Change the Calculation Logic for Better Performance: Flow Calculation 495 Use Server Native Features Rather Than Scripts for Aggregation-Related Calculations 497 Summary 498 Chapter 17 Working with Local Cubes 501 Choosing Which Syntax to Use 502 Using the CREATE CUBE Statement 502 Overview of the Process 502 Anatomy of the CREATE CUBE Statement 503 Defining Dimensions 504 Overall Dimension 504 Named Hierarchies 505 Levels 505 Member Properties 508 FORMAT_NAME and FORMAT_KEY 510 Defining Measures 511 Adding Commands 512 ROLAP versus MOLAP 513 Anatomy of the INSERT INTO Statement 514 Cube Targets 515 Regular Dimension Levels 515 Parent-Child Dimensions 516 Member Properties 516 Custom Rollups 517 Measures 517 Column Placeholders in the Targets 517 Options for the INSERT INTO 517 The SELECT Clause 518 Select Statements That Are Not SQL 520 More Advanced Programming: Using Rowsets in Memory 520 Tips for Construction 521 Local Cubes from Server Cubes 521 Rollups and Custom Member Formulas 522 Using the CREATE GLOBAL CUBE Statement 524 Overview of the Process 524 Anatomy of the CREATE GLOBAL CUBE Statement 525 Defining Measures 525 Defining Dimensions 525 Defining Levels 526 Defining Members for Slicing 527 Things to Look Out For 527 Using Analysis Services Scripting Language 528 Overview of the Process 528 Anatomy of an ASSL Statement 528 Security 529 Summary 530 Appendix A MDX Function and Operator Reference 531 Appendix B Connection Parameters That Affect MDX 637 Appendix C Intrinsic Cell and Member Properties 661 Appendix D Format String Codes 675 Index 685 ...展开收缩

(系统自动生成,下载前可以参看下载内容)