Naming standards for dimensional modeling

data-warehouse dimensional-modeling
vikc picture vikc · Feb 19, 2014 · Viewed 8k times · Source

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.

Answer

Victor HDC picture Victor HDC · Feb 20, 2014

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.

Related questions

What are the types of dimension tables in star schema design?

When reading about star schema design I have seen that many people uses various names for different types of dimension tables. Please list the names and a small description of each type. If any list also an alias name.

Read More
Handling multiple fact tables in Qlikview

I have a PostgreSQL database containing various education data such school-level test scores and enrollment figures. I need to separate enrollment from test scores because the data is on different grains. Even though enrollment is on a different granularity from …

Read More
joining across multiple fact tables with a dimension in between

What's a good approach to data warehouse design if requested reports require summarized information about the same dimensions (and at the same granularity) but the underlying data is stored in separate fact tables? For example, a report showing total salary …

Read More