none
create calculation column in dimension RRS feed

  • Question

  • thank you.  i need to create a calculation in a dimension based off a measure in fact..eg: if Sales_Amount > 0, then i have create a Yes/No flag in a dimension. can i create this flag in dimension based on measure?

    i was trying to create calculation in cube script

    CREATE MEMBER CURRENTCUBE.[Product].[Product_Flag]
     AS Case
        When [Measures].[Sales_Amount]>0
        Then "Yes"
        Else "No"
        End, 
    VISIBLE = 1  ; 

    would this work? thank you


    aaron

    Tuesday, June 12, 2012 9:46 AM

All replies

  • Hi Aaron,

    Theses calculations should be handled while designing the data source view. You can create a named calculation.


    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    Tuesday, June 12, 2012 9:53 AM
  • hi,

    CREATE MEMBER CURRENTCUBE.[Product].[Product_Flag]
     AS Case
        When SUM([Measures].[Sales_Amount]>0)
        Then 'Yes'
        Else 'No'
        End 



    Hope this will help you !!!
    Sanjeewan

    Tuesday, June 12, 2012 10:08 AM
  • Hi,

    The name of Named calculation is invalid. 

    For the rest the formula will work.

    You cannot add an attribute flag this way if this was your intention. I don't think it is a good idea to create named queries in dsv. As your calculation is an aggregation  this can be better done in the cube as a result of a query rather than an sql statement as an imput. After all aggregations is what it is mainly about in cubes.

    You have all you need in the cube  to flag attributes by facts. Just give your calculated member a valid name and you can use it as a measure.

    Philip,







    • Edited by VHteghem_Ph Tuesday, June 12, 2012 12:05 PM
    Tuesday, June 12, 2012 10:45 AM
  • but how can i make sure the calculation gets added in the dimension and not the measure group. (users want to filter on the flag column).. thank you

    aaron

    Tuesday, June 12, 2012 1:09 PM
  • What is it that you want the users to ultimately get? You can't really create an attribute that they will be able to select to filter out items as you will need to allocate the items within the dimension?

    If you know what the users want you can probably set up a set that will contain the items filtered out as you require. Otherwise you will need a dummy attribute on the dimension that you can allocate the values to, but they won't really be able to give you a filter.

    Tuesday, June 12, 2012 2:20 PM
  • ok this is what users want. a flag column (in dimension) which gives them an option to select yes or no.

    yes -- if the count(calls) > 0

    no -- otherwise

    count(calls) --- is a measure in one of the measure groups.


    aaron

    Tuesday, June 12, 2012 2:46 PM
  • Hi,

    The name of Named calculation is invalid. 

    For the rest the formula will work.

    You cannot add an attribute flag this way if this was your intention. I don't think it is a good idea to create named queries in dsv. As your calculation is an aggregation  this can be better done in the cube as a result of a query rather than an sql statement as an imput. 

    "ok this is what users want. a flag column (in dimension) which gives them an option to select yes or no.yes -- if the count(calls) > 0no -- otherwise"

    The only way to achieve this is creating a flag in the ETL or with a named query in dsv. but you will suffer bad performances. The issue that will also arise is  as the users browse the cube the visual context as experienced by the users will change but not the flag

    The best solution is to create a Dynamic set of [Product]  with the calculated measure as defined in your question and instruct the users how to Use it. Also, if you succeed to create this flag, you may want it to be calculated with a dynamic set defined before the flag calculation.

    Philip,



    Tuesday, June 12, 2012 3:33 PM