Basic Universe structure question BO 6.5

[pandpp]

Hello there.

I've had to become something of a Crystal to BO convert. I've used Crystal since the late 90's, but this is my first attempt at BO, 6.5.

I am using a product called Ellipse, which is using BO 6.5 as it's reporting tool, called MER (Mincom Ellipse Reporting).

Ellipse contains some 1200+ tables.
MER comes out of the box with 28 Universes, based on 5400+ Views.

Sorry for the background, but decided it might be useful. Here is my dilemma:

In Ellipse, there are things called AltRef Codes. For a given Asset, there could be literally hundreds of AltRef Codes, or, there could be none. Each AltRef Code is likely to contain different types of data.
Now, all of these AltRef Codes are stored in a single table. There is a second table that contains the data structure for each type of AltRef Code.

I have included an example of the data structure below to hopefully make it clearer; it seems as clear as mud to me, & I just wrote it.

[tt]Asset Table
-----------
abc
def
ghi
jkl

AltRef Code Table
-----------------
123
456
789

Joining Table
-------------
Asset AltRef Value
==================
abc 123 Monday
abc 456 Yes
def 123 Tuesday
jkl 456 No
[/tt]
In one of the Universes that is supplied, I have three Dimension objects for the AltRef Codes, the Joining Table in my example above, Asset, AltRef & Value.
To my way of thinking, should I not have one Dimension object for each AltRef Code that exists, AltRef_123, AltRef_456 & AltRef_789?
If not, how would I determine which AltRef Code I am wishing to report on?

I am looking for ideas & concepts.
All opinions greatly read & accepted.

If you require more info for understanding, please just ask.

Thank you in advance,

Peter.

 

[blom0344]

Transactional databases often store a special table for lookup actions. The trouble is that everything is stored into 1 table. This makes it a little hard to understand its use at first. Identical codes may have different descriptions. The trick is to use the second key to add the proper condition on.

If you look into the structure of the general lookup table you will see at least 2 columns for the primary key.

For instance:

The first one will relate to the value to be 'translated'
(= columnvalue in the datatable)
The second one indicates the type (narrows down for which datatable it should be used)

Unfortunatily, this second key is usual non-descriptive, another meaningless key.

If there are no further clues in the database, you can often deduce relations by running a distinct select on a datatable column and match the output with the lookuptable for a certain type.

Ties Blom