ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

DWH Periods Show Differently In Clarity vs DWH Schema

book

Article ID: 130620

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

The DWH time slice IDs show different minimum and maximum dates on the Clarity schema vs the min/max dates on the DWH schema (dwh_com_periods table).
 
Clarity schema:

select min(SR.FROM_DATE),
             max(SR.TO_DATE)
from prj_blb_slicerequests sr
where SR.IS_DWH_REQUEST = 1

DWH schema:

select min(period_start_date),
             max(period_end_date)
from DWH_CMN_PERIOD

Why do the end dates do not match? Should they match?

Environment

Release: All

Resolution

In order get the periods for the DWH, there are other criteria that need to be taken into consideration.

The query to compare on the Clarity schema is:

select min(period_start_date), 
MAX(PERIOD_END_DATE) 
from DWH_CMN_PERIOD_V 

If you then review the SQL behind this view you will see it looks at items such as fiscal periods (biz_com_periods) and data in rpt_calendar table in addition to the actual DWH slices in the Time Slices page.