Additional information on facts

[ObjectiveC]

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 !

[BoxesAndLines]

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

[ObjectiveC]

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" ?

[BoxesAndLines]

Descriptive attributes go into dimensions.

[Jeff Smith]

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?

[ObjectiveC]

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).

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 ?

[LAndrews]

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?

[ObjectiveC]

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).

[LAndrews]

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"?

[ObjectiveC]

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.

[Jeff Smith]

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.

[John Simon]

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

[Sponsored content]