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

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

by ; ; ; ;
Edition: 2nd
ISBN13: 9780471748083
ISBN10: 0471748080
Format: Paperback
Pub. Date: 2006-03-06
Publisher(s): Wiley
  • Free Shipping Icon

    Free Shipping On Orders Over $59

    Free standard shipping on order over $59 to your home address. Marketplace purchases through third-party sellers are excluded from free shipping promotions.

List Price: $78.78

Buy New

Usually Ships in 8 - 10 Business Days.
$75.03
Add to Cart

Rent Textbook

Select for Price
Add to Cart
There was a problem. Please try again later.

Digital

Rent Digital Options
Online:1825 Days access
Downloadable:Lifetime Access
$70.80
*To support the delivery of the digital material to you, a non-refundable digital delivery fee of $3.99 will be charged on each digital item.
$70.80*
Add to Cart

Used Textbook

We're Sorry
Sold Out

Buy from our Marketplace starting at $5.49

How Marketplace Works:

  • This item is offered by an independent seller and not shipped from our warehouse
  • Item details like edition and cover design may differ from our description; see seller's comments before ordering.
  • Sellers much confirm and ship within two business days; otherwise, the order will be cancelled and refunded.
  • Marketplace purchases cannot be returned to eCampus.com. Contact the seller directly for inquiries; if no response within two days, contact customer service.
  • Additional shipping costs apply to Marketplace purchases. Review shipping costs at checkout.

Summary

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.

Author Biography

George Spofford is a Distinguished Engineer at Hyperion Solutions, Inc. and the author of the first edition of MDX Solutions (Wiley).

Sivakumar Harinath is the Test Lead for Analysis Services 2005 at Microsoft.

Christopher Webb is a technical architect who specializes in BI solutions at IMS Health in London.

Dylan Hai Huang is currently working as a program manager in Microsoft Office Business Application Team. Before joining the current team, he had been working in Microsoft Analysis Service Performance Team. His main interests are business intelligence, high performance computation and data mining.

Francesco Civardi is Chief Scientist at DaisyLabs, a Business Intelligence Company. He is also Professor of Data Analysis Tools and Techniques at the Catholic Universities of Brescia and Cremona.

Table of Contents

Acknowledgments xxi
Introduction xxiii
A First Introduction to MDX
1(36)
What Is MDX?
1(1)
Query Basics
2(3)
Axis Framework: Names and Numbering
5(1)
Case Sensitivity and Layout
6(1)
Simple MDX Construction
7(7)
Comma (,) and Colon (:)
7(2)
Members
9(1)
Getting the Children of a Member with Children
10(1)
Getting the Descendants of a Member with Descendants()
11(3)
Removing Empty Slices from Query Results
14(2)
Comments in MDX
16(1)
The MDX Data Model: Tuples and Sets
17(6)
Tuples
18(2)
Sets
20(1)
Queries
21(1)
Queries with Zero Axes
22(1)
Axis-Only Queries
23(1)
More Basic Vocabulary
23(7)
CrossJoin()
23(2)
Filter()
25(3)
Order()
28(2)
Querying for Member Properties
30(2)
Querying Cell Properties
32(2)
Client Result Data Layout
34(1)
Summary
35(2)
Introduction to MDX Calculated Members and Named Sets
37(24)
Dimensional Calculations As Calculated Members
38(1)
Calculated Member Scopes
39(9)
Calculated Members and WITH Sections in Queries
39(3)
Formula Precedence (Solve Order)
42(6)
Basic Calculation Functions
48(7)
Arithmetic Operators
48(1)
Summary Statistical Operators
49(1)
Avg()
50(1)
Count(), Count
50(1)
DistinctCount() (Microsoft extension)
51(1)
Sum()
52(1)
Max()
52(1)
Median()
52(1)
Min()
53(1)
NonEmptyCount() (Hyperion extension)
53(1)
Stdev(), Stddev()
54(1)
StdevP(), StddevP() (Microsoft Extension)
54(1)
Var(), Variance()
54(1)
VarP(), VarianceP() (Microsoft Extension)
55(1)
Additional Functions
55(2)
Introduction to Named Sets
57(3)
Named Set Scopes
58(2)
Summary
60(1)
Common Calculations and Selections in MDX
61(36)
Metadata Referencing Functions in MDX
64(1)
Many Kinds of Ratios, Averages, Percentages, and Allocations
65(1)
Percent Contribution (Simple Ratios between Levels in a Hierarchy)
65(1)
Percent Contribution to Total
66(1)
Using the CurrentMember function
66(1)
Using the Parent function
66(1)
Taking the Share-of-Parent Using CurrentMember and Parent
67(1)
Using the Ancestor() function
67(1)
Calculating the Share-of-Ancestor using CurrentMember and Ancestor()
67(2)
Handling Division by Zero
69(1)
Basic Allocations
70(1)
Proportional Allocation of One Quantity Based on Ratios of Another
70(1)
Unweighted Allocations down the Hierarchy
71(1)
Averages
71(1)
Simple Averages
72(1)
Weighted Averages
73(1)
Time-Based References and Time-Series Calculations
74(7)
Period-to-Period References and Calculations
75(1)
Same-Period-Last-Year References and Calculations
76(1)
Year-to-Date (Period-to-Date) Aggregations
76(3)
Rolling Averages and 52-week High/Low
79(2)
Using LastPeriods() to Select Time Ranges Based on a Target Member
81(1)
Different Aggregations along Different Dimensions (Semi-Additive Measures Using MDX)
82(7)
Mixing Aggregations: Sum across Non-Time, Average/Min/Max along Time
82(1)
Mixing Aggregations: Sum across Non-time, Opening/Closing Balance along Time
83(1)
Carryover of Balances for Slowly Changing Values and Reporting of Last Entered Balance
84(3)
Finding the Last Child/Descendant with Data
87(1)
Finding the Last Time Member for Which Any Data Has Been Entered
88(1)
Using Member Properties in MDX Expressions (Calculations and Sorting)
89(3)
Handling Boundary Conditions (Members out of Range, Division by Zero, and More)
92(3)
Handling Insufficient Range Size
93(1)
Handling Insufficient Hierarchical Depth
93(1)
Handling a Wrong-Level Reference
94(1)
Handling Division by Zero
95(1)
Summary
95(2)
MDX Query Context and Execution
97(52)
Cell Context and Resolution Order in Queries
98(21)
The Execution Stages of a Query
99(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

An electronic version of this book is available through VitalSource.

This book is viewable on PC, Mac, iPhone, iPad, iPod Touch, and most smartphones.

By purchasing, you will be able to view this book online, as well as download it, for the chosen number of days.

Digital License

You are licensing a digital product for a set duration. Durations are set forth in the product description, with "Lifetime" typically meaning five (5) years of online access and permanent download to a supported device. All licenses are non-transferable.

More details can be found here.

A downloadable version of this book is available through the eCampus Reader or compatible Adobe readers.

Applications are available on iOS, Android, PC, Mac, and Windows Mobile platforms.

Please view the compatibility matrix prior to purchase.