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.
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-31' ) ,md as ( select cast('2005-01-01' as datetime) DV2 union all select DV2 + 1 from md where DV2 + 1 < '2010-10-31' ) select cast(convert(varchar, DV1, 112) as integer) DateKey, cast(convert(varchar, DV2, 112) as integer) YTD_Key into DimYTD from report, md where DV1 >= DV2 and DV2 >= DateAdd(yy, DateDiff(yy,0,DV1),0) order by DV1, DV2 OPTION (MAXRECURSION 0)
Once the table is created, we need to import that table to the project using a warehouse catalog and then we can start creating the transformation based on the following steps:
- Select File | New | Transformation. In the transformation editor, select the attribute from, say,
YTD_key, which in our table maps this year to the previous year. - Click Save and Close on the toolbar. Name the transformation Last Year.
The transformation creation screen is as follows:
One can use transformation within the report as follows:
- Go to the report folder and create a report.
- Add the required objects; in our example, add the date, organization name, and revenue.
- Also, we must have attribute onto the report being used on the transformation.
- Now, right click on the revenue metric and select Insert | Transformations | YTD | Normal:
- After adding the transformation, run the report and the result should be like the following:
For expression based transformation, the steps to create a last-year transformation based on an expression are:
- Select File | New | Transformation. In the Transformation Editor, click Add and select the attribute, in our case, year.
- Define the attribute expression; say, for a previous year, type
-1after the year attribute (year-1). This will subtract 1 from the selected year and calculates the previous year. - Click Validate to confirm the accuracy of an expression. Once a green check mark displays, click OK.
- Click Save and Close on the toolbar. Name the transformation Last Year:
In the following sample report, we have created a transformation which selects the year as 2008 and subtracts 1, giving us the revenue for 2007. To validate our transformation, we have created a metric with a filter of 2007; so, based on that, we can see that the result from the transformation is the same as the result from the metric with a filter. We can use either, but transformation provides us with reusability:
Member attributes: Transformation is applied to this component. For example, in year to date transformations, the member attributes are the year and day.
Member tables: This component stores the data for the member attribute. For example, for a table-based transformation, it could be a logical table, such as LU_Year.
Member expression: This is the expression that is associated with the member attribute. For example, for expression-based transformations it could be logical, arithmetic operator, or a function such as applysimple.
Mapping type: This component determines the creation of the transformation based on the nature of the data. Mapping can be:
- One-to-one
- Many-to-many