How to create duplicate members in Anlaysis Services 2005 (by Richard Tkachuk)-SSAS

How to create duplicate members in Anlaysis Services 2005 (by Richard Tkachuk)

Written by Richard Tkachuk

Tuesday, 01 May 2007 15:33

Introduction

In a previous document, I discussed how many-to-many dimensions could be used when a single fact was associated with more than one member of a dimension. But there are situations where one member can have multiple parents In this document, I’ll outline how a dimension can be configured so that the same member appears in multiple places in the same hierarchy.

Problem Statement

Consider the example of an organization where employees have a primary manager but matrix report to other managers. For analysis purposes, the same employee needs to appear in multiple places.

If the employee is to appear under multiple managers without its descendants, the recommended solution is to create additional members with the same caption but with a new key with a custom member formula that populates its values with the original.

If the employee is to appear in multiple places with its descendants intact, the solution is requires some extra work – this is what this document describes.