Building attributes based on calculated values

I have a measure: "# of clicks". I have a calculated measure, which is "# of clicks in last 30 days". What I'd like to do is segment my users using this "# of clicks in the last 30 days" calculated measure so that I have three different segments: "No usage in last 30 days", "Some usage in last 30 days" and "Tons of usage in last 30 days".

Here's my question...what's the best way to create an attribute in my user dimension for these usage segments? The only thing I've been able to come up with is to pre-calculate the "# of clicks in last 30 days" when I build the warehouse and then put in the "case WHEN [# of clicks]" segmentation in the dsv. I don't like this method because it will like slow down the warehouse build dramatically.

It doesn't seem like there's a way to build that logic into cube or dimension. What am I missing? I thought about trying to define "sets", but if I do it that way I can't use the segment in reports as attributes to show.

Anyone have any advice? Thanks for any help can provide,

P

You could create calculated members in the calculation script of your cube to do this sort of thing. Although it will not slow down your build times, depending on your data, the performance could be slow at runtime.

I have assumed that getting the "last 30 days" is as simple as grabbing the last 30 nonempty days, in practice it is often not this simple, other approaches would include using date functions to determine the current system date and then lagging back 30 members, or some people even build their time dimensions as they are needed, so the last member in the dimension is the last day.

CREATE MEMBER CurrentCube.User.[tons of clicks] as AGGREGATE(
FILTER(Users.UserName.members
, SUM(TAIL(NONEMPTY([Date].[Days].Members),30)
Measures.[# of clicks]) > 100
)
)

You could also create a similar expression using a custom rollup formula in your dimension table.