We are in the phase of warehouse designing. What are the recommendations and restrictions for FACT TABLE and DIMENSIONS? What other things should be taken care of?
The choice of whether a table is a dimension or a fact are pretty clear. Dimensions are tables that contain attributes that describe an object. A fact is a table that collects quantitative information, such as sales. So I don't really see any restrictions.
If we think in terms of functionality there are always physical and data limits. These are where you will find limitations. When creating facts and dimensions you must consider the ultimate size of the object as well as access paths. You will generally add indexes to help you with both.
From a logical design perspective, there should be no restrictions. Build based on current and future requirements. You must make sure you build as much flexibility as possible into your design so that you can allow for future enhancements without having to rebuild your data.
Dig Deeper on Oracle data warehousing
Related Q&A from Ian Abramson
We are working on the development of a datamart (in 9i) which takes data from two source systems. Since this is a transaction system, there is a lot ... Continue Reading
I am new to Oracle's ETL tool and I need information on data migration using Oracle ETL. Continue Reading
How can PL/SQL be best used for the ETL process? Continue Reading