Sql-server – How to make a nullable “parent” hierarchy level

dimensional-modelinghierarchyslowly-changing-dimensionsql serverssas

I'm setting up my first dimensional database with SSAS, and I have this [Materials] dimension that needs a hierarchy that goes something like this:

[PriceCode    v] --> nullable
  Price Code
  ...
[Material     v]
  Code
  AltCode
  Name
  ...
[Id           v]  --> not actually exposed as a hierarchy level
  DateInserted
  DateUpdated
  DateDeleted
  EffectiveFrom
  EffectiveTo

The problem is that the [PriceCode] attribute is nullable; the DSV has a FK between a [Materials] and a [PriceCodes] table, and [Materials].[PriceCodeId] is nullable.

Is there a way to still define a hierarchy where a nullable attribute is a parent? I've fiddled with UnkownMember and UnknownMemberName and the attribute key's NullProcessing setting, but couldn't get the dimension to process.

Bonus points if someone could confirm whether I'm approaching the slowly-changing dimension problem correctly, by making a hierarchy level based on business keys (i.e. the Code field; the natural key includes the EffectiveTo field, which is null for current image of a record), and treating the SCD metadata as a level of its own.

Best Answer

You actually have 2 questions in one question. If you create a new question for the attributes it would be neater and I'll cut/paste half of this as an answer there :)

Nullable Parent Level

You probably don't want NULLs in your OLAP dimensions, and Kimball seems to agree.

Nulls should also be avoided when we can’t provide a value for a dimension attribute in a valid dimension row. There are a several reasons why the value of a dimension attribute may not be available:

Missing Value – The attribute was missing from the source data.
Not Happened Yet – The attribute is not yet available due to source system timing issues.
Domain Violation – Either we have a data quality issue, or we don’t understand all the business rules surrounding the attribute. The data provided by the source system is invalid for the column type or outside the list of valid domain values.
Not Applicable – The attribute is not valid for the dimension row in question.

It kind of depends if you have an ETL process and a Data Warehouse or not how you should be handling them, but there are different types of 'not found'.

Think about the difference in a foreign key, one has an empty field, another has a field that's filled but the related record can't (or no longer) be found. I like to diffentiate between BLANK and DATA ERROR in my dimension.

In your example you could differentiate between 'no price code' and 'a price code I can no longer find'

If you have an ETL process with a Data Warehouse you can handle that easily in your ETL process, if you don't you would need some case statements in your DSV queries.

This question seems to reveal issues with the underlying Data Warehouse. There are arguments for and against both star and snowflake schema's but personally I tend towards a star schema, with some snowflake mixed in when necessary.
In any case the data cleansing and missing links needs to be solved in your Data Warehouse long before you reach the dsv.

Slowly Changing Dimension attributes

With regards to your Slowly Changing Dimension I don't see how the data type of hierarchies or keys in your dimension would change because the dimension somehow is SCD, that doesn't matter at all. You just need a validity rule somewhere in your ETL that gets picked up by your SSAS dimension definition (See here). But for any dimension key you create I suggest you use a surrogate key mostly because your surrogate key can be an int or bigint instead of a varchar and that could massively improve performance even for attribute keys.

Using a numeric key column instead of a string key column or a composite key will improve the performance of attributes that contain many members. This best practice is based on the same concept as using surrogate keys in relational tables for more efficient indexing. You can specify the numeric surrogate column as the key column and still use a string column as the name column so that the attribute members appear the same to end-users. As a guideline, if the attribute has more than one million members, you should consider using a numeric key.

Off course that numeric key would be a representation of 'the attribute' and not necessarily include the validity fields. The validity of the record is specified at the record in your dimension table, but as you state isn't necessary for your attribute keys.

For example this could be your dimension data

+---------------+-------+-----------+----------+
| DIMENSION_KEY |  NAME |  NAME_KEY |  CURRENT |
+---------------+-------+-----------+----------+
| 1             | tom   | 1         | y        |
| 2             | mat   | 2         | n        |
| 3             | mat   | 2         | y        |
+---------------+-------+-----------+----------+

Where you can pick dimension_key for the key of your key attribute and you could pick either name or name_key as the key of your name attribute.

Determining if it's worth the hassle for name depends on how many members your attribute will have (and your key attribute typically has most members).

In the end there isn't really any relation between the fact that you have a SCD and your decision what key is a good pick for your attribute. End user requirements make that decision for you. In the example dimension you would want all sales by mat reported under mat, and not have 2 mat's in your members when users report on that.

Related Question