Analyst: Multi-item Lookups

[Rutulian]

Hi All,

Just made the step from EP Infra/Architecture to Analyst Modelling, and I'm bending my head around quite a tricky problem.

My import source contains number of different codes (CODE, mop, SS1, SS2, SC, COB) as well as a few data items and the Balance Sheet Group to which they usually belong.

For this application, there are a set of exceptions which are required to override the Balance Sheet Group for the line in certain circumstances.  These can intriguingly be defined by arbitrary combinations of the codes - for instance:

If SS1=XY, then BSGroup=OverRide1
If (CODE=RF and SS2=AA), then BSGroup=OverRide2
If (mop=SC, SS1=XX and COB=B), then BSGroup=OverRide3

The current solution is pretty ingenious, there's a 2-d input cube where the potential values for each code are set as D-List formats and the users set up the codes to match against and the BSGroup to which line items matching this rule should be overridden.

This exports to a CSV to output the IIDs along with the rules, where Excel is used to manipulate all the entries from this cube to produce a giant conditional statement which is fed back into the data import cube and apply matching by nested if statements against the IIDs in the line items (phew!)

It now takes a very long time to open this cube as for each line item all 150 nested if statements with up to 4 IID comparisons in each condition have to be evaluated.

I thought this might be possible with some kind of cube made of all the code d-lists, with the intersections being the IIDs of the Balance Sheet items to override with.  I can't quite get my head around how to make this work, though, and I'm not sure if it's a dead end.  Also it's not going to deal with all the non-overridden ones (which won't be matched and would thus pick up a zero).

Another option springing to mind is admitting that Analyst's not the best tool for pattern matching and push this back into the data mart, writing some VB/SQL to add another column with 'final BSGroup' which is calculated with some good old fashioned regular expressions.  This would get me the cube inspectability back, but creates a loop back from Analyst to the data mart.

I hope the explanation makes sense and the problem is a novel one - your opinions are very welcome!

Alexis