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

Columns to Rows Issue in Dimension

5 posters

Go down

Columns to Rows Issue in Dimension Empty Columns to Rows Issue in Dimension

Post  ohmycamote 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

Back to top Go down

Columns to Rows Issue in Dimension Empty Re: Columns to Rows Issue in Dimension

Post  BoxesAndLines 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.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Columns to Rows Issue in Dimension Empty Re: Columns to Rows Issue in Dimension

Post  ohmycamote 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

Back to top Go down

Columns to Rows Issue in Dimension Empty Re: Columns to Rows Issue in Dimension

Post  apermag 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

Back to top Go down

Columns to Rows Issue in Dimension Empty Re: Columns to Rows Issue in Dimension

Post  ohmycamote 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

Back to top Go down

Columns to Rows Issue in Dimension Empty Re: Columns to Rows Issue in Dimension

Post  BoxesAndLines 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.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Columns to Rows Issue in Dimension Empty Re: Columns to Rows Issue in Dimension

Post  hang 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

Back to top Go down

Columns to Rows Issue in Dimension Empty Re: Columns to Rows Issue in Dimension

Post  ohmycamote 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

Back to top Go down

Columns to Rows Issue in Dimension Empty Re: Columns to Rows Issue in Dimension

Post  cjrinpdx 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

Back to top Go down

Columns to Rows Issue in Dimension Empty Re: Columns to Rows Issue in Dimension

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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