CODEWITHSUNDEEP
dimensional-modelingentity-relationship
Bob
Bob

Reputation: 477

How to classify dates going from Entity-Relationship to Dimensional model

My question may be simple, but I've poured through a considerable amount of sources and still stuck.

In my ER model (order, ship, callcenter processes), I have several entities that contain various date fields, such as: call date, order date, payment date, shipping date. So as I do my dimensional models, I do create a Time dimension that contains hierarchies such as Year, month, date. However, I recurrently end up with a date field in my fact table. Say my fact table is Deliveries, can I simply take the DeliveryDate field from that ER entity and put it in a separate Time dimension in my dimensional model?

This is a purely theoretical exercise, any help, a copy/paste of a link to an article or anything like that will help! Thanks!

EDIT

I'd like to simply provide the context to this question, which will surely clarify it as well. Essentially, I have a homework project where we are provided with a situation where a company has a transactional ER model (for their daily operational activities) and where management wants to create a BI model that will allow them to extract KPIs and that's my goal too. (i.e. I need to create several star-schemas out of an Entity-Relationship Diagram)

As mentioned above, there are 3 main business processes: the callcenter, the sales and the shipping departments. All this implies that the ER model that we are giving at he start has date fields scattered in different Entity boxes (OrderDate in the Order entity, ShippingDate in the Shipping entity, etc.). Although I understand that a dimensional model must have a DateDim, but I couldn't understand how could we transfer these date fields from different Entity boxes into one DateDim in my dimensional model. This question may be very green, but I just can't find suitable articles that either go through the ER-Model/Dimensional-Model remodeling concept.

Upvotes: 0

Views: 2891

Answers (2)

Ron Dunn
Ron Dunn

Reputation: 3078

Rather than foreign key relationships to one dimension, as suggested by Rich, you may prefer to create role-playing views over the base dimension.

For example, dimDeliveryDate would be created as a view over dimDate, as would roles such as OrderDate, ShipDate, etc.

This will make it much easier to work with in tools such as Tableau and PowerBI.

https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/role-playing-dimension/

You may also wish to take the following discussion into account when deciding on a foreign key. The risk with using a 'date as an integer' as a key is that people will start to use it as a DATE, rather than a meaningless surrogate key.

https://www.kimballgroup.com/2004/02/design-tip-51-latest-thinking-on-time-dimension-tables/

Upvotes: 2

Rich
Rich

Reputation: 2279

The usual way to do it is for your time dimension to be at the level of a date, with columns representing the date itself, month, year, etc. The key of that table is an integer that looks pretty much like the date, e.g. 20180303. For example:

DateKey,CalendarDate, MonthName, MonthNum, Year

20180303, 2018-03-03, March, 1, 2018

(You'd have many more columns here to make it very useful in lots of different ways)

You'd have one row in here for each and every date from a date back in the past to a date in the future, and you create it first, up front, before you load any facts, even dates you don't have in your Facts. There are ways of doing this easily in your chosen language or ETL tool, for example with stored procedures, from a spreadsheet, from C#, etc.

Next, for the Facts, and just the facts.

In your fact table, you need to represent the Delivery Date. You'd do this by having (in your fact table) DeliveryDateKey. Say your fact record has a delivery date of 2nd January 2018, you'd store 20180102 in your Fact table. You wouldn't store anything else about the date in your fact table, just that key. Neither would you need to change the date dimension: you're just creating a foreign key relationship from your Fact table to your DimTime or DimDate or whatever you'd like to call that table.

You might have many other dates in this fact table and others (for example OrderDateKey), all of which would point to the same DimDate table.

In the same way, if your different facts are referring to Products, they all point to DimProduct.

Upvotes: 1

Related Questions