Columns to Rows Issue in Dimension

View previous topic View next topic Go down

Columns to Rows Issue in Dimension

Post  ohmycamote on Thu Jul 14, 2011 5:04 pm

This is my DimCustomer:

SKey NKey FOOD1 FOOD2 COLOR1 COLOR2
1 111CHI AMRED BLUE
2 222 AM ITA ORANGE YELLOW

We want to be able to tell how many customers at any point in time have RED as favorite color, or ITA as favorite food.
My guess is that this probably needs to be a Factless-Fact table:

SKey FKey K V
1 1FOOD CHI
2 1FOOD AM
3 1COLOR RED
4 1COLOR BLUE
5 2 FOOD AM
6 2 FOOD ITA
7 2 COLOR ORANGE
8 2 COLOR YELLOW

Is that the best way to approach this problem?

ohmycamote

Posts : 14
Join date : 2011-07-05

View user profile

Back to top Go down

Re: Columns to Rows Issue in Dimension

Post  BoxesAndLines on Thu Jul 14, 2011 5:10 pm

Or a bridge table. You could cram all the colors into one column, but I always cringe when I see that.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Columns to Rows Issue in Dimension

Post  ohmycamote on Fri Jul 15, 2011 1:44 pm

I'm sorry but I am quite new to BI, so what would the bridge look like in this case?

Thank you for your time.

ohmycamote

Posts : 14
Join date : 2011-07-05

View user profile

Back to top Go down

Re: Columns to Rows Issue in Dimension

Post  apermag on Fri Jul 15, 2011 4:27 pm

why donīt you launch a simple query against the dimension and filtering by the criteria you want?
Unless you have any other type of specific reporting requirements, I wouldnīt do anything but a quick query.

apermag

Posts : 17
Join date : 2011-06-28

View user profile

Back to top Go down

Re: Columns to Rows Issue in Dimension

Post  ohmycamote on Fri Jul 15, 2011 4:34 pm

I just found this reply from ngallemo:
http://forum.kimballgroup.com/t689-bridge-table-for-patient-diagnosis#2893

I assume you are referring to the same thing?

ohmycamote

Posts : 14
Join date : 2011-07-05

View user profile

Back to top Go down

Re: Columns to Rows Issue in Dimension

Post  BoxesAndLines on Fri Jul 15, 2011 5:06 pm

Yes. Although I am still not sure what you are trying to build. A better approach is to tell us what you want and then we can help with the how.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Columns to Rows Issue in Dimension

Post  hang on Fri Jul 15, 2011 5:34 pm

In the first table of your initial post, why do you have two columns for food and color? what's the difference between FOOD1 and FOOD2?

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Columns to Rows Issue in Dimension

Post  ohmycamote on Mon Jul 18, 2011 9:38 am

Hang,

My original DimCustomer looks like that with two FOOD and two COLOR fields because the source DB stored it like that.
That's why I am asking what is the best practice when storing such data in the data warehouse DB.

Thank you

ohmycamote

Posts : 14
Join date : 2011-07-05

View user profile

Back to top Go down

Re: Columns to Rows Issue in Dimension

Post  cjrinpdx on Mon Jul 18, 2011 4:34 pm

What you have is a many-to-many relationship between dimensions, i.e. customer to color, and customer to food. You could create a factless fact table, or combine the color and food dimensions into a junk dimension and make it an outrigger off the customer employee.

cjrinpdx

Posts : 51
Join date : 2011-07-14
Location : Portland, OR

View user profile

Back to top Go down

Re: Columns to Rows Issue in 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