I'm wondering if it's worthwhile and possible to create lookup tables in Framework Manager to assist in normalizing our relational database. Essentially we're trying to give functional names to enumerations used in the production application (ex. for Priority; 0 = Low, 1 = Medium, 2 = High). Does it make sense to create a lookup table or parameter map to resolve these enumerations from a maintenance and/or performance perspective? If so, how is this done?
Would it make more/less sense to do this via case statements on the model query subject level? There's very little documentation available on this subject, so any assistance would be quite helpful.
I'd say it is a matter of personal taste ;D
If you would need to translate gender (M/F/hermafrodite (= H) / Unknown (=U) ) using a CASE would be okay, as there is very little chance you need to change this ever again.
However , some lookups tend to grow over time and that could be a pain.
Is it a mandatory field? Hopefully , as it makes a regular join possible. Non-mandatory fields are a different matter, meaning an outer join in case of a lookup. A CASE may be more logical then..
Good point, and in the example I mentioned, a simple CASE statement probably would be the best course of action. I'm looking at a scenario where I have probably around 30-40 lookup table "opportunities" with varying levels of complexity, some of which could be addressed with the simple CASE logic but others which would more than likely be better addressed with a lookup table of some type. If I could figure out the syntax of the parameter map to get it to replace the field value the way I need it, I think I'd be good to go! Problem is, I can't quite figure it out. Anyone out there good with the macro syntax for how to use the parameter maps?
this should help you:
http://www.ibm.com/developerworks/data/library/cognos/page32.html
Thanks for the link, Uttam. I checked that out yesterday and gave some of the suggestions a try but with no luck. Here's a quick example of what I'm trying to achieve: I created a very simple parameter map named "IN_WF_ITEM Priority" to help translate our numeric priority (0,1,2) to a name value (Low, Medium, High). I made the key values the numbers and the value values the names. Now what?
The field I'd like to alter/replace/modify is Workflow.IN_WF_ITEM.PRIORITY. What would be the proper syntax that I would use to utilize the new parameter map? I've tried the following (with no luck):
#$[IN_WF_ITEM Priority]{[Workflow].[IN_WF_ITEM].[PRIORITY]}#
#$[IN_WF_ITEM Priority]{'[Workflow].[IN_WF_ITEM].[PRIORITY]'}#
#$[IN_WF_ITEM Priority]{sq([Workflow].[IN_WF_ITEM].[PRIORITY])}#
These all have some variety of parsing error which causes the calculation to fail. I've got a support ticket in to Cognos for their assistance and they went through these same permutations with no luck and are now researching to see if they can figure it out. But thought I'd pose the question to the pros!
as far as I know, macro only accepts constants (say 'abc') & variables (prompt or session parameters).
The macros (parameter maps are used inside the macros) are evaluated first i.e. before creating a query, value for macro expression is calculated and then only query is formed.
this is useful when you want to select columns in a report based on prompts, etc etc...
so i dont think parameter map is the correct solution for what
you want to achieve...
anyone can please correct me, if i am wrong....