1 / 22

MDX Unplugged

MDX Unplugged. Nauzad Kapadia nauzadk@quartzsystems.com. Scenarios for using Calculated Members . What are Calculated Members When will you use calculated members What are the drawbacks of calculated members. Considerations for using calculated members. Scope of calculated members

starr
Download Presentation

MDX Unplugged

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. MDX Unplugged Nauzad Kapadia nauzadk@quartzsystems.com

  2. Scenarios for using Calculated Members • What are Calculated Members • When will you use calculated members • What are the drawbacks of calculated members

  3. Considerations for using calculated members • Scope of calculated members • Use of NON_EMPTY_BEHAVIOUR • Ordering expression arguments • Eliminate the code of computing formatted values • Use IS instead of = • Consider moving calculations to the relational engine

  4. Scenarios for using Named Sets • What are named sets • When would you use named sets

  5. Considerations for using Named Sets • Joining sets • Filtering Sets • Ordering Sets

  6. When to use Dynamic Named Sets • What are dynamic named sets

  7. What is Solve Order • What is solve order or formula precedence • Situations where solve order is critical • Specifying the solve order in queries

  8. Considerations for working with Solve Order • Solve order priority • Same solve order • Solve order only affects priority of calculations between dimensions • Leave gaps in solve order

  9. Handling Boundary conditions • Handling division by zero • Handling insufficient range size • Handling insufficient hierarchical depth

  10. Cell context and resolution order

  11. What are sub-cubes • What are Sub cubes • Creating Sub cubes

  12. Considerations for using sub-cubes • When are sub cubes preferred over slicer axis • How does the presence of sub cubes affect the outcome of a query • Sub cubes are scoped at the connection level

  13. Identifying performance bottlenecks • Determine if a unique design is involved • Determine if a poorly performing query always performs inadequately • Determine if the performance bottleneck is within the formula engine or storage engine

  14. Query Execution analysis tools • SQL Server Profiler • System Monitor • Kernrate and Kernrate Viewer • MDX Script Performance Analyzer

  15. Analyzing Query Execution • Cache matching, filtering and perfetching • Prefetching and Request Reordering

  16. Storage Engine bottlenecks and solutions • Reduce Disk I/O • Using partitions and aggregations • Manually warming the cache

  17. Formula Engine bottlenecks and solutions • Common causes • Dealing with CASE, IIF and NON_EMPTY_BEHAVIOR

  18. Using Stored Procedures with MDX • What are MDX Stored Procedures • When would you create your own stored procedures • Deploying a .NET Assembly containing MDX Stored Procedures

  19. Working with ADOMD Server Objects • Using ADOMD in .NET Assemblies • Common ADOMD objects

  20. How to create Stored Procedures with MDX (Demo)

  21. AMO.Net Management Stored Procedures • What are AMO stored procedures • Advantages of AMO stored procedures

  22. Considerations for Extending MDX • Static and non-static functions • Handle null values appropriately • Performance

More Related