THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Microsoft OLAP by Mosha Pasumansky

Outline of the 'Deep dive to MDX' PASS presentation

My PASS presentation is quickly approaching. Currently there are about twice as many people registered for it than I thought there will be. I am getting a little nervous about it. After all, 7 hours is a lot of time, and I am not sure what people expect from this session. Since there is still time to make changes, I decided to publish the tentative outline of the presentation here, in the hope to get more feedback. The outline is not final, the order of subjects will change for sure, but this should give a good idea what to expect from the session. If this isn't what you had in mind - there is still time to cancel and go for another session. (For example, I think about half of the presentation is based on former blog entries (although somewhat expanded), so if you read all my blogs, then you already know at least half the content).

Introduction

Architecture

  • SE and FE

Subcube

  • Granularity
  • Single grain/mixed grain
  • Slice
  • DataIDs
  • OR slice
  • Slice below granularity
  • Arbitrary shape

SE

  • Architecture
  • Sonar
    • Cache Ratio

SE Measure group Cache

  • Cache registry
  • Aggregate & Filter from cache
  • Prefetching
  • Cache sharing
    • Slice below granularity
    • Visual totals
    • Subselects
    • Arbitrary shapes
  • Spliting measure group
    • MDX stiching
  • CREATE CACHE

SE query plan

  • Aggregations
  • Scan vs. indexes
  • Bitmap indexes
  • Segment compression
  • Arbitrary shapes
    • Normalize mixed grain with Descendants
  • Partitions
    • Slice
    • Autoslice
    • OR slice

SE dimensions

  • Natural vs. unnatural hierarchies
  • Dimension cache

FE

  • Architecture

FE Cache

  • Global scope
    • Security
      • Dynamic
    • Username
    • Dynamic functions
    • Sprocs
      • Non deterministic functions
    • Outline calcs
  • Session/query scope
    • Visual totals/subselects
      • Hidden members
    • Calculations
  • Registry vs flat MCache
  • Subselect visual totals & overwrite history

MDX Caching techniques

  • Value caching
    • Subexpressions
    • KPI/cell security/calc properties
  • Member caching
    • Today
    • Tuple caching
    • Dynamic
  • Set caching
    • Static/dynamic/alias

FE execution

  • Sonar
  • Calc covers
    • Calculate

FE execution plans

  • Cell by cell
  • Bulk evaluation (aka block computation mode)
    • Space transformations
    • Space partitioning

Forcing block mode

  • Optimized MDX functions
    • Static literal references
  • Dense vs. sparse subcubes
    • Multiplication
    • ValidMeasure vs. IgnoreUnrelatedDimensions
    • Writing over NULLs
  • Conditional execution
    • IIF vs. IF vs. SCOPE
  • Imprecise Sonar subcubes
    • Cache Ratio
    • Hidden members
  • NON_EMPTY_BEHAVIOR
  • “Cache Policy”

Aggregate function

  • Implicit aggregations
    • Calcs on leaves
    • No cascading
  • Single tuple set
  • Arbitrary shapes
    • No NonEmpty
  • Space partitioning

Scenarios

  • Ratios
  • Comparison to previous period
  • Running sum
  • Inventory
  • Moving average

Non Empty

  • Sonar
  • SE data
  • Calculated members
  • Calculated measures
    • NON_EMPTY_BEHAVIOR
  • Calculations

Exists and Autoexists

  • Autoexist
    • cell
  • Single dimension
  • Multi-dimension
    • Position in crossjoin
  • Arbitrary shapes

MDX

Sequence

  • Bootstrapping
  • MDX Script
    • Static vs. Dynamic statements
    • Calculation properties
  • Personalization extensions

Sets

  • Iterator pipelining
  • Containers
  • Rankers
  • Enumerated set and Materialization
  • Crossjoin
    • cannot operate on a set with more than 4,294,967,296 tuples
  • Not materializable sets

Strong hierarchies

  • Attribute relationships
  • Diamond shape
  • Decoding attributes
  • Cube space
  • Non-idempotence of CurrentMember

MDX in UDM

  • Existing vs. Descendants
  • Strong hierarchies vs. User hierarchies

Calculation precedence

  • History
  • SOLVE_ORDER
  • Pass
  • Implicit aggregations
  • MDX Script order
    • CREATE MEMBER vs. CREATE
  • Outline calcs
  • Aggregate over calc measures
  • WHERE
  • Visual totals
  • SCOPE_ISOLATION

Calculated members

  • Non-aggregatable
    • Multiselect on assignment
  • Inside subcubes
    • Excel 2007

WHERE vs. subselects

  • What WHERE does
  • What subselect does
  • Implicit Exists
  • Coordinate overwrites
    • Overwrite history

Multiselect

  • WHERE
    • Existing
  • Classic
  • Subselects

Semiadditive measures

  • LastNonEmpty
    • RunningSum + LastChild
    • Manual with member caching
  • AverageOfChildren

Security

  • Dimension security
    • Orthogonal design
  • Cell security
  • Dynamic security

Dimension query

  • How to query dimension
    • Dimension cube
  • Exists
  • Filter
    • Enumeration
    • NonEmpty
    • Extract
    • Non SE materializable
  • Search
  • Internal VBA
  • LIKE

Stored procedures

  • Best practices
  • ASSP project
Published Thursday, September 06, 2007 12:05 AM by mosha
Anonymous comments are disabled
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement