MDX Solutions With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase

, by Spofford, George; Harinath, Sivakumar; Webb, Christopher; Huang, Dylan Hai; Civardi, Francesco

Note: Supplemental materials are not guaranteed with Rental or Used book purchases.

ISBN: 9780471748083 | 0471748080
Cover: Paperback
Copyright: 3/6/2006

Buy New

Print on Demand: 2-4 Weeks. This item cannot be cancelled or returned.

$73.55

Serving as both a tutorial and a reference guide to the MDX (Multidimensional Expressions) query language, this book shows data warehouse developers what they need to know to build effective multidimensional data warehouses After a brief overview of the MDX language and a look at how it is used to access data in sophisticated, multidimensional databases and data warehousing, the authors move directly to providing practical examples of MDX in use New material covers changes in the MDX language itself as well as major changes in its implementation with the latest software releases of Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase Also covers more advanced techniques, like aggregation, query templates, and MDX optimization, and shows users what they need to know to access and analyze data to make better business decisions Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

Acknowledgments

xxi

Introduction

xxiii

A First Introduction to MDX

(36)

What Is MDX?

(1)

Query Basics

(3)

Axis Framework: Names and Numbering

(1)

Case Sensitivity and Layout

(1)

Simple MDX Construction

(7)

Comma (,) and Colon (:)

(2)

Members

(1)

Getting the Children of a Member with Children

(1)

Getting the Descendants of a Member with Descendants()

(3)

Removing Empty Slices from Query Results

(2)

Comments in MDX

(1)

The MDX Data Model: Tuples and Sets

(6)

Tuples

(2)

Sets

(1)

Queries

(1)

Queries with Zero Axes

(1)

Axis-Only Queries

(1)

More Basic Vocabulary

(7)

CrossJoin()

(2)

Filter()

(3)

Order()

(2)

Querying for Member Properties

(2)

Querying Cell Properties

(2)

Client Result Data Layout

(1)

Summary

(2)

Introduction to MDX Calculated Members and Named Sets

(24)

Dimensional Calculations As Calculated Members

(1)

Calculated Member Scopes

(9)

Calculated Members and WITH Sections in Queries

(3)

Formula Precedence (Solve Order)

(6)

Basic Calculation Functions

(7)

Arithmetic Operators

(1)

Summary Statistical Operators

(1)

Avg()

(1)

Count(), Count

(1)

DistinctCount() (Microsoft extension)

(1)

Sum()

(1)

Max()

(1)

Median()

(1)

Min()

(1)

NonEmptyCount() (Hyperion extension)

(1)

Stdev(), Stddev()

(1)

StdevP(), StddevP() (Microsoft Extension)

(1)

Var(), Variance()

(1)

VarP(), VarianceP() (Microsoft Extension)

(1)

Additional Functions

(2)

Introduction to Named Sets

(3)

Named Set Scopes

(2)

Summary

(1)

Common Calculations and Selections in MDX

(36)

Metadata Referencing Functions in MDX

(1)

Many Kinds of Ratios, Averages, Percentages, and Allocations

(1)

Percent Contribution (Simple Ratios between Levels in a Hierarchy)

(1)

Percent Contribution to Total

(1)

Using the CurrentMember function

(1)

Using the Parent function

(1)

Taking the Share-of-Parent Using CurrentMember and Parent

(1)

Using the Ancestor() function

(1)

Calculating the Share-of-Ancestor using CurrentMember and Ancestor()

(2)

Handling Division by Zero

(1)

Basic Allocations

(1)

Proportional Allocation of One Quantity Based on Ratios of Another

(1)

Unweighted Allocations down the Hierarchy

(1)

Averages

(1)

Simple Averages

(1)

Weighted Averages

(1)

Time-Based References and Time-Series Calculations

(7)

Period-to-Period References and Calculations

(1)

Same-Period-Last-Year References and Calculations

(1)

Year-to-Date (Period-to-Date) Aggregations

(3)

Rolling Averages and 52-week High/Low

(2)

Using LastPeriods() to Select Time Ranges Based on a Target Member

(1)

Different Aggregations along Different Dimensions (Semi-Additive Measures Using MDX)

(7)

Mixing Aggregations: Sum across Non-Time, Average/Min/Max along Time

(1)

Mixing Aggregations: Sum across Non-time, Opening/Closing Balance along Time

(1)

Carryover of Balances for Slowly Changing Values and Reporting of Last Entered Balance

(3)

Finding the Last Child/Descendant with Data

(1)

Finding the Last Time Member for Which Any Data Has Been Entered

(1)

Using Member Properties in MDX Expressions (Calculations and Sorting)

(3)

Handling Boundary Conditions (Members out of Range, Division by Zero, and More)

(3)

Handling Insufficient Range Size

(1)

Handling Insufficient Hierarchical Depth

(1)

Handling a Wrong-Level Reference

(1)

Handling Division by Zero

(1)

Summary

(2)

MDX Query Context and Execution

(52)

Cell Context and Resolution Order in Queries

(21)

The Execution Stages of a Query

(1)

The DefaultMember Function

100

(1)

Default Context and Slicers

101

(1)

The Simplest Query: All Context, Nothing Else

101

(1)

The WHERE Clause: Default Context and Slicers

102

(1)

Adding Axes to a Query

103

(1)

Cell Context When Resolving Axes

104

(2)

Overriding Slicer Context

106

(1)

Cell Evaluation (For Any Cell)

107

(1)

Drilling in on Solve Order and Recursive Evaluation

108

(2)

Resolving NON EMPTY Axes

110

(1)

Resolving the HAVING Clause in AS2005

111

(3)

Looping Context and CurrentMember

114

(2)

Interdependence of Members in AS2005: Strong Hierarchies, Autoexists, and Attribute Relationships

116

(1)

Strong Hierarchies

116

(2)

Autoexists

118

(1)

Modifying the Cube Context in AS2005

119

(16)

CREATE SUBCUBE Described

120

(3)

Subcube Restrictions and Attribute Relations

123

(2)

Further Details of Specifying a Subcube

125

(1)

Tuple Specifications for Subcubes

125

(2)

Subcubes Based on Data Values

127

(1)

Subcubes for Iterative Query Refinement

127

(1)

Points to Consider When Using Subcubes

128

(1)

Using SELECT in the FROM Clause in AS2005

128

(3)

Infinite Recursion: A ``Gotcha'' Related to Calculation Context

131

(1)

Product-Specific Solve Order Use

132

(1)

Use of Solve Order between Global, Session, and Query Calculations in Analysis Services 2005

132

(2)

Use of Solve Orders in Essbase

134

(1)

Use of Solve Orders in Analysis Services 2000

135

(1)

Nondata: Invalid Numbers, NULLs, and Invalid Members

135

(8)

Invalid Calculations: Division by Zero and Numerical Errors

136

(1)

Semantics of Empty Cells

136

(2)

NULLs in Comparisons and Calculations

138

(2)

Invalid Locations

140

(3)

Precedence of Cell Properties in Calculations

143

(3)

Precedence of Display Formatting

143

(1)

Data Types from Calculated Cells

144

(2)

Cube Context in Actions

146

(1)

Cube Context in KPIs

146

(1)

Visibility of Definitions between Global, Session, and Query-Specific Calculations in Analysis Services 2005

146

(2)

Summary

148

(1)

Named Sets and Set Aliases

149

(12)

Named Sets: Scopes and Context

149

(1)

Common Uses for Named Sets

150

(2)

Set Aliases

152

(8)

An Example of a Set Alias

153

(2)

Set Aliases in More Detail

155

(2)

When Set Aliases Are Required

157

(3)

Summary

160

(1)

Sorting and Ranking in MDX

161

(18)

The Function Building Blocks

161

(1)

Classic Top-N Selections

162

(10)

Adding Ranking Numbers (Using the Rank() function)

165

(3)

Handling Tied Ranks: Analysis Services

168

(1)

Taking the Top-N Descendants or Other Related Members across a Set

169

(3)

Getting the Fewest/Most Tuples to Reach a Threshold

172

(2)

Retrieving the Top N Percent of Tuples

174

(1)

Retrieving the Top N Percent of the Top N Percent

174

(1)

Putting Members/Tuples in Dimension Order (Ancestors First or Last)

175

(1)

Reversing a Set

176

(1)

Summary

177

(2)

Advanced MDX Application Topics

179

(60)

Arranging Parents/Ancestors after Children, Not Before

181

(1)

Returning the Subtree under a Member and the Ancestors of That Member Along with the Member

181

(1)

Using Generate() to Turn Tuple Operations into Set Operations

182

(1)

Calculating Dates/Date Arithmetic

183

(4)

Defining Ratios against the Members Selected on Rows/Columns/Axes, Instead of against a Specific Dimension

187

(3)

Report-Based Totals-to-Parent, Percentage Contribution to Report Totals

190

(10)

Technique 1: Only Standard MDX Techniques

191

(6)

Technique 2: Considering Using VisualTotals() in Analysis Services

197

(1)

Using VisualTotals in Analysis Services 2000

197

(1)

Using VisualTotals in AS2005

198

(1)

Technique 3: Using AS2005 Subcubes

199

(1)

Hierarchical Sorting That Skips Levels in the Hierarchy

200

(2)

Sorting a Single Set on Multiple Criteria

202

(1)

Multiple Layers or Dimensions of Sorting

202

(5)

Sort Nested Dimensions with the Same Sorting Criterion for Each Dimension

203

(1)

Sort Nested Dimensions by Different Criteria

204

(3)

Pareto Analysis and Cumulative Sums

207

(4)

Returning the Top-Selling Product (or Top-Selling Month or Other Most-Significant Name) As a Measure

211

(1)

Most Recent Event for a Set of Selected Members

212

(4)

How Long Did It Take to Accumulate This Many ? (Building a Set That Sums Backward or Forward in Time)

216

(3)

Aggregating by Multiplication (Product Instead of Sum)

219

(6)

One Member Formula Calculating Different Things in Different Places

220

(5)

Including All Tuples with Tied Ranking in Sets

225

(2)

Time Analysis Utility Dimensions

227

(2)

A Sample Analysis

229

(8)

Summary

237

(2)

Using the Attribute Data Model of Microsoft Analysis Services

239

(34)

The Unified Dimensional Model (UDM)

240

(2)

Dimensions

242

(15)

Attributes, Hierarchies, and Relationships

244

(1)

Attributes

245

(2)

Hierarchies and Levels

247

(2)

Relationships

249

(1)

Querying Dimensions

249

(3)

Member Properties

252

(2)

Parent-Child Hierarchies

254

(3)

Time Dimension

257

(1)

Cubes

257

(9)

Dimension Relationships

260

(4)

Role-Playing Dimensions

264

(1)

Perspectives

265

(1)

Drill-Through

266

(1)

The Calculation Model in UDM

266

(1)

Defining Security on UDM

267

(5)

Summary

272

(1)

Using Attribute Dimensions and Member Properties in Hyperion Essbase

273

(10)

UDAs and Attributes

273

(1)

Retrieving UDAs and Attribute Values on Query Axes

274

(1)

Predefined Attributes

275

(1)

Using UDA and Attribute Values in Calculations

275

(1)

Selecting Base Dimension Members Based on UDA and Attribute Values

276

(4)

Using Attribute() to Select Members Based on Shared Attribute Values

276

(2)

Using WithAttr() to Select Members Based on Attribute Values

278

(1)

Using UDA() to Select Members Sharing a UDA Value

279

(1)

Connecting Base Members to the Attribute Hierarchy with IN

280

(1)

Connecting Base Members to Their Actual Attribute Member

280

(1)

Connecting Attribute Members to Their Attribute Values

281

(1)

Summary

281

(2)

Extending MDX through External Functions

283

(40)

Using Stored Procedures with MDX

285

(4)

NET Stored Procedures

286

(1)

.NET Stored Procedure Parameters and Return Values

287

(2)

ADOMD Server objects

289

(6)

Expression

291

(1)

TupleBuilder

291

(1)

SetBuilder

292

(1)

MDX

292

(1)

Context

293

(1)

Server Metadata Objects

294

(1)

AMO.NET Management Stored Procedures

295

(2)

Performance Considerations of Static Functions and Nonstatic Functions

297

(9)

Debugging .NET Stored Procedures

299

(1)

Additional Programming Aspects NULL, ERROR(), and Exception

300

(1)

NULL Value As an Input Parameter

300

(1)

NULL Value As an Output Parameter

301

(1)

Exceptions during Execution

301

(1)

Error() Function

302

(1)

Using Stored Procedures for Dynamic Security

303

(2)

COM DLL Stored Procedures

305

(1)

Argument and Return-Type Details

306

(6)

Passing Arrays of Values to COM Stored Procedures

307

(5)

MDX Functions for Use with COM Stored Procedures

312

(3)

SetToStr(), TupleToStr()

312

(1)

Members(), StrToSet(), StrToTuple()

313

(2)

External Function Example: Time Span until Sum

315

(1)

Loading and Using Stored Procedures

316

(2)

Security of Stored Procedures

317

(1)

Stored Procedure Name Resolution

318

(1)

Invoke Stored Procedures in MDX

319

(1)

Additional Considerations for Stored Procedures

320

(1)

Summary

321

(2)

Changing the Cube and Dimension Environment through MDX

323

(12)

Altering the Default Member for a Dimension in Your Session

324

(1)

Dimension Writeback Operations

325

(3)

Creating a New Member

325

(1)

Moving a Member within Its Dimension

326

(1)

Dropping a Member

327

(1)

Updating a Member's Definition

327

(1)

Refresh Cell Data and Dimension Members

328

(1)

Writing Data-Back to the Cube

329

(5)

Standard Cell Writeback

329

(1)

Commit and Rollback

330

(1)

Using UPDATE CUBE

330

(4)

Summary

334

(1)

The Many Ways to Calculate in Microsoft Analysis Services

335

(30)

Overview of Calculation Mechanisms

336

(15)

Intrinsic Aggregation for a Measure

336

(2)

Rollup by Unary Operator

338

(1)

Custom Member Formula

339

(2)

Calculated Member

341

(1)

Defining a Calculated Member

342

(3)

Dropping a Calculated Member

345

(1)

Cell Calculation

346

(1)

Defining a Cell Calculation

346

(4)

Dropping a Cell Calculation

350

(1)

Conditional Formatting

351

(1)

How Types of Calculations Interact

351

(11)

Interaction without Any Cell Calculations

352

(1)

Precedence of Custom Member Formulas on Multiple Dimensions

352

(1)

Precedence of Unary Operators on Multiple Dimensions

352

(1)

Cell Calculation Passes

353

(3)

Equation Solving and Financial Modeling

356

(2)

Using Solve Order to Determine the Formula in a Pass

358

(2)

Calculated Members Not Themselves Aggregated

360

(1)

Intrinsic Aggregation of Custom Rollups, Custom Members, and Calculated Cell Results

360

(2)

Tips on Using the Different Calculation Techniques

362

(1)

Summary

362

(3)

MDX Scripting in Analysis Services 2005

365

(46)

MDX Scripting Basics

366

(13)

What Is an MDX Script?

366

(1)

The Calculate Statement

367

(1)

Subcubes

368

(3)

Assignments and Aggregation

371

(5)

Assignments and Calculated Members

376

(1)

Assignments and Named Sets

377

(2)

MDX Scripting and More Complex Cubes

379

(16)

Multiple Attribute Hierarchies

379

(7)

User Hierarchies

386

(1)

Parent/Child Attribute Hierarchies

387

(1)

Many-to-Many Dimensions

388

(2)

Fact Dimensions and Reference Dimensions

390

(1)

Semi-additive and Nonadditive Measures

390

(3)

Unary Operators and Custom Member Formulas

393

(2)

Advanced MDX Scripting

395

(10)

Defining Subcubes with SCOPE

395

(3)

Assignments That Are MDX Expressions

398

(4)

Assigning Error Values to Subcubes

402

(1)

Assigning Cell Property Values to Subcubes

402

(2)

Conditional Assignments

404

(1)

Real-World MDX Scripts

405

(5)

The Time Intelligence Wizard

405

(3)

Basic Allocations Revisited

408

(2)

Summary

410

(1)

Enriching the Client Interaction

411

(30)

Using Drill-Through

412

(7)

Improvements and Changes in Microsoft Analysis Services 2005 for Drill-Through

413

(1)

MDX for Drill-Through I

413

(2)

Programmatic Aspects of Drill-Through

415

(2)

MDX for Drill-Through II

417

(1)

Drill-Through Security

418

(1)

Using Actions

419

(13)

What Can You Do with an Action?

419

(5)

Targets for Actions

424

(1)

Defining an Action

425

(3)

Programmatic Aspects of Actions

428

(4)

Dropping an Action

432

(1)

Using KPIs

432

(7)

Creating KPI

433

(3)

MDX KPI Function

436

(1)

Using KPI

437

(2)

Summary

439

(2)

Client Programming Basics

441

(30)

ADOMD.NET Basics

442

(2)

Prerequisites

443

(1)

Making a Connection

443

(1)

Working with Metadata

444

(6)

Retrieving Schema Rowsets

445

(1)

Interoperability Considerations When Using Schema Rowsets

446

(1)

Working with the Metadata Object Model

446

(1)

Interoperability Considerations When Working with the Metadata Object Model

447

(1)

Dimension Particularities

448

(1)

Handling ADOMD.NET Metadata Caching

449

(1)

Executing a Query

450

(14)

Executing Commands

450

(1)

Parameterized Commands

451

(1)

Working with the CellSet Object

452

(1)

Olaplnfo Holds Metadata

452

(2)

Axes Hold Axis Information

454

(1)

Cells Hold Cell Information

455

(2)

Further Details on Retrieving Information from a Query

457

(1)

Retrieving Member Property Information

457

(3)

Retrieving Additional Member Information

460

(1)

Further Details about Retrieving Cell Data

460

(2)

Retrieving Drill-Through Data As a Recordset

462

(1)

Key Performance Indicators

463

(1)

Executing Actions

464

(2)

Handling ``Flattened'' MDX Results

466

(4)

DataReader and Tabular Results for MDX Queries

466

(1)

Axis 0

467

(1)

Other Axes

468

(2)

Summary

470

(1)

Optimizing MDX

471

(30)

Architecture Change from Analysis Services 2000 to 2005

472

(1)

Optimizing Set Operations

473

(7)

Sums along Cross-Joined Sets

474

(1)

Filtering across Cross-Joined Sets

475

(2)

Optimizing TopCount() and BottomCount()

477

(1)

NonEmpty function In Analysis Services 2005

478

(2)

Optimizing Sorting: Order()

480

(1)

UnOrder Function for a Query with a Large Dataset

480

(1)

Optimizing Summation

480

(2)

Designing Calculations into Your Database (Putting Member Properties into Measures and the New MDX function MemberValue)

482

(1)

MDX Script Optimization

483

(15)

Scope the Calculation in Detail

484

(1)

Avoid Leaf-Level Calculations

485

(1)

Cube Design to Avoid Leaf-Level Calculation

486

(1)

Measure Expression to Optimize Leaf-Level Calculation

487

(1)

MDX Script Optimization for Leaf-Level Calculation

488

(1)

Avoid Unnecessary Leaf-Level Calculation

489

(1)

Using NONEMPTY for Higher-Level Calculations

490

(1)

Using NonemptyBehavior to Provide a Hint for Server Calculations

491

(1)

Analysis Service 2005: Use Attribute Hierarchy Instead of Member Property

491

(1)

Analysis Service 2005: Use Scope Instead of IIF

492

(3)

Avoid Slow Functions in MDX Scripts

495

(1)

Change the Calculation Logic for Better Performance: Flow Calculation

495

(2)

Use Server Native Features Rather Than Scripts for Aggregation-Related Calculations

497

(1)

Summary

498

(3)

Working with Local Cubes

501

(30)

Choosing Which Syntax to Use

502

(1)

Using the CREATE CUBE Statement

502

(22)

Overview of the Process

502

(1)

Anatomy of the CREATE CUBE Statement

503

(1)

Defining Dimensions

504

(1)

Overall Dimension

504

(1)

Named Hierarchies

505

(1)

Levels

505

(3)

Member Properties

508

(2)

FORMAT_NAME and FORMAT_KEY

510

(1)

Defining Measures

511

(1)

Adding Commands

512

(1)

ROLAP versus MOLAP

513

(1)

Anatomy of the INSERT INTO Statement

514

(1)

Cube Targets

515

(1)

Regular Dimension Levels

515

(1)

Parent-Child Dimensions

516

(1)

Member Properties

516

(1)

Custom Rollups

517

(1)

Measures

517

(1)

Column Placeholders in the Targets

517

(1)

Options for the INSERT INTO

517

(1)

The SELECT Clause

518

(2)

Select Statements That Are Not SQL

520

(1)

More Advanced Programming: Using Rowsets in Memory

520

(1)

Tips for Construction

521

(1)

Local Cubes from Server Cubes

521

(1)

Rollups and Custom Member Formulas

522

(2)

Using the CREATE GLOBAL CUBE Statement

524

(4)

Overview of the Process

524

(1)

Anatomy of the CREATE GLOBAL CUBE Statement

525

(1)

Defining Measures

525

(1)

Defining Dimensions

525

(1)

Defining Levels

526

(1)

Defining Members for Slicing

527

(1)

Things to Look Out For

527

(1)

Using Analysis Services Scripting Language

528

(2)

Overview of the Process

528

(1)

Anatomy of an ASSL Statement

528

(1)

Security

529

(1)

Summary

530

(1)

Appendix A MDX Function and Operator Reference

531

(106)

Appendix B Connection Parameters That Affect MDX

637

(24)

Appendix C Intrinsic Cell and Member Properties

661

(14)

Appendix D Format String Codes

675

(10)

Index

685

Please wait while the item is added to your bag...

FREE SHIPPING

Amazon no longer offers textbook rentals. We do!

MDX Solutions With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase

Summary

Author Biography

Table of Contents

Supplemental Materials