Additional information on facts

View previous topic View next topic Go down

Additional information on facts

Post  ObjectiveC on Tue Mar 22, 2011 7:23 am

Hello everyone,

I was wondering what to do with additional information on facts. At the moment I am putting them in a separate dimension, but the problem with this is that the dimension will become as large as the fact_table is.

How would you go about this ?


Cheers !

ObjectiveC

Posts : 25
Join date : 2011-03-18

View user profile

Back to top Go down

Re: Additional information on facts

Post  BoxesAndLines on Tue Mar 22, 2011 9:46 am

Your question is too vague to provide any sort of meaningful answer. Please be more specific.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Additional information on facts

Post  ObjectiveC on Tue Mar 22, 2011 10:06 am

Hello,

Well, I have a fact_table with additives like "amount_produced" but I also have attributes like "name_of_phase" which is in this case a name. So I can see the "amount_produced" for a given "phase". Now I have all the additives like "amount_produced" in a fact_table, but what should I do with attributes like "name_of_phase" ?

ObjectiveC

Posts : 25
Join date : 2011-03-18

View user profile

Back to top Go down

Re: Additional information on facts

Post  BoxesAndLines on Tue Mar 22, 2011 11:23 am

Descriptive attributes go into dimensions.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Additional information on facts

Post  Jeff Smith on Tue Mar 22, 2011 2:05 pm

When you say, "At the moment I am putting them in a separate dimension", does that mean that all of the attributes are going into a single dimension?

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Additional information on facts

Post  ObjectiveC on Tue Mar 22, 2011 2:59 pm

BoxesAndLines wrote:Descriptive attributes go into dimensions.
But is this not bad on performance, since the dimension will be as large as the fact_table (millions of records).


Jeff Smith wrote:When you say, "At the moment I am putting them in a separate dimension", does that mean that all of the attributes are going into a single dimension?
Yes, all the descriptive attributes that are directly related to the fact_table attributes are put in a separate dimension. Which concerns me, because I don't think that's the way to go, since the dimension will become as large as the fact_table which might/will effect, negatively, on performance.


Any suggestions ?

ObjectiveC

Posts : 25
Join date : 2011-03-18

View user profile

Back to top Go down

Re: Additional information on facts

Post  LAndrews on Tue Mar 22, 2011 3:05 pm

I think the question is still to vague.

What is the grain of the fact table?

The way you describe the issue, "Name of Phase" has a 1:1 relationship with the facts.... are you saying that each fact record is a unique phase, and that your business process has millions of unique phases?


LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Additional information on facts

Post  ObjectiveC on Tue Mar 22, 2011 3:25 pm

Hello LAndrews,

Bear with me, I'm still new to all of this and apparently I'm doing a bad job explaining stuff, but to answer your question the facts and the descriptive attributes are definetly a 1:1. The grain of the fact_table is an ingredient that is used in a recipe (recipe -< ingredients).

The ingredient or recipe is never updated so it's always an insert (don't know if that makes any difference).

ObjectiveC

Posts : 25
Join date : 2011-03-18

View user profile

Back to top Go down

Re: Additional information on facts

Post  LAndrews on Tue Mar 22, 2011 3:36 pm

No need to apologize, dimensional modelling is a different method of looking at things, it takes time and experience to get a solid grasp of ... thats why this forum is here.

The more information you can share - the easier it will be for people to add some insight. I'd start with describing your fact, list the dimensions you are sure of, and then provide some examples of those you are not sure of.

So, if each fact represents the use of an ingredient in a recipe - do you have any examples of "Phase"?

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Additional information on facts

Post  ObjectiveC on Tue Mar 22, 2011 3:54 pm

It's definitely harder than I thought, but to give an example, a phase could be "dosing". So in this case this would mean that the ingredient was in a "dosing phase" when it added some "amount of .." (fact) .

Examples will be added tomorrow since I have no access to the db at the moment.

EDITED: I'm not allowed to add the information, but I'll try to create an example and post it here.


Last edited by ObjectiveC on Thu Apr 07, 2011 4:38 pm; edited 1 time in total

ObjectiveC

Posts : 25
Join date : 2011-03-18

View user profile

Back to top Go down

Re: Additional information on facts

Post  Jeff Smith on Tue Mar 22, 2011 4:11 pm

All of the attributes are going into 1 dimension table and the dimension table has the same number of rows as the fact.

Instead of one large dimension, break it up into multiple smaller dimension tables. If you have one large junk dimension, then go back the the source and start selecting different combinations of attributes until you arrive at combinations that are reasonable.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Additional information on facts

Post  John Simon on Tue Mar 22, 2011 5:36 pm

Is there any reason why the ingredients cannot be in one dimension, the phase in another dimension etc?

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Additional information on facts

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