MDX Unplugged

1 / 22

# MDX Unplugged - PowerPoint PPT Presentation

MDX Unplugged. Nauzad Kapadia [email protected] 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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about 'MDX Unplugged' - starr

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

### MDX Unplugged

[email protected]

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
• 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
Scenarios for using Named Sets
• What are named sets
• When would you use named sets
Considerations for using Named Sets
• Joining sets
• Filtering Sets
• Ordering Sets
When to use Dynamic Named Sets
• What are dynamic named sets
What is Solve Order
• What is solve order or formula precedence
• Situations where solve order is critical
• Specifying the solve order in queries
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
Handling Boundary conditions
• Handling division by zero
• Handling insufficient range size
• Handling insufficient hierarchical depth
What are sub-cubes
• What are Sub cubes
• Creating Sub cubes
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
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
Query Execution analysis tools
• SQL Server Profiler
• System Monitor
• Kernrate and Kernrate Viewer
• MDX Script Performance Analyzer
Analyzing Query Execution
• Cache matching, filtering and perfetching
• Prefetching and Request Reordering
Storage Engine bottlenecks and solutions
• Reduce Disk I/O
• Using partitions and aggregations
• Manually warming the cache
Formula Engine bottlenecks and solutions
• Common causes
• Dealing with CASE, IIF and NON_EMPTY_BEHAVIOR
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