Wednesday, February 1, 2017

how to write a effective SQL query to retrieve most recent records

It is often required to retrieve most recent record when building ETL to create data mart. The source table is normally very big, which make us pay attention to performance .The performance could be an issue even with data warehousing appliance like Netezza database. Three methods are introduced in this document.  Though many people use the method 1 (Use a Complete Subquery), it is not a good solution, as this method could results in duplicated records, and poor performance. Based on my experience, the method 3 (Use Window Functions)  is best option, as it provides you no duplicates and best performance.

 Use a Complete Subquery

SELECT F.*

FROM   SourceFact F

INNER JOIN (SELECT SourceID,Max(TIMESTAMP_SUBMITTED) AS LAST_TIMESTAMP_SUBMITTED

            FROM   SourceFact

            GROUP  BY SourceID) F1

ON F.SourceID = F1.SourceID

AND F.TIMESTAMP_SUBMITTED = F1.last_TIMESTAMP_SUBMITTED;

 

if more than one sourceID with the same TIMESTAMP_SUBMITTED, then more than  records for this sourceID will be selected, which breaks the uniqueness of this query. Also, the performance is very poor when the table SourceFact is very big.

 
Use Correlated Subqueries
Correlated subqueries are subqueries that depend on the outer query. It’s like a for loop in SQL. The subquery will run once for each row in the outer query:

SELECT *

FROM   SourceFact

WHERE  ( SourceID, TIMESTAMP_SUBMITTED )

IN (SELECT SourceID,Max(TIMESTAMP_SUBMITTED) AS LAST_TIMESTAMP_SUBMITTED

    FROM   SourceFact

    GROUP  BY SourceID);

 This solution is even worse for big table, as it result in very poor performance.
 

Use Window Functions
Window function is little complicated, but it yields much better performance, 10-100 times for big table. In addition, it will grantee the uniqueness, which is extremely important in data warehouse design.

SELECT F.*

FROM   (SELECT ROW_NUMBER() OVER (

        PARTITION BY SourceID

ORDER BY TIMESTAMP_SUBMITTED desc)      LAST_TIMESTAMP_SUBMITTED,

          *

        FROM   SourceFact) F

WHERE  F.last_TIMESTAMP_SUBMITTED = 1;  

Tuesday, January 31, 2017

How to avoid problem with currency data load in IBM OpenPages

Check list of all posts
  1. Context

When input data on OpenPage interface, you don’t have any problem, as system will automatically convert local currency amount into base current amount. The problem is to load currency amount with fastmap, the currency amount will be automatically converted into base currency amount, which is not correct from business point of view. Business want to see the exact the same amount as provided from fastmap.  The document provides a solution about how to resolve this problem.
  1. Work flow

There are two tiers behind the scene:
  • The default built-in feature, which is to convert local amount into base currency amount using the current exchange rate.
  • The trigger, in which recognition date is used to get historical exchange rate to get the base currency amount, also get performance many other action, such as calculate net loss from loss impact and loss recovery.
Even we can disable trigger, system will still convert local currency amount to base currency amount. The problem still persists. We cannot disable the built-in feature. Also, disable triggers is not solution for ongoing processing, or on monthly or quarterly basis.
  1. Solutions

When exchange rate is provided, Built-in function will use the provided exchange rate. This function opens a door for us to provide exchange rate to calculate base current amount. We can provide exchange rate as
(base amount /  local amount), then get base amount as
(local amount) * (base amount /  local amount)


Technically, as exchange rate cannot be 0 or 1, the implementation is look like below
(DT_DECIMAL,5)(Local_Currency_Code == "CAD" || (DT_DECIMAL,5)[Net Loss (in CAD).Local Amount] == 0.0 ? NULL(DT_I4) : (DT_DECIMAL,5)[Net Loss (in CAD).Base Amount] / (DT_DECIMAL,5)[Net Loss (in CAD).Local Amount])


However, it is still not working, as need to disable triggers, the solution is to build trigger with criteria on row level. If fastmap with [run trigger] = No, then trigger won’t take in effect.
Also please remember making the exchange rate with 8 decimal places for small currency to make currency conversion correctly reflect the base currency amount.

Sunday, January 1, 2017

How to make a dynamic date DMR dimension


(This is an idea shared from my co-worker Ivan Li) There are some cases where we can implement dynamic fact table.(refer to How to handle “dynamic fact table” using #prompt in framework manger: http://cognosknowhow.blogspot.ca/2012/09/how-to-handle-dynamic-fact-table-using.html.)  This is the case where we use #prompt in framework manger to make “dynamic date dimension”.  When running report, user will get the prompt automatically as below



You can get any dynamic period as needed such as Rolling 14 Periods, Rolling FYTD, Rolling CYTD

This document is indented to explain how was this concept implemented.

 Rolling 14 Periods

Framework manager model.




Rolling 14 Period Category

CASE WHEN [Business Layer].[Reporting Date Dim Dynamic Part].[Rolling Fiscal Period (iN)]  >=0 and [Business Layer].[Reporting Date Dim Dynamic Part].[Rolling Fiscal Period (iN)]<=13
THEN 'Rolling 14 Periods'
WHEN [Business Layer].[Reporting Date Dim Dynamic Part].[Rolling Fiscal Period (iN)]  <0
THEN
'The Future Periods Non-Rolling 14 Periods'
else 'Past Non-Rolling 14 Periods'
end

Rolling Fiscal Period (iN)
#$MAP_AS_OF_FYYYYMM_2_FISCAL_PERIOD_ABS_VALUE{'MASTER'}#  - [Business Layer].[Reporting Date Dim Dynamic Part].[Fiscal Period ABS Value (N)]

 

Fiscal Period ABS Value (N)

cast([Business Layer].[Reporting Date Dim Dynamic Part].[Fiscal Year (YYYY)]  as int)*12+CASE WHEN [Business Layer].[Reporting Date Dim Dynamic Part].[Fiscal Month (13)]  ='11' THEN 1 WHEN [Business Layer].[Reporting Date Dim Dynamic Part].[Fiscal Month (13)]  ='12' THEN 2 WHEN [Business Layer].[Reporting Date Dim Dynamic Part].[Fiscal Month (13)]  ='13' THEN 3 ELSE
cast([Business Layer].[Reporting Date Dim Dynamic Part].[Fiscal Month (13)]   as int)+2 END

 
Query for parameters map

SELECT 'MASTER'             AS KEY,

       FISCAL_YEAR_STR      AS FISCAL_YEAR,

       FISCAL_MONTH_STR_13  AS FISCAL_MONTH,

       FISCAL_QUARTER_STR   AS FISCAL_QTR,

       CALENDAR_YEAR_STR    AS CALENDAR_YEAR,  

       CALENDAR_QUARTER_STR AS CALENDAR_QTR,

       CALENDAR_MONTH_STR   AS CALENDAR_MONTH,

       Cast(FISCAL_YEAR_STR AS INT)*12+

       CASE

              WHEN FISCAL_MONTH_STR_13='11' THEN 1

              WHEN FISCAL_MONTH_STR_13='12' THEN 2

              WHEN FISCAL_MONTH_STR_13='13' THEN 3

              ELSE Cast(FISCAL_MONTH_STR_13 AS INT)+Cast('2' AS INT)

       END AS ROLLING_PERIOD_VALUE,

       Cast(FISCAL_YEAR_STR AS INT)*12+

       CASE

              WHEN FISCAL_MONTH_STR_13='11' THEN 1

              WHEN FISCAL_MONTH_STR_13='12' THEN 2

              WHEN FISCAL_MONTH_STR_13='13' THEN 3

              ELSE Cast(FISCAL_MONTH_STR_13 AS INT)+Cast('2' AS INT)

       END AS FISCAL_PERIOD_ABS_VALUE,

       Cast(CALENDAR_YEAR_STR AS INT)*12+

         Cast(CALENDAR_MONTH_STR AS INT) AS CALENDAR_PERIOD_ABS_VALUE,

       Cast(FISCAL_YEAR_STR AS INT)*4+

       CASE 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,

       Cast(CALENDAR_YEAR_STR AS INT)*4+

       CASE CALENDAR_QUARTER_STR

              WHEN 'Q1' THEN 1

              WHEN 'Q2' THEN 2

              WHEN 'Q3' THEN 3

              WHEN 'Q4' THEN 4

       END AS CALENDAR_QUARTER_ABS_VALUE

FROM   [MAP34881_NZ_MPL_MPL_BI].MPL_DATE_DIM aa

WHERE  (

              #PROMPT('PLEASE SELECT AS OF MONTH IN YYYYMM FORMAT','STRING','''CURRENT''')# ='CURRENT'

       AND    CALENDAR_YEAR_STR=CAST(date_part('year',add_months(CURRENT_DATE,-2)) AS   VARCHAR(20))

       AND    CALENDAR_MONTH_STR=CAST(date_part('month',add_months(CURRENT_DATE,-2)) AS VARCHAR(20)) )

OR     (

              #PROMPT('PLEASE SELECT AS OF MONTH IN YYYYMM FORMAT','STRING','''CURRENT''')# =CAST( CAST(CALENDAR_YEAR_STR AS INTEGER)*100+CAST( CALENDAR_MONTH_STR AS INTEGER) AS VARCHAR(20)) )

 


Rolling Period String (N)
cast([Business Layer].[Reporting Date Dim Dynamic Part].[Rolling Fiscal Period (iN)] as varchar(20))

 Rolling FYTD



Rolling Fiscal Year String (N)
cast([Business Layer].[Reporting Date Dim Dynamic Part].[Rolling Fiscal Year (iN)] as varchar(20))

 
Rolling Fiscal Year (iN)
#$MAP_AS_OF_FYYYYMM_2_FISCAL_YEAR{'MASTER'}#  -
[Business Layer].[Reporting Date Dim Dynamic Part].[Fiscal Year (YYYY)]

 




R FYTD Ctg (N-FYTD/NON-FYTD)

[Business Layer].[Reporting Date Dim Dynamic Part].[Rolling Fiscal Year String (N)]  ||'-'||[Business Layer].[Reporting Date Dim Dynamic Part].[FYTD Ctg (FYTD/NON-FYTD)]

 

FYTD Ctg (FYTD/NON-FYTD)

CASE WHEN MOD([Business Layer].[Reporting Date Dim Dynamic Part].[Fiscal Period ABS Value (N)]  -1 ,12)

<=

MOD(#$MAP_AS_OF_FYYYYMM_2_FISCAL_PERIOD_ABS_VALUE{'MASTER'}#-1 ,12) THEN 'FYTD'

ELSE 'NON-FYTD'

END

 

Rolling CYTD


Rolling CY String (N)

cast([Business Layer].[Reporting Date Dim Dynamic Part].[Rolling Calendar Year (iN)] as varchar(20))

 
Rolling Calendar Year (iN)

#$MAP_AS_OF_FYYYYMM_2_CALENDAR_YEAR{'MASTER'}#  -
[Business Layer].[Reporting Date Dim Dynamic Part].[Calendar Year (YYYY)]

 

R CYTD Ctg (N-CYTD)
[Business Layer].[Reporting Date Dim Dynamic Part].[Rolling CY String (N)]  ||'-'||[Business Layer].[Reporting Date Dim Dynamic Part].[CYTD Ctg (CYTD)]




CYTD Ctg (CYTD)
CASE WHEN MOD([Business Layer].[Reporting Date Dim Dynamic Part].[Ca Period ABS Value (N)]  -1 ,12)
<=
MOD(#$MAP_AS_OF_FYYYYMM_2_CALENDAR_PERIOD_ABS_VALUE{'MASTER'}#-1 ,12) THEN 'CYTD'
ELSE 'NON-CYTD'
END
 
Sample data of derived date dimension

FISCAL_YEAR__YYYY_ FISCAL_YEAR__FYNN_ FISCAL_QUARTER__QN_ FISCAL_MONTH__13_ FISCAL_PERIOD_ABS_VALUE__N_ FISCAL_QUARTER_ABS_VALUE__N_ FISCAL_YQ__YYYYQN_ FIS_YQ__QNYY_ ROLLING_FISCAL_YEAR__IN_ ROLLING_FISCAL_YEAR_STRING__N_ ROLLING_FISCAL_QUARTER__IN_ ROLLING_FISCAL_QUARTER_STR__N_ ROLLING_FISCAL_PERIOD__IN_ ROLLING_PERIOD_STRING__N_ FYTD_CTG__FYTD_NON_FYTD_ FQTD_CTG__FQTD_NON_FQTD_ R_FYTD_CTG__N_FYTD_NON_FYTD_ R_FYTD_CTG_FYTDYY_NON_FYTDYY_ R_FQTD_CTG__N_FQTD_NON_FQTD_ ROLLING_FY__IN__BY_LAG1P ROLLING_FY_STR__N__BY_LAG1P ROLLING_FQ__IN__BY_LAG1P ROLLING_FQ_STR__N__BY_LAG1P ROLLING_FP__IN__BY_LAG1P ROLLING_P_STR__N__BY_LAG1P FYTD_CTG_FYTD__LAG1P FQTD_CTG_FQTD__LAG1P R_FYTD_CTG__NFYTD__BY_LAG1P R_FYTD_CTG__FYTDYY_LAG1P R_FQTD_CTG_FQTDYY_LAG1P FYTD_CTG__FYTD__BY_LAG2P FQTD_CTG__FQTD__BY_LAG2P R_FYTD_CTG__NFYTD__BY_LAG2P R_FYTD_CTG__FYTDYY_LAG2P R_FQTD_CTG__FQTDYY_LAG2P ROLLING_26_P_SUB_CTG CALENDAR_YEAR__YYYY_ CALENDAR_YM__YYYYMM_ CA_YM__MMM_YYYY_ CA_YM__MONTH_YYYY_ CA_MONTH__M_OCT_YY_ CA_MONTH__MM_ CA_M_SORT_IN_FM CA_MONTH__M_ CA_MONTH__MMM_ ROLLING_13_PERIOD_CATEGORY ROLLING_14_PERIOD_CATEGORY RP_IN_12_P_CYCLE__IN_ FISCAL_YEAR_AND_MONTH__YYYY13_ CYTD_CTG__CYTD_ CQTD_CTG__CQTD_ R_CYTD_CTG__N_CYTD_ R_CYTD_CTG__CYTDYY_ R_CQTD_CTG__N_CQTD_ CA_YEAR__CYNN_ CA_YQ__YYYYQN_ FIS_Q_END_MON__YYYY01_ FIS_Q_END_MON__JAN_YY_ CA_YM__MMM_YY_ CA_Q_END_MON_MAR_YY_ CALENDAR_YQ__QNYY_ CALENDAR_YQ__YYYYQN_

2015

FY15

Q1

13

24183

8061

2015 - Q1

Q115

1

1

6

6

16

16

FYTD

NON-FQTD

1-FYTD

FYTD15

6-NON-FQTD

1

1

5

5

15

15

FYTD

NON-FQTD

1-FYTD

FYTD15

NON-FQTD

FYTD

NON-FQTD

1-FYTD

FYTD15

NON-FQTD

Rolling Second 12 Periods

2015

201501

Jan-15

Jan-15

J

1

3

J

Jan

Past Non-Rolling 13 Periods

Past Non-Rolling 14 Periods

4

2015 - 13

CYTD

FQTD

1-CYTD

CYTD15

5-FQTD

CY15

2015 - Q1

201501

15-Jan

15-Jan

15-Mar

Q115

2015 - Q1

2015

FY15

Q2

2

24184

8062

2015 - Q2

Q215

1

1

5

5

15

15

FYTD

FQTD

1-FYTD

FYTD15

5-FQTD

1

1

4

4

14

14

FYTD

NON-FQTD

1-FYTD

FYTD15

NON-FQTD

FYTD

FQTD

1-FYTD

FYTD15

FQTD

Rolling Second 12 Periods

2015

201502

Feb-15

Feb-15

F

2

4

F

Feb

Past Non-Rolling 13 Periods

Past Non-Rolling 14 Periods

3

2015 - 02

CYTD

FQTD

1-CYTD

CYTD15

5-FQTD

CY15

2015 - Q1

201504

15-Apr

15-Feb

15-Mar

Q115

2015 - Q1

2015

FY15

Q2

3

24185

8062

2015 - Q2

Q215

1

1

5

5

14

14

FYTD

NON-FQTD

1-FYTD

FYTD15

5-NON-FQTD

1

1

4

4

13

13

FYTD

NON-FQTD

1-FYTD

FYTD15

NON-FQTD

FYTD

NON-FQTD

1-FYTD

FYTD15

NON-FQTD

Rolling Second 12 Periods

2015

201503

Mar-15

Mar-15

M

3

5

M

Mar

Past Non-Rolling 13 Periods

Past Non-Rolling 14 Periods

2

2015 - 03

CYTD

NON-FQTD

1-CYTD

CYTD15

5-NON-FQTD

CY15

2015 - Q1

201504

15-Apr

15-Mar

15-Mar

Q115

2015 - Q1

2015

FY15

Q2

4

24186

8062

2015 - Q2

Q215

1

1

5

5

13

13

FYTD

NON-FQTD

1-FYTD

FYTD15

5-NON-FQTD

1

1

4

4

12

12

FYTD

NON-FQTD

1-FYTD

FYTD15

NON-FQTD

NON-FYTD

NON-FQTD

1-NON-FYTD

NON-FYTD15

NON-FQTD

Rolling Second 12 Periods

2015

201504

Apr-15

Apr-15

A

4

6

A

Apr

Past Non-Rolling 13 Periods

Rolling 14 Periods

1

2015 - 04

CYTD

FQTD

1-CYTD

CYTD15

4-FQTD

CY15

2015 - Q2

201504

15-Apr

15-Apr

15-Jun

Q215

2015 - Q2

2015

FY15

Q3

5

24187

8063

2015 - Q3

Q315

1

1

4

4

12

12

FYTD

FQTD

1-FYTD

FYTD15

4-FQTD

1

1

3

3

11

11

NON-FYTD

NON-FQTD

1-NON-FYTD

NON-FYTD15

NON-FQTD

NON-FYTD

FQTD

1-NON-FYTD

NON-FYTD15

FQTD

Rolling Second 12 Periods

2015

201505

May-15

May-15

M

5

7

M

May

Rolling 13 Periods

Rolling 14 Periods

0

2015 - 05

CYTD

FQTD

1-CYTD

CYTD15

4-FQTD

CY15

2015 - Q2

201507

15-Jul

15-May

15-Jun

Q215

2015 - Q2

2015

FY15

Q3

6

24188

8063

2015 - Q3

Q315

1

1

4

4

11

11

NON-FYTD

NON-FQTD

1-NON-FYTD

NON-FYTD15

4-NON-FQTD

1

1

3

3

10

10

NON-FYTD

NON-FQTD

1-NON-FYTD

NON-FYTD15

NON-FQTD

NON-FYTD

NON-FQTD

1-NON-FYTD

NON-FYTD15

NON-FQTD

Rolling First 12 Periods

2015

201506

Jun-15

Jun-15

J

6

8

J

Jun

Rolling 13 Periods

Rolling 14 Periods

11

2015 - 06

NON-CYTD

NON-FQTD

1-NON-CYTD

NON-CYTD15

4-NON-FQTD

CY15

2015 - Q2

201507

15-Jul

15-Jun

15-Jun

Q215

2015 - Q2

2015

FY15

Q3

7

24189

8063

2015 - Q3

Q315

1

1

4

4

10

10

NON-FYTD

NON-FQTD

1-NON-FYTD

NON-FYTD15

4-NON-FQTD

1

1

3

3

9

9

NON-FYTD

NON-FQTD

1-NON-FYTD

NON-FYTD15

NON-FQTD

NON-FYTD

NON-FQTD

1-NON-FYTD

NON-FYTD15

NON-FQTD

Rolling First 12 Periods

2015

201507

Jul-15

Jul-15

J

7

9

J

Jul

Rolling 13 Periods

Rolling 14 Periods

10

2015 - 07

NON-CYTD

FQTD

1-NON-CYTD

NON-CYTD15

3-FQTD

CY15

2015 - Q3

201507

15-Jul

15-Jul

15-Sep

Q315

2015 - Q3

2015

FY15

Q4

8

24190

8064

2015 - Q4

Q415

1

1

3

3

9

9

NON-FYTD

FQTD

1-NON-FYTD

NON-FYTD15

3-FQTD

1

1

2

2

8

8

NON-FYTD

NON-FQTD

1-NON-FYTD

NON-FYTD15

NON-FQTD

NON-FYTD

FQTD

1-NON-FYTD

NON-FYTD15

FQTD

Rolling First 12 Periods

2015

201508

Aug-15

Aug-15

A

8

10

A

Aug

Rolling 13 Periods

Rolling 14 Periods

9

2015 - 08

NON-CYTD

FQTD

1-NON-CYTD

NON-CYTD15

3-FQTD

CY15

2015 - Q3

201510

15-Oct

15-Aug

15-Sep

Q315

2015 - Q3

2015

FY15

Q4

9

24191

8064

2015 - Q4

Q415

1

1

3

3

8

8

NON-FYTD

NON-FQTD

1-NON-FYTD

NON-FYTD15

3-NON-FQTD

1

1

2

2

7

7

NON-FYTD

NON-FQTD

1-NON-FYTD

NON-FYTD15

NON-FQTD

NON-FYTD

NON-FQTD

1-NON-FYTD

NON-FYTD15

NON-FQTD

Rolling First 12 Periods

2015

201509

Sep-15

Sep-15

S

9

11

S

Sep

Rolling 13 Periods

Rolling 14 Periods

8

2015 - 09

NON-CYTD

NON-FQTD

1-NON-CYTD

NON-CYTD15

3-NON-FQTD

CY15

2015 - Q3

201510

15-Oct

15-Sep

15-Sep

Q315

2015 - Q3

2015

FY15

Q4

10

24192

8064

2015 - Q4

Q415

1

1

3

3

7

7

NON-FYTD

NON-FQTD

1-NON-FYTD

NON-FYTD15

3-NON-FQTD

1

1

2

2

6

6

NON-FYTD

NON-FQTD

1-NON-FYTD

NON-FYTD15

NON-FQTD

NON-FYTD

NON-FQTD

1-NON-FYTD

NON-FYTD15

NON-FQTD

Rolling First 12 Periods

2015

201510

Oct-15

Oct-15

15-Oct

10

12

O

Oct

Rolling 13 Periods

Rolling 14 Periods

7

2015 - 10

NON-CYTD

FQTD

1-NON-CYTD

NON-CYTD15

2-FQTD

CY15

2015 - Q4

201510

15-Oct

15-Oct

15-Dec

Q415

2015 - Q4

2016

FY16

Q1

11

24193

8065

2016 - Q1

Q116

0

0

2

2

6

6

FYTD

FQTD

0-FYTD

FYTD16

2-FQTD

0

0

1

1

5

5

FYTD

NON-FQTD

0-FYTD

FYTD16

NON-FQTD

FYTD

FQTD

0-FYTD

FYTD16

FQTD

Rolling First 12 Periods

2015

201511

Nov-15

Nov-15

N

11

1

N

Nov

Rolling 13 Periods

Rolling 14 Periods

6

2016 - 11

NON-CYTD

FQTD

1-NON-CYTD

NON-CYTD15

2-FQTD

CY15

2015 - Q4

201601

16-Jan

15-Nov

15-Dec

Q415

2015 - Q4

2016

FY16

Q1

12

24194

8065

2016 - Q1

Q116

0

0

2

2

5

5

FYTD

NON-FQTD

0-FYTD

FYTD16

2-NON-FQTD

0

0

1

1

4

4

FYTD

NON-FQTD

0-FYTD

FYTD16

NON-FQTD

FYTD

NON-FQTD

0-FYTD

FYTD16

NON-FQTD

Rolling First 12 Periods

2015

201512

Dec-15

Dec-15

D

12

2

D

Dec

Rolling 13 Periods

Rolling 14 Periods

5

2016 - 12

NON-CYTD

NON-FQTD

1-NON-CYTD

NON-CYTD15

2-NON-FQTD

CY15

2015 - Q4

201601

16-Jan

15-Dec

15-Dec

Q415

2015 - Q4

2016

FY16

Q1

13

24195

8065

2016 - Q1

Q116

0

0

2

2

4

4

FYTD

NON-FQTD

0-FYTD

FYTD16

2-NON-FQTD

0

0

1

1

3

3

FYTD

NON-FQTD

0-FYTD

FYTD16

NON-FQTD

FYTD

NON-FQTD

0-FYTD

FYTD16

NON-FQTD

Rolling First 12 Periods

2016

201601

Jan-16

Jan-16

J

1

3

J

Jan

Rolling 13 Periods

Rolling 14 Periods

4

2016 - 13

CYTD

FQTD

0-CYTD

CYTD16

1-FQTD

CY16

2016 - Q1

201601

16-Jan

16-Jan

16-Mar

Q116

2016 - Q1

2016

FY16

Q2

2

24196

8066

2016 - Q2

Q216

0

0

1

1

3

3

FYTD

FQTD

0-FYTD

FYTD16

1-FQTD

0

0

0

0

2

2

FYTD

NON-FQTD

0-FYTD

FYTD16

NON-FQTD

FYTD

FQTD

0-FYTD

FYTD16

FQTD

Rolling First 12 Periods

2016

201602

Feb-16

Feb-16

F

2

4

F

Feb

Rolling 13 Periods

Rolling 14 Periods

3

2016 - 02

CYTD

FQTD

0-CYTD

CYTD16

1-FQTD

CY16

2016 - Q1

201604

16-Apr

16-Feb

16-Mar

Q116

2016 - Q1

2016

FY16

Q2

3

24197

8066

2016 - Q2

Q216

0

0

1

1

2

2

FYTD

NON-FQTD

0-FYTD

FYTD16

1-NON-FQTD

0

0

0

0

1

1

FYTD

NON-FQTD

0-FYTD

FYTD16

NON-FQTD

FYTD

NON-FQTD

0-FYTD

FYTD16

NON-FQTD

Rolling First 12 Periods

2016

201603

Mar-16

Mar-16

M

3

5

M

Mar

Rolling 13 Periods

Rolling 14 Periods

2

2016 - 03

CYTD

NON-FQTD

0-CYTD

CYTD16

1-NON-FQTD

CY16

2016 - Q1

201604

16-Apr

16-Mar

16-Mar

Q116

2016 - Q1

2016

FY16

Q2

4

24198

8066

2016 - Q2

Q216

0

0

1

1

1

1

FYTD

NON-FQTD

0-FYTD

FYTD16

1-NON-FQTD

0

0

0

0

0

0

FYTD

NON-FQTD

0-FYTD

FYTD16

NON-FQTD

NON-FYTD

NON-FQTD

0-NON-FYTD

NON-FYTD16

NON-FQTD

Rolling First 12 Periods

2016

201604

Apr-16

Apr-16

A

4

6

A

Apr

Rolling 13 Periods

Rolling 14 Periods

1

2016 - 04

CYTD

FQTD

0-CYTD

CYTD16

0-FQTD

CY16

2016 - Q2

201604

16-Apr

16-Apr

16-Jun

Q216

2016 - Q2

2016

FY16

Q3

5

24199

8067

2016 - Q3

Q316

0

0

0

0

0

0

FYTD

FQTD

0-FYTD

FYTD16

0-FQTD

0

0

-1

-1

-1

-1

NON-FYTD

NON-FQTD

0-NON-FYTD

NON-FYTD16

NON-FQTD

NON-FYTD

FQTD

0-NON-FYTD

NON-FYTD16

FQTD

Rolling First 12 Periods

2016

201605

May-16

May-16

M

5

7

M

May

Rolling 13 Periods

Rolling 14 Periods

0

2016 - 05

CYTD

FQTD

0-CYTD

CYTD16

0-FQTD

CY16

2016 - Q2

201607

16-Jul

16-May

16-Jun

Q216

2016 - Q2

2016

FY16

Q3

6

24200

8067

2016 - Q3

Q316

0

0

0

0

-1

-1

NON-FYTD

NON-FQTD

0-NON-FYTD

NON-FYTD16

0-NON-FQTD

0

0

-1

-1

-2

-2

NON-FYTD

NON-FQTD

0-NON-FYTD

NON-FYTD16

NON-FQTD

NON-FYTD

NON-FQTD

0-NON-FYTD

NON-FYTD16

NON-FQTD

Non-Rolling 26 Periods

2016

201606

Jun-16

Jun-16

J

6

8

J

Jun

The Future Periods Non-Rolling 13 Periods

The Future Periods Non-Rolling 14 Periods

11

2016 - 06

NON-CYTD

NON-FQTD

0-NON-CYTD

NON-CYTD16

0-NON-FQTD

CY16

2016 - Q2

201607

16-Jul

16-Jun

16-Jun

Q216

2016 - Q2

2016

FY16

Q3

7

24201

8067

2016 - Q3

Q316

0

0

0

0

-2

-2

NON-FYTD

NON-FQTD

0-NON-FYTD

NON-FYTD16

0-NON-FQTD

0

0

-1

-1

-3

-3

NON-FYTD

NON-FQTD

0-NON-FYTD

NON-FYTD16

NON-FQTD

NON-FYTD

NON-FQTD

0-NON-FYTD

NON-FYTD16

NON-FQTD

Non-Rolling 26 Periods

2016

201607

Jul-16

Jul-16

J

7

9

J

Jul

The Future Periods Non-Rolling 13 Periods

The Future Periods Non-Rolling 14 Periods

10

2016 - 07

NON-CYTD

FQTD

0-NON-CYTD

NON-CYTD16

-1-FQTD

CY16

2016 - Q3

201607

16-Jul

16-Jul

16-Sep

Q316

2016 - Q3

2016

FY16

Q4

8

24202

8068

2016 - Q4

Q416

0

0

-1

-1

-3

-3

NON-FYTD

FQTD

0-NON-FYTD

NON-FYTD16

-1-FQTD

0

0

-2

-2

-4

-4

NON-FYTD

NON-FQTD

0-NON-FYTD

NON-FYTD16

NON-FQTD

NON-FYTD

FQTD

0-NON-FYTD

NON-FYTD16

FQTD

Non-Rolling 26 Periods

2016

201608

Aug-16

Aug-16

A

8

10

A

Aug

The Future Periods Non-Rolling 13 Periods

The Future Periods Non-Rolling 14 Periods

9

2016 - 08

NON-CYTD

FQTD

0-NON-CYTD

NON-CYTD16

-1-FQTD

CY16

2016 - Q3

201610

16-Oct

16-Aug

16-Sep

Q316

2016 - Q3

2016

FY16

Q4

9

24203

8068

2016 - Q4

Q416

0

0

-1

-1

-4

-4

NON-FYTD

NON-FQTD

0-NON-FYTD

NON-FYTD16

-1-NON-FQTD

0

0

-2

-2

-5

-5

NON-FYTD

NON-FQTD

0-NON-FYTD

NON-FYTD16

NON-FQTD

NON-FYTD

NON-FQTD

0-NON-FYTD

NON-FYTD16

NON-FQTD

Non-Rolling 26 Periods

2016

201609

Sep-16

Sep-16

S

9

11

S

Sep

The Future Periods Non-Rolling 13 Periods

The Future Periods Non-Rolling 14 Periods

8

2016 - 09

NON-CYTD

NON-FQTD

0-NON-CYTD

NON-CYTD16

-1-NON-FQTD

CY16

2016 - Q3

201610

16-Oct

16-Sep

16-Sep

Q316

2016 - Q3

2016

FY16

Q4

10

24204

8068

2016 - Q4

Q416

0

0

-1

-1

-5

-5

NON-FYTD

NON-FQTD

0-NON-FYTD

NON-FYTD16

-1-NON-FQTD

0

0

-2

-2

-6

-6

NON-FYTD

NON-FQTD

0-NON-FYTD

NON-FYTD16

NON-FQTD

NON-FYTD

NON-FQTD

0-NON-FYTD

NON-FYTD16

NON-FQTD

Non-Rolling 26 Periods

2016

201610

Oct-16

Oct-16

16-Oct

10

12

O

Oct

The Future Periods Non-Rolling 13 Periods

The Future Periods Non-Rolling 14 Periods

7

2016 - 10

NON-CYTD

FQTD

0-NON-CYTD

NON-CYTD16

-2-FQTD

CY16

2016 - Q4

201610

16-Oct

16-Oct

16-Dec

Q416

2016 - Q4

2017

FY17

Q1

11

24205

8069

2017 - Q1

Q117

-1

-1

-2

-2

-6

-6

FYTD

FQTD

-1-FYTD

FYTD17

-2-FQTD

-1

-1

-3

-3

-7

-7

FYTD

NON-FQTD

-1-FYTD

FYTD17

NON-FQTD

FYTD

FQTD

-1-FYTD

FYTD17

FQTD

Non-Rolling 26 Periods

2016

201611

Nov-16

Nov-16

N

11

1

N

Nov

The Future Periods Non-Rolling 13 Periods

The Future Periods Non-Rolling 14 Periods

6

2017 - 11

NON-CYTD

FQTD

0-NON-CYTD

NON-CYTD16

-2-FQTD

CY16

2016 - Q4

201701

17-Jan

16-Nov

16-Dec

Q416

2016 - Q4

2017

FY17

Q1

12

24206

8069

2017 - Q1

Q117

-1

-1

-2

-2

-7

-7

FYTD

NON-FQTD

-1-FYTD

FYTD17

-2-NON-FQTD

-1

-1

-3

-3

-8

-8

FYTD

NON-FQTD

-1-FYTD

FYTD17

NON-FQTD

FYTD

NON-FQTD

-1-FYTD

FYTD17

NON-FQTD

Non-Rolling 26 Periods

2016

201612

Dec-16

Dec-16

D

12

2

D

Dec

The Future Periods Non-Rolling 13 Periods

The Future Periods Non-Rolling 14 Periods

5

2017 - 12

NON-CYTD

NON-FQTD

0-NON-CYTD

NON-CYTD16

-2-NON-FQTD

CY16

2016 - Q4

201701

17-Jan

16-Dec

16-Dec

Q416

2016 - Q4