Quick Tip – SSAS Degenerate Attributes

Not a huge amount of technical detail in this one… just a simple tip for SSAS because I’ve seen it more times now than I can remember and ultimately it boils down to a fundamental misunderstanding of technical architecture and what the role of SSAS plays in that architecture. A common response is that SSAS is “doing something weird”, or “SSAS is rubbish and not fit for purpose” leading to awkward overly complex work arounds. ┬áSSAS is awesome if you use it for what it’s meant for.

Don’t put degenerate attributes in a SSAS cube

 

A degenerate attribute has a unique attribute value for every row in a fact table. A cube is for browsing aggregates and drilling down into a narrow set of detail.

  • How is a degenerate useful for a person to look at holistically or to analytically slice and dice measures? It isn’t. What will most likely happen is that a user will drag it onto ad-hoc report and then get very frustrated waiting for it to return
  • Secondly the cube will take a very very long time to process. Particularly with column storage and especially if it’s a string data type. Do you really want to process millions of unique values into a distinct column store? Is that going to achieve anything?

If you really must put degenerate information into a BI model:

  • Bucket them up into bandings so they can be aggregated
  • Don’t include the leaf level detail as an attribute or column… Use drill through or a report action to navigate to low level transactions on a narrow selection of data… Cubes are for aggregates and drilling down into a narrow set of detail.
  • If they’re needed for a relationship something has gone wrong in your Kimball model. Rethink the model so it provides detail for drill through but binds on something that can be better aggregated and grouped.

 

Quick Tip – SSAS Tabular Referential Integrity

SSAS tabular is a tad more loosely coupled than multi-dim. A quick and easy way to determine referential integrity violations on columns is to use the $system.DISCOVER_STORAGE_TABLES. This DMV query will return a rowset with the table details and a column called RIVIOLATION_COUNT that indicates the number of rows with a referential integrity violation.

select * from $system.DISCOVER_STORAGE_TABLES

SSAS RI Violation