Is it a common practice that we use one Parameter file for one project for all workflows?
May 27, 2017
Why denormalized data is there in Data Warehosue and normalized in OLTP?
May 29, 2017
Show all

Is there any limit on number of Dimensions as per general or best practice for a Data Warehouse?



No, there is no limit as such. The whole purpose of the dimensions which you design should be to hold the key reference data of the business which will help the fact numbers to be more meaningful. And, if you are following any specific structure of snow flake or star schema or a combination then you also must see how meaning full these are.

There will be cases where dimensions will be created as detailed as possible with multiple hierarchies (levels) which can be drilled down but if the business does not use it, then it’s of no use. So, there should also be a check at regular intervals to see how much of the existing number of dimensions are being used or useful and if there should be any remodeling done to the existing ones or removing some of them or merging couple of related dimensions into one etc. And, of course all of this should happen along with the business consent and if that will improve the overall process.

Another viewpoint to have the number of dimensions meaning full is to have them within the below types, so that we would know what is meant for what. Usually, this is not required but if you have to have the grouping of the dimensions then you can have them into below groups/sections for easy identification.

  • Role-playing dimensions
  • Junk dimensions
  • Type 2 Slowly Changing Dimensions (SCD)
  • Conformed (common) dimensions

Well, immaterial of what dimensions are used in your project and how many ever number there is one which is always present in almost all Data warehouse implementations and it’s the date dimension. Some date dimensions will/might be very simple (based on the business needs), with attributes for date, month, and year. Other date dimensions might be more complex, with the following sets of attributes:

  • Week-ending dates (e.g. for Sunday to Saturday ranges)
  • Regular calendar month/quarters and Fiscal month/quarters
  • Week or period numbers
  • Attributes for seasonality (i.e., events whose dates vary across years, such as Lent weeks, Black Friday, etc.)

Last but not the least, there are certain restrictions in the reporting tools on the number of dimensions to be used for a dashboard or a cube and this varies based on the BI tools.