First of al the original inspiration for these “Golden Rules” Series are based on the “20 GOLDEN RULES FOR REPOSITORY DESIGN” from the people at Peak Indicators. Kudos to them.
The series contains:
- RPD-Physical Layer
- RPD-Business Model Layer
- RPD-Presentation Layer
- Catalog Structure
- Report Building
- Dashboard Building
The “rules” is this article are somewhat in random order
This is always a “work in progress” and please feel free to make any suggestions!
- Clear the cache check box
Using cache should be a last resource, reconsider your data model and ETL processes first!
- Always use “Foreign Key” joins, not “Complex Joins” on the Physical Layer
If your join looks like D_DATE = TRUNC(S_DATETIME) try add a extra column S_DATE in your DWH. Any matching processing done by the BI-server costs time and you often loose the advantage of an index in your DWH.
- Prefix your tables
When modelling a star-schema data-model, create aliases for all your physical tables (prefixed with either “Dim_”, “Fact_” or “Fact_Agg_” )
- Physical Display Folder
Use Physical Display Folder to organise your stars
- Call Interface
- Parameterize your data source name
This way you only have to change it in one place when moving from development to production.
- 3NF in DWH
Try to avoid doing 3NF to Star Schema Modelling in the Physical Layer. Flatten the table if possible during the ETL or in a view on the database.
- Connecting User
The User you use to connect to your data should by default not by the “owner” of the table, but should have only select rights trough a role.
Till Next Time