SQLBits 8 Session Notes #1

Advanced Analysis Services Best Practices

Marco Russo
marco@sqlbi.com

Relational schema

    Snowflake schemas not always great for performance, because the JOIN in A/S is not very smart

    Star schemas don\’t do these types of JOINs
        use views to generate the star schema
        eliminates ambiguity

Data source decoupling
    use views to decouple layers
        views at database layer so they\’re visible to the DBA
        one view for each dimension
        columns in the view should be the same as the attributes in the dimension
        avoid calculated columns in the view
        use LEFT JOIN over INNER JOIN (?)
       

Dimensional Patterns
    surrogate keys independant from application keys
        -but: they don\’t have semantic value, so they should not be visible to the end-user : AttributeHierarchyVisible = False

    attribute keys
        use unique keys, again hidden from the end-user


NOTE: Get the slide deck and presentation off the website, there\’s more there than I can write down now.

Grouping
    automatic or manual ?
    manual gives more control, and can be built into a view for the dimension
    use business logic in the view

Banding
    categorise a measure according to a range of values
    Direct relationship or indirect relationship

SCD
    type1
        process update doesn\’t detect duplicate key errors
            process full would fail if there were duplicates
        flexible aggregations need to be regenerated

    type2
        process add instead of process update
        attribute relationships always Rigid

Junk Dimensions
    OK I missed that….

Parent – Child hierarchies
    if performance is good enough, stick with them
    -but: one PCH per dimension
        must use attribute key
        unique name for a member includes surrogate keys (which we don\’t want to show tot the end user.
    -cf: Parent – Child naturalizer from Thursday

Role Dimensions
    look this up, he\’s very fast…
    -alternative: create a view for each dimension (duplicate dimension)

Drill-through
    BIDS won\’t let you change the column order
        -but: you can edit the XML directly to reorder the columns (but don\’t ever open it in a GUI again)
    not supported on calculated members

Calculation Dimension
    select [name] from [delegate] where [willToLive] > 0
        (0 rows affected)
    Time Intelligence Wizard
        there are limits in BIDS. Perhaps it\’s not a great tool 🙂
    remove [All] member (IsAggregatable = false)

Conclusion
    Avoid MDX like the plague
    Don\’t tell the end-user anything they don\’t need to know
    Calculate values in the ETL layer