Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

One-to-one dimension to fact

2 posters

Go down

One-to-one dimension to fact Empty One-to-one dimension to fact

Post  wjordan Wed May 12, 2010 8:48 am

I have inherited a design with a very wide fact table. It includes many timestamps, long varchar descriptions, booleans, as well as legitimate FKs to dimensions and numerous measures. There are also numerous dates, but I have a DATE_DIM, and consider the dates as legitimate FKs to to that dim. I have considered a dim table for booleans and then one dim table that has all the varchars and timestamps as attributes. However, that new dim would be one-to-one with the fact. And the varshars would not work well in a junk dim, as they are free-form descriptions (not to search by, but to display on BI reports). Is the one-to-one dim the way to go or am I better leaving these timestamps and varchars in the fact as degenerate dims? This is Oracle. The current fact has 200 columns and 100,000,000 rows.


Posts : 6
Join date : 2010-05-05

Back to top Go down

One-to-one dimension to fact Empty Re: One-to-one dimension to fact

Post  ngalemmo Wed May 12, 2010 12:47 pm

You have your work cut out for you.

You need to break things up a bit, and you may want to consider moving the text to a junk dimension. Even though the text is free-form, people are creatures of habit and, depending on the nature of the text, there may be more commonality than you may think.

When I have implemented a junk text dimension in the past, I build the table with a surrogate primary key, a hash number as a non-unique alternate key and the text field. I have two indexes, one on the PK and the other on the hash number ( a pseudo natural key). The range of the hash number should be a large as resonably possible, a 32 bit CRC hash of the text is best (the Oracle hash function will let you use 2^30 -1 as the upper limit). The hash will not be unique, but it avoids having to index the text and is close enough so that doing a lookup to find an existing text is efficient.

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

Back to top Go down

Back to top

- Similar topics

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