This document is intended to demonstrate a new method to model relative time. The method should archive four goals below:
1) Apply for both DMR and Relational based framework manager;
2) Make Framework manager model easy to use for report authors and end users;
3) Make sure that report can be generated with most efficient SQL to achieve best performance ;
4) Make model extendable to accommodate new relative time without changing database structure.
There are three major advantages of modelling relative time in framework manager:
1) Make all relative time related logic in reports be consistent, as the logic is built into framework manager and shared with all reports.
2) Reduce a big effort for induvial report authors and end users, and therefore reduce report development cost.
3) Improve the report generation performance.
2 Use framework manager with relative time
To get relative time, we need to define the current period. There are many different ways to get current period, listed below:
1) Specified from database;
2) Dynamically updated based on ETL last successful load month;
3) Determined by the latest month of exiting data
4) Specified by end user when running reports
This sample is based on option 4. We have defined sample relative time as Current Month, Last Month, Current Quarter, Last Quarter, YTD and Prior YTD. Additional periods can be added as requested per project.
2.1 DMR relative time hierarchy
This hierarchy below is dynamically generated, according to the input from end user. You may have noticed that there are many domain properties within hierarchy at month level. These are different name conventions for month, we can also add different name convention at period level.
You can use this hierarchy to achieve relative time as individual member and level. One or more members can be dragged into context filter, slicer, category in chart, and tuple.
This hierarchy makes report authors easy to use with minimal code effort, as report authors can directly drag and drop directly mixed members into data containers
2.2 Relational with relative time Query Subject
Report users can directly use relative time query subject, together with filters to generate query as needed. This way will enable us to get query much quicker. Normally, have to use case multiple case statements in query to realize these queries.
2.3 Relational with relative time Filters
Introduction of these filters are dramatically improve the performance of developing query. It can be used in crosstab with multiple union query, where each query uses different filter.
2.4 Relational with relative time calculations
Calculations provides the best performance in relational model. Report authors can simply consider each calculation as value and added it into filters.
3.1 Sample Data
In order to explain the concept, the following database tables are created:
CALENDAR_
YEAR |
CALENDAR_
QUARTER |
CALENDAR_
MONTH |
FISCAL_
YEAR |
FISCAL_
MONTH |
FISCAL_
QUARTER |
2016
|
3
|
7
|
2016
|
9
|
3
|
2016
|
3
|
8
|
2016
|
10
|
4
|
2016
|
3
|
9
|
2016
|
11
|
4
|
2016
|
4
|
10
|
2016
|
12
|
4
|
2016
|
4
|
11
|
2017
|
1
|
1
|
2016
|
4
|
12
|
2017
|
2
|
1
|
2017
|
1
|
1
|
2017
|
3
|
1
|
2017
|
1
|
2
|
2017
|
4
|
2
|
2017
|
1
|
3
|
2017
|
5
|
2
|
2017
|
2
|
4
|
2017
|
6
|
2
|
2017
|
2
|
5
|
2017
|
7
|
3
|
2017
|
2
|
6
|
2017
|
8
|
3
|
CALENDAR_
YEAR |
CALENDAR_
MONTH |
SALES
|
2016
|
7
|
7
|
2016
|
8
|
8
|
2016
|
9
|
9
|
2016
|
10
|
10
|
2016
|
11
|
11
|
2016
|
12
|
12
|
2017
|
1
|
21
|
2017
|
2
|
22
|
2017
|
3
|
23
|
2017
|
4
|
24
|
2017
|
5
|
25
|
2017
|
6
|
26
|
Sample Report – Crosstab
Assume that current month is 201706 (CALENDAR_YEAR = 2017 and CALENDAR_MONTH = 06), all relative time periods below are based on fiscal year.
SALES
|
|
Current Month
|
26
|
Last Month
|
25
|
Current Quarter
|
26+25 = 51
|
Last Quarter
|
22+23+24 = 69
|
YTD
|
11+12+21+22+23+24+25+26
= 164
|
Prior YTD
|
(ignore here)
|
3.2 Framework Manager – Data Layer – Current Period
Based on sample data, the lowest granularity is at Month level. The current period, or current month can be determined according to different business requirement. It is assumed that user can specify what month to report. If not given, then use the last complete calendar month. The query in this case is built into framework manager as below.
SELECT
CALENDAR_YEAR
,CALENDAR_QUARTER
,CALENDAR_MONTH
,FISCAL_YEAR
,FISCAL_MONTH
,FISCAL_QUARTER
,FISCAL_YEAR*12 + FISCAL_MONTH AS FISCAL_PERIOD_ABS_VALUE
,CALENDAR_YEAR*12 + CALENDAR_MONTH AS CALENDAR_PERIOD_ABS_VALUE
,FISCAL_YEAR*4 + FISCAL_QUARTER AS FISCAL_QUARTER_ABS_VALUE
,CALENDAR_YEAR*4 + CALENDAR_QUARTER AS CALENDAR_QUARTER_ABS_VALUE
FROM [{Database Source Connection Name}]. DATE_DIM
WHERE ( #PROMPT('PLEASE SELECT AS OF MONTH IN YYYYMM FORMAT','STRING','''CURRENT''')# ='CURRENT'
AND CALENDAR_YEAR=date_part('year',add_months(CURRENT_DATE,-1))
AND CALENDAR_MONTH=date_part('month',add_months(CURRENT_DATE,-1)) )
OR ( #PROMPT('PLEASE SELECT AS OF MONTH IN YYYYMM FORMAT','STRING','''CURRENT''')#
=CAST(CALENDAR_YEAR*100+CALENDAR_MONTH AS VARCHAR(20)) )
CALENDAR_YEAR
,CALENDAR_QUARTER
,CALENDAR_MONTH
,FISCAL_YEAR
,FISCAL_MONTH
,FISCAL_QUARTER
,FISCAL_YEAR*12 + FISCAL_MONTH AS FISCAL_PERIOD_ABS_VALUE
,CALENDAR_YEAR*12 + CALENDAR_MONTH AS CALENDAR_PERIOD_ABS_VALUE
,FISCAL_YEAR*4 + FISCAL_QUARTER AS FISCAL_QUARTER_ABS_VALUE
,CALENDAR_YEAR*4 + CALENDAR_QUARTER AS CALENDAR_QUARTER_ABS_VALUE
FROM [{Database Source Connection Name}]. DATE_DIM
WHERE ( #PROMPT('PLEASE SELECT AS OF MONTH IN YYYYMM FORMAT','STRING','''CURRENT''')# ='CURRENT'
AND CALENDAR_YEAR=date_part('year',add_months(CURRENT_DATE,-1))
AND CALENDAR_MONTH=date_part('month',add_months(CURRENT_DATE,-1)) )
OR ( #PROMPT('PLEASE SELECT AS OF MONTH IN YYYYMM FORMAT','STRING','''CURRENT''')#
=CAST(CALENDAR_YEAR*100+CALENDAR_MONTH AS VARCHAR(20)) )
It can be any record listed below, if specified 201709, then the selected record would be the highlighted record. The extended three column is help columns, which will be used to easily determine relative time.
CALENDAR_
YEAR |
CALENDAR_
QUARTER |
CALENDAR_
MONTH |
FISCAL_
YEAR |
FISCAL_
MONTH |
FISCAL_
QUARTER |
FISCAL_
MONTH_ ABS_VALUE |
CALENDAR_
MONTH_ ABS_VALUE |
FISCAL_
QUARTER_ ABS_VALUE |
CALENDAR_
QUARTER_ ABS_VALUE |
2016
|
3
|
7
|
2016
|
9
|
3
|
24201
|
24199
|
8067
|
8067
|
2016
|
3
|
8
|
2016
|
10
|
4
|
24202
|
24200
|
8068
|
8067
|
2016
|
3
|
9
|
2016
|
11
|
4
|
24203
|
24201
|
8068
|
8067
|
2016
|
4
|
10
|
2016
|
12
|
4
|
24204
|
24202
|
8068
|
8068
|
2016
|
4
|
11
|
2017
|
1
|
1
|
24205
|
24203
|
8069
|
8068
|
2016
|
4
|
12
|
2017
|
2
|
1
|
24206
|
24204
|
8069
|
8068
|
2017
|
1
|
1
|
2017
|
3
|
1
|
24207
|
24205
|
8069
|
8069
|
2017
|
1
|
2
|
2017
|
4
|
2
|
24208
|
24206
|
8070
|
8069
|
2017
|
1
|
3
|
2017
|
5
|
2
|
24209
|
24207
|
8070
|
8069
|
2017
|
2
|
4
|
2017
|
6
|
2
|
24210
|
24208
|
8070
|
8070
|
2017
|
2
|
5
|
2017
|
7
|
3
|
24211
|
24209
|
8071
|
8070
|
2017
|
2
|
6
|
2017
|
8
|
3
|
24212
|
24210
|
8071
|
8070
|
3.3 Framework Manager – Data Layer – RELATIVE_TIME
The following query is defined in query subject. To avoid local processing, we have to make sure that the SQL statement should be Cognos compatible. This is why we can’t make CURRENT PERIOD as common table expression (CTE). RELATIVE_TIME
SELECT 'Current Month' as PERIOD_KEY,1 as PERIOD_SORT,'Current Month' as PERIOD_CAPTION,MIN(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) AS START_PERIOD,MAX(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) AS END_PERIOD
FROM [{Database Source Connection Name}].DATE_DIM D
INNER JOIN (
SELECT
CALENDAR_YEAR
,CALENDAR_QUARTER
,CALENDAR_MONTH
,FISCAL_YEAR
,FISCAL_MONTH
,FISCAL_QUARTER
,FISCAL_YEAR*12 + FISCAL_MONTH AS FISCAL_PERIOD_ABS_VALUE
,CALENDAR_YEAR*12 + CALENDAR_MONTH AS CALENDAR_PERIOD_ABS_VALUE
,FISCAL_YEAR*4 + FISCAL_QUARTER AS FISCAL_QUARTER_ABS_VALUE
,CALENDAR_YEAR*4 + CALENDAR_QUARTER AS CALENDAR_QUARTER_ABS_VALUE
FROM [{Database Source Connection Name}].DATE_DIM
WHERE ( #PROMPT('PLEASE SELECT AS OF MONTH IN YYYYMM FORMAT','STRING','''CURRENT''')# ='CURRENT'
AND CALENDAR_YEAR=date_part('year',add_months(CURRENT_DATE,-1))
AND CALENDAR_MONTH=date_part('month',add_months(CURRENT_DATE,-1)) )
OR ( #PROMPT('PLEASE SELECT AS OF MONTH IN YYYYMM FORMAT','STRING','''CURRENT''')#
=CAST(CALENDAR_YEAR*100+CALENDAR_MONTH AS VARCHAR(20)) )
) CONTROL ON ( CONTROL.FISCAL_PERIOD_ABS_VALUE - (D.FISCAL_YEAR * 12 + D.FISCAL_MONTH) = 0 )
UNION ALL
SELECT 'Last Month' as PERIOD_KEY,2 as PERIOD_SORT,'Last Month' as PERIOD_CAPTION,MIN(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) AS START_PERIOD,MAX(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) AS END_PERIOD
FROM [{Database Source Connection Name}].DATE_DIM D
INNER JOIN CONTROL ON ( CONTROL.FISCAL_PERIOD_ABS_VALUE - (D.FISCAL_YEAR * 12 + D.FISCAL_MONTH) = 1 )
UNION ALL
SELECT 'QTD' as PERIOD_KEY,3 as PERIOD_SORT,'QTD' as PERIOD_CAPTION,MIN(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) AS START_PERIOD,MAX(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) AS END_PERIOD
FROM [{Database Source Connection Name}].MPL_DATE_DIM D INNER JOIN CONTROL ON ( CONTROL.FISCAL_QUARTER_ABS_VALUE - (D.FISCAL_YEAR * 4 + D.FISCAL_QUARTER) = 0 AND D.FISCAL_MONTH <= CONTROL.FISCAL_MONTH)
UNION ALL
SELECT 'Prior QTD' as PERIOD_KEY,4 as PERIOD_SORT,'Prior QTD' as PERIOD_CAPTION,MIN(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) AS START_PERIOD,MAX(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) AS END_PERIOD
FROM [{Database Source Connection Name}].DATE_DIM D INNER JOIN CONTROL ON ( CONTROL.FISCAL_QUARTER_ABS_VALUE - (D.FISCAL_YEAR * 4 + D.FISCAL_QUARTER) = 0 AND D.FISCAL_MONTH <= CONTROL.FISCAL_MONTH)
UNION ALL
SELECT 'YTD' as PERIOD_KEY,5 as PERIOD_SORT,'YTD' as PERIOD_CAPTION,MIN(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) AS START_PERIOD,MAX(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) AS END_PERIOD
FROM [{Database Source Connection Name}].DATE_DIM D INNER JOIN CONTROL ON CONTROL.FISCAL_YEAR = D.FISCAL_YEAR AND D.FISCAL_MONTH <= CONTROL.FISCAL_MONTH
UNION ALL
SELECT 'Prior YTD' as PERIOD_KEY,6 as PERIOD_SORT,'Prior YTD' as PERIOD_CAPTION,MIN(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) AS START_PERIOD,MAX(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) AS END_PERIOD
FROM [{Database Source Connection Name}].DATE_DIM D INNER JOIN CONTROL ON CONTROL.FISCAL_YEAR - 1 = D.FISCAL_YEAR AND D.FISCAL_MONTH <= CONTROL.FISCAL_MONTH
FROM [{Database Source Connection Name}].DATE_DIM D
INNER JOIN (
SELECT
CALENDAR_YEAR
,CALENDAR_QUARTER
,CALENDAR_MONTH
,FISCAL_YEAR
,FISCAL_MONTH
,FISCAL_QUARTER
,FISCAL_YEAR*12 + FISCAL_MONTH AS FISCAL_PERIOD_ABS_VALUE
,CALENDAR_YEAR*12 + CALENDAR_MONTH AS CALENDAR_PERIOD_ABS_VALUE
,FISCAL_YEAR*4 + FISCAL_QUARTER AS FISCAL_QUARTER_ABS_VALUE
,CALENDAR_YEAR*4 + CALENDAR_QUARTER AS CALENDAR_QUARTER_ABS_VALUE
FROM [{Database Source Connection Name}].DATE_DIM
WHERE ( #PROMPT('PLEASE SELECT AS OF MONTH IN YYYYMM FORMAT','STRING','''CURRENT''')# ='CURRENT'
AND CALENDAR_YEAR=date_part('year',add_months(CURRENT_DATE,-1))
AND CALENDAR_MONTH=date_part('month',add_months(CURRENT_DATE,-1)) )
OR ( #PROMPT('PLEASE SELECT AS OF MONTH IN YYYYMM FORMAT','STRING','''CURRENT''')#
=CAST(CALENDAR_YEAR*100+CALENDAR_MONTH AS VARCHAR(20)) )
) CONTROL ON ( CONTROL.FISCAL_PERIOD_ABS_VALUE - (D.FISCAL_YEAR * 12 + D.FISCAL_MONTH) = 0 )
UNION ALL
SELECT 'Last Month' as PERIOD_KEY,2 as PERIOD_SORT,'Last Month' as PERIOD_CAPTION,MIN(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) AS START_PERIOD,MAX(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) AS END_PERIOD
FROM [{Database Source Connection Name}].DATE_DIM D
INNER JOIN CONTROL ON ( CONTROL.FISCAL_PERIOD_ABS_VALUE - (D.FISCAL_YEAR * 12 + D.FISCAL_MONTH) = 1 )
UNION ALL
SELECT 'QTD' as PERIOD_KEY,3 as PERIOD_SORT,'QTD' as PERIOD_CAPTION,MIN(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) AS START_PERIOD,MAX(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) AS END_PERIOD
FROM [{Database Source Connection Name}].MPL_DATE_DIM D INNER JOIN CONTROL ON ( CONTROL.FISCAL_QUARTER_ABS_VALUE - (D.FISCAL_YEAR * 4 + D.FISCAL_QUARTER) = 0 AND D.FISCAL_MONTH <= CONTROL.FISCAL_MONTH)
UNION ALL
SELECT 'Prior QTD' as PERIOD_KEY,4 as PERIOD_SORT,'Prior QTD' as PERIOD_CAPTION,MIN(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) AS START_PERIOD,MAX(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) AS END_PERIOD
FROM [{Database Source Connection Name}].DATE_DIM D INNER JOIN CONTROL ON ( CONTROL.FISCAL_QUARTER_ABS_VALUE - (D.FISCAL_YEAR * 4 + D.FISCAL_QUARTER) = 0 AND D.FISCAL_MONTH <= CONTROL.FISCAL_MONTH)
UNION ALL
SELECT 'YTD' as PERIOD_KEY,5 as PERIOD_SORT,'YTD' as PERIOD_CAPTION,MIN(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) AS START_PERIOD,MAX(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) AS END_PERIOD
FROM [{Database Source Connection Name}].DATE_DIM D INNER JOIN CONTROL ON CONTROL.FISCAL_YEAR = D.FISCAL_YEAR AND D.FISCAL_MONTH <= CONTROL.FISCAL_MONTH
UNION ALL
SELECT 'Prior YTD' as PERIOD_KEY,6 as PERIOD_SORT,'Prior YTD' as PERIOD_CAPTION,MIN(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) AS START_PERIOD,MAX(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) AS END_PERIOD
FROM [{Database Source Connection Name}].DATE_DIM D INNER JOIN CONTROL ON CONTROL.FISCAL_YEAR - 1 = D.FISCAL_YEAR AND D.FISCAL_MONTH <= CONTROL.FISCAL_MONTH
Given that PLEASE SELECT AS OF MONTH IN YYYYMM FORMAT = 201709, then the generated RELATIVE_TIME as below.
PERIOD_KEY
|
PERIOD_SORT
|
PERIOD_CAPTION
|
START_PERIOD
|
END_PERIOD
|
Current Month
|
1
|
Current Month
|
201706
|
201706
|
Last Month
|
2
|
Last Month
|
201705
|
201705
|
Current Quarter
|
3
|
Current Quarter
|
201702
|
201704
|
Last Quarter
|
4
|
Last Quarter
|
201611
|
201701
|
YTD
|
5
|
YTD
|
201611
|
201706
|
Prior YTD
|
6
|
Prior YTD
|
201511
|
201606
|
3.4 Framework Manager – Business Layer – Relationship between TIME and RELATIVE_TIME
This relationship establishes the Relative time dimension with all facts via all Facts. If you select any measures from facts and relative time period, then the Time itself becomes a bridge, the query will be generated by joining relative time, time and fact.
3.5 Framework Manager – Business Layer – Relative Time
Relative time can be exposed in framework manager, which can be directly used to generate report, as described in section Relational with relative time Query Subject.
3.6 Framework Manager – Business Layer – Filters
Each relative time period can be exposed as a filter. The expression is listed as follows:
[Business Layer].[Relative Time].[PERIOD_KEY] = 'Current Month'
[Business Layer].[Relative Time].[PERIOD_KEY] = 'Last Month'[Business Layer].[Relative Time].[PERIOD_KEY] = 'Current Quarter'
[Business Layer].[Relative Time].[PERIOD_KEY] = ‘Last Quarter’
[Business Layer].[Relative Time].[PERIOD_KEY] = 'YTD'
[Business Layer].[Relative Time].[PERIOD_KEY] = 'Prior YTD'
Calculation is most effective way to get report with best performance, as the calculation is set as value in SQL statement. To get all relative time calculation, we need to expose it as two parameter maps: MAP_RELATIVE_TIME_START and MAP_RELATIVE_TIME_END.
MAP_RELATIVE_TIME_START:
All calculations are defined as follows:
#$MAP_RELATIVE_TIME_START{'Current Month'}#
#$MAP_RELATIVE_TIME_END{'Current Month'}##$MAP_RELATIVE_TIME_START{'Last Month'}#
#$MAP_RELATIVE_TIME_END{'Last Month'}#
#$MAP_RELATIVE_TIME_START{'Current Quarter'}#
#$MAP_RELATIVE_TIME_END{' Current Quarter '}#
#$MAP_RELATIVE_TIME_START{'Last Quarter'}#
#$MAP_RELATIVE_TIME_END{Last Quarter'}#
#$MAP_RELATIVE_TIME_START{'YTD'}#
#$MAP_RELATIVE_TIME_END{'YTD'}#
#$MAP_RELATIVE_TIME_START{'Prior YTD'}#
#$MAP_RELATIVE_TIME_END{'Prior YTD'}#
3.8 Framework Manager – DM Layer – Relative time hierarchy
We can add new hierarchy with two levels: Period and Month level. please note that not all properties are from relative time query subject, instead, we can leverage all columns from standard time dimension. The relative time hierarchy is actually built based on both query subjects: relative time and standard time dimension, where the join clause is applied.
4 Test – SQL analysis
The goal to check SQL statement generated by Cognos engine is to make sure that report performance is ensured as expected.
4.1 DMR relative time hierarchy
The SQL generated below is as expected. Three tables, or RELATIVE_TIME, FACT, and DATE_TIME are joined to yield the best performance. No local processing is needed.
with RELATIVE_TIME as (
select 'Current Month' PERIOD_KEY , 1 PERIOD_SORT , 'Current Month' PERIOD_CAPTION , min(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) START_PERIOD , max(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) END_PERIOD
from DATE_DIM D INNER JOIN (
select CALENDAR_YEAR, CALENDAR_QUARTER, CALENDAR_MONTH, FISCAL_YEAR, FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR * 12 + FISCAL_MONTH FISCAL_PERIOD_ABS_VALUE , CALENDAR_YEAR * 12 + CALENDAR_MONTH CALENDAR_PERIOD_ABS_VALUE , FISCAL_YEAR * 4 + FISCAL_QUARTER FISCAL_QUARTER_ABS_VALUE , CALENDAR_YEAR * 4 + CALENDAR_QUARTER CALENDAR_QUARTER_ABS_VALUE
from DATE_DIM
where 'CURRENT' = 'CURRENT' and CALENDAR_YEAR = date_part('year', add_months(current_date, -1)) and CALENDAR_MONTH = date_part('month', add_months(current_date, -1)) or 'CURRENT' = cast(CALENDAR_YEAR * 100 + CALENDAR_MONTH as varchar( 20 ))) CONTROL on CONTROL.FISCAL_PERIOD_ABS_VALUE - (D.FISCAL_YEAR * 12 + D.FISCAL_MONTH) = 0 union all
select 'Last Month' PERIOD_KEY , 2 PERIOD_SORT , 'Last Month' PERIOD_CAPTION , min(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) START_PERIOD , max(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) END_PERIOD
from DATE_DIM D INNER JOIN (
select CALENDAR_YEAR, CALENDAR_QUARTER, CALENDAR_MONTH, FISCAL_YEAR, FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR * 12 + FISCAL_MONTH FISCAL_PERIOD_ABS_VALUE , CALENDAR_YEAR * 12 + CALENDAR_MONTH CALENDAR_PERIOD_ABS_VALUE , FISCAL_YEAR * 4 + FISCAL_QUARTER FISCAL_QUARTER_ABS_VALUE , CALENDAR_YEAR * 4 + CALENDAR_QUARTER CALENDAR_QUARTER_ABS_VALUE
from DATE_DIM
where 'CURRENT' = 'CURRENT' and CALENDAR_YEAR = date_part('year', add_months(current_date, -1)) and CALENDAR_MONTH = date_part('month', add_months(current_date, -1)) or 'CURRENT' = cast(CALENDAR_YEAR * 100 + CALENDAR_MONTH as varchar( 20 ))) CONTROL on CONTROL.FISCAL_PERIOD_ABS_VALUE - (D.FISCAL_YEAR * 12 + D.FISCAL_MONTH) = 1 union all
select 'Current Quarter' PERIOD_KEY , 3 PERIOD_SORT , 'Current Quarter' PERIOD_CAPTION , min(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) START_PERIOD , max(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) END_PERIOD
from DATE_DIM D INNER JOIN (
select CALENDAR_YEAR, CALENDAR_QUARTER, CALENDAR_MONTH, FISCAL_YEAR, FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR * 12 + FISCAL_MONTH FISCAL_PERIOD_ABS_VALUE , CALENDAR_YEAR * 12 + CALENDAR_MONTH CALENDAR_PERIOD_ABS_VALUE , FISCAL_YEAR * 4 + FISCAL_QUARTER FISCAL_QUARTER_ABS_VALUE , CALENDAR_YEAR * 4 + CALENDAR_QUARTER CALENDAR_QUARTER_ABS_VALUE
from DATE_DIM
where 'CURRENT' = 'CURRENT' and CALENDAR_YEAR = date_part('year', add_months(current_date, -1)) and CALENDAR_MONTH = date_part('month', add_months(current_date, -1)) or 'CURRENT' = cast(CALENDAR_YEAR * 100 + CALENDAR_MONTH as varchar( 20 ))) CONTROL on CONTROL.FISCAL_QUARTER_ABS_VALUE - (D.FISCAL_YEAR * 4 + D.FISCAL_QUARTER) = 0 union all
select 'Last Quarter' PERIOD_KEY , 4 PERIOD_SORT , 'Last Quarter' PERIOD_CAPTION , min(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) START_PERIOD , max(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) END_PERIOD
from DATE_DIM D INNER JOIN (
select CALENDAR_YEAR, CALENDAR_QUARTER, CALENDAR_MONTH, FISCAL_YEAR, FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR * 12 + FISCAL_MONTH FISCAL_PERIOD_ABS_VALUE , CALENDAR_YEAR * 12 + CALENDAR_MONTH CALENDAR_PERIOD_ABS_VALUE , FISCAL_YEAR * 4 + FISCAL_QUARTER FISCAL_QUARTER_ABS_VALUE , CALENDAR_YEAR * 4 + CALENDAR_QUARTER CALENDAR_QUARTER_ABS_VALUE
from DATE_DIM
where 'CURRENT' = 'CURRENT' and CALENDAR_YEAR = date_part('year', add_months(current_date, -1)) and CALENDAR_MONTH = date_part('month', add_months(current_date, -1)) or 'CURRENT' = cast(CALENDAR_YEAR * 100 + CALENDAR_MONTH as varchar( 20 ))) CONTROL on CONTROL.FISCAL_QUARTER_ABS_VALUE - (D.FISCAL_YEAR * 4 + D.FISCAL_QUARTER) = 1 union all
select 'YTD' PERIOD_KEY , 5 PERIOD_SORT , 'YTD' PERIOD_CAPTION , min(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) START_PERIOD , max(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) END_PERIOD
from DATE_DIM D INNER JOIN (
select CALENDAR_YEAR, CALENDAR_QUARTER, CALENDAR_MONTH, FISCAL_YEAR, FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR * 12 + FISCAL_MONTH FISCAL_PERIOD_ABS_VALUE , CALENDAR_YEAR * 12 + CALENDAR_MONTH CALENDAR_PERIOD_ABS_VALUE , FISCAL_YEAR * 4 + FISCAL_QUARTER FISCAL_QUARTER_ABS_VALUE , CALENDAR_YEAR * 4 + CALENDAR_QUARTER CALENDAR_QUARTER_ABS_VALUE
from DATE_DIM
where 'CURRENT' = 'CURRENT' and CALENDAR_YEAR = date_part('year', add_months(current_date, -1)) and CALENDAR_MONTH = date_part('month', add_months(current_date, -1)) or 'CURRENT' = cast(CALENDAR_YEAR * 100 + CALENDAR_MONTH as varchar( 20 ))) CONTROL on CONTROL.FISCAL_YEAR = D.FISCAL_YEAR and D.FISCAL_MONTH <= CONTROL.FISCAL_MONTH union all
select 'Prior YTD' PERIOD_KEY , 6 PERIOD_SORT , 'Prior YTD' PERIOD_CAPTION , min(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) START_PERIOD , max(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) END_PERIOD
from DATE_DIM D INNER JOIN (
select CALENDAR_YEAR, CALENDAR_QUARTER, CALENDAR_MONTH, FISCAL_YEAR, FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR * 12 + FISCAL_MONTH FISCAL_PERIOD_ABS_VALUE , CALENDAR_YEAR * 12 + CALENDAR_MONTH CALENDAR_PERIOD_ABS_VALUE , FISCAL_YEAR * 4 + FISCAL_QUARTER FISCAL_QUARTER_ABS_VALUE , CALENDAR_YEAR * 4 + CALENDAR_QUARTER CALENDAR_QUARTER_ABS_VALUE
from DATE_DIM
where 'CURRENT' = 'CURRENT' and CALENDAR_YEAR = date_part('year', add_months(current_date, -1)) and CALENDAR_MONTH = date_part('month', add_months(current_date, -1)) or 'CURRENT' = cast(CALENDAR_YEAR * 100 + CALENDAR_MONTH as varchar( 20 ))) CONTROL on CONTROL.FISCAL_YEAR - 1 = D.FISCAL_YEAR and D.FISCAL_MONTH <= CONTROL.FISCAL_MONTH)
select 'Current Month' PERIOD_KEY , 1 PERIOD_SORT , 'Current Month' PERIOD_CAPTION , min(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) START_PERIOD , max(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) END_PERIOD
from DATE_DIM D INNER JOIN (
select CALENDAR_YEAR, CALENDAR_QUARTER, CALENDAR_MONTH, FISCAL_YEAR, FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR * 12 + FISCAL_MONTH FISCAL_PERIOD_ABS_VALUE , CALENDAR_YEAR * 12 + CALENDAR_MONTH CALENDAR_PERIOD_ABS_VALUE , FISCAL_YEAR * 4 + FISCAL_QUARTER FISCAL_QUARTER_ABS_VALUE , CALENDAR_YEAR * 4 + CALENDAR_QUARTER CALENDAR_QUARTER_ABS_VALUE
from DATE_DIM
where 'CURRENT' = 'CURRENT' and CALENDAR_YEAR = date_part('year', add_months(current_date, -1)) and CALENDAR_MONTH = date_part('month', add_months(current_date, -1)) or 'CURRENT' = cast(CALENDAR_YEAR * 100 + CALENDAR_MONTH as varchar( 20 ))) CONTROL on CONTROL.FISCAL_PERIOD_ABS_VALUE - (D.FISCAL_YEAR * 12 + D.FISCAL_MONTH) = 0 union all
select 'Last Month' PERIOD_KEY , 2 PERIOD_SORT , 'Last Month' PERIOD_CAPTION , min(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) START_PERIOD , max(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) END_PERIOD
from DATE_DIM D INNER JOIN (
select CALENDAR_YEAR, CALENDAR_QUARTER, CALENDAR_MONTH, FISCAL_YEAR, FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR * 12 + FISCAL_MONTH FISCAL_PERIOD_ABS_VALUE , CALENDAR_YEAR * 12 + CALENDAR_MONTH CALENDAR_PERIOD_ABS_VALUE , FISCAL_YEAR * 4 + FISCAL_QUARTER FISCAL_QUARTER_ABS_VALUE , CALENDAR_YEAR * 4 + CALENDAR_QUARTER CALENDAR_QUARTER_ABS_VALUE
from DATE_DIM
where 'CURRENT' = 'CURRENT' and CALENDAR_YEAR = date_part('year', add_months(current_date, -1)) and CALENDAR_MONTH = date_part('month', add_months(current_date, -1)) or 'CURRENT' = cast(CALENDAR_YEAR * 100 + CALENDAR_MONTH as varchar( 20 ))) CONTROL on CONTROL.FISCAL_PERIOD_ABS_VALUE - (D.FISCAL_YEAR * 12 + D.FISCAL_MONTH) = 1 union all
select 'Current Quarter' PERIOD_KEY , 3 PERIOD_SORT , 'Current Quarter' PERIOD_CAPTION , min(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) START_PERIOD , max(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) END_PERIOD
from DATE_DIM D INNER JOIN (
select CALENDAR_YEAR, CALENDAR_QUARTER, CALENDAR_MONTH, FISCAL_YEAR, FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR * 12 + FISCAL_MONTH FISCAL_PERIOD_ABS_VALUE , CALENDAR_YEAR * 12 + CALENDAR_MONTH CALENDAR_PERIOD_ABS_VALUE , FISCAL_YEAR * 4 + FISCAL_QUARTER FISCAL_QUARTER_ABS_VALUE , CALENDAR_YEAR * 4 + CALENDAR_QUARTER CALENDAR_QUARTER_ABS_VALUE
from DATE_DIM
where 'CURRENT' = 'CURRENT' and CALENDAR_YEAR = date_part('year', add_months(current_date, -1)) and CALENDAR_MONTH = date_part('month', add_months(current_date, -1)) or 'CURRENT' = cast(CALENDAR_YEAR * 100 + CALENDAR_MONTH as varchar( 20 ))) CONTROL on CONTROL.FISCAL_QUARTER_ABS_VALUE - (D.FISCAL_YEAR * 4 + D.FISCAL_QUARTER) = 0 union all
select 'Last Quarter' PERIOD_KEY , 4 PERIOD_SORT , 'Last Quarter' PERIOD_CAPTION , min(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) START_PERIOD , max(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) END_PERIOD
from DATE_DIM D INNER JOIN (
select CALENDAR_YEAR, CALENDAR_QUARTER, CALENDAR_MONTH, FISCAL_YEAR, FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR * 12 + FISCAL_MONTH FISCAL_PERIOD_ABS_VALUE , CALENDAR_YEAR * 12 + CALENDAR_MONTH CALENDAR_PERIOD_ABS_VALUE , FISCAL_YEAR * 4 + FISCAL_QUARTER FISCAL_QUARTER_ABS_VALUE , CALENDAR_YEAR * 4 + CALENDAR_QUARTER CALENDAR_QUARTER_ABS_VALUE
from DATE_DIM
where 'CURRENT' = 'CURRENT' and CALENDAR_YEAR = date_part('year', add_months(current_date, -1)) and CALENDAR_MONTH = date_part('month', add_months(current_date, -1)) or 'CURRENT' = cast(CALENDAR_YEAR * 100 + CALENDAR_MONTH as varchar( 20 ))) CONTROL on CONTROL.FISCAL_QUARTER_ABS_VALUE - (D.FISCAL_YEAR * 4 + D.FISCAL_QUARTER) = 1 union all
select 'YTD' PERIOD_KEY , 5 PERIOD_SORT , 'YTD' PERIOD_CAPTION , min(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) START_PERIOD , max(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) END_PERIOD
from DATE_DIM D INNER JOIN (
select CALENDAR_YEAR, CALENDAR_QUARTER, CALENDAR_MONTH, FISCAL_YEAR, FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR * 12 + FISCAL_MONTH FISCAL_PERIOD_ABS_VALUE , CALENDAR_YEAR * 12 + CALENDAR_MONTH CALENDAR_PERIOD_ABS_VALUE , FISCAL_YEAR * 4 + FISCAL_QUARTER FISCAL_QUARTER_ABS_VALUE , CALENDAR_YEAR * 4 + CALENDAR_QUARTER CALENDAR_QUARTER_ABS_VALUE
from DATE_DIM
where 'CURRENT' = 'CURRENT' and CALENDAR_YEAR = date_part('year', add_months(current_date, -1)) and CALENDAR_MONTH = date_part('month', add_months(current_date, -1)) or 'CURRENT' = cast(CALENDAR_YEAR * 100 + CALENDAR_MONTH as varchar( 20 ))) CONTROL on CONTROL.FISCAL_YEAR = D.FISCAL_YEAR and D.FISCAL_MONTH <= CONTROL.FISCAL_MONTH union all
select 'Prior YTD' PERIOD_KEY , 6 PERIOD_SORT , 'Prior YTD' PERIOD_CAPTION , min(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) START_PERIOD , max(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) END_PERIOD
from DATE_DIM D INNER JOIN (
select CALENDAR_YEAR, CALENDAR_QUARTER, CALENDAR_MONTH, FISCAL_YEAR, FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR * 12 + FISCAL_MONTH FISCAL_PERIOD_ABS_VALUE , CALENDAR_YEAR * 12 + CALENDAR_MONTH CALENDAR_PERIOD_ABS_VALUE , FISCAL_YEAR * 4 + FISCAL_QUARTER FISCAL_QUARTER_ABS_VALUE , CALENDAR_YEAR * 4 + CALENDAR_QUARTER CALENDAR_QUARTER_ABS_VALUE
from DATE_DIM
where 'CURRENT' = 'CURRENT' and CALENDAR_YEAR = date_part('year', add_months(current_date, -1)) and CALENDAR_MONTH = date_part('month', add_months(current_date, -1)) or 'CURRENT' = cast(CALENDAR_YEAR * 100 + CALENDAR_MONTH as varchar( 20 ))) CONTROL on CONTROL.FISCAL_YEAR - 1 = D.FISCAL_YEAR and D.FISCAL_MONTH <= CONTROL.FISCAL_MONTH)
,FACT_SALES8 as (
select FACT_SALES.CALENDAR_YEAR CALENDAR_YEAR , FACT_SALES.CALENDAR_MONTH CALENDAR_MONTH , FACT_SALES.SALES SALES
from FACT_SALES)
select FACT_SALES.CALENDAR_YEAR CALENDAR_YEAR , FACT_SALES.CALENDAR_MONTH CALENDAR_MONTH , FACT_SALES.SALES SALES
from FACT_SALES)
, T as (
select DATE_DIM. CALENDAR_YEAR CALENDAR_YEAR, DATE_DIM. CALENDAR_MONTH CALENDAR_MONTH, DATE_DIM.CALENDAR_YEAR * 100 + DATE_DIM.CALENDAR_MONTH YYYYMM
from DATE_DIM DATE_DIM)
select Relative_Time7.PERIOD_KEY Periodkey , sum(FACT_SALES8.SALES) SALES , min(Relative_Time7.PERIOD_SORT) rc
from RELATIVE_TIME Relative_Time7
, FACT_SALES8
, T
where T.YYYYMM between Relative_Time7.START_PERIOD and Relative_Time7.END_PERIOD
and T. CALENDAR_YEAR _ = FACT_SALES8.CALENDAR_YEAR and T. CALENDAR_MONTH = FACT_SALES8.CALENDAR_MONTH
group by Relative_Time7.PERIOD_KEY
order by 3 asc
select DATE_DIM. CALENDAR_YEAR CALENDAR_YEAR, DATE_DIM. CALENDAR_MONTH CALENDAR_MONTH, DATE_DIM.CALENDAR_YEAR * 100 + DATE_DIM.CALENDAR_MONTH YYYYMM
from DATE_DIM DATE_DIM)
select Relative_Time7.PERIOD_KEY Periodkey , sum(FACT_SALES8.SALES) SALES , min(Relative_Time7.PERIOD_SORT) rc
from RELATIVE_TIME Relative_Time7
, FACT_SALES8
, T
where T.YYYYMM between Relative_Time7.START_PERIOD and Relative_Time7.END_PERIOD
and T. CALENDAR_YEAR _ = FACT_SALES8.CALENDAR_YEAR and T. CALENDAR_MONTH = FACT_SALES8.CALENDAR_MONTH
group by Relative_Time7.PERIOD_KEY
order by 3 asc
4.3 Relational with relative time Filters
The SQL generated below is as expected. Three tables, or RELATIVE_TIME, FACT, and DATE_TIME are joined to yield the best performance. No local processing is needed.
WITH RELATIVE_TIME as (
select 'Current Month' PERIOD_KEY , 1 PERIOD_SORT , 'Current Month' PERIOD_CAPTION , min(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) START_PERIOD , max(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) END_PERIOD
from DATE_DIM D INNER JOIN (
select CALENDAR_YEAR, CALENDAR_QUARTER, CALENDAR_MONTH, FISCAL_YEAR, FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR * 12 + FISCAL_MONTH FISCAL_PERIOD_ABS_VALUE , CALENDAR_YEAR * 12 + CALENDAR_MONTH CALENDAR_PERIOD_ABS_VALUE , FISCAL_YEAR * 4 + FISCAL_QUARTER FISCAL_QUARTER_ABS_VALUE , CALENDAR_YEAR * 4 + CALENDAR_QUARTER CALENDAR_QUARTER_ABS_VALUE
from DATE_DIM
where 'CURRENT' = 'CURRENT' and CALENDAR_YEAR = date_part('year', add_months(current_date, -1)) and CALENDAR_MONTH = date_part('month', add_months(current_date, -1)) or 'CURRENT' = cast(CALENDAR_YEAR * 100 + CALENDAR_MONTH as varchar( 20 ))) CONTROL on CONTROL.FISCAL_PERIOD_ABS_VALUE - (D.FISCAL_YEAR * 12 + D.FISCAL_MONTH) = 0 union all
select 'Last Month' PERIOD_KEY , 2 PERIOD_SORT , 'Last Month' PERIOD_CAPTION , min(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) START_PERIOD , max(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) END_PERIOD
from DATE_DIM D INNER JOIN (
select CALENDAR_YEAR, CALENDAR_QUARTER, CALENDAR_MONTH, FISCAL_YEAR, FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR * 12 + FISCAL_MONTH FISCAL_PERIOD_ABS_VALUE , CALENDAR_YEAR * 12 + CALENDAR_MONTH CALENDAR_PERIOD_ABS_VALUE , FISCAL_YEAR * 4 + FISCAL_QUARTER FISCAL_QUARTER_ABS_VALUE , CALENDAR_YEAR * 4 + CALENDAR_QUARTER CALENDAR_QUARTER_ABS_VALUE
from DATE_DIM
where 'CURRENT' = 'CURRENT' and CALENDAR_YEAR = date_part('year', add_months(current_date, -1)) and CALENDAR_MONTH = date_part('month', add_months(current_date, -1)) or 'CURRENT' = cast(CALENDAR_YEAR * 100 + CALENDAR_MONTH as varchar( 20 ))) CONTROL on CONTROL.FISCAL_PERIOD_ABS_VALUE - (D.FISCAL_YEAR * 12 + D.FISCAL_MONTH) = 1 union all
select 'Current Quarter' PERIOD_KEY , 3 PERIOD_SORT , 'Current Quarter' PERIOD_CAPTION , min(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) START_PERIOD , max(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) END_PERIOD
from DATE_DIM D INNER JOIN (
select CALENDAR_YEAR, CALENDAR_QUARTER, CALENDAR_MONTH, FISCAL_YEAR, FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR * 12 + FISCAL_MONTH FISCAL_PERIOD_ABS_VALUE , CALENDAR_YEAR * 12 + CALENDAR_MONTH CALENDAR_PERIOD_ABS_VALUE , FISCAL_YEAR * 4 + FISCAL_QUARTER FISCAL_QUARTER_ABS_VALUE , CALENDAR_YEAR * 4 + CALENDAR_QUARTER CALENDAR_QUARTER_ABS_VALUE
from DATE_DIM
where 'CURRENT' = 'CURRENT' and CALENDAR_YEAR = date_part('year', add_months(current_date, -1)) and CALENDAR_MONTH = date_part('month', add_months(current_date, -1)) or 'CURRENT' = cast(CALENDAR_YEAR * 100 + CALENDAR_MONTH as varchar( 20 ))) CONTROL on CONTROL.FISCAL_QUARTER_ABS_VALUE - (D.FISCAL_YEAR * 4 + D.FISCAL_QUARTER) = 0 union all
select 'Last Quarter' PERIOD_KEY , 4 PERIOD_SORT , 'Last Quarter' PERIOD_CAPTION , min(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) START_PERIOD , max(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) END_PERIOD
from DATE_DIM D INNER JOIN (
select CALENDAR_YEAR, CALENDAR_QUARTER, CALENDAR_MONTH, FISCAL_YEAR, FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR * 12 + FISCAL_MONTH FISCAL_PERIOD_ABS_VALUE , CALENDAR_YEAR * 12 + CALENDAR_MONTH CALENDAR_PERIOD_ABS_VALUE , FISCAL_YEAR * 4 + FISCAL_QUARTER FISCAL_QUARTER_ABS_VALUE , CALENDAR_YEAR * 4 + CALENDAR_QUARTER CALENDAR_QUARTER_ABS_VALUE
from DATE_DIM
where 'CURRENT' = 'CURRENT' and CALENDAR_YEAR = date_part('year', add_months(current_date, -1)) and CALENDAR_MONTH = date_part('month', add_months(current_date, -1)) or 'CURRENT' = cast(CALENDAR_YEAR * 100 + CALENDAR_MONTH as varchar( 20 ))) CONTROL on CONTROL.FISCAL_QUARTER_ABS_VALUE - (D.FISCAL_YEAR * 4 + D.FISCAL_QUARTER) = 1 union all
select 'YTD' PERIOD_KEY , 5 PERIOD_SORT , 'YTD' PERIOD_CAPTION , min(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) START_PERIOD , max(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) END_PERIOD
from DATE_DIM D INNER JOIN (
select CALENDAR_YEAR, CALENDAR_QUARTER, CALENDAR_MONTH, FISCAL_YEAR, FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR * 12 + FISCAL_MONTH FISCAL_PERIOD_ABS_VALUE , CALENDAR_YEAR * 12 + CALENDAR_MONTH CALENDAR_PERIOD_ABS_VALUE , FISCAL_YEAR * 4 + FISCAL_QUARTER FISCAL_QUARTER_ABS_VALUE , CALENDAR_YEAR * 4 + CALENDAR_QUARTER CALENDAR_QUARTER_ABS_VALUE
from DATE_DIM
where 'CURRENT' = 'CURRENT' and CALENDAR_YEAR = date_part('year', add_months(current_date, -1)) and CALENDAR_MONTH = date_part('month', add_months(current_date, -1)) or 'CURRENT' = cast(CALENDAR_YEAR * 100 + CALENDAR_MONTH as varchar( 20 ))) CONTROL on CONTROL.FISCAL_YEAR = D.FISCAL_YEAR and D.FISCAL_MONTH <= CONTROL.FISCAL_MONTH union all
select 'Prior YTD' PERIOD_KEY , 6 PERIOD_SORT , 'Prior YTD' PERIOD_CAPTION , min(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) START_PERIOD , max(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) END_PERIOD
from DATE_DIM D INNER JOIN (
select CALENDAR_YEAR, CALENDAR_QUARTER, CALENDAR_MONTH, FISCAL_YEAR, FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR * 12 + FISCAL_MONTH FISCAL_PERIOD_ABS_VALUE , CALENDAR_YEAR * 12 + CALENDAR_MONTH CALENDAR_PERIOD_ABS_VALUE , FISCAL_YEAR * 4 + FISCAL_QUARTER FISCAL_QUARTER_ABS_VALUE , CALENDAR_YEAR * 4 + CALENDAR_QUARTER CALENDAR_QUARTER_ABS_VALUE
from DATE_DIM
where 'CURRENT' = 'CURRENT' and CALENDAR_YEAR = date_part('year', add_months(current_date, -1)) and CALENDAR_MONTH = date_part('month', add_months(current_date, -1)) or 'CURRENT' = cast(CALENDAR_YEAR * 100 + CALENDAR_MONTH as varchar( 20 ))) CONTROL on CONTROL.FISCAL_YEAR - 1 = D.FISCAL_YEAR and D.FISCAL_MONTH <= CONTROL.FISCAL_MONTH)
select 'Current Month' PERIOD_KEY , 1 PERIOD_SORT , 'Current Month' PERIOD_CAPTION , min(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) START_PERIOD , max(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) END_PERIOD
from DATE_DIM D INNER JOIN (
select CALENDAR_YEAR, CALENDAR_QUARTER, CALENDAR_MONTH, FISCAL_YEAR, FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR * 12 + FISCAL_MONTH FISCAL_PERIOD_ABS_VALUE , CALENDAR_YEAR * 12 + CALENDAR_MONTH CALENDAR_PERIOD_ABS_VALUE , FISCAL_YEAR * 4 + FISCAL_QUARTER FISCAL_QUARTER_ABS_VALUE , CALENDAR_YEAR * 4 + CALENDAR_QUARTER CALENDAR_QUARTER_ABS_VALUE
from DATE_DIM
where 'CURRENT' = 'CURRENT' and CALENDAR_YEAR = date_part('year', add_months(current_date, -1)) and CALENDAR_MONTH = date_part('month', add_months(current_date, -1)) or 'CURRENT' = cast(CALENDAR_YEAR * 100 + CALENDAR_MONTH as varchar( 20 ))) CONTROL on CONTROL.FISCAL_PERIOD_ABS_VALUE - (D.FISCAL_YEAR * 12 + D.FISCAL_MONTH) = 0 union all
select 'Last Month' PERIOD_KEY , 2 PERIOD_SORT , 'Last Month' PERIOD_CAPTION , min(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) START_PERIOD , max(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) END_PERIOD
from DATE_DIM D INNER JOIN (
select CALENDAR_YEAR, CALENDAR_QUARTER, CALENDAR_MONTH, FISCAL_YEAR, FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR * 12 + FISCAL_MONTH FISCAL_PERIOD_ABS_VALUE , CALENDAR_YEAR * 12 + CALENDAR_MONTH CALENDAR_PERIOD_ABS_VALUE , FISCAL_YEAR * 4 + FISCAL_QUARTER FISCAL_QUARTER_ABS_VALUE , CALENDAR_YEAR * 4 + CALENDAR_QUARTER CALENDAR_QUARTER_ABS_VALUE
from DATE_DIM
where 'CURRENT' = 'CURRENT' and CALENDAR_YEAR = date_part('year', add_months(current_date, -1)) and CALENDAR_MONTH = date_part('month', add_months(current_date, -1)) or 'CURRENT' = cast(CALENDAR_YEAR * 100 + CALENDAR_MONTH as varchar( 20 ))) CONTROL on CONTROL.FISCAL_PERIOD_ABS_VALUE - (D.FISCAL_YEAR * 12 + D.FISCAL_MONTH) = 1 union all
select 'Current Quarter' PERIOD_KEY , 3 PERIOD_SORT , 'Current Quarter' PERIOD_CAPTION , min(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) START_PERIOD , max(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) END_PERIOD
from DATE_DIM D INNER JOIN (
select CALENDAR_YEAR, CALENDAR_QUARTER, CALENDAR_MONTH, FISCAL_YEAR, FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR * 12 + FISCAL_MONTH FISCAL_PERIOD_ABS_VALUE , CALENDAR_YEAR * 12 + CALENDAR_MONTH CALENDAR_PERIOD_ABS_VALUE , FISCAL_YEAR * 4 + FISCAL_QUARTER FISCAL_QUARTER_ABS_VALUE , CALENDAR_YEAR * 4 + CALENDAR_QUARTER CALENDAR_QUARTER_ABS_VALUE
from DATE_DIM
where 'CURRENT' = 'CURRENT' and CALENDAR_YEAR = date_part('year', add_months(current_date, -1)) and CALENDAR_MONTH = date_part('month', add_months(current_date, -1)) or 'CURRENT' = cast(CALENDAR_YEAR * 100 + CALENDAR_MONTH as varchar( 20 ))) CONTROL on CONTROL.FISCAL_QUARTER_ABS_VALUE - (D.FISCAL_YEAR * 4 + D.FISCAL_QUARTER) = 0 union all
select 'Last Quarter' PERIOD_KEY , 4 PERIOD_SORT , 'Last Quarter' PERIOD_CAPTION , min(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) START_PERIOD , max(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) END_PERIOD
from DATE_DIM D INNER JOIN (
select CALENDAR_YEAR, CALENDAR_QUARTER, CALENDAR_MONTH, FISCAL_YEAR, FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR * 12 + FISCAL_MONTH FISCAL_PERIOD_ABS_VALUE , CALENDAR_YEAR * 12 + CALENDAR_MONTH CALENDAR_PERIOD_ABS_VALUE , FISCAL_YEAR * 4 + FISCAL_QUARTER FISCAL_QUARTER_ABS_VALUE , CALENDAR_YEAR * 4 + CALENDAR_QUARTER CALENDAR_QUARTER_ABS_VALUE
from DATE_DIM
where 'CURRENT' = 'CURRENT' and CALENDAR_YEAR = date_part('year', add_months(current_date, -1)) and CALENDAR_MONTH = date_part('month', add_months(current_date, -1)) or 'CURRENT' = cast(CALENDAR_YEAR * 100 + CALENDAR_MONTH as varchar( 20 ))) CONTROL on CONTROL.FISCAL_QUARTER_ABS_VALUE - (D.FISCAL_YEAR * 4 + D.FISCAL_QUARTER) = 1 union all
select 'YTD' PERIOD_KEY , 5 PERIOD_SORT , 'YTD' PERIOD_CAPTION , min(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) START_PERIOD , max(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) END_PERIOD
from DATE_DIM D INNER JOIN (
select CALENDAR_YEAR, CALENDAR_QUARTER, CALENDAR_MONTH, FISCAL_YEAR, FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR * 12 + FISCAL_MONTH FISCAL_PERIOD_ABS_VALUE , CALENDAR_YEAR * 12 + CALENDAR_MONTH CALENDAR_PERIOD_ABS_VALUE , FISCAL_YEAR * 4 + FISCAL_QUARTER FISCAL_QUARTER_ABS_VALUE , CALENDAR_YEAR * 4 + CALENDAR_QUARTER CALENDAR_QUARTER_ABS_VALUE
from DATE_DIM
where 'CURRENT' = 'CURRENT' and CALENDAR_YEAR = date_part('year', add_months(current_date, -1)) and CALENDAR_MONTH = date_part('month', add_months(current_date, -1)) or 'CURRENT' = cast(CALENDAR_YEAR * 100 + CALENDAR_MONTH as varchar( 20 ))) CONTROL on CONTROL.FISCAL_YEAR = D.FISCAL_YEAR and D.FISCAL_MONTH <= CONTROL.FISCAL_MONTH union all
select 'Prior YTD' PERIOD_KEY , 6 PERIOD_SORT , 'Prior YTD' PERIOD_CAPTION , min(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) START_PERIOD , max(D.CALENDAR_YEAR * 100 + D.CALENDAR_MONTH) END_PERIOD
from DATE_DIM D INNER JOIN (
select CALENDAR_YEAR, CALENDAR_QUARTER, CALENDAR_MONTH, FISCAL_YEAR, FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR * 12 + FISCAL_MONTH FISCAL_PERIOD_ABS_VALUE , CALENDAR_YEAR * 12 + CALENDAR_MONTH CALENDAR_PERIOD_ABS_VALUE , FISCAL_YEAR * 4 + FISCAL_QUARTER FISCAL_QUARTER_ABS_VALUE , CALENDAR_YEAR * 4 + CALENDAR_QUARTER CALENDAR_QUARTER_ABS_VALUE
from DATE_DIM
where 'CURRENT' = 'CURRENT' and CALENDAR_YEAR = date_part('year', add_months(current_date, -1)) and CALENDAR_MONTH = date_part('month', add_months(current_date, -1)) or 'CURRENT' = cast(CALENDAR_YEAR * 100 + CALENDAR_MONTH as varchar( 20 ))) CONTROL on CONTROL.FISCAL_YEAR - 1 = D.FISCAL_YEAR and D.FISCAL_MONTH <= CONTROL.FISCAL_MONTH)
, T as (
select DATE_DIM.FISCAL_YEAR_STR CALENDAR_YEAR , DATE_DIM.FISCAL_MONTH_STR_13 CALENDAR_MONTH , DATE_DIM.CALENDAR_YEAR * 100 + DATE_DIM.CALENDAR_MONTH YYYYMM
from DATE_DIM DATE_DIM)
, FACT_SALES8 as (
select FACT_SALES.CALENDAR_YEAR CALENDAR_YEAR , FACT_SALES.CALENDAR_MONTH CALENDAR_MONTH , FACT_SALES.NUM_RECS SALES
from FACT_SALES),
select T.YYYYMM YYYYMM , sum(FACT_SALES8.SALES) SALES
from FACT_SALES8
,T
, RELATIVE_TIME Relative_Time9
where Relative_Time9.PERIOD_KEY = 'YTD' and T.CALENDAR_YEAR = FACT_SALES8.CALENDAR_YEAR and T.CALENDAR_MONTH = FACT_SALES8.CALENDAR_MONTH
and T.YYYYMM between Relative_Time9.START_PERIOD and Relative_Time9.END_PERIOD
group by T.YYYYMM
4.4 Relational with relative time calculationsselect DATE_DIM.FISCAL_YEAR_STR CALENDAR_YEAR , DATE_DIM.FISCAL_MONTH_STR_13 CALENDAR_MONTH , DATE_DIM.CALENDAR_YEAR * 100 + DATE_DIM.CALENDAR_MONTH YYYYMM
from DATE_DIM DATE_DIM)
, FACT_SALES8 as (
select FACT_SALES.CALENDAR_YEAR CALENDAR_YEAR , FACT_SALES.CALENDAR_MONTH CALENDAR_MONTH , FACT_SALES.NUM_RECS SALES
from FACT_SALES),
select T.YYYYMM YYYYMM , sum(FACT_SALES8.SALES) SALES
from FACT_SALES8
,T
, RELATIVE_TIME Relative_Time9
where Relative_Time9.PERIOD_KEY = 'YTD' and T.CALENDAR_YEAR = FACT_SALES8.CALENDAR_YEAR and T.CALENDAR_MONTH = FACT_SALES8.CALENDAR_MONTH
and T.YYYYMM between Relative_Time9.START_PERIOD and Relative_Time9.END_PERIOD
group by T.YYYYMM
The SQL generated below is as expected. Three tables, or FACT, and DATE_TIME are joined to yield the best performance. No local processing is needed. Please also note that RELTIVE_TIME is not involved in query, instead, the time range (201611,201706) is directly given.
with T as (
select DATE_DIM.FISCAL_YEAR_STR CALENDAR_YEAR , DATE_DIM.FISCAL_MONTH_STR_13 CALENDAR_MONTH , DATE_DIM.CALENDAR_YEAR * 100 + DATE_DIM.CALENDAR_MONTH YYYYMM
from DATE_DIM DATE_DIM)
, FACT_SALES6 as (
select FACT_SALES.CALENDAR_YEAR CALENDAR_YEAR , FACT_SALES.CALENDAR_MONTH CALENDAR_MONTH , FACT_SALES.NUM_RECS SALES
from FACT_SALES)
select T.YYYYMM YYYYMM , sum(FACT_SALES6.SALES) SALES
from T, FACT_SALES6
where T.YYYYMM between 201611 and 201706 and T.CALENDAR_YEAR = FACT_SALES6.CALENDAR_YEAR and T.CALENDAR_MONTH = FACT_SALES6.CALENDAR_MONTH
group by T.YYYYMM
5 Note
Additional relative time period can be added as per request in Framework manager, without database change.
Excellent post.keep posting more topics
ReplyDeleteThank you.
cognos training