SQL Server Performance

Calculated Measure

Discussion in 'Analysis Services/Data Warehousing' started by Mds203, Dec 17, 2004.

  1. Mds203 New Member

    I have a measure of of Unit cost * sales at the fact table level .

    I need to create a formula of Sum of (Unit cost * sales ) My question was when I created the measure The Aggregate function for that is Sum .My question was if I create a calculated member as Sum of the measure (Unit cost * sales ) would it give me a correct values
    Md
  2. Raulie New Member

    It will yeild inaccurate results. Use a Calculated member and not a derived measure instead when multiplying/Dividing one column from another. Calculated Members are calculated after aggregations are created as opposed to Derived measures. If your derived measure invlolves multiplying one column by a constant (Unit Sales * 1.5) then it doesnt matter.


    Raulie
    Hewlett-Packard Company
  3. Mds203 New Member

    Would it be correct If I created a derived measure of Unit Sales * 1.5 and then in the calculated member i only doo a division and not a sum since the Cube would already sum the data and get it .




    quote:Originally posted by Raulie

    It will yeild inaccurate results. Use a Calculated member and not a derived measure instead when multiplying/Dividing one column from another. Calculated Members are calculated after aggregations are created as opposed to Derived measures. If your derived measure invlolves multiplying one column by a constant (Unit Sales * 1.5) then it doesnt matter.


    Raulie
    Hewlett-Packard Company
  4. Raulie New Member

    I'm not following your last question, basically if your derived measure is a multiplication by a constant then setting the aggregation to SUM is not a problem, so after that in a calculated member you can do what ever you want with it.

    Raulie
    Hewlett-Packard Company

Share This Page