Fast Track to MDX (eBook)

Contents 6
Foreword 10
Introduction 13
Why should you read this book? 13
What is MDX? 13
Who should read this book? 14
Who are we? 14
Bugs (sorry – readware anomalies) 16
Acknowledgements 16
What do we cover? 17
What we don’t cover 17
Disclaimer 18
The ‘d’ word 19
What’s on the CD-ROM 19
Chapter 1 Readme.doc – definitions you need to know 22
Sample data 22
Italics 22
Introduction 22
Dimensions, measures, members and cells 23
Cranking up the complexity 27
Hierarchies and aggregations 29
Levels 31
Naming conventions 32
Tuples and sets 34
Exploring the differences between tuples and sets 39
Tuples don’t have to use a member from every dimension 44
Tuples and hierarchies 45
Sometimes measures behave like dimensions 45
Tuples revisited 46
Sets revisited 46
Measures revisited 46
Member properties 47
Summary 48
Chapter 2 How MDX is used 50
MDX queries 52
MDX expressions 52
Chapter 3 MDX queries 56
Using MDX for queries 57
SELECT, FROM, ON COLUMNS, ON ROWS 60
WHERE 71
Slicer 73
Summary 73
Chapter 4 MDX syntax 74
Brackets, braces and the odd dot and comma 74
Brackets [ ] – Dimension names and member names 74
Dots . – Separators 75
Braces ( ) – Tuples 75
Curly braces { } – Sets 76
Chapter 5 MDX expressions 79
Recap of cell naming 80
The concept of the current cell 82
Relative cell referencing 83
The practicalities – how to look at the data in a cube 86
1 Comparing values 90
3 Calculating values to date 98
The practicalities – how to create a calculated member 93
2 Comparing values between years 95
Summary 99
Chapter 6 Navigating the hierarchy 101
Children 103
Parent 104
Nesting functions 105
Outside the limits 106
Reality check 106
Descendants 111
Reality check 114
Ancestor 115
Siblings 116
Cousin 117
Summary 117
Chapter 7 Snapshot data analysis 120
The general problem 121
The general solution 122
The specific requirements 122
1 Average stockholding 122
2 Closing period 126
3 Maximum value 128
Why use Descendants? 129
Summary 130
Chapter 8 Moving averages 131
A simple moving average 132
A more complex moving average 136
Summary 139
Chapter 9 Filters 140
Summary 147
Chapter 10 Setting the default member 149
Defining a custom default member 150
Defining a different custom default member 154
Defining a fully dynamic custom default member 155
Default measures 157
Summary 158
Chapter 11 Member properties and dimension security 159
Member properties 159
Dimension security 161
Using member properties and dimension security 162
Summary 171
Chapter 12 Distinct Count 172
Chapter 13 Parent–Child dimensions 177
Chapter 14 Advanced data modeling – Custom Order, Custom Rollup, Custom Members 183
Problem 1: Custom Order – ordering of members in a hierarchy 185
Member Key Column and Member Name Column 187
Creating a custom order 189
Practical summary 193
Problem 1: Custom Order – ordering of members in a hierarchy 193
Problem 2: Custom Rollup – when the cube’s default behavior doesn’t do the right job 194
Practical summary 199
Problem 2: Custom Rollup – when the cube’s default behavior doesn’t do the right job 199
Problem 3: Custom Members – filling in missing information 199
Practical summary 203
Problem 3: Custom Members – filling in missing information 203

Summary 206
Chapter 15 Further advanced data modeling techniques 207
Write-enabled dimensions and working with data in other cubes 207
Problem 4: Write-enabled dimensions – allowing users to add a member to a dimension 208
Practical summary Problem 4: Write-enabled dimensions – allowing users to add a member to a dimension 213
Problem 5: Write-enabled dimensions – deriving values for a member using formulae 214
Practical summary 217
Problem 6: Missing data – bringing it in from other cubes 217
Practical summary 220
Summary 221
Chapter 16 Actions 222
Chapter 17 Server side color coding 233
Summary 240
Chapter 18 More about querying 241
Named sets 241
CROSSJOIN 242
NON EMPTY 247
From top to bottom 251
More than two dimensions – PAGES, SECTIONS, CHAPTERS 260
When logic and people collide... 262
Summary 271
Summary of the book 271
Appendix 1 Sample files 272
Where and what are the sample files? 272
CAB files 272
MDB files 273
TXT files 273
When to use which files 273
How to manage the files 274
Step-by-step guide to restoring an Analysis Service Database (containing one or more OLAP cubes) from a .CAB file 274
Data sources 277
Appendix 2 ProClarity 279
Installing ProClarity and connecting it to a cube 279
Using ProClarity’s MDX Editor 282
Index 283