I'd like to know a list of things on what to look out for (tech gotchas!) while developing an Oracle Star Schema...
data warehouse design.
- Conform to the dimensional rules: a fact table linked to hierarchical dimensions that contains only measures and links to those dimensions, Fact-dimension relationships only and proper dimension hierarchicalization (1-1 or 1-many only). Rule violations will not be permissable to OLAP tool interfaces.
- Star means 1 table per dimension
- During modeling, make it "snowflake" not star so you are clear about the hierarchies within the dimensions
- Don't start star schema modeling by modeling the fact tables. Fact tables naturally fall out of the process of modeling business questions. Common measures = 1 fact. Start by modeling the business questions, the model the measures against the dimension levels.
- Don't be a slave to star schema modeling. There's a place for normalized modeling in a data warehouse too. Also consider the management benefits of snowflake modeling.
Read expert advice on a star schema design for BI.