Lookup table in Framework Manager

[akhattri]

Hello,

We currently have a lookup table in the database which stores the descriptions for the various codes (not belonging to any one particular area). We have a couple of dimensions which store code value as one of the fields. We wish to have descriptions displayed and want to utilize the lookup table.
I thought of two different approaches to it :

1.   Create a merge query subject in Business Layer and create a join based on the lookup_code value to create this.
2.   Create a snowflake type structure which is sort of like 1, above but, has two different query subjects.


Is there any other approach to this? And if not, which one of the above would be ideal. If any one of you have any documentation addressing this, I will appreciate if you can pass this along.


Thanks

[bdbits]

Is this a relational model? If so, then here we would typically pull in the descriptive label into the query subject. We often have three attributes for sort of thing - the code, the description, and a code-and-description. If you know they only will ever want to use one of the three, you could just set the preferred label. In any case, I would want to avoid snowflaking the model just for code/description lookups.

If it is dimensional it is not quite as straightforward. If yours is dimensional, post back which type you use - DMR, Transformer cubes, TM1 cubes, Dynamic cubes... I know a fair bit about how you can do this with DMR and Transformer cubes. If it is another type, someone here will probably know how to get what you want to work.

[navissar]

Assuming this i relational:
If you create a merged query subject after having joined the lookup and the dimension, a query that selects fields from the merged query subject will look something like this:
SELECT a.code, a,name
FROM
(SELECT dimension.code,lookup.name
FROM
dimension, lookup
WHERE
dimension.code=lookup.code) a
If you create a snow-flakey structure (fact joined with dim, dim joined with lookup, so you get a two-hop relationship when displaying fact and lookup name), the SQL would look a bit like this:
SELECT
dim.code,lookup.name
FROM
dim,code
WHERE
dim.code=lookup.code
So as you can see, there's not much difference. On the long run the first is better (It creates a more readable model, and the "view" generated by the merge query will be cached in a long use), but the differences are minor.
What I would do is go one step backwards: If you have an ETL process, create the dim tables with the description in them already.
If not, use an indexed view (SQL Server)/material view(Oracle) to create a joined dimension table that includes the description. You can schedule it to update whenever necessary (This is dimensional data so I assume it doesn't really change all that often). That would improve the SQL because it will go to one source and save on a join.