Open harrisward opened 4 years ago
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
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
Tested two views joined - working.
We need to define the views we need. closed, created, modified etc etc
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
Needs to add views to cover the roleplay aspect of date lookups