Naming standards for dimensional modeling

Question

I am working on my first dimensional modeling assignment for a Data Warehouse project using Kimball's approach. As I prepare my model and think about physical objects, I wonder what is the recommended naming scheme for database objects. We're going to use Oracle, and we don't really have any standards at present. Any help would be appreciated.

Cop

Answer 1

You can take some ideas from the Oracle BI Applications Data Model. Log in to your Oracle support account and look for this document: Oracle Business Analytics Warehouse Data Model Reference Version 7.9.6.3 (Doc ID 1325948.1)

These are some of the naming conventions included:

PREFIX

W_ = Warehouse

SUFFIX

_A = Aggregate

_D = Dimension

_DH = Dimension Hierarchy

_DHS = Staging for Dimension Hierarchy

_DS = Staging for Dimension

_F = Fact

_FS = Staging for Fact

_H = Helper

_MD = Mini Dimension

_TMP = Pre-staging temporary table

For example: Sales fact table would be W_Sales_F

This document from northwestern university has useful tips for naming columns, such as using prime, qualifier and class words (e.g. STUDENT_FIRST_NAME)

The kimball group's design tip #71 contains general guidelines for naming conventions

For example, a sales analyst would be interested in Sales numbers, but it turns out that this Sales number is really Sales_Commissionable_Amount, which is different from Sales_Gross_Amount and Sales_Net_Amount.

Malatya