Year to Date selection in SAP BI4

In a previous post  I explained how to display Year To Date values. In that particular example a WebI user was prompted a month and based on that input the YTD (*) values are calculated in the WebI document. This approach has two big disadvantages:

  1. When dealing with large data volumes, the performance can be compromised.
  2. The solution is not reusable over WebI reports without manually recreating the logic.

One of my colleagues, Frits Nagtegaal, recently pointed me to a different and more elegant approach for calculating To Date values. In this post I will explain how to implement a YTD value that is calculated in the DB layer.

I’m assuming

  • There is a time dimension available in your DWH
  • You have rights to alter Universes

I’m using

  • BO BI4 SP05
  • Oracle 11gR2

The trick is to create a view that defines the period that belongs to each day available in the time dimension. To clarify this, take a look at the following image:

image1

The first column is the day that you can select in your report prompt, the second column are the days that make up the Year to Date selection belonging to that day. So for example the YTD from 3 January 2013 has three days (20130101, 20130102 and 20130103) associated in the YTD column.

Generating the day codes and the belonging YTD day codes is done with either a (materialized) view in the database or with a derived table in the Universe. The SQL code is:

SELECT

sel_day.dimension_key sel_dimension_key,

sel_day.day_code sel_day_code,

ytd.dimension_key ytd_dimension_key,

ytd.day_code ytd_day_code

FROM

vw_vw_d_time sel_day, –selected day by user

vw_vw_d_time ytd –return all days representing the YTD

WHERE

sel_day.day_code IS NOT NULL –exclude month/quarter/year records

AND sel_day.calendar_year_number BETWEEN to_number(TO_CHAR(sysdate,’yyyy’))-2 AND to_number(TO_CHAR(sysdate,’yyyy’)) –limit results to current year and previous 2 years

AND ytd.day_code BETWEEN to_char(sel_day.calendar_year_start_date, ‘YYYYMMDD’) AND sel_day.day_code

Where clauses explained:

  • In order to retrieve only day records from the time dimension, filter out all NULL’s
  • To limit the results, generate the set of records for three years. You can increase or decrease this as desired
  • Define the relation between the selection day code and the belonging YTD day codes

I’ve used a derived table in the universe in this example, but as stated above it would also work with a view in the database. Create the joins on the keys as following:

image2

Don’t forget to create a context for each fact table!

When you select a measure from each cube along with the days, you will get the following result in WebI:

image3

MS SQL code:

SELECT

sel_day.dimension_key sel_dimension_key,

sel_day.day_code sel_day_code,

ytd.dimension_key ytd_dimension_key,

ytd.day_code ytd_day_code

FROM

NF_DWH.d_time sel_day, –selected day by user

NF_DWH.d_time ytd –return all days representing the YTD

WHERE

sel_day.day_code IS NOT NULL –exclude month/quarter/year records

AND sel_day.calendar_year_number BETWEEN YEAR(GETDATE())-2 AND YEAR(GETDATE()) –limit results to current year and previous 2 years

AND ytd.day_calendar_date BETWEEN sel_day.calendar_year_start_date AND sel_day.day_calendar_date

Teradata SQL code:

SELECT

SEL_DAY.DAY_CALENDAR_DATE SEL_KEY

,SEL_DAY.DAY_CODE SEL_DAY_CODE

,YTD.DAY_CALENDAR_DATE YTD_KEY

,YTD.DAY_CODE YTD_DAY_CODE

FROM

D_TIME SEL_DAY –selected day by user

,D_TIME YTD –return all days representing the YTD

WHERE

SEL_DAY.DAY_CODE IS NOT NULL –exclude month/quarter/year records

AND  SEL_DAY.CALENDAR_YEAR_NUMBER BETWEEN EXTRACT(YEAR FROM CURRENT_DATE)-2 AND EXTRACT(YEAR FROM CURRENT_DATE) –limit results to current year and previous 2 years

AND  YTD.DAY_CALENDAR_DATE BETWEEN SEL_DAY.CALENDAR_YEAR_START_DATE AND SEL_DAY.DAY_CALENDAR_DATE

ORDER BY SEL_KEY, YTD_KEY;

* : The YTD explanation in the previous post  was actually a Year to Month value.

Linkedin Twitter Facebook Stumbleupon Tumblr Email

One thought on “Year to Date selection in SAP BI4

Leave a Reply

Your email address will not be published.