Tuesday, August 1, 2017

How to extract Datawarehouse Bus Matrix from Cognos Framework Manager Model


Data Warehouse Bus Matrix, shown below, is a key design tool representing the organization’s core business processes and associated dimensionality. It’s the architectural blueprint providing the top-down strategic perspective to ensure data in the DW/BI environment can be integrated across the enterprise. This design tool can be used at different level during the life cycle of project:

-       High level requirement, decide what dimension with what facts should be built

-       Communication between business requirement and technical design

-       Detail design to denote the relationship between columns from fact and dimension.

We can also some retrieve this information from Cognos framework manager model.  There are different ways to get this information. This document provides a very practical way to do it.
Step 1:  Open model.xml from FM model from XML notepad, then remove all nodes except relationships, then save it as model2.xml
 
Step 2:  Open saved model2.xml from Microsoft Excel with spreadsheet
 

Step 3:  Further remove all columns except 4 major columns as below
-       Remove all detail columns
-       Remove name space such as business layer
-       Make sure all dimension listed on left and facts on right side
-       Remove duplicated records
 

Step 4:  Make the spreadsheet as three columns below

Step 5:  Pivot table and filter whatever dimensions and facts, please note that you can use grand total to identify usage of dimension and sort them


Step 6:  Copy spreadsheet and paste as value and then make it is presentable as the first screenshot.

Saturday, July 1, 2017

How to create customized date dimension properties to speed up report development (DMR)

It not very easy to get different date description in report development, especially with DMR model. The solution is to create customized date dimension properties in framework manager. It will provide following advantages:
1)     Unified logic in framework manager, it will be much easy to change if needed.
2)     Speed up report development, as report can simply just  use the domain properties, or role value

select
           DimDate.FISCAL_YEAR_STR  as  Fiscal_Year__YYYY_,
           ('FY' || substring(DimDate.FISCAL_YEAR_STR from 3 for 2))  as  Fiscal_Year__FYNN_,
           DimDate.FISCAL_QUARTER_STR  as  Fiscal_Quarter__QN_,
           DimDate.FISCAL_MONTH_STR_13  as  Fiscal_Month__13_,
           ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)  as  Fiscal_Period_ABS_Value__N_,
           ((cast(DimDate.FISCAL_YEAR_STR as int) * 4) + case DimDate.FISCAL_QUARTER_STR
  when 'Q1' then 1
  when 'Q2' then 2
  when 'Q3' then 3
  when 'Q4' then 4
end)  as  Fiscal_Quarter_ABS_Value__N_,
           ((DimDate.FISCAL_YEAR_STR || ' - ') || DimDate.FISCAL_QUARTER_STR)  as  Fiscal_YQ__YYYYQN_,
           (DimDate.FISCAL_QUARTER_STR || substr(DimDate.FISCAL_YEAR_STR,3,2))  as  Fis_YQ__QNYY_,
           ((DimDate.FISCAL_QUARTER_STR || '-') || substr(DimDate.FISCAL_YEAR_STR,3,2))  as  c9,
           (2016 - DimDate.FISCAL_YEAR_STR)  as  Rolling_Fiscal_Year__iN_,
           cast((2016 - DimDate.FISCAL_YEAR_STR) as varchar (20))  as  Rolling_Fiscal_Year_String__N_,
           (8068 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 4) + case DimDate.FISCAL_QUARTER_STR
  when 'Q1' then 1
  when 'Q2' then 2
  when 'Q3' then 3
  when 'Q4' then 4
end))  as  Rolling_Fiscal_Quarter__iN_,
           cast((8068 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 4) + case DimDate.FISCAL_QUARTER_STR
  when 'Q1' then 1
  when 'Q2' then 2
  when 'Q3' then 3
  when 'Q4' then 4
end)) as varchar (20))  as  Rolling_Fiscal_Quarter_Str__N_,
           (24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end))  as  Rolling_Fiscal_Period__iN_,
           cast((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) as varchar (20))  as  Rolling_Period_String__N_,
           case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),12) <= mod((24202 - 1),12)) then 'FYTD'
             else 'NON-FYTD'
           end  as  FYTD_Ctg__FYTD_NON_FYTD_,
           case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),3) <= mod((24202 - 1),3)) then 'FQTD'
             else 'NON-FQTD'
           end  as  FQTD_Ctg__FQTD_NON_FQTD_,
           ((cast((2016 - DimDate.FISCAL_YEAR_STR) as varchar (20)) || '-') || case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),12) <= mod((24202 - 1),12)) then 'FYTD'
  else 'NON-FYTD'
end)  as  R_FYTD_Ctg__N_FYTD_NON_FYTD_,
           (case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),12) <= mod((24202 - 1),12)) then 'FYTD'
  else 'NON-FYTD'
end || substring(DimDate.FISCAL_YEAR_STR from 3 for 2))  as  R_FYTD_Ctg_FYTDYY_NON_FYTDYY_,
           ((cast((8068 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 4) + case DimDate.FISCAL_QUARTER_STR
  when 'Q1' then 1
  when 'Q2' then 2
  when 'Q3' then 3
  when 'Q4' then 4
end)) as varchar (20)) || '-') || case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),3) <= mod((24202 - 1),3)) then 'FQTD'
  else 'NON-FQTD'
end)  as  R_FQTD_Ctg__N_FQTD_NON_FQTD_,
           (2016 - DimDate.FISCAL_YEAR_STR)  as  Rolling_FY__iN__By_Lag1P,
           cast((2016 - DimDate.FISCAL_YEAR_STR) as varchar (20))  as  Rolling_FY_Str__N__By_Lag1P,
           (8067 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 4) + case DimDate.FISCAL_QUARTER_STR
  when 'Q1' then 1
  when 'Q2' then 2
  when 'Q3' then 3
  when 'Q4' then 4
end))  as  Rolling_FQ__iN__By_Lag1P,
           cast((8067 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 4) + case DimDate.FISCAL_QUARTER_STR
  when 'Q1' then 1
  when 'Q2' then 2
  when 'Q3' then 3
  when 'Q4' then 4
end)) as varchar (20))  as  Rolling_FQ_Str__N__By_Lag1P,
           (24201 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end))  as  Rolling_FP__iN__By_Lag1P,
           cast((24201 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) as varchar (20))  as  Rolling_P_Str__N__By_Lag1P,
           case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),12) <= mod((24202 - 2),12)) then 'FYTD'
             else 'NON-FYTD'
           end  as  FYTD_Ctg_FYTD__Lag1P,
           case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),3) <= mod((-2),3)) then 'FQTD'
             else 'NON-FQTD'
           end  as  FQTD_Ctg_FQTD__Lag1P,
           ((cast((2016 - DimDate.FISCAL_YEAR_STR) as varchar (20)) || '-') || case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),12) <= mod((24202 - 2),12)) then 'FYTD'
  else 'NON-FYTD'
end)  as  R_FYTD_Ctg__NFYTD__By_Lag1P,
           (case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),12) <= mod((24202 - 2),12)) then 'FYTD'
  else 'NON-FYTD'
end || substring(DimDate.FISCAL_YEAR_STR from 3 for 2))  as  R_FYTD_Ctg__FYTDYY_Lag1P,
           case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),3) <= mod((-2),3)) then 'FQTD'
             else 'NON-FQTD'
           end  as  R_FQTD_Ctg_FQTDYY_Lag1P,
           (2016 - DimDate.FISCAL_YEAR_STR)  as  R_FY__iN__By_Lag2P,
           cast((2016 - DimDate.FISCAL_YEAR_STR) as varchar (20))  as  R_FY_Str_N__By_Lag2P,
           (8067 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 4) + case DimDate.FISCAL_QUARTER_STR
  when 'Q1' then 1
  when 'Q2' then 2
  when 'Q3' then 3
  when 'Q4' then 4
end))  as  R_FQ__iN__By_Lag2P,
           cast((8067 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 4) + case DimDate.FISCAL_QUARTER_STR
  when 'Q1' then 1
  when 'Q2' then 2
  when 'Q3' then 3
  when 'Q4' then 4
end)) as varchar (20))  as  R_FQ_Str__N__By_Lag2P,
           (24200 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end))  as  R_FP__iN__By_Lag2P,
           cast((24200 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) as varchar (20))  as  R_P_Str__N__By_Lag2P,
           case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),12) <= mod((24202 - 3),12)) then 'FYTD'
             else 'NON-FYTD'
           end  as  FYTD_Ctg__FYTD__By_Lag2P,
           case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),3) <= mod((-3),3)) then 'FQTD'
             else 'NON-FQTD'
           end  as  FQTD_Ctg__FQTD__By_Lag2P,
           ((cast((2016 - DimDate.FISCAL_YEAR_STR) as varchar (20)) || '-') || case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),12) <= mod((24202 - 3),12)) then 'FYTD'
  else 'NON-FYTD'
end)  as  R_FYTD_Ctg__NFYTD__By_Lag2P,
           (case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),12) <= mod((24202 - 3),12)) then 'FYTD'
  else 'NON-FYTD'
end || substring(DimDate.FISCAL_YEAR_STR from 3 for 2))  as  R_FYTD_Ctg__FYTDYY_Lag2P,
           case when (mod((((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end) - 1),3) <= mod((-3),3)) then 'FQTD'
             else 'NON-FQTD'
           end  as  R_FQTD_Ctg__FQTDYY_Lag2P,
           case
             when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 0) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 11)) then 'Rolling First 12 Periods'
             when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 12) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 23)) then 'Rolling Second 12 Periods'
             when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 24) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 25)) then 'Rolling Series Remainiing 2 Periods'
             else 'Non-Rolling 26 Periods'
           end  as  Rolling_26_P_Sub_Ctg,
           case
             when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
             when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
             else DimDate.FISCAL_YEAR_STR
           end  as  Calendar_Year__YYYY_,
           (case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
  when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
  else DimDate.FISCAL_YEAR_STR
end || case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
  when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
  else DimDate.FISCAL_MONTH_STR_13
end)  as  Calendar_YM__YYYYMM_,
           ((case case
      when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
      when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
      else DimDate.FISCAL_MONTH_STR_13
    end
  when '02' then 'Feb'
  when '03' then 'Mar'
  when '04' then 'Apr'
  when '05' then 'May'
  when '06' then 'Jun'
  when '07' then 'Jul'
  when '08' then 'Aug'
  when '09' then 'Sep'
  when '10' then 'Oct'
  when '11' then 'Nov'
  when '12' then 'Dec'
  when '01' then 'Jan'
  else 'Ukn'
end || '-') || case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
  when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
  else DimDate.FISCAL_YEAR_STR
end)  as  Ca_YM__Mmm_YYYY_,
           ((case case
      when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
      when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
      else DimDate.FISCAL_MONTH_STR_13
    end
  when '02' then 'February'
  when '03' then 'March'
  when '04' then 'April'
  when '05' then 'May'
  when '06' then 'June'
  when '07' then 'July'
  when '08' then 'August'
  when '09' then 'September'
  when '10' then 'October'
  when '11' then 'November'
  when '12' then 'December'
  when '01' then 'January'
  else 'Ukn'
end || ' ') || case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
  when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
  else DimDate.FISCAL_YEAR_STR
end)  as  Ca_YM__Month_YYYY_,
           case case
                 when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
                 when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
                 else DimDate.FISCAL_MONTH_STR_13
               end
             when '02' then 'F'
             when '03' then 'M'
             when '04' then 'A'
             when '05' then 'M'
             when '06' then 'J'
             when '07' then 'J'
             when '08' then 'A'
             when '09' then 'S'
             when '10' then (('Oct' || '-') || substring(case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
  when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
  else DimDate.FISCAL_YEAR_STR
end from 3 for 2))
             when '11' then 'N'
             when '12' then 'D'
             when '01' then 'J'
             else 'Unk'
           end  as  Ca_Month__M_Oct_YY_,
           case
             when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
             when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
             else DimDate.FISCAL_MONTH_STR_13
           end  as  Ca_Month__MM_,
           case case
                 when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
                 when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
                 else DimDate.FISCAL_MONTH_STR_13
               end
             when '11' then 1
             when '12' then 2
             when '01' then 3
             when '02' then 4
             when '03' then 5
             when '04' then 6
             when '05' then 7
             when '06' then 8
             when '07' then 9
             when '08' then 10
             when '09' then 11
             when '10' then 12
             else 13
           end  as  Ca_M_Sort_In_FM,
           case case
                 when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
                 when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
                 else DimDate.FISCAL_MONTH_STR_13
               end
             when '02' then 'F'
             when '03' then 'M'
             when '04' then 'A'
             when '05' then 'M'
             when '06' then 'J'
             when '07' then 'J'
             when '08' then 'A'
             when '09' then 'S'
             when '10' then 'O'
             when '11' then 'N'
             when '12' then 'D'
             when '01' then 'J'
             else 'U'
           end  as  Ca_Month__M_,
           case case
                 when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
                 when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
                 else DimDate.FISCAL_MONTH_STR_13
               end
             when '02' then 'Feb'
             when '03' then 'Mar'
             when '04' then 'Apr'
             when '05' then 'May'
             when '06' then 'Jun'
             when '07' then 'Jul'
             when '08' then 'Aug'
             when '09' then 'Sep'
             when '10' then 'Oct'
             when '11' then 'Nov'
             when '12' then 'Dec'
             when '01' then 'Jan'
             else 'Unk'
           end  as  Ca_Month__Mmm_,
           case
             when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 0) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 11)) then 'Rolling 12 Periods'
             when ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) < 0) then 'The Future Periods Non-Rolling 12 Periods'
             else 'Past Non-Rolling 12 Periods'
           end  as  Rolling_12_Period_Category,
           case
             when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 0) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 12)) then 'Rolling 13 Periods'
             when ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) < 0) then 'The Future Periods Non-Rolling 13 Periods'
             else 'Past Non-Rolling 13 Periods'
           end  as  Rolling_13_Period_Category,
           case
             when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 0) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 13)) then 'Rolling 14 Periods'
             when ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) < 0) then 'The Future Periods Non-Rolling 14 Periods'
             else 'Past Non-Rolling 14 Periods'
           end  as  Rolling_14_Period_Category,
           case
             when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 0) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 14)) then 'Rolling 15 Periods'
             when ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) < 0) then 'The Future Periods Non-Rolling 15 Periods'
             else 'Past Non-Rolling 15 Periods'
           end  as  Rolling_15_Period_Category,
           case
             when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 0) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 15)) then 'Rolling 16 Periods'
             when ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) < 0) then 'The Future Periods Non-Rolling 16 Periods'
             else 'Past Non-Rolling 16 Periods'
           end  as  Rolling_16_Period_Category,
           case
             when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 0) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 17)) then 'Rolling 18 Periods'
             when ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) < 0) then 'The Future Periods Non-Rolling 18 Periods'
             else 'Past Non-Rolling 18 Periods'
           end  as  Rolling_18_Period_Category,
           case
             when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 0) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 22)) then 'Rolling 23 Periods'
             when ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) < 0) then 'The Future Periods Non-Rolling 23 Periods'
             else 'Past Non-Rolling 23 Periods'
           end  as  Rolling_23_Periods_Category,
           case
             when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 0) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 25)) then 'Rolling 26 Periods'
             when ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) < 0) then 'The Future Periods Non-Rolling 26 Periods'
             else 'Past Non-Rolling 26 Periods'
           end  as  Rolling_26_Period_Category,
           case
             when (((24200 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 0) and ((24200 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 11)) then 'Rolling 12 Periods'
             when ((24200 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) < 0) then 'The Future Periods Non-Rolling 12 Periods'
             else 'Past Non-Rolling 12 Periods'
           end  as  R_12_P_Ctg_By_Lag2P,
           mod(((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) + 12000),12)  as  RP_in_12_P_Cycle__iN_,
           ((DimDate.FISCAL_YEAR_STR || ' - ') || DimDate.FISCAL_MONTH_STR_13)  as  Fiscal_Year_And_Month__YYYY13_,
           floor(((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) / 12))  as  Repet_R_Cycl_in_12_PCycl__iN_,
           cast(mod(((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) + 12000),12) as varchar (20))  as  Repet_R_Period_in_12_PCycle,
           cast(floor(((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) / 12)) as varchar (20))  as  Repet_R_Cycle_in_12_PCycle_Str,
           ((cast(mod(((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) + 12000),12) as varchar (20)) || ' - ') || cast(floor(((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) / 12)) as varchar (20)))  as  Repet_RP_in_12_P_Cycle_Full,
           ((cast(floor(((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) / 12)) as varchar (20)) || ' - ') || cast(mod(((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) + 12000),12) as varchar (20)))  as  Repet_R_Cycl_in_12P_Cycl_Full,
           (2016 - case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
  when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
  else DimDate.FISCAL_YEAR_STR
end)  as  Rolling_Calendar_Year__iN_,
           cast((2016 - case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
  when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
  else DimDate.FISCAL_YEAR_STR
end) as varchar (20))  as  Rolling_CY_String__N_,
           cast((8067 - ((cast(case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
  when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
  else DimDate.FISCAL_YEAR_STR
end as int) * 4) + case DimDate.CALENDAR_QUARTER_STR
  when 'Q1' then 1
  when 'Q2' then 2
  when 'Q3' then 3
  when 'Q4' then 4
end)) as varchar (20))  as  c71,
           (8067 - ((cast(case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
  when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
  else DimDate.FISCAL_YEAR_STR
end as int) * 4) + case DimDate.CALENDAR_QUARTER_STR
  when 'Q1' then 1
  when 'Q2' then 2
  when 'Q3' then 3
  when 'Q4' then 4
end))  as  Rolling_Calendar_Quarter__iN_,
           ((cast(case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
  when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
  else DimDate.FISCAL_YEAR_STR
end as int) * 12) + cast(case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
  when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
  else DimDate.FISCAL_MONTH_STR_13
end as int))  as  Ca_Period_ABS_Value__N_,
           DimDate.CALENDAR_QUARTER_STR  as  Calendar_Quarter__QN_,
           ((cast(case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
  when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
  else DimDate.FISCAL_YEAR_STR
end as int) * 4) + case DimDate.CALENDAR_QUARTER_STR
  when 'Q1' then 1
  when 'Q2' then 2
  when 'Q3' then 3
  when 'Q4' then 4
end)  as  Ca_Quarter_ABS_Value__N_,
           (8067 - ((cast(case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
  when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
  else DimDate.FISCAL_YEAR_STR
end as int) * 4) + case DimDate.CALENDAR_QUARTER_STR
  when 'Q1' then 1
  when 'Q2' then 2
  when 'Q3' then 3
  when 'Q4' then 4
end))  as  Rolling_CQ__iN_,
           cast((8067 - ((cast(case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
  when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
  else DimDate.FISCAL_YEAR_STR
end as int) * 4) + case DimDate.CALENDAR_QUARTER_STR
  when 'Q1' then 1
  when 'Q2' then 2
  when 'Q3' then 3
  when 'Q4' then 4
end)) as varchar (20))  as  Rolling_CQ_Str__N_,
           case when (mod((((cast(case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
  when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
  else DimDate.FISCAL_YEAR_STR
end as int) * 12) + cast(case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
  when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
  else DimDate.FISCAL_MONTH_STR_13
end as int)) - 1),12) <= mod((24200 - 1),12)) then 'CYTD'
             else 'NON-CYTD'
           end  as  CYTD_Ctg__CYTD_,
           case when (mod((((cast(case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
  when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
  else DimDate.FISCAL_YEAR_STR
end as int) * 12) + cast(case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
  when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
  else DimDate.FISCAL_MONTH_STR_13
end as int)) - 1),3) <= mod((24200 - 1),3)) then 'FQTD'
             else 'NON-FQTD'
           end  as  CQTD_Ctg__CQTD_,
           ((cast((2016 - case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
  when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
  else DimDate.FISCAL_YEAR_STR
end) as varchar (20)) || '-') || case when (mod((((cast(case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
  when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
  else DimDate.FISCAL_YEAR_STR
end as int) * 12) + cast(case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
  when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
  else DimDate.FISCAL_MONTH_STR_13
end as int)) - 1),12) <= mod((24200 - 1),12)) then 'CYTD'
  else 'NON-CYTD'
end)  as  R_CYTD_Ctg__N_CYTD_,
           (case when (mod((((cast(case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
  when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
  else DimDate.FISCAL_YEAR_STR
end as int) * 12) + cast(case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
  when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
  else DimDate.FISCAL_MONTH_STR_13
end as int)) - 1),12) <= mod((24200 - 1),12)) then 'CYTD'
  else 'NON-CYTD'
end || substring(case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
  when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
  else DimDate.FISCAL_YEAR_STR
end from 3 for 2))  as  R_CYTD_Ctg__CYTDYY_,
           ((cast((8067 - ((cast(case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
  when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
  else DimDate.FISCAL_YEAR_STR
end as int) * 4) + case DimDate.CALENDAR_QUARTER_STR
  when 'Q1' then 1
  when 'Q2' then 2
  when 'Q3' then 3
  when 'Q4' then 4
end)) as varchar (20)) || '-') || case when (mod((((cast(case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
  when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
  else DimDate.FISCAL_YEAR_STR
end as int) * 12) + cast(case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
  when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
  else DimDate.FISCAL_MONTH_STR_13
end as int)) - 1),3) <= mod((24200 - 1),3)) then 'FQTD'
  else 'NON-FQTD'
end)  as  R_CQTD_Ctg__N_CQTD_,
           ('CY' || substring(case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
  when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
  else DimDate.FISCAL_YEAR_STR
end from 3 for 2))  as  Ca_Year__CYNN_,
           ((case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
  when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
  else DimDate.FISCAL_YEAR_STR
end || ' - ') || DimDate.CALENDAR_QUARTER_STR)  as  Ca_YQ__YYYYQN_,
           DimDate.CALENDAR_FIRST_DAY_OF_MONTH  as  Ca_1st_Day_Of_Month,
           DimDate.CALENDAR_LAST_DAY_OF_MONTH  as  Ca_Lst_Day_Of_Month,
           DimDate.CALENDAR_FIRST_DAY_OF_YEAR  as  Ca_1st_Day_Of_Year,
           DimDate.CALENDAR_LAST_DAY_OF_YEAR  as  Ca_Lst_Day_Of_Year,
           to_char(DimDate.CALENDAR_FIRST_DAY_OF_MONTH,'YYYY-MM-DD')  as  Ca_1st_DOfM__YYYY_MM_01_,
           to_char(DimDate.CALENDAR_LAST_DAY_OF_MONTH,'YYYY-MM-DD')  as  Ca_Lst_DOfM_YYYY_MM_31_,
           to_char(DimDate.CALENDAR_FIRST_DAY_OF_MONTH,'YYYY-Mon-DD')  as  Ca_1st_DOfM__YYYY_Mmm_01_,
           to_char(DimDate.CALENDAR_LAST_DAY_OF_MONTH,'YYYY-Mon-DD')  as  Ca_Lst_DOfM__YYYY_Mmm_31_,
           ((((substr(to_char(DimDate.CALENDAR_LAST_DAY_OF_MONTH,'YYYY-Mon-DD'),10,2) || '-') || substr(to_char(DimDate.CALENDAR_LAST_DAY_OF_MONTH,'YYYY-Mon-DD'),6,3)) || '-') || substr(to_char(DimDate.CALENDAR_LAST_DAY_OF_MONTH,'YYYY-Mon-DD'),3,2))  as  Ca_Lst_DOfM__31_Mmm_YY_,
           DimDate.FISCAL_QUERTER_END_MONTH  as  Fis_Q_End_Mon__YYYY01_,
           ((case substring(DimDate.FISCAL_QUERTER_END_MONTH from 5 for 2)
  when '01' then 'Jan'
  when '02' then 'Feb'
  when '03' then 'Mar'
  when '04' then 'Apr'
  when '05' then 'May'
  when '06' then 'Jun'
  when '07' then 'Jul'
  when '08' then 'Aug'
  when '09' then 'Sep'
  when '10' then 'Oct'
  when '11' then 'Nov'
  when '12' then 'Dec'
  else 'Unk'
end || '-') || substring(DimDate.FISCAL_QUERTER_END_MONTH from 3 for 2))  as  Fis_Q_End_Mon__Jan_YY_,
           ((case case
      when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
      when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
      else DimDate.FISCAL_MONTH_STR_13
    end
  when '02' then 'Feb'
  when '03' then 'Mar'
  when '04' then 'Apr'
  when '05' then 'May'
  when '06' then 'Jun'
  when '07' then 'Jul'
  when '08' then 'Aug'
  when '09' then 'Sep'
  when '10' then 'Oct'
  when '11' then 'Nov'
  when '12' then 'Dec'
  when '01' then 'Jan'
  else 'Ukn'
end || '-') || substring(case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
  when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
  else DimDate.FISCAL_YEAR_STR
end from 3 for 2))  as  Ca_YM__Mmm_YY_,
           ((case substring(cast((100 + (floor(((cast(case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then DimDate.FISCAL_MONTH_STR_13
  when DimDate.FISCAL_MONTH_STR_13 = '13' then '01'
  else DimDate.FISCAL_MONTH_STR_13
end as int) + 2) / 3)) * 3)) as varchar (20)) from 2 for 2)
  when '01' then 'Jan'
  when '02' then 'Feb'
  when '03' then 'Mar'
  when '04' then 'Apr'
  when '05' then 'May'
  when '06' then 'Jun'
  when '07' then 'Jul'
  when '08' then 'Aug'
  when '09' then 'Sep'
  when '10' then 'Oct'
  when '11' then 'Nov'
  when '12' then 'Dec'
end || '-') || substring(case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
  when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
  else DimDate.FISCAL_YEAR_STR
end from 3 for 2))  as  Ca_Q_End_Mon_Mar_YY_,
           (DimDate.CALENDAR_QUARTER_STR || substring(case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
  when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
  else DimDate.FISCAL_YEAR_STR
end from 3 for 2))  as  Calendar_YQ__QNYY_,
           ((case
  when (DimDate.FISCAL_MONTH_STR_13 in ('11','12')) then cast((cast(DimDate.FISCAL_YEAR_STR as integer) - 1) as varchar (20))
  when DimDate.FISCAL_MONTH_STR_13 = '13' then DimDate.FISCAL_YEAR_STR
  else DimDate.FISCAL_YEAR_STR
end || ' - ') || DimDate.CALENDAR_QUARTER_STR)  as  Calendar_YQ__YYYYQN_,
           case
             when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 0) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 11)) then 'Rolling First 12 Periods'
             when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 12) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 29)) then 'Rolling Series Remainiin 18 Periods'
             else 'Non-Rolling 30 Periods'
           end  as  Rolling_30_P_Sub_Ctg,
           case
             when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 0) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 29)) then 'Rolling 30 Periods'
             when ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) < 0) then 'The Future Periods Non-Rolling 30 Periods'
             else 'Past Non-Rolling 30 Periods'
           end  as  Rolling_30_Period_Category,
           case
             when (((8068 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 4) + case DimDate.FISCAL_QUARTER_STR
  when 'Q1' then 1
  when 'Q2' then 2
  when 'Q3' then 3
  when 'Q4' then 4
end)) >= 0) and ((8068 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 4) + case DimDate.FISCAL_QUARTER_STR
  when 'Q1' then 1
  when 'Q2' then 2
  when 'Q3' then 3
  when 'Q4' then 4
end)) <= 31)) then 'Rolling 32 Fiscal Qarters'
             when ((8068 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 4) + case DimDate.FISCAL_QUARTER_STR
  when 'Q1' then 1
  when 'Q2' then 2
  when 'Q3' then 3
  when 'Q4' then 4
end)) < 0) then 'The Future Periods Non-Rolling 32 Fiscal Qarters'
             else 'Past Non-Rolling 32 Fiscal Qarters'
           end  as  c102,
           cast(to_char(DimDate.CALENDAR_LAST_DAY_OF_MONTH,'Month DD, YYYY') as date)  as  Ca_Lst_DOfM__Mmm_31__YYYY_,
           case
             when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 0) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 11)) then 'Rolling First 12 Periods'
             when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 12) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 59)) then 'Rolling Series Remainiin 48 Periods'
             else 'Non-Rolling 60 Periods'
           end  as  Rolling_60_P_Sub_Ctg,
           case
             when (((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) >= 0) and ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) <= 59)) then 'Rolling 60 Periods'
             when ((24202 - ((cast(DimDate.FISCAL_YEAR_STR as int) * 12) + case
  when DimDate.FISCAL_MONTH_STR_13 = '11' then 1
  when DimDate.FISCAL_MONTH_STR_13 = '12' then 2
  when DimDate.FISCAL_MONTH_STR_13 = '13' then 3
  else (cast(DimDate.FISCAL_MONTH_STR_13 as int) + 2)
end)) < 0) then 'The Future Periods Non-Rolling 60 Periods'
             else 'Past Non-Rolling 60 Periods'
           end  as  Rolling_60_Period_Category
     from DimDate