Subset vs Alternative Hierarchy

[lav4you]

Dear All,

I am working on a model wherein I have a dimension name employees (around 500

For the ease of use the client has requested to group employee by contracts(300+).
The contracts and employees dimensions will keep growing but contract will change more frequently (on a weekly basis) than employees.

There are two possible options here to do the subsets or alternative hierarchy.

I would like to know from performance and maintenance perspective which one would you recommend?


Regards,
Lav

[jim wood]

Are contracts and employees in the same dimension?

[lav4you]

Hi Jim,

Thanks for the reply.

At present its not. I just have a list of employee in one dimensions and contracts in another. I received a request from client to group employee by contract in order to improve the usability(this does not mean contract dimension will be deleted)

The reason for the request is that the users dont have to scroll through a long list of employees and don't have to remember which employee is assigned to which contract.

The options are - either I create an alternative hierarchy by contract within employee dimension or
create a process to do employee subsets(and end up having 300+ subset) by contract

Each option has its own advantages and disadvantages

Option 1 will introduce contation and locking where options 2 will add an overhead of subset maintenance.
My personal preference is option 1.

I would like to know from performance perspective which one is a better option?


Regards,
Jitesh

[jim wood]

If they're in seperate dimensions then really shouldn't they be doing it in a view? If they have a contract selected and all employees selected and they supress zeroes then it will only show employees associated with the contract? Otherwise your going to have to run a process each night that creates a subset for each contract, which isn't really practical if you have a large amount of contracts. I guess you could create a hierarchy but that would involve mixing the contract and employee information, which again may not be practical as I'm guessing you get contract level data?

For me this is a training issue rather than a technical one,

Jim.

[PavoGa]

Alternatively, if you need to show employees who are associated with a contract who may not have any sales/other measurables and zero suppress would hide, you could use a drill through process with an MDX subset on the Employee dimension. You'll need a lookup/reference cube that has a value at each intersection of employee/contract assuming the relationship between employee to contract is one to many.

Ty