Transformations

A transformation is a schema object that is created using other project attributes and can only be assigned to metrics. This is used for time series analysis; say, you want to calculate the growth percentage, namely how much your company revenue has grown this year compared to how much it grew last year. The transformation, once created, can be used for n number of reports that require time-based analysis.

Building a transformation

For table based transformation (YTD), we create a reference table with two columns, where the first column is the current date and the second column holds the date, required to calculate the running total based on the current date.

The reference table in our case looks like the following:

As we migrated from SQL server to Oracle, all the tables we created were based on SQL server. Use the following query:

with report as

(

select cast('2005-01-01' as datetime) DV1

union all

select DV1 + 1

from report

where DV1 + 1 < '2010-10...