Lookup Dimension

View previous topic View next topic Go down

Lookup Dimension

Post  dbadwh on Thu Dec 22, 2011 1:47 pm

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?

dbadwh

Posts : 31
Join date : 2011-09-30

View user profile

Back to top Go down

Re: Lookup Dimension

Post  ngalemmo on Thu Dec 22, 2011 3:04 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Lookup Dimension

Post  dbadwh on Fri Dec 23, 2011 2:07 am

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

dbadwh

Posts : 31
Join date : 2011-09-30

View user profile

Back to top Go down

Re: Lookup Dimension

Post  Jeff Smith on Fri Dec 23, 2011 2:56 pm

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Lookup Dimension

Post  ngalemmo on Sun Dec 25, 2011 3:21 am

dbadwh wrote: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?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Lookup Dimension

Post  dbadwh on Tue Dec 27, 2011 1:51 am

Sorry, I am not able to get your point.

dbadwh

Posts : 31
Join date : 2011-09-30

View user profile

Back to top Go down

Re: Lookup Dimension

Post  ngalemmo on Tue Dec 27, 2011 11:40 am

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?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Lookup Dimension

Post  rbs100 on Wed Jan 04, 2012 3:11 am

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.

rbs100

Posts : 12
Join date : 2011-09-14

View user profile

Back to top Go down

Re: Lookup Dimension

Post  ngalemmo on Wed Jan 04, 2012 11:21 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Lookup Dimension

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum