Experts,

Below are counts for DIM and FACT tables.

Can somebody please help me with the big volume of data under Dimension tables versus FACT tables?

What are the drawbacks and how DIM tables with big volume of data can hurt in Data Warehousing?

Also, in near future we are target for cubes designing above this DIM & FACT tables.

Your thoughts/responses are much appreciated.

Thanks in a

Hi gk1393,

I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.

Thank you for your understanding and support.

Regards,
Katherine Xiong

We have worked with huge dimensions before

We mostly go for incremental approach for loading dimensions (in case it has audit columns etc)

Otherwise you've to go for snapshot approach

Another question is how volatile (frequency of change) is your dimension data?

Hi gk1393, 

Incremental loading is fundamental when facing these volumes you are showing. But, besides the ETL considerations, you may want to re-think about the design of this datawarehouse. 

Are these dimensions actually dimensions? In some businesses dimensions are very big, but is not that common to find many dimensions bigger than the facts (at least not in a mature datawarehouse). 

Building dimensions on top of these big tables presents some performance problems, both processing and querying the cube. Disabling attributes for processing, removing extra text attributes that are not used in aggregations and reducing data type sizes (if you can store data in a smallint data type don't use bigint, for example) are useful techniques to mitigate these drawbacks. 

But, again, I'd think about the datawarehouse design itself to check if these cardinalities make sense within your business needs. 

Regards.

Pau