Asked by:
create calculation column in dimension

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
All replies
-
-
-
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
-
-
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.
-
-
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,
- Edited by VHteghem_Ph Tuesday, June 12, 2012 3:47 PM