text to numbers transformation

[Anonymous]

Hi,

There are two types of data model I'm choosing between. Could you advise which one is more efficient in terms of calculation speed?

1) A big fact table with 6 or more dimensions containing text values (some text values can be as long as 50 symbols). And 10 more fields containing measures.

2) The same big fact table (from point 1) on which some manipulations have been done. The distinct text values have been autonumerated. So now numbers are used instead of text values. But on the forefront it is still neccessary to show text values, but not numbers. That's why 6 small tables which show how numbers correspond to text values (for each of the six dimensions) have been linked to the big fact table. So, instead of one table we have got a star scheme model. The good thing is that there is no text values in the big fact table but numbers only. On the other hand we have autonumerated 6 text fields and thus added 6 extra numeric fields which were unneccessary in point 1.

Thank you in advance,

Larisa

[jagan · Accepted solution]

Hi,

I think if you are getting all the dimensions in the same table then it is better to use single table in the datamodel instead of splitting it into multiple dimension tables by using keys.  The calculation speed will be more in this case because there is no joins required during the runtime since all columns are in the same table.

You cannot this difference in a small dataset, but if the data is huge then you can see a minute difference.

Hope this helps you.

Regards,

Jagan.