Sign in  |  Register
Advanced search
Publication Cover

CITATION

Tennick, Art. Practical MDX Queries: For Microsoft SQL Server Analysis Services 2008. US: McGraw-Hill Osborne Media, 2010.

Practical MDX Queries: For Microsoft SQL Server Analysis Services 2008

Authors: Art Tennick

Published:  May 2010

eISBN: 9780071713375 0071713379 | ISBN: 9780071713368
Open eBook
  • Contents
  • Acknowledgments
  • Introduction
  • Chapter 1 Hello World: Easy Yet Effective Queries
  • Hello World MDX Query 1/4
  • Hello World MDX Query 2/4
  • Hello World MDX Query 3/4
  • Hello World MDX Query 4/4
  • Dimension Data on Columns
  • More Than One
  • Dimension Data on Rows
  • A Total Row
  • Hiding Nulls
  • Displaying a Different Measure
  • Hiding Nulls Again
  • Chapter 2 Astrophysical: Playing with Dimensions
  • Single-Hierarchy Dimension
  • Multiple-Hierarchy Dimension 1/2
  • Multiple-Hierarchy Dimension 2/2
  • Explicitly Requesting the All Level Member
  • Introducing .members
  • More on .members
  • Putting Two Levels Together
  • Some MDX Shorthand
  • Individual Members
  • Multiple Members
  • User Hierarchies
  • User Hierarchy with .members
  • Two Hierarchies from the Same Dimension
  • Transposing Columns onto Rows
  • Adding a Measure Explicitly
  • Two Dimensions on Two Axes
  • Same Non-measure Dimension on Two Axes
  • Same Non-measure Dimension on Two Axes with Differing Hierarchies
  • Years and Months on the Same Axis
  • Years and Months on the Same Axis with a Crossjoin
  • Introducing a Second Measure into the Crossjoin
  • A Second Crossjoin on a Second Axis
  • Crossjoin on Two Separate Non-measure Dimensions
  • A More Complex Crossjoin
  • Alternative Crossjoin Syntax 1/2
  • Alternative Crossjoin Syntax 2/2
  • More on Members
  • Chapter 3 Families and Friends: Navigating Dimensions
  • Dimensions Have Members
  • Hierarchies Have Members
  • Levels Have Members
  • Do Member Have Members?
  • Members Have Children
  • How to Get to Paris from France
  • Traversing Levels with Descendants
  • More on Descendants
  • Proving a Member (Crystal Zheng) Exists 1/3
  • Proving a Member (Crystal Zheng) Exists 2/3
  • Proving a Member (Crystal Zheng) Exists 3/3
  • Children of Crystal Zheng
  • Parent of Crystal Zheng
  • Grandparent
  • Great-Grandparent
  • Back to France
  • Last of the Parents
  • Ancestor Rather Than .parent
  • Ascendants Function
  • Ascendants with Hierarchize Function
  • Combing Navigation Functions, Brothers and Sisters
  • Siblings, Brothers and Sisters
  • First Customer with .firstchild
  • First Customer with .firstsibling
  • Now for the Last Customer
  • The Last Customer Another Way
  • Applying Vertical Navigation 1/2
  • Applying Vertical Navigation 2/2
  • Extending the Descendants Functionality
  • Descendants with self
  • Descendants with self_and_after
  • Descendants with after
  • Descendants with before
  • Descendants Using a Distance
  • Cousin
  • More on Cousin
  • A Simple Range
  • A Simpler Simple Range
  • Range with Cousin
  • Positive Lead and Negative Lag
  • Negative Lead and Positive Lag
  • Lead (or Lag) with a Range
  • Head
  • Tail
  • prevmember
  • prevmember.prevmember
  • nextmember
  • nextmember.nextmember
  • nextmember with a Range
  • Descendants with an Unbalanced Hierarchy 1/2
  • Descendants with an Unbalanced Hierarchy 2/2
  • Chapter 4 Bringing Order: Sorting Results
  • By Default, Measures Are Not Sorted
  • Using Order
  • Explicit Ascend
  • Descending Sort
  • The Sort Only Works for a Few Rows
  • Breaking Hierarchies
  • Order with desc on User Hierarchy
  • Order with bdesc on User Hierarchy
  • Sorting a Measure by Another Hidden Measure
  • Showing the Hidden Sort Measure
  • Sorting Columns Rather Than Rows
  • Sorting Hierarchies, Not Measures
  • Hierarchize Function
  • Hierarchize Function, Upside Down
  • Real-World Example of Sorting 1/2
  • Real-World Example of Sorting 2/2
  • Chapter 5 Slice, Dice, and Filter: Using Where and Filter
  • Where Clause
  • Another Measure in a Where Clause
  • Measure in a Where Clause and on an Axis
  • Default Measure in a Where Clause
  • A Non-measure Member in the Slicer
  • A Non-measure Member in the Slicer (Corrected) 1/2
  • A Non-measure Member in the Slicer (Corrected) 2/2
  • Two Non-measure Members from the Same Dimension Hierarchy
  • Two Non-measure Members from the Same Dimension Hierarchy (Fixed)
  • A Change of Slicer to United Kingdom
  • Without the United Kingdom
  • Introducing Filter
  • A Simple Filter
  • Another Simple Filter
  • A More Complex Filter with And
  • A More Complex Filter with Or
  • An Even More Complex Filter with And and Or 1/2
  • An Even More Complex Filter with And and Or 2/2
  • Comparing Two Measures in a Filter
  • Non-measure Dimension in Filter Test
  • Two Non-measure Dimensions in Filter Test
  • Now with Measures Criteria
  • Not with Is
  • Introduction to Non Empty
  • Non Empty
  • Tops and Bottoms
  • Topcount
  • Topcount with a Measure
  • Topcount with a Different Measure
  • Topcount with Two Measures
  • Bottomcount
  • Bottomcount Hiding the Nulls
  • Toppercent
  • Bottompercent
  • Topsum
  • Bottomsum
  • Chapter 6 Using the Abacus: Introduction to Calculations
  • With Clause
  • Aliases Through With
  • Useful Calculations Through With
  • Formatting Through With
  • With Set 1/2
  • With Set 2/2
  • The Scope of With
  • The Scope of Create
  • Some Classic Calculated Measures
  • Percentage of Parent 1/2
  • Percentage of Parent 2/2
  • Percentage of All
  • Chapter 7 Is Time a Dimension? Working with Dates and Times
  • Returning a Specific Fiscal Year
  • The Year Before FY 2003
  • The Year After
  • A Range of Dates Without a Range Operator
  • Going Back in Time with ParallelPeriod
  • Going Forward in Time with ParallelPeriod
  • Too Far into the Future
  • How Far into the Future?
  • How Far Back?
  • Range with OpeningPeriod and Null
  • Range with ClosingPeriod and Null
  • Range with OpeningPeriod and ClosingPeriod
  • An Extension to OpeningPeriod
  • Time Gone By
  • More on Date Ranges with LastPeriods
  • YTD (Year to Date)
  • YTD Not Working
  • QTD (Quarter to Date)
  • MTD (Month to Date)
  • WTD (Week to Date)
  • Chapter 8 Clockwork: Calculations Using Dates and Times
  • Calculated Measures with the Time Dimension
  • Year-on-Year Growth in Orders
  • Orders Compared to Two Years Ago 1/2
  • Orders Compared to Two Years Ago 2/2
  • Nulls as Zero 1/2
  • Nulls as Zero 2/2
  • Simplifying the Calculation 1/2
  • Simplifying the Calculation 2/2
  • Into the Future
  • A Two-Step Approach
  • Introduction to Sum
  • Applying Sum 1/2
  • Applying Sum 2/2
  • Sum Is Not Always Suitable
  • Aggregate Function
  • Sum and Aggregate Together
  • More on Sum and Aggregate
  • Avg Function
  • Min Function
  • Max Function
  • Moving Average with Avg
  • Sum Giving a Running Total
  • Avg Giving a Running Total
  • ParallelPeriod Revisited 1/2
  • ParallelPeriod Revisited 2/2
  • Cousin 1/2
  • Cousin 2/2
  • Workaround 1/2 Using Lag
  • Workaround 1/2 Using Lag Breaks
  • Workaround 2/2 Using Crossjoin
  • Chapter 9 Venn Diagrams: Visualizing and Manipulating Sets
  • Distinct
  • Item on a Set
  • Item on a Tuple
  • Generate
  • Rank
  • Union, Intersect, Except Base Query 1/2
  • Union, Intersect, Except Base Query 2/2
  • Union
  • Intersect
  • Except 1/2
  • Except 2/2
  • Head
  • Subset
  • Tail
  • Subset with Count
  • Nonvisual Totals
  • Visual Totals
  • Named Sets 1/2
  • Named Sets 2/2
  • MeasureGroupMeasures
  • Extract 1/2
  • Extract 2/2
  • Sorting Non-measure Dimensions 1/4
  • Sorting Non-measure Dimensions 2/4
  • Sorting Non-measure Dimensions 3/4
  • Sorting Non-measure Dimensions 4/4
  • Dimension Properties
  • Sorting by Dimension Properties
  • Missing Member Caused by a Typo
  • Missing Member Caused by a Non-Typo 1/2
  • Missing Member Caused by a Non-Typo 2/2
  • Hiding Non-Typo Missing Members 1/2
  • Hiding Non-Typo Missing Members 2/2
  • More on Exists 1/3
  • More on Exists 2/3
  • More on Exists 3/3
  • Counting Members in a Set
  • Chapter 10 Views on Cubes: Working with Subcubes
  • Select from a Perspective
  • Base Query for Subselects
  • Subselect 1/2
  • Subselect 2/2
  • Visual Totals
  • Nonvisual Totals
  • Default Measure of a Cube
  • Creating a Subcube
  • Default Measure of a Subcube
  • Querying a Subcube
  • Dropping a Subcube
  • Subcube with Visual Totals
  • Subcube with Nonvisual Totals
  • Chapter 11 Not All There: Dealing with Empty Cells
  • Empty Cells
  • Hiding Empty Cells
  • Another Way to Hide Cells
  • More on NonEmpty
  • Explaining NonEmpty
  • NonEmpty with a Different Measure
  • This Time NonEmpty Produces Different Results
  • Chapter 12 Smiley Faces: Working with Key Performance Indicators (KPIs)
  • Selecting KPIs
  • Formatting KPIs
  • Changing KPIs
  • Creating KPIs
  • Undoing Create
  • Chapter 13 Hodgepodge: A Chapter of Miscellaneous Techniques
  • Conditional Formatting Base Query
  • Conditional Formatting
  • Measure Member Aliases
  • Non-measure Dimension Member Aliases
  • DrillDownMember
  • Drillthrough 1/2
  • Drillthrough 2/2
  • Chapter 14 After You Finish
  • Where to Use MDX
  • SSRS
  • SSIS
  • SQL
  • DMX
  • XMLA
  • Winforms and Webforms
  • Performance Point Server and ProClarity
  • Third-Party Software
  • Copy and Paste
  • Index