0

I have a dimension with a column called 'Type'. There is a list of default 'Types' in this dimension.

However, this 'Type' column can be overriden. i.e. each user can replace the default value with their own value. So in theory there could be a different type for each user. Or multiple users could be using the same type.

What is the best way of doing this? Add a 'Type_User_Value' column in the fact table? Or how would I do this in the dimension?

Thanks!

  • 1
    I add a Type: sprocket. The fact indicates there were 100 of these. You then modify sprocket to become "Schprocket". There's still 100 things. Are you saying that it should report Sprocket to me, Schprocket to you? What about the third person, what value do they see? Would you ever see both Sprocket and Schprocket based on the effective date? – billinkc Feb 18 '14 at 13:46
  • Yes. The 3rd person could see Sprocket or their own value. Sprocket or Schprocket would be visible depending on date selected. So default might be replaced after March. Before march, Sprocket would be visible. After march, Schprocket is visible. – K09 Feb 18 '14 at 14:48
1

Sounds like you've already implemented some form of Slowly Changing Dimension, dealing with times, but need to also deal with user preferences as well? For ease, I'm going to use Type 2 SCDs and assume temporal changes, but you could obviously swap those out for a more appropriate approach as your specific solution requires. I'm also going to imagine the relevant parts of your structure completely abstractly in MS SQL so I can also refer to this SQL Fiddle later:

The example of an abstract system without any form of SCD involves the following structures and forms our base case:

CREATE TABLE dbo.DimFoo
(
    DimFoo_PK       TINYINT IDENTITY( 1, 1 ) NOT NULL,
                        PRIMARY KEY ( DimFoo_PK ),
    FooName         NVARCHAR( 5 )
);

CREATE TABLE dbo.FctBar
(
    FctBar_PK       SMALLINT IDENTITY( 1, 1 ) NOT NULL,
                        PRIMARY KEY ( FctBar_PK ),
    DateCreated     DATE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    DimFoo_FK       TINYINT NOT NULL, FOREIGN KEY ( DimFoo_FK )
                        REFERENCES dbo.DimFoo ( DimFoo_PK )
);

In this example, the fact table contains two entries, each keyed to the dimension table. When queried, both entries show the dimension value to be "Sprocket". The requirement handed down is that in 2010, the definition of "Sprocket" changed to "Schprocket," giving way to the need for an SCD to be implemented. In the academic example, often these SCDs emerge through a replacement of the dimension table, but in practice, I've found it largely preferable to simply add SCDs to the structure and re-key the necessary fact tables. This allows use of the original dimension table as form of master look-up and a continued entry point for new dimension values which can then propagated into the SCD and fact table.

CREATE TABLE dbo.ScdFoo
(
    ScdFoo_PK       TINYINT IDENTITY( 1, 1 ) NOT NULL,
                        PRIMARY KEY ( ScdFoo_PK ),
    DimFoo_FK       TINYINT NOT NULL, FOREIGN KEY ( DimFoo_FK )
                        REFERENCES dbo.DimFoo ( DimFoo_PK ),
    FooName         NVARCHAR( 16 ),
    DateStart       DATE NOT NULL,
    DateEnd         DATE
);

With this structure in place, we can run an update as outlined in the simple example of a Type 2 SCD, making sure to then drop the old foreign key reference from the fact table for the purpose of retaining normalization levels. The new query pulls the expected name for each entry in the fact table, now reflecting changes in time.

At this point, I believe your question is largely based around the idea that a number of specific users are also interested in seeing this same fact data keyed differently. In the vein of how this has been illustrated thus far, I assume the existence of another table for storing user profile information, which I will define as follows:

CREATE TABLE dbo.UserProfile
(
    UserProfile_PK   TINYINT IDENTITY( 1, 1 ) NOT NULL,
                         PRIMARY KEY ( UserProfile_PK ),
    Code             NVARCHAR( 8 )
); 

An additional requirement detail is required at this point, either way - do the users which are interested in seeing their own definitions wish to do so per time slice ( as already expressed in the SCD ) or by value ( as originally stored in the dimension from the base case )? In either solution, this can be accomplished in a simple manner through a one-to-many relationship between the user profile table and the original dimension table, then queried as shown in the original SQL Fiddle, or alternatively by simply swapping the reference from the original dimension to the slowly changing dimension instead.

CREATE TABLE dbo.DimUserProfileFoo
(
    DimUserProfileFoo_PK
                     TINYINT IDENTITY( 1, 1 ) NOT NULL,
                         PRIMARY KEY ( DimUserProfileFoo_PK ),
    UserProfile_FK   TINYINT NOT NULL, FOREIGN KEY ( UserProfile_FK )
                         REFERENCES dbo.UserProfile ( UserProfile_PK ),
    DimFoo_FK        TINYINT NOT NULL, FOREIGN KEY ( DimFoo_FK )
                         REFERENCES dbo.DimFoo ( DimFoo_PK ),
                         UNIQUE( UserProfile_FK, DimFoo_FK ),
    FooName          NVARCHAR( 16 ) NOT NULL
);

If your users are particularly crusty, they may wish to see their own definitions of dimensions based on their own time slices. This would simply call for the new user-keyed dimension to also be a SCD, which, sticking to the Type 2 SCDs we've used, could be queried as expressed in this SQL Fiddle.

CREATE TABLE dbo.ScdUserProfileFoo
(
    ScdUserProfileFoo_PK
                     TINYINT IDENTITY( 1, 1 ) NOT NULL,
                         PRIMARY KEY ( ScdUserProfileFoo_PK),
    UserProfile_FK   TINYINT NOT NULL, FOREIGN KEY ( UserProfile_FK )
                         REFERENCES dbo.UserProfile ( UserProfile_PK ),
    DimFoo_FK        TINYINT NOT NULL, FOREIGN KEY ( DimFoo_FK )
                         REFERENCES dbo.DimFoo ( DimFoo_PK ),
    DateStart        DATE NOT NULL,
                         UNIQUE( UserProfile_FK, DimFoo_FK, DateStart ),
    DateEnd          DATE,
    FooName          NVARCHAR( 16 ) NOT NULL
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.