This presentation is the property of its rightful owner.
1 / 22

# MDX Unplugged PowerPoint PPT Presentation

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

MDX Unplugged

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 - - - - - - - - - - - - - - - - - - - - - - - - - -

## MDX Unplugged

### 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

### Working with ADOMD Server Objects

• Using ADOMD in .NET Assemblies

### AMO.Net Management Stored Procedures

• What are AMO stored procedures

• Advantages of AMO stored procedures

### Considerations for Extending MDX

• Static and non-static functions

• Handle null values appropriately

• Performance