Type II dimension joins

Question

I have the following table lookup table in OLTP

CREATE TABLE TransactionState
(
    TransactionStateId INT IDENTITY (1, 1) NOT NULL,
    TransactionStateName VarChar (100)
)

When this comes into my OLAP, I change the structure as follows:

CREATE TABLE TransactionState
(
    TransactionStateId INT NOT NULL, /* not an IDENTITY column in OLAP */
    TransactionStateName VarChar (100) NOT NULL,
    StartDateTime DateTime NOT NULL,
    EndDateTime NULL
)

My question is regarding the TransactionStateId column. Over time, I may have duplicate TransactionStateId values in my OLAP, but with the combination of StartDateTime and EndDateTime, they would be unique.

I have seen samples of Type-2 Dimensions where an OriginalTransactionStateId is added and the incoming TransactionStateId is mapped to it, plus a new TransactionStateId IDENTITY field becomes the PK and is used for the joins.

CREATE TABLE TransactionState
(
    TransactionStateId INT IDENTITY (1, 1) NOT NULL,
    OriginalTransactionStateId INT NOT NULL, /* not an IDENTITY column in OLAP */
    TransactionStateName VarChar (100) NOT NULL,
    StartDateTime DateTime NOT NULL,
    EndDateTime NULL
)

Should I go with bachellorete #2 or bachellorete #3?

Answer 1

By this phrase:

With the combination of StartDateTime and EndDateTime, they would be unique.

you mean that they never overlap or that they satisfy the database UNIQUE constraint?

If the former, then you can use the StartDateTime in joins, but note that it may be inefficient, since it will use a "<=" condition instead of "=".

If the latter, then just use a fake identity.

Databases in general do not allow an efficient algorithm for this query:

SELECT  *
FROM    TransactionState
WHERE   @value BETWEEN StartDateTime AND EndDateTime

, unless you do arcane tricks with SPATIAL data.

That's why you'll have to use this condition in a JOIN:

SELECT  *
FROM    factTable
CROSS APPLY
        (
        SELECT  TOP 1 *
        FROM    TransactionState
        WHERE   StartDateTime <= factDateTime
        ORDER BY
                StartDateTime DESC
        )

, which will deprive the optimizer of possibility to use HASH JOIN, which is most efficient for such queries in many cases.

See this article for more details on this approach:

• Converting currencies

Rewriting the query so that it can use HASH JOIN resulted in 600% times performance gain, though it's only possible if your datetimes have accuracy of a day or lower (or a hash table will grow very large).

Since your time component is stripped of your StartDateTime and EndDateTime, you can create a CTE like this:

WITH    cal AS
        (
        SELECT CAST('2009-01-01' AS DATE) AS cdate
        UNION ALL
        SELECT DATEADD(day, 1, cdate)
        FROM   cal
        WHERE  cdate <= '2009-03-01'
        ),
        state AS
        (
        SELECT  cdate, ts.*
        FROM    cal
        CROSS APPLY
                (
                SELECT  TOP 1 *
                FROM    TransactionState
                WHERE   StartDateTime <= cdate
                ORDER BY
                        StartDateTime DESC
                ) ts
        WHERE   ts.EndDateTime >= cdate
        )
SELECT  *
FROM    factTable
JOIN    state
ON      cdate = DATE(factDate)

If your date ranges span more than 100 dates, adjust MAXRECURSION option on CTE.

Answer 2

Please be aware that IDENTITY(1,1) is a declaration for auto-generating values in that column. This is different than PRIMARY KEY, which is a declaration that makes a column into a primary key clustered index. These two declarations mean different things and there are performance implications if you don't say PRIMARY KEY.

Answer 3

You could also use SSIS to load the DW. In the slowly changing dimension (SCD) transformation, you can set how to treat each attribute. If a historical attribute is selected, the type 2 SCD is applied to the whole row, and the transformation takes care of details. You also get to configure if you prefer start_date, end_date or a current/expired column.

The thing to differentiate here is difference between the primary key and a the business (natural) key. Primary key uniquely identifies a row in the table. Business key uniquely identifies a business object/entity and it can be repeated in a dimension table. Each time a SCD 2 is applied, a new row is inserted, with a new primary key, but the same business key; the old row is then marked as expired, while the new one is marked as current -- or start date and end date fields are populated appropriately.

The DW should not expose primary keys, so incoming data from OLTP contains business keys, while assignment of primary keys is under control of the DW; IDENTITY int is good for PKs in dimension tables.

The cool thing is that SCD transformation in SSIS takes care of this.