Large dimension tables

[Former Member]

I am trying to get a grip with how a dimension table can have more records than the fact table since the key for the dimension table is DIMID. Can someone give me a practical example? Thanks

[Former Member]

Niten,

If the dimension table have chars whose relation is m:n and not 1:n, then the dimension tables will have more records. Suppose you take a document number, assume that lakhs of documents are created everyday. Then the dimension obviously increases. If some other chars are there with document number the dimension table size iincreases gradually which is not good for performance.

In such a case, you make document number as a line item dimension.

[Former Member]

Thanks for this lets work with the Doc No example.

1) I have a cube with 4 dimensions - the fourth dimension contains material no, doc no and plant, the key for this dimension table is DIMID (system generated ??) and each record in the Dimension contains the corresponding SID values for mat no, doc no and plant

2) My first load of data into the cube - 1000 records

3) For each of these 1000 records a DIMID is generated in dimension 4 and the corresponding values of the 3 SIDs are retained as part of these records

4) My next load is a delta 200 lines - 150 new records and 50 updated records - in this case 150 new DIMIDs are generated and the 3 SIDS are retained whereas the original 50 updated records do not have new DIMIDs generated

Is how I described the scenario correct

[Former Member]

In the fourth step, dim id's are not created but only the SID's. Apart from that ur scenario is fine.

[Former Member]

Thanks Venkat, I am still not clear.

Pre step 4 I have 1000 DIMID which represent all records in the fact table since it is the first load

Post step 4 I have 200 transactions which are on a different calendar day so 200 new dimids generated in terms of time (correct)

Post step 4 for Dimension 4 I have 50 DIMIDs which represent the SID combination and 150 which are not represented so for these there would be 150 dimids generated is that not the case and if not could you explain why