Lookup Dimension

[dbadwh]

We have lot of lookup values in the source system, which we have combined and made it as a one junk dimension. The dimension is linked to other dimension through the fact less fact tables. I have two questions here:
1. How to handle the SCD in case of status values?
2. Do anybody foresee any performance issues?

[ngalemmo]

The approach you describe has a lot of potential issues, far too numerous to get into here. Suffice it to say, it is a bad idea.

Rethink what you are trying to do.

[dbadwh]

In one of the posts in this forum, you have mentioned, it can be done as junk dimensions based on the situation.
See the URL: http://forum.kimballgroup.com/t1406-lookup-tables-to-dimension?highlight=lookup

[Jeff Smith]

I have junk dimensions that pull information from reference tables. The elements of the junk dimensions might include flags or codes. I will create reference tables that translate the flags into a description that works well with a report. Same with codes.

There is a danger with this approach. The way the junk dimension gets populated/updated is that it takes the combinations of the elements from the transaction system. If the description of a code in the reference table gets changed, it will only get updated in the rows of the junk dimension that were in the source data for that load. Rows with combinations of junk elements not in the source will not get updated.

A way around this is to use the junk dimension as a source for the junk dimension along the the transaction data. Join codes, flags, etc. from the Junk dimension back to the lookup tables and load it back into the Junk Dimension. I do it with a UNION and have 1 load process.

Any dimension that uses look up tables go through this process in my loads.

[ngalemmo]

In one of the posts in this forum, you have mentioned, it can be done as junk dimensions based on the situation.
See the URL: http://forum.kimballgroup.com/t1406-lookup-tables-to-dimension?highlight=lookup

There are two things that concern me:

1. You are only talking about one dimension. Junk dimension populations are based on unique combinations of the attributes. What goes into a junk dimensions depends on cardinality and correlation.

2. That you need a factless fact table. Why?

[dbadwh]

Sorry, I am not able to get your point.

[ngalemmo]

From your original post:

We have lot of lookup values in the source system, which we have combined and made it as a one junk dimension. The dimension is linked to other dimension through the fact less fact tables.

There isn't much information to go on...

You have 'a lot' of attributes you put into one junk dimension. How many is 'a lot'? What is the nature of those attributes? How many unique combinations of values do you expect? What is their relationship to the measures?

Why do you link the junk dimension to other dimensions?

The post you quoted only said junk is an option and it depends on the circumstances. What are your circumstances?

[rbs100]

Apologies for the delayed revert. The lookup table will consists of Surrogate Key,lookup id and description for the lookup. The table will contain the values for the lookups from OLTP system.

[ngalemmo]

I was confused by the original description.

You don't usually use such tables in the data warehouse other than as background tables to support ETL and some BI tools. In other words, you do not snowflake codes and descriptions off dimensions, instead you populate the code and description fields in the dimension itself based on lookups into the support tables during the ETL process. You also use these tables to detect changes in a description so you can update related dimension tables with the changed description.

You also use such tables to aid a BI tool in presenting a list of values. In most cases you can redirect tools to use these smaller tables rather than performing a distinct query on the dimension table itself.

[Sponsored content]