Topic
  • 13 replies
  • Latest Post - ‏2013-01-09T03:38:10Z by SystemAdmin
SystemAdmin
SystemAdmin
15496 Posts

Pinned topic compare multiple measures in crosstab

‏2011-08-18T01:25:33Z |
Hi

Much appreciated if anyone has any tips on doing a report as attached.

Thanks.
Updated on 2013-01-09T03:38:10Z at 2013-01-09T03:38:10Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: compare multiple measures in crosstab

    ‏2011-08-18T01:33:22Z  
    attachment
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: compare multiple measures in crosstab

    ‏2011-08-18T01:35:20Z  
    another try
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: compare multiple measures in crosstab

    ‏2011-08-18T01:50:26Z  
    another try
    This looks like a crosstab layout with two dimensions on the columns (Time and a Scenario dimension) and a Store dimension on the rows with a set of measures nested beneath it. This could be accomplished using the following steps:
    • Drag the Year level of the Time dimension to the columns
    • Drag the Scenario level of the Scenario dimension to the columns and nest it beneath the Year node.
    • Drag the Store level of the Store dimension to the rows
    • Multi-select (ctrl-click) the measures you want on the rows and then drag them to the rows and nest them beneath the Store node

    If your report is more complicated than that then please explain the requirements in more detail.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: compare multiple measures in crosstab

    ‏2011-08-18T02:05:06Z  
    This looks like a crosstab layout with two dimensions on the columns (Time and a Scenario dimension) and a Store dimension on the rows with a set of measures nested beneath it. This could be accomplished using the following steps:
    • Drag the Year level of the Time dimension to the columns
    • Drag the Scenario level of the Scenario dimension to the columns and nest it beneath the Year node.
    • Drag the Store level of the Store dimension to the rows
    • Multi-select (ctrl-click) the measures you want on the rows and then drag them to the rows and nest them beneath the Store node

    If your report is more complicated than that then please explain the requirements in more detail.
    Thanks Phil

    My Actual and Budget measures are coming from two fact tables. I suppose I could have a Union query and merge the two facts. Is it possible to do these kinds without a Union? i.e. from the model itself?

    Thanks again.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: compare multiple measures in crosstab

    ‏2011-08-18T02:11:38Z  
    Thanks Phil

    My Actual and Budget measures are coming from two fact tables. I suppose I could have a Union query and merge the two facts. Is it possible to do these kinds without a Union? i.e. from the model itself?

    Thanks again.
    Strange, I would have expected Sales, People, and Units to be the measures. Using Actual and Budget as measures means that they will have to contain values with different units of measure (Sales in dollars, and People as a count).

    However, if you are dealing with measures from different tables then this is exactly what FM modelling can do for you. You just have to set up the Time, Store, and other dimensions as shared/conformed dimensions between the two fact tables. Then you simply drag and drop as in my last message and Cognos will generate the appropriate join expressions for you automatically.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: compare multiple measures in crosstab

    ‏2011-08-18T02:39:13Z  
    Strange, I would have expected Sales, People, and Units to be the measures. Using Actual and Budget as measures means that they will have to contain values with different units of measure (Sales in dollars, and People as a count).

    However, if you are dealing with measures from different tables then this is exactly what FM modelling can do for you. You just have to set up the Time, Store, and other dimensions as shared/conformed dimensions between the two fact tables. Then you simply drag and drop as in my last message and Cognos will generate the appropriate join expressions for you automatically.
    Not sure I get your reply.

    I have set up the actuals and budgets as facts with shared dimensions in FM. It's just that I need to "combine" matching measures from both facts. Only way I can see doing this is through a Union using two queries.

    Or what I could do is do the UNION in SQL to create a new fact table and a scenario dimension in FM.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: compare multiple measures in crosstab

    ‏2011-08-18T04:04:43Z  
    Not sure I get your reply.

    I have set up the actuals and budgets as facts with shared dimensions in FM. It's just that I need to "combine" matching measures from both facts. Only way I can see doing this is through a Union using two queries.

    Or what I could do is do the UNION in SQL to create a new fact table and a scenario dimension in FM.
    Now I'm not sure what you are trying to say. Maybe start with an explanation of your model setup and more information on the problem as you see it. Attaching the model or images of the relationship diagram would help.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: compare multiple measures in crosstab

    ‏2011-08-18T08:52:10Z  
    Now I'm not sure what you are trying to say. Maybe start with an explanation of your model setup and more information on the problem as you see it. Attaching the model or images of the relationship diagram would help.
    Hi

    I have updated the attachment and hopefully it's more clearer as to what I am doing. I am using non-DMR standard relational query here.

    I am having difficulty writing the calculation for % var (actual-budget)/actual. Can it be done using the query I have given?

    I am aware that a dimensional model will make it easier to write this calc but wondering if it can be done with I have done so far.

    Thank you.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: compare multiple measures in crosstab

    ‏2011-08-19T01:16:21Z  
    Hi

    I have updated the attachment and hopefully it's more clearer as to what I am doing. I am using non-DMR standard relational query here.

    I am having difficulty writing the calculation for % var (actual-budget)/actual. Can it be done using the query I have given?

    I am aware that a dimensional model will make it easier to write this calc but wondering if it can be done with I have done so far.

    Thank you.
    It might help to post your report spec. The layout that you have shown in the Excel file looks like a crosstab but the query result looks like a list. The crosstab layout with your data set should have produced your expected crosstab result without the % calculation. If you already have this result and the real problem is the variance calculation then it would have been much more useful to put this in your original post.

    To get the variance calculation with a purely relational model is going to be quite difficult because this involves calculations between different rows. Attached is an example using a relational model. You would be better off creating a dimensional model if you can.

    Attachments

  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: compare multiple measures in crosstab

    ‏2011-08-19T03:07:23Z  
    It might help to post your report spec. The layout that you have shown in the Excel file looks like a crosstab but the query result looks like a list. The crosstab layout with your data set should have produced your expected crosstab result without the % calculation. If you already have this result and the real problem is the variance calculation then it would have been much more useful to put this in your original post.

    To get the variance calculation with a purely relational model is going to be quite difficult because this involves calculations between different rows. Attached is an example using a relational model. You would be better off creating a dimensional model if you can.
    Hi Phil

    The Report spec is as shown in my Excel which is a crosstab built from the Cognos query (list).

    I normally try to solve reporting problems with a relational model and then try to see how far I can go. My initial problem was how to get the crosstab going without the variance - hence my post. I just need to know what approach (relational or dimensional) is best suited to given task. It seems relational is more work in this case.

    I have built a dimensional Framework Manager model for this case with a scenario dimension. Do you know if it's possible to disable rollups since it doesn't make sense here.

    Thanks again.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: compare multiple measures in crosstab

    ‏2011-08-19T04:58:12Z  
    Hi Phil

    The Report spec is as shown in my Excel which is a crosstab built from the Cognos query (list).

    I normally try to solve reporting problems with a relational model and then try to see how far I can go. My initial problem was how to get the crosstab going without the variance - hence my post. I just need to know what approach (relational or dimensional) is best suited to given task. It seems relational is more work in this case.

    I have built a dimensional Framework Manager model for this case with a scenario dimension. Do you know if it's possible to disable rollups since it doesn't make sense here.

    Thanks again.
    I'm not sure why you would want to disable rollups here. I find it hard to believe that your Actuals and Budgets are only reported at a Yearly level.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: compare multiple measures in crosstab

    ‏2011-08-19T05:33:18Z  
    I'm not sure why you would want to disable rollups here. I find it hard to believe that your Actuals and Budgets are only reported at a Yearly level.
    Hi again.

    Because the scenario dimension consists of only two members 'Actual' and 'Plan' and doesn't make sense for them to roll up at root. Also, our budgets are at monthly level. I just used yearly to simplify my reporting problem.

    So now I have a monthly fact table with a scenario dimension in FM. Should be good from now but just wondered if I should disable roll-up. Shouldn't need to bother I suppose.

    Thanks.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: compare multiple measures in crosstab

    ‏2013-01-09T03:38:10Z  
    Hi again.

    Because the scenario dimension consists of only two members 'Actual' and 'Plan' and doesn't make sense for them to roll up at root. Also, our budgets are at monthly level. I just used yearly to simplify my reporting problem.

    So now I have a monthly fact table with a scenario dimension in FM. Should be good from now but just wondered if I should disable roll-up. Shouldn't need to bother I suppose.

    Thanks.
    Hi Surgib,

    Let try the scenario dimension consist of four members 'Actual', 'Plan', 'Variance' and '% Variance'.