Best practices for resolving look-ups?

[McFish]

I'm working with a vendor's database and can't change the ETL job to resolve lookups so I need to do this in FM. 

A sample from TBL1 is something like this:

id	ex_type	ex_action	ex_activity
23	A		2
24		BB
25	G	Q	7

The look-up table is:

lu_name	lu_code	lu_translation
ex_type	A	Type A
ex_type	B	Type B
ex_type	C	Type C
ex_action	AA	Action AA
ex_action	BB	Action BB
ex_action	CC	Action CC
ex_activity	1	Activity 1
ex_activity	2	Activity 2
ex_activity	3	Activity 3

If I'm not mistaken (and I could be), I'd like to create a new query subject with the translations all resolved to the text for the single dimension table.  If I can't do this in the ETL, how do I do this in FM?

[JGirl]

Normally, the way to handle this would be to import your fact and lookup table into the FM model, then create a separate alias table of lookup for each lu_name (and apply the lu_name filter to each), and then from TBL1 create x number of joins to the x number of lookup aliases.

Assuming your data below is the entire set, you'd end up with 4 query subjects in FM:
TBL1: select * from TBL1
LOOKUP_ex_type: select * from LOOKUP where lu_name = 'ex_type'
LOOKUP_ex_action: select * from LOOKUP where lu_name = 'ex_type'
LOOKUP_ex_activity: select * from LOOKUP where lu_name = 'ex_type'

The joins between these would be:
TBL1 <---> LOOKUP_ex_type on TBL1.ex_type = LOOKUP_ex_type.lu_code
TBL1 <---> LOOKUP_ex_action on TBL1.ex_action = LOOKUP_ex_type.lu_code
TBL1 <---> LOOKUP_ex_activity on TBL1.ex_activity = LOOKUP_ex_type.lu_code

But - whether or not the solution above would be practical of course depends on your reporting requirements.

Hope this helps.
J

[blom0344]

Other than using the proper aliasses for the lookups you may need to use outer joins to avoid losing data from the facttable. Make sure whether this is required before building the model in FM

[McFish]

Thanks JGirl!  I have started by creating an alias for each look-up and wow, there are a lot as I have 30 look-ups in TBL1. 

But, I did run into the problem that you mentioned blom0344.  How do I make them outer joins?  And yeah, I do need them to be.

[blom0344]

You specifiy the cardinality settings of  a join in the Relationship Definition GUI.
Caridinality at the dim side should be 0..1.
Cognos automatically adds an outer join indication to the join definition which reads something like:

Each dim entry has one or more fact entries
Each fact has zero or one dim entries

[McFish]

Ugh, much better.  I had the cardinality set to one or more, not zero +.  Many thanks!

[McFish]

It's me again.  In testing for some results and looking at the SQL I notice that the Cognos SQL has some nice left outer joins (good) but at the native SQL (Informix) it's running an equal (i.e. TBL1.ex_type = LOOKUP_ex_type.lu_code, not good).  So when the test data comes back, it's definitely not right.

What am I doing wrong now?  The cardinality is set as suggested and the Cognos SQL looks good so why is the native SQL not?

[blom0344]

Outer join syntax in Informix seems to be non-ANSI standard. I wonder whether the Cognos UDA compiler will know that it should translate Cognos SQL to the Informix syntax.

[McFish]

Looks like that wasn't the problem, it's that I had the relationship reversed.  Once I changed that it seemed to work.

BUT, it just got better.  ::) Some of the look-ups can be multiple values.  So, an entry can be 'ABC' which would translate to 'Activity A, Activity B, Activity C'.  Not all, but some.  Is there a macro or function I can create to make the translation?

[blom0344]

You can ultimately check the length of a code and parse the individual components using a substring function. But that would be an action after performing the lookup itself..