Add Roleplay Dimension Views for dates

[harrisward]

Needs to add views to cover the roleplay aspect of date lookups

[harrisward]

Review Fact Tables for date fields and decide on the Date Views that need to be created. For example, D_LEAD_CREATED_DATE, D_LEAD_CONVERTED_DATE

[harrisward]

Before we implement this fully its best we create a test based on F_CASE_MANAGEMENT dates.

Create some cases and close them on different days from the create date (CASE_CREATED_TK and CASE_CLOSED_TK in the F_CASE_MANAGEMENT table). You can dummy this data after the ETL has loaded it from the source system.

Then create 2 new views (and DDL) for D_CASE_CREATED_DATE and D_CASE_CLOSED_DATE. Once the views are in place run some queries using these combined views. For example,

SELECT * FROM F_CASE_MANAGEMENT ....
INNER JOIN D_CASE_CREATED_DATE
ON F_CASE_MANAGEMENT.CASE_CREATED_TK = D_CASE_CREATED_DATE.CASE_CREATED_TK
INNER JOIN D_CASE_CLOSED_DATE
ON F_CASE_MANAGEMENT.CASE_CLOSED_TK = D_CASE_CLOSED_DATE.CASE_CLOSED_TK

This should test if the two views that are based off D_DATE work when referenced at the same times as views

[fraserward]

Tested two views joined - working.

[fraserward]

We need to define the views we need. closed, created, modified etc etc

[harrisward]

We need to create the following Date Views:

This List could change!

D_CASE_CREATED_DATE
D_CASE_CLOSED_DATE

D_LEAD_CREATED_DATE
D_LEAD_CONVERTED_DATE
D_LEAD_CLOSED_DATE

D_OPPORTUNITY_CREATED_DATE
D_OPPORTUNITY_CLOSED_DATE

D_INVOICE_CREATED_DATE
D_INVOICE_DUE_DATE