Custom rollup formula or calculated member

Question

Post reply

Custom rollup formula or calculated member

Del Piero

SSCrazy

Points: 2265
More actions
July 10, 2005 at 11:50 am

#152459

Hi all,
Suppose I have a dimension containing members which are some sort of group numbers, e.g. 111,123,155, ....,266,288, .......
Querying my cube will give this result:
111 6
123 8
155 9
...
266 16
288 18
..............
If I want to insert a member, call it 100, which contains the total value of all group numbers starting with "1", i.e. 1XX, and this member '100' is NOT belonging to the original dimension ... So it would look like:
100 23
111 6
123 8
155 9
...
200 34
266 16
288 18
..............
What should I use to achieve this? Custom rollup formula or calculated member? Thanks.
delpiero

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Juan Gerardo SSC Journeyman Points: 89 More actions · Answer 1

Hi, you have two ways:

Get those members (100, 200...) from the fact table, as an aggregated level where 100 is the parent for 111, 123... This can be done by using a view with this column instead of the dimension table.
Create one Calculated Member for each new line you want to insert. An example formula for member 100 could be:
Aggregate
(Filter([Dim_X].[Level_Y].Members, Left([Dim_X].CurrentMember.Name, 1) = "1"))

I prefer method 1, because it is automatic, and if you add a new member 539, parent 500 will be created next time you process your dimension.

JG

Del Piero SSCrazy Points: 2265 More actions · Answer 2

Thanks JG,

For Method 1, is it I can't use it if my original fact table does not have the members 100,200? They are just treated as rollup members and they dun actually exist in the fact table.

Actually what I want to do is just what the ROLLUP operator in T-SQL does. Is there a disadvantage to retrieve the cube data into a temp table in T-SQL and then use the rollup operator to produce my desired results? Will it have a poor performance?

Best regards,

delpiero