MDX Reporting and Analytics with SAP NetWeaver BW

An Up-to-Date Guide for Business Intelligence Reporting and Analytics with SAP

Larry Sackett (Autor)

Buch | Hardcover

383 Seiten

2009
SAP Press (Verlag)
978-1-59229-249-3 (ISBN)

Titel ist leider vergriffen;
keine Neuauflage

Artikel merken

MDX (Multi-Dimensional eXpressions) is extraordinarily powerful, and is used for both self-service reporting and advanced analytics. If you are going to do multi-dimensional analysis, you have to know MDX. MDX enables business users to perform analyses and generate reports that are next to impossible in any other query language, such as SQL, or even in BEx queries. This is the complete guide to generating effective reports and solving business problems using MDX in NetWeaver BW. First, the author introduces you to this extraordinarily powerful query and calculation language for both advanced analytics and self-service reporting. With expert guidance, you'll explore the idiosyncrasies of the MDX language and understand what object(s) each function takes as inputs and what they generate as outputs. Then, get an in-depth look at self-service reporting and analytics with MDX and how well these tasks are accomplished depending in large part on the modeling of InfoCubes. You'll find out about techniques for improving query response time, including use of the SAP NetWeaver BW Accelerator.
Finally, you'll also learn about the recent and substantial enhancements to the SAP NetWeaver BW MDX engine, the MDX-based OLAP BAPI, and the OLAP Data Access (ODA) component in SAP BusinessObjects.

Larry Sackett is president of E.J. Barry, a business intelligence consulting firm that specializes in the end-to-end design of BI systems. This includes building the ETL (extract, transform, and load) processes for extracting and integrating data from source systems, designing multi-dimensional data warehouses, building OLAP cubes, and designing reporting and analytics applications.Mr. Sackett is a former journalist and was one of the four original founders of USA TODAY, where he directed the technical launch of "the nation's newspaper." He has written dozens of articles on the use of information, technology, and best practices to increase revenue and profits. He is a frequent presenter at industry conferences, including the SAP Reporting and Analytics Conference and the BI and Portals Conference. He has written extensively about MDX, including articles in Intelligent Enterprise, DM Review, and the BW/BI Expert newsletter.

* 1 ... Why MDX Matters ... 29 * 1.1 ... The Business Case for MDX ... 29 * 1.2 ... What Makes MDX Unique ... 30 * 1.3 ... How MDX Navigates External SAP NetWeaver BW Hierarchies ... 33 * 1.4 ... History of MDX with SAP ... 37 * 1.5 ... SAP Adopts ODBO Standard ... 38 * 1.6 ... MDX with BusinessObjects ... 39 * 1.7 ... Alternatives to SAP BEx and SAP BusinessObjects ... 41 * 1.8 ... Summary ... 42 * 2 ... Fundamentals of MDX OLAP Structures ... 43 * 2.1 ... Introduction to MDX Structures and Objects ... 43 * 2.2 ... MDX Objects and Definitions ... 45 * 2.3 ... DalSegno Company Reporting Cube ... 52 * 2.4 ... Flat InfoCube Dimensions ... 55 * 2.5 ... DalSegno Reporting Cube with MDX ... 57 * 2.6 ... Summary ... 59 * 3 ... Basic MDX Statement ... 61 * 3.1 ... MDX Test Editor ... 62 * 3.2 ... The MDX WHERE Clause Slicing the Cube ... 66 * 3.3 ... Calculated Members and Named Sets ... 69 * 3.4 ... Support for SAP NetWeaver BW Variables ... 74 * 3.5 ... Solve Order ... 75 * 3.6 ... Dimension Properties ... 76 * 3.7 ... Summary ... 78 * 4 ... MDX Set Functions ... 79 * 4.1 ... Set Functions ... 80 * 4.1.1 ... Constructing Simple Sets Using Comma and Colon Operators ... 80 * 4.1.2 ... Sets Created with the .Members Function ... 81 * 4.1.3 ... Returning Sets from a Parent Member Using the .Children Function ... 83 * 4.1.4 ... Crossjoining Two Sets ... 84 * 4.1.5 ... Using the NON EMPTY Keyword to Remove Empty Cells ... 85 * 4.1.6 ... Using the Asterisk (*) Operator to Crossjoin Sets ... 86 * 4.1.7 ... Functions that Compare Members of Different Sets ... 87 * 4.1.8 ... Filtering Sets by Search Conditions ... 91 * 4.1.9 ... Filtering Sets with TopCount, TopSum, and TopPercent ... 92 * 4.1.10 ... Finding Descendants at Lower Levels in a Hierarchy ... 96 * 4.1.11 ... Ordered Sets ... 104 * 4.1.12 ... MDX Functions Can Be Combined Like Lego Bricks ... 105 * 4.2 ... Summary ... 106 * 5 ... MDX Member and Tuple Functions ... 107 * 5.1 ... Member Functions ... 107 * 5.2 ... Combining Member Functions for Advanced Analytics ... 111 * 5.3 ... Member Functions for Time Analysis ... 114 * 5.4 ... Tuple Function .Item() ... 118 * 5.5 ... Conditional Function IIF ... 122 * 5.6 ... Summary ... 124 * 6 ... Numeric, Logical, and String Functions ... 125 * 6.1 ... Mathematic Operators ... 125 * 6.2 ... Basic Numeric Functions ... 126 * 6.2.1 ... Count Function ... 126 * 6.2.2 ... Average Function ... 127 * 6.2.3 ... Median Function ... 127 * 6.2.4 ... Sum Function ... 127 * 6.2.5 ... Rank Function ... 128 * 6.2.6 ... Min and Max Functions ... 131 * 6.2.7 ... Aggregate Function ... 132 * 6.3 ... Logical Functions ... 134 * 6.3.1 ... IsEmpty, IFF, and NOT ... 134 * 6.3.2 ... CoalesceEmtpy ... 136 * 6.3.3 ... AND, OR, and XOR ... 137 * 6.3.4 ... Boolean Operators and Comparisons ... 139 * 6.4 ... String Functions ... 139 * 6.5 ... Summary ... 140 * 7 ... MDX and Time Functions ... 141 * 7.1 ... Finding the Current Period ... 141 * 7.2 ... LastPeriods ... 143 * 7.3 ... Rolling Average ... 144 * 7.4 ... PeriodsToDate ... 144 * 7.5 ... ParallelPeriod ... 147 * 7.6 ... Putting It All Together ... 151 * 7.7 ... OpeningPeriod and ClosingPeriod ... 152 * 7.8 ... PrevMember ... 153 * 7.9 ... Min and Max Values Over Time ... 154 * 7.10 ... Summary ... 155 * 8 ... MDX Query Context ... 157 * 8.1 ... OLE DB for OLAP Specification ... 157 * 8.2 ... MDX Metadata in Schema Rowsets ... 158 * 8.3 ... MDX Query Resolution ... 164 * 8.3.1 ... The FROM Clause Is Evaluated First ... 164 * 8.3.2 ... Next, the WHERE Clause and Axes Definitions are Evaluated ... 164 * 8.3.3 ... Dependencies in the WITH Clause ... 165 * 8.4 ... Problems with Query Context ... 166 * 8.4.1 ... Invalid WHERE Clause ... 166 * 8.4.2 ... Ambiguous .CurrentMember ... 167 * 8.5 ... Summary ... 169 * 9 ... Advanced Topics in MDX ... 171 * 9.1 ... Generate Function ... 171 * 9.2 ... Infinite Recursion ... 173 * 9.3 ... Statistical Functions ... 176 * 9.3.1 ... Covariance ... 178 * 9.3.2 ... Correlation ... 178 * 9.3.3 ... Linear Regression ... 179 * 9.3.4 ... Standard Deviation ... 182 * 9.4 ... Utility Time Series Dimension ... 184 * 9.5 ... Dimension Properties ... 188 * 9.6 ... Automatic Calculations Using Multiple Time Hierarchies ... 192 * 9.7 ... Calculations at Different Levels of a Hierarchy ... 193 * 9.8 ... MDX User Interface Functions ... 195 * 9.8.1 ... DrillDownMember ... 195 * 9.8.2 ... DrillUpMember ... 197 * 9.8.3 ... ToggleDrillState ... 198 * 9.8.4 ... DrillDownLevel ... 199 * 9.8.5 ... The Top and Bottom Drill-Down Functions ... 200 * 9.9 ... Summary ... 201 * 10 ... SAP NetWeaver BW: The Data Warehouse System ... 203 * 10.1 ... Why This Matters to You: Knowledge is Power ... 204 * 10.2 ... SAP NetWeaver Platform ... 205 * 10.3 ... SAP NetWeaver BW ... 206 * 10.4 ... ETL in SAP NetWeaver BW ... 208 * 10.5 ... Getting Data into SAP NetWeaver BW ... 210 * 10.6 ... Data Integration and Transformation ... 213 * 10.7 ... Overview of DalSegno ETL Process ... 217 * 10.7.1 ... DataSource ... 219 * 10.7.2 ... InfoPackage ... 220 * 10.7.3 ... Transformations ... 220 * 10.7.4 ... Data Transfer Process ... 222 * 10.8 ... Getting Data Out of SAP NetWeaver BW ... 222 * 10.8.1 ... Data Mart Interface ... 223 * 10.8.2 ... Open Hub Destination ... 223 * 10.8.3 ... Microsoft Connector 1.0 for SAP NetWeaver BW ... 225 * 10.9 ... Summary ... 225 * 11 ... Data Modeling in SAP NetWeaver BW ... 227 * 11.1 ... Why Data Modeling Matters to You ... 230 * 11.2 ... Multidimensional Data Model ... 231 * 11.3 ... InfoObjects ... 233 * 11.3.1 ... InfoObject Naming Conventions ... 233 * 11.3.2 ... Defining InfoObjects as Characteristics ... 234 * 11.3.3 ... Key Figures ... 238 * 11.3.4 ... Attributes ... 239 * 11.4 ... InfoCubes ... 240 * 11.5 ... MultiProviders ... 241 * 11.6 ... Slowly Changing Dimensions ... 242 * 11.7 ... Hierarchies ... 244 * 11.8 ... Balanced, Unbalanced, and Network Hierarchies ... 245 * 11.9 ... Virtual Time Hierarchies ... 247 * 11.10 ... External Hierarchy: Product Group/Product ... 249 * 11.11 ... Summary ... 259 * 12 ... Query Performance in SAP NetWeaver BW ... 261 * 12.1 ... Hardware Sizing ... 262 * 12.2 ... Database Tuning ... 265 * 12.3 ... Analytical Tools ... 267 * 12.4 ... Data Modeling ... 269 * 12.4.1 ... Dimension Size ... 269 * 12.4.2 ... Line Item Dimension ... 269 * 12.4.3 ... Fact Table Partitioning ... 270 * 12.4.4 ... Fact Table Compression ... 270 * 12.4.5 ... Aggregates ... 270 * 12.4.6 ... DataStore Objects ... 272 * 12.5 ... Analyzing Individual Queries ... 273 * 12.6 ... The OLAP Cache ... 278 * 12.7 ... SAP NetWeaver BW Accelerator ... 279 * 12.8 ... Summary ... 281 * 13 ... Excel 2007 and SAP NetWeaver BW ... 283 * 13.1 ... Connecting to NetWeaver BW ... 286 * 13.2 ... Designing a Pivot Table ... 292 * 13.3 ... Pivot Charts ... 297 * 13.4 ... OLAP Formulas ... 299 * 13.5 ... Conditional Formatting ... 301 * 13.6 ... Excel Services ... 303 * 13.7 ... Summary ... 304 * 14 ... Panorama NovaView ... 305 * 14.1 ... A Brief History of Panorama and MDX ... 306 * 14.2 ... NovaView Web Client ... 307 * 14.3 ... NovaView MDX Editors ... 313 * 14.3.1 ... Basic MDX Editor in Web Client ... 313 * 14.3.2 ... Advanced MDX Editor in the Analytics Client ... 314 * 14.3.3 ... Defining Parameters ... 318 * 14.4 ... NovaView Smart Reports ... 321 * 14.5 ... Drill Through to Detail ... 324 * 14.6 ... NovaView Dashboard ... 326 * 14.7 ... Security and Caching ... 327 * 14.7.1 ... Security ... 327 * 14.7.2 ... Caching ... 327 * 14.8 ... Summary ... 328 * 15 ... Cognos 8 BI with SAP NetWeaver BW ... 329 * 15.1 ... Cognos Server ... 332 * 15.2 ... Dimensional Functions in Cognos ... 335 * 15.3 ... Reporting and Analytics in Cognos 8 BI ... 340 * 15.3.1 ... Report Studio ... 341 * 15.3.2 ... Analysis Studio ... 344 * 15.4 ... Security and Caching ... 348 * 15.4.1 ... Security ... 348 * 15.4.2 ... Caching ... 349 * 15.5 ... Summary ... 349 * 16 ... SAP BusinessObjects Roadmap and MDX ... 351 * 16.1 ... SAP BusinessObjects Frontend BI Tools ... 353 * 16.2 ... SAP BusinessObjects Web Intelligence (WebI ... 354 * 16.3 ... Semantic Layer (Universes ... 355 * 16.4 ... Xcelsius ... 357 * 16.5 ... Crystal Reports ... 359 * 16.6 ... Pioneer ... 360 * 16.7 ... SAP BusinessObjects Explorer (Polestar ... 361 * 16.8 ... Enhancements in Interoperability ... 362 * 16.9 ... Summary ... 364 * A ... MDX Functions ... 365 * A.1 ... Arguments ... 365 * A.2 ... Functions ... 366 * B ... The Author ... 377

Erscheint lt. Verlag	30.1.2010
Reihe/Serie	SAP Press
Verlagsort	Maryland
Sprache	englisch
Maße	175 x 229 mm
Einbandart	gebunden
Themenwelt	Mathematik / Informatik ► Informatik ► Netzwerke
Themenwelt	Informatik ► Weitere Themen ► SAP
Schlagworte	MDX (MultiDimensional eXpressions) • SAP NetWeaver BW
ISBN-10	1-59229-249-6 / 1592292496
ISBN-13	978-1-59229-249-3 / 9781592292493
Zustand	Neuware