Q

Working with dimensional tables

We are building a star schema with several slowly changing dimensions (Type 2). One of our dimensions is a date dimension. We use the date dimension key in the fact table. But what about other dimensions, should we use a date datatype, or should we use a date_key column linking by foreign key back to the date dimension? Using a date datatype allows us to not have to join to the date dimension every time we do a query. But what is the best practice?
The basic approach that we take to dimensional tables does provide us with a clear direction that all dimensional values should be linked to a fact table via a surrogate key. The date dimension is no different. It is a very important dimension and by providing users with a date table, it provides you with significant business benefits in terms of the business specific date analysis that you will be able to perform. As well, certain BI tools will insist on having a separate dimension for dates to supply data to dimensional filters.

Bottom line here is that I would create a surrogate key, but I will also let you in on a trick that we use around date keys. We define the surrogate date key to always be supplied in the numerical format of YYYYDDMM, although a true surrogate key, we can now infer meaning from this key through the use of TO_CHAR or TO_DATE functions.

This was first published in April 2005

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close