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

• Juan Gerardo

  SSC Journeyman

  Points: 89

  More actions

  July 11, 2005 at 1:08 am

  #572794

  Hi, you have two ways:

  1. 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.
  2. 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

  July 11, 2005 at 2:19 am

  #572800

  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