×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

year over year metrics/reports

year over year metrics/reports

year over year metrics/reports

(OP)
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)

RE: year over year metrics/reports

(OP)
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?

RE: year over year metrics/reports

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.

RE: year over year metrics/reports

(OP)
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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close