Transformation 1

[sntrao]

Hi, I want to create a report that looks like this:

Metric 1
============
2004 2005

A

Data is updated fortnightly (not exactly on the 15th and 30th of every month. updates take place on 13th or 14th or 15th or 16th of every month). Today I want sum(Metric 1) from Jan 1, 2005 to Jan 31, 2005 under column 2005 and sum(Metric 1) during the same period in 2004 under column 2004. On Feb 15th I might want sum(Metric 1) from Jan 1, 2005 to Feb 15, 2005 under column 2005 and sum(Metric 1) during the same period in 2004 under column 2004. and so on.

I know that I need to use a transformation. cud someone pls explain in detail as how to go about in creating this Year to Date transformation and the corresponding metric?

Thanks
Thukaram

 

[TJM2]

Year-To-Date metric (M1) for 2005:
1-Create a Year-To-Date filter (F1). I assume this is clear.
2-Create the approperiate metric (M1).
3-Add F1 to filter section of M1 in metric editor.
4-Save metric as (YTD M1)
You have a Year-To-Date metric.

In order to display the 2004 data along side of (YTD M1) for 2005, there are couple of ways to go about creating the transformation:

1-Open a new transformation
2-Select "Year" attribute corresponding to to date dimension of your metric as "Member attribute"
3- This is where you define the nature of the transformation. In your case, you want to pick a year info and decrement it by 1. Do either of the following:
3.1- Enter an expression to decrement the year portion of the date attribute selected in step 2 by 1. For example, if year_id was the name of the column corresponding to the attribute selected in step 2, then you would enter "year_id - 1" for "Member Attribute Expression"
3.2- If in your time series table in DW you already have a table which contains "Year" and "Last Year" columns, then pick the attribute which corresponds to "Last Year" DW column as your "Member Attribute Expression."

The data in your DW should be similar to following:

your table (T1) structure:
1-Year
2-Last Year
3-...
..

Data sample for table T1:

Year Last Year
----- ---------
2000 1999
2001 2000
2002 2001
2003 2002

Hope this helps.

 

[entaroadun]

TJM2, I don't think that your approach would work. If the transformation is done right, a filter isn't necessary.

Just to review:

The purpose of a transformation is to take a single attribute element and transform it into one or more different elements.

A Last Year transform would take one year (one element of the Year attribute, i.e. 1999) and transform it into another year (i.e. 2000).

What you are attempting to do is take one date (one element of your Day attribute, i.e. 2/15/2005) and transform it into the set of dates between January 1 of that same year and the date itself, inclusive (i.e. 1/1/2005 to 2/15/2005).

Notice that you aren't changing attributes. You put in a Date, and you get out a Date. It's the elements of the attribute that transform.

The most straightforward method is to store the transformation values in a table:

[b]Transform Table[/b]
[u]Day[/u]             [u]YTD Day[/u]
2/15/2005       2/15/2005
2/15/2005       2/14/2005
...
2/15/2005       1/02/2005
2/15/2005       1/01/2005
2/14/2005       2/14/2005
2/14/2005       2/13/2005
...
2/14/2005       1/02/2005
2/14/2005       1/01/2005

As you can see, this table can get quite large. If you have the space, go right ahead.

I wouldn't attempt an expression in MSTR... they're really much more suited for one-to-one transformations. Any working solution you produce will have performance issues anyways, because of the nature of the expression you would have to write.

I would recommend a view. I assume you have a date table which forms the basis of your Day attribute, and that column that you use in the ID attribute form expression is the primary key of the table. I also assume that your date table contains an entry for every day, whether or not there are any fact records for that day.

Modify your date table so it includes these columns:

[b]Day[/b]
[u]Day[/u]             [u]YTD Begin[/u]
2/15/2005       1/01/2005
2/14/2005       1/01/2005

You want to store the January 1 date in a column to speed up the query. Using a year column and appending 1/1 to it is slow.

Create a view:

SELECT       A.Day AS Day
  ,          B.Day AS [YTD Day]
FROM         Day AS A
  INNER JOIN Day AS B
    ON       B.Day Between A.[YTD Begin] And A.Day

Add this view to your Day attribute, pointing the ID form to the Day column.

Create a transformation. Select the Day attribute. In the expression, use the YTD Day column from this view. In your metrics, add the YTD transformation. A filter is not necessary.

Hope this helps.

 

[TJM2]

Dear entaroadun,

I probably this not articulate clear enough, but in essence, what I suggested was to make a YTD metric ( which is what he needs for current year) and apply LY transformation to it. I don't see how this would not work, but I let others judge.

 

[sntrao]

Hi TJM2 and entaroadun,

thanks a lot for your replies. That was really helpful.

TJM2, I know what a Year to Date transformation is but I haven't heard of a Year to Date Filter! Could you please elaborate on this?

Thanks
Thukaram

 

[entaroadun]

TJM2, I'm sorry. You're absolutely right. My interpretation was that the report would contain the Year attribute in the column heading and a single YTD metric. The user would select 2/15/2005 and 2/15/2004 in the attribute element prompt and this would give two columns, 2005 and 2004, in the final result.

What sntrao actually wants is what you described: the report would contain no attributes in the column heading, but would contains two metrics across the top: current year YTD and last year YTD.

In this case, you would want a metric with a YTD filter for current year YTD, and a metric with a YTD filter and a last year transformation for last year YTD. You are absolutely correct.