Fast track to mdx

Fast Track to MDX

Springer
London Berlin Heidelberg New York Hong Kong Milan Paris Tokyo

Fast Track to

MDX
Mark Whitehorn, Robert Zare and Mosha Pasumansky

13

Mark Whitehorn University College Worcester, Worcester, UK Robert Zare Microsoft Corporation, Redmond, WA, USA Mosha Pasumansky Microsoft Corporation, Redmond, WA, USA

British Library Cataloguing in Publication DataWhitehorn, Mark, 1953Fast track to MDX 1.OLAP technology 2.Data warehousing I.Title II.Zare, Robert III.Pasumansky, Mosha 005.7'4 ISBN 1852336811 Library of Congress Cataloging-in-Publication Data A catalog record for this book is available from the Library of Congress Apart from any fair dealing for the purposes of research or private study, or criticism or review, as permitted under theCopyright, Designs and Patents Act 1988, this publication may only be reproduced, stored or transmitted, in any form or by any means, with the prior permission in writing of the publishers, or in the case of reprographic reproduction in accordance with the terms of licences issued by the Copyright Licensing Agency. Enquiries concerning reproduction outside those terms should be sent to the publishers.ISBN 1-85233-681-1 Springer London Berlin Heidelberg Springer is a part of Springer Science+Business Media springeronline.com © Mark Whitehorn 2002 Printed in the United States of America Reprinted with corrections, 2003 Reprinted with corrections, 2004 Reprinted, 2004 The use of registered names, trademarks etc. in this publication does not imply, even in the absence of a specific statement, thatsuch names are exempt from the relevant laws and regulations and therefore free for general use. The publisher makes no representation, express or implied, with regard to the accuracy of the information contained in this book and cannot accept any legal responsibility or liability for any errors or omissions that may be made. Typeset by Ian Kingston Editorial Services, Nottingham 34/3830-543 Printedon acid-free paper SPIN 11018650

Contents

Foreword

ix xiii 1

Introduction

Chapter 1 ! Readme.doc – definitions you need to know
Sample data 1 Italics 1 Introduction 1 Dimensions, measures, members and cells 2 Cranking up the complexity 6 Hierarchies and aggregations 8 Levels 10 Naming conventions 11 Tuples and sets 13 Tuples and hierarchies 24 Sometimes measures behave likedimensions 24 Tuples revisited 25 Sets revisited 25 Measures revisited 25 Member properties 26 Summary 27

Chapter 2 ! How MDX is used Chapter 3 ! MDX queries 35

29

Using MDX for queries 36 SELECT, FROM, ON COLUMNS, ON ROWS WHERE 50 Summary 52

39

v

Contents

Chapter 4 ! MDX syntax

53
53

Brackets, braces and the odd dot and comma

Chapter 5 ! MDX expressions

58

Recapof cell naming 59 The concept of the current cell 61 Relative cell referencing 62 The practicalities – how to look at the data in a cube 65 1 Comparing values 69 The practicalities – how to create a calculated member 72 2 Comparing values between years 74 3 Calculating values to date 77 Summary 78

Chapter 6 ! Navigating the hierarchy
Children 82 Parent 83 Nesting functions 84 Outside the limits85 Reality check 85 Descendants 90 Reality check 93 Ancestor 94 Siblings 95 Cousin 96 Summary 96

80

Chapter 7 ! Snapshot data analysis
The general problem 100 The general solution 101 The specific requirements 101 Why use Descendants? 108 Summary 109

99

Chapter 8 ! Moving averages

110
115

A simple moving average 111 A more complex moving average Summary 118

vi

ContentsChapter 9 ! Filters
Summary 126

119 128

Chapter 10 ! Setting the default member

Defining a custom default member 129 Defining a different custom default member 133 Defining a fully dynamic custom default member 134 Default measures 136 Summary 137

Chapter 11 ! Member properties and dimension security
Member properties 138 Dimension security 140 Using member properties and...