year over year metrics/reports

[DebbieKat]

Can anyone give me some tips on how best to structure microstrategy reports to compare year over year metrics? We tried to create underlying prompted filters used in a metric to allow the user to select the current year and month values because if we put the filter in the report itself, it automatically uses the same filter for this year and last year. However, when we put the filter in the underlying metric we are getting a cartesian product because it is using the same dimension table as an attribute filter for both year and month. We are also finding that unless we bring in at least one attribute from the date dimension, microstrategy won't use our filtering at all. Here is what we are trying to do in the report we want to build:

Client Selected Month Rev Same Month Last Year Rev


We tried building a prompted filter based on an attribute to obtain the appropriate list of month_key(s) from our date dimension that allows the user to select a year and month. Then we layered a metric over this to use the month_key in a metric that determines current revenue and then used the same month_key - 12 for use in a previous revenue metric. But the resulting SQL is not quite right. I would think that this is a common report (comparing current year to last year). Has anyone accomplished this and is there a better way to do it?

This is the code microstrategy is generating:


with pa1 as
(select a11.DATE_MONTH_NUMBER DATE_MONTH_NUMBER,
max(a11.DATE_MONTH_KEY) MONTHKEYTHIS
from DATE_MONTH_DIMENSION a11
where (a11.DATE_YEAR_KEY in (2001)
and a11.DATE_MONTH_NUMBER in (8))
group by a11.DATE_MONTH_NUMBER
),
pa2 as
(select a11.DATE_MONTH_NUMBER DATE_MONTH_NUMBER,
max(a11.DATE_MONTH_KEY) MONTHKEYTHIS
from DATE_MONTH_DIMENSION a11
where (a11.DATE_YEAR_KEY in (2001)
and a11.DATE_MONTH_NUMBER in (8))
group by a11.DATE_MONTH_NUMBER
),
pa3 as
(select distinct pa1.DATE_MONTH_NUMBER DATE_MONTH_NUMBER
from pa1
join pa2
on (pa1.DATE_MONTH_NUMBER = pa2.DATE_MONTH_NUMBER)
where (pa1.MONTHKEYTHIS = pa2.MONTHKEYTHIS)
),
pa7 as
(select a12.DATE_MONTH_NUMBER DATE_MONTH_NUMBER,
sum(a11.REVENUE_AMOUNT_USD) REVTHISYEAR
from REVENUE_FACTS_AGG1 a11
join DATE_MONTH_DIMENSION a12
on (a11.DATE_MONTH_KEY = a12.DATE_MONTH_KEY)
join pa3
on (a12.DATE_MONTH_NUMBER = pa3.DATE_MONTH_NUMBER)
group by a12.DATE_MONTH_NUMBER
),
pa4 as
(select a11.DATE_MONTH_NUMBER DATE_MONTH_NUMBER,
(max(a11.DATE_MONTH_KEY) - 12.0) WJXBFS1
from DATE_MONTH_DIMENSION a11
where (a11.DATE_YEAR_KEY in (2001)
and a11.DATE_MONTH_NUMBER in (8))
group by a11.DATE_MONTH_NUMBER
),
pa5 as
(select a11.DATE_MONTH_NUMBER DATE_MONTH_NUMBER,
(max(a11.DATE_MONTH_KEY) - 12.0) WJXBFS1
from DATE_MONTH_DIMENSION a11
where (a11.DATE_YEAR_KEY in (2001)
and a11.DATE_MONTH_NUMBER in (8))
group by a11.DATE_MONTH_NUMBER
),
pa6 as
(select distinct pa4.DATE_MONTH_NUMBER DATE_MONTH_NUMBER
from pa4
join pa5
on (pa4.DATE_MONTH_NUMBER = pa5.DATE_MONTH_NUMBER)
where (pa4.WJXBFS1 = pa5.WJXBFS1)
),
pa8 as
(select a12.DATE_MONTH_NUMBER DATE_MONTH_NUMBER,
sum(a11.REVENUE_AMOUNT_USD) SAMEMONTHLAS
from REVENUE_FACTS_AGG1 a11
join DATE_MONTH_DIMENSION a12
on (a11.DATE_MONTH_KEY = a12.DATE_MONTH_KEY)
join pa6
on (a12.DATE_MONTH_NUMBER = pa6.DATE_MONTH_NUMBER)
group by a12.DATE_MONTH_NUMBER
)
select distinct a11.DATE_YEAR_KEY DATE_YEAR_KEY,
pa7.DATE_MONTH_NUMBER DATE_MONTH_NUMBER,
a11.DATE_MONTH_NAME DATE_MONTH_NAME,
pa7.REVTHISYEAR REVTHISYEAR,
pa8.SAMEMONTHLAS SAMEMONTHLAS
from pa7
join pa8
on (pa7.DATE_MONTH_NUMBER = pa8.DATE_MONTH_NUMBER)
join DATE_MONTH_DIMENSION a11
on (pa7.DATE_MONTH_NUMBER = a11.DATE_MONTH_NUMBER)

 

[DebbieKat]

Additional note. We tried building a transformation to accomplish this and the transformation isn't working either. I don't see where the formula is being applied in the SQL mstr is generating. The formula we built is year_key - 1. But it is still bringing in all years. Has anyone used transformations before?

 

[nlim]

The transformation you want is "last year this month". Here's a suggestion, create a relationship table with the following :

month_key last_year_this_month_key
--------- ------------------------

then create a transformation that uses this table as the member table, memeber expression = last_year_this_month_key

(or you can add the last_year_this_month_key column to your month_dimension table)

then in your Same Month Last Year Rev metric, add this transformation to the definition.

let us know if this works.

 

[DebbieKat]

Hi nlim -

Thanks for your help. We're still having trouble setting this up. But I think the issue is more the relationships between the attributes than the actual transformation itself. Originally, we tried to do this using a formula transformation. The problem was that the SQL MSTR generated wasn't using the formula to determine the previous year value. I think we are just setting up the relationships wrong. I am going to try changing the way these relationships were created based on your response from the other issue I posted and hopefully that will address the problem.

Thanks again!