Fast track to MDX PDF
Preview Fast track to MDX
Fast Track to MDX Mark Whitehorn, Robert Zare and Mosha Pasumansky 1 3 MarkWhitehorn UniversityCollegeWorcester,Worcester,UK RobertZare MicrosoftCorporation,Redmond,WA,USA MoshaPasumansky MicrosoftCorporation,Redmond,WA,USA BritishLibraryCataloguinginPublicationData Whitehorn,Mark,1953- FasttracktoMDX 1.OLAPtechnology2.Datawarehousing I.TitleII.Zare,RobertIII.Pasumansky,Mosha 005.7'4 ISBN1852336811 LibraryofCongressCataloging-in-PublicationData AcatalogrecordforthisbookisavailablefromtheLibraryofCongress Apartfromanyfairdealingforthepurposesofresearchorprivatestudy,orcriticismor review, as permitted under the Copyright, 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 CopyrightLicensingAgency.Enquiriesconcerningreproductionoutsidethoseterms shouldbesenttothepublishers. ISBN1-85233-681-1 Springer LondonBerlinHeidelberg Springer is apartofSpringerScience+BusinessMedia springeronline.com ©MarkWhitehorn2002 Printedinthe United States of America Reprintedwithcorrections,2003 Reprintedwithcorrections,2004 Reprinted,2004 Theuseofregisterednames,trademarksetc.inthispublicationdoesnotimply,evenin theabsenceofaspecificstatement,thatsuchnamesareexemptfromtherelevantlaws andregulationsandthereforefreeforgeneraluse. Thepublishermakesnorepresentation,expressorimplied,withregardtotheaccuracy oftheinformationcontainedinthisbookandcannotacceptanylegalresponsibilityor liabilityforanyerrorsoromissionsthatmaybemade. TypesetbyIanKingstonEditorialServices,Nottingham 34/3830-543 Printedonacid-freepaperSPIN11018650 Contents Foreword ix Introduction xiii (cid:2) Chapter 1 Readme.doc – definitions you need to know 1 Sampledata 1 Italics 1 Introduction 1 Dimensions,measures,membersandcells 2 Crankingupthecomplexity 6 Hierarchiesandaggregations 8 Levels 10 Namingconventions 11 Tuplesandsets 13 Tuplesandhierarchies 24 Sometimesmeasuresbehavelikedimensions 24 Tuplesrevisited 25 Setsrevisited 25 Measuresrevisited 25 Memberproperties 26 Summary 27 (cid:2) Chapter 2 How MDX is used 29 (cid:2) Chapter 3 MDX queries 35 UsingMDXforqueries 36 SELECT,FROM,ON COLUMNS,ON ROWS 39 WHERE 50 Summary 52 v Contents (cid:2) Chapter 4 MDX syntax 53 Brackets,bracesandtheodddotandcomma 53 (cid:2) Chapter 5 MDX expressions 58 Recapofcellnaming 59 Theconceptofthecurrentcell 61 Relativecellreferencing 62 Thepracticalities–howtolookatthedatainacube 65 1 Comparingvalues 69 Thepracticalities–howtocreateacalculatedmember 72 2 Comparingvaluesbetweenyears 74 3 Calculatingvaluestodate 77 Summary 78 (cid:2) Chapter 6 Navigating the hierarchy 80 Children 82 Parent 83 Nestingfunctions 84 Outsidethelimits 85 Realitycheck 85 Descendants 90 Realitycheck 93 Ancestor 94 Siblings 95 Cousin 96 Summary 96 (cid:2) Chapter 7 Snapshot data analysis 99 Thegeneralproblem 100 Thegeneralsolution 101 Thespecificrequirements 101 WhyuseDescendants? 108 Summary 109 (cid:2) Chapter 8 Moving averages 110 Asimplemovingaverage 111 Amorecomplexmovingaverage 115 Summary 118 vi Contents (cid:2) Chapter 9 Filters 119 Summary 126 (cid:2) Chapter 10 Setting the default member 128 Definingacustomdefaultmember 129 Definingadifferentcustomdefaultmember 133 Definingafullydynamiccustomdefaultmember 134 Defaultmeasures 136 Summary 137 (cid:2) Chapter 11 Member properties and dimension security 138 Memberproperties 138 Dimensionsecurity 140 Usingmemberpropertiesanddimensionsecurity 141 Summary 150 (cid:2) Chapter 12 Distinct Count 151 (cid:2) Chapter 13 Parent–Child dimensions 156 (cid:2) Chapter 14 Advanced data modeling – Custom Order, Custom Rollup, Custom Members 162 Problem1:Custom Order–orderingofmembersina hierarchy 164 Creatingacustomorder 168 Practicalsummary 172 Problem2:CustomRollup–whenthecube’sdefault behaviordoesn’tdotherightjob 173 Practicalsummary 178 Problem3:CustomMembers–fillinginmissing information 178 Practicalsummary 182 Moreabout& 182 Summary 185 (cid:2) Chapter 15 Furtheradvanceddatamodelingtechniques 186 Write-enableddimensionsandworkingwithdatainother cubes 186 vii Contents Problem4:Write-enableddimensions–allowinguserstoadda membertoadimension 187 Practicalsummary 192 Problem5:Write-enableddimensions–derivingvaluesfora memberusingformulae 193 Practicalsummary 196 Problem6:Missingdata–bringingitinfromothercubes 196 Practicalsummary 199 Summary 200 (cid:2) Chapter 16 Actions 201 (cid:2) Chapter 17 Server side color coding 212 Summary 219 (cid:2) Chapter 18 More about querying 220 Namedsets 220 CROSSJOIN 221 NON EMPTY 226 Fromtoptobottom 230 Morethantwodimensions–PAGES,SECTIONS,CHAPTERS 239 Whenlogicandpeoplecollide... 241 Summary 250 Summaryofthebook 250 (cid:2) Appendix 1 Sample files 251 Whereandwhatarethesamplefiles? 251 Whentousewhichfiles 252 Howtomanagethefiles 253 Step-by-stepguidetorestoringanAnalysisServicesdatabase froma.CABfile 253 Datasources 256 (cid:2) Appendix 2 ProClarity 258 InstallingProClarityandconnectingittoacube 258 UsingProClarity’sMDXEditor 261 Index 262 viii Foreword IloveBusinessIntelligence.IloveBIbecauseitisallaboutbecomingbetter. BIisallaboutempoweringuswithknowledgeandthatknowledgeisthe power to realize our full potential. As Zorge the spy said, “knowledge is power”,andwhodoesn’tlovetohavethepowertoknow,tounderstand andtomakeintelligentdecision?Ido. Sincethedawnofthemoderninformationsystemitwasobviousthatthe information accumulated in the machine is wasted if there is no way to analyze it and learn from it. From as early as the 1950s, data analysis systems and, later, decision support systems were designed, developed anddeployedwiththatintent.However,onlyinthelastdecadehavethese systems become both reasonably affordable and mainstream and their businessimpactindisputable. ThelastdecadehasalsoseentheemergenceofOLAPasthecenterpieceof the BI technologies. The OLAP multidimensional databases combine incredible performance with unsurpassed analytical power and, in my opinion,arethefoundationoftheBIplatform. While the performance differences between the multidimensional data- basesandthetraditionalrelationaldatabasesareverysignificant,Moore’s law, which states that the hardware computing power doubles every 18 months,rendersthisadvantageoftheOLAPdatabasestemporary.Sooner orlater,therawcomputingpowerofthecommonservermachineswillbe sufficient to provide the performance needed for sophisticated analysis evenwhenthedataisstoredinarelationaldatabase. However,whereOLAPislikelytomaintainasustainedadvantageoverthe relationaldatabaseisinitsanalyticalcapabilities.Herethedifferencesare much deeper. The multidimensional data model is vastly superior to the relational data model when it comes to the expressiveness of analytical operations.Theabilitytohaverandomaccesstoanypointinspace,both detaileddataandaggregates,makesitabreezetoexpresscalculationsthat wouldotherwisetakepagesofSQLstatementsusingarelationaldatabase. ThisisalsowhereMDXentersthepictureandwhyMDXissoimportant. MDX is the way to express the analytics in the OLAP database. Without ix Foreword MDX, all of the sophisticated calculations and smart analytics that we expectofOLAPtechnologiesaresimplyimpossible. Moreover, by making MDX into an industry standard, the language has becomethelinguafrancaoftheBIworld.VirtuallyeveryBIclientapplica- tionandalmostallOLAPservershaveadoptedMDXastheirprimaryifnot exclusivequerylanguage.Inashortperiodof5yearsMDXhasbecometo multidimensionaldatabaseswhatSQListorelationaldatabases.Canyou imagine where the technology of relational databases would have been todaywithouthavingacommonquerylanguage? Undoubtedly,Microsoft’sentranceintotheBImarketattheendof1998with the release of OLAP Services in SQL Server 7.0 was the most influential event in the young BI industry. Starting from early 1997 when I and the small Plato development team assembled in Building 6 on the Redmond campus our mission was clear: “BI to the masses.” We were tasked with creatingaverypowerfulyeteasytouseOLAPServerthatcouldbedistributed andassimilatedonamammothscaleandwithunprecedentedlowprices. While we looked at every release as the most important product release andhadhighexpectationsfromeveryoneoftheversionsweshipped,itis clear to us that bringing BI to the masses is a long journey that will take almost a decade to complete. We have made huge progress in the four yearssinceweshippedthefirstreleaseofOLAPServices.SQLServer7.0 shookallpreviouslyknownconventionsofeaseofuseandpricesinOLAP products.AnalysisServicesinSQLServer2000capturedthemarketshare leadershipandbecamethemostwidelydeployedOLAPServerever.We areverypleasedwiththeaccomplishmentssofarbutweknowthatwestill havesomewaytogobeforeBIistrulyavailabletothemasses. WearenowworkingonamajornewreleaseofAnalysisServicesunderthe code name Yukon and by the time these lines are published some of the dramatic innovations in the product will be known to the public. I view YukonasthereleasethatwillbringBIserversveryclosetothepointofreal- izingthe“BIforthemasses”vision.MDXstaysacriticalcomponentofthe platform and we are making a huge investment to make it even more powerfulaswellasmucheasiertouse.ThepowerofMDXisthepowerof analyticsandwhatmakesBIsoimportant. Mosha Pasumansky is the development lead for the MDX engine of AnalysisServicesinMicrosoft.MoshaandIgoalongwaybacktothedays whenwebothworkedforPanoramaSoftware.FromthemomentIsawthe codethatheproduceditwascleartomethatMoshaisaprodigyandwas oneofthefinestdevelopersIhaveeverseen.Moshatookoverfrommethe responsibility for the calculation engine of the Panorama OLAP product andhehasbeendoingmultidimensionalcalculationseversince. x Foreword WhenMicrosoftacquiredPanoramaandthedevelopmentteamrelocated toRedmondinJanuary1997Moshajoinedaverysmalltaskforcethatwas responsibleforcreatingTensor,thecodenamefortheOLEDBforOLAP specification.Aspartofthateffortwedefinedthecentralcomponentofthe standard–theMDXlanguage.MDXcarriesalotofMosha’sgenesinit.For yearsMoshahasbeenanauthorityonthepracticalusagesofMDXtosolve commonbusinessandanalyticalproblems.ReadingabookaboutMDXby Mosha is reading a book from the guy who knows everything there is to knowaboutMDX. RobZarejoinedtheteamforthelatephasesofSQL2000release.Astonish- ingly, Rob joined us with almost no background in computing and was hiredtodosomeofthegrunttestingworkoftheproduct;testingtheuser interfacetoensurethatitwasnotdefectiveinanyobviousway.Veryquickly wediscoveredthatRobpossessesexplosiveenergyandauniquedesireto excel.WhileRobwasdoingthegruntworkduringtheday,superblyatthat, he moonlighted at building some impressive OLAP applications using the technology and writing up a bunch of product improvement suggestions. VeryquicklyRobcaughttheattentionofsomeoftheseniormembersofthe teamwhostartedmentoringhiminthevariousaspectsofproductdesign. Soon afterwards, Rob got a double promotion and was reassigned to the teamasa“programmanager,”apositionthatinMicrosoftmeansaperson thatdesignsandwritestheproductspecifications. Rob’sexpertiseisinbuildingusablesystems.Heisresponsibleforsomeof thekeyaspectsoftheuserinterfaceintheYukonreleaseandhedesigned majorportionsoftheMDXauthoringtools.Iamincrediblyimpressedwith Rob’sworkandwhenYukonisreleasedIamsurethatwhenyoulookatthe outstandingproductdesignyou’llagreewithme.Rob’spassionisinmaking hardthingseasyandwhenyoureadthisbookIamsureyou’llappreciate thatMDXispresentedinaneasytodigestformthankstoRob’swork. Mark Whitehorn is the professional author of the trio. He is the one that took Mosha’s and Rob’s ideas and formed them into an easy to read and entertainingtext.Iamsureyou’llenjoyhiswriteupasmuchasIdid. To truly understand modern Business Intelligence and to harness the power of the OLAP platform one must understand MDX, and this book, written by some of the creators of MDX, goes a long way in bringing the readerintotheMDXwayofthinking. AmirNetz ProductUnitManager SQLServer–AnalysisServices MicrosoftCorp. xi Introduction Thisiswherewetrytoconvinceyoutobuythebookandtellyouwhatit triestodoandwhatitdoesn’ttrytodo.Wealsocoverthehousekeeping informationsuchasintroducingyoutothesamplefiles,pointingyoutoa websiteforup-to-dateinformationandgenerallysettingthesceneforthe book.Ifyouhavealreadyboughtthebookand/orknowwhatitdoes,feel freetoskiptoChapter1wheretheactionstarts.Youcanalwayscomeback laterforthehousekeepinginformation. Why should you read this book? OLAP (On-Line Analytical Processing) is an extremely potent tool and MDX(Multi-DimensionaleXpressions)isthekeythatunlocksthepowerof OLAP. If you have started to use Analysis Manager to create and/or use OLAPcubesthenyou’llrapidlyreachthepointwhereknowledgeofMDX becomes useful, not to say essential. (OK, that’s the major selling pitch over). What is MDX? Well, we suspect you have some kind of idea, otherwise the title of this bookwouldnothaveattractedyou,butfortherecord: MDXisalanguagethatallowsyoutoqueryOLAPcubesinawayreminis- centofthatinwhichSQLallowsyoutoqueryrelationaldatabases.Inaddi- tionMDXexpressions(astheyarecalled)canbeusedtoaddbusinesslogic tothecubes,todefinesimpleandadvancedsecuritysettings,toimplement colorcodingforpurposesofexceptionalerting,tocreatecustommember roll-ups,customlevelroll-ups,actionsandsoon–inotherwords,MDXis usedalmosteverywhereinthedesignofeffectiveOLAPcubes.Ifyoubuild OLAPdatabasesofanycomplexitythenyouaregoingtoneedMDX. xiii
The list of books you might like
