Logical Tables » Logical view examples » Business case 4: One-to-many transformation tables

Business case 4: One-to-many transformation tables

In order to support time-series analysis, such as month-to-date and year-to-date calculations, you need to define transformations. Although one-to-one transformations, such as Last Month, can be defined in terms of an expression, one-to-many transformations require tables in the database that map each date to all the previous dates that make up “month-to-date.”

If you do not already have such a table in the warehouse and your circumstances do not allow you to add additional tables to the database, then you can use the logical view approach to address this issue as long as you already have a lookup table for the Day attribute.

The SQL below can be used to define a logical MTD_DATE table, which contains the Day attribute. The MTD transformation can then be defined using the MTD_DATE column.

Select day_date day_date, B.day_date mtd_date
From lu_day A, lu_day B
Where A.day_date >= B.day_date
And MONTH(A.day_date)= MONTH(B.day_date)

The same technique can be used to define a year-to-date transformation.

Select A.day_date day_date, B.day_date
ytd_date
From lu_day A, lu_day B
Where A.day_date >= B.day_date
And YEAR(A.day_date) = YEAR(B.day_date)