More Dimension Records than Fact Records
I am in the process of reorganizing the dimension tables of a certain cube because the ratios of dimension to fact records were way off. In many cases the dims were 98% of the fact table. So I pulled many chars out of some dims and made them line item in some cases. It appears to me that the dims should now be smaller. However, almost every dim is now much larger than the fact table. Many are almost twice as large. How is this possible? What am i missing? I dont have the entire dataset loaded yet but the ratios so far and much worse. Is it possible that the ratios will start balancing out as i increase the volumes? Any thoughts?
Olivier Cora replied
It cannot be that a dimension table has more entries than in the fact tables except if some dim entries aren't used by a fact record (can be cleaned via RSRV)
- you are getting your numbers wrongly: open SE11 with the DIM table and get its number of records. Get the number of entries of BOTH fact tables (F and E).
If you're seing that from statistics, make sure they're updated! The best is to get the current from SE11.
- you deleted the data of the ICube and didn't delete the dimensions data; when right clicking a cube and delete data the system prompts if you want to delete dimensions entries as well; if you answered NO the dims will still be filled.
Your dim should be empty anyway right after your changes since you have reorganized your cube model (the fact and dim should be totally empty).
hope this helps....