MER Universe design

[pandpp]

Hello there.

I have cross posted this in the Business Objects solutions Forum as well, since I notice that this forum doesn't seem to get much traffic, but, my responses here would be much more appropriate.

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.

 

[Calator]

Some of the "objects" ("entities") of Ellipse , eg Equipment, can have "user-defined" "Alternate references". Other entities can also have user-defined "Reference Codes". Both are ways of extending the database model so that it can add user-defined fields against an entity (table). Both are Ellipse terminology!

From your posting it is not clear to me, if this is the case of alternate ref, or ref codes. I am alos not sure if you refer to teh Fixed Asset register, or the Equipment Register. In the future it may help if you quoted exact details such as the Universe name, etc, instead of saying "one of the Universes"

It may also help you if you went to the ellipse 'data dictionary' which is closer to the physical implementation of the db model.

The joining table looks ok to me, such as joining Asset Number with Alternate Ref Code with Alternate Ref Value, eg AltRef code '123' might have been defined as 'Day-of-Week', then Asset Number 'abc' has recorded the value 'Monday' in the Day-Of-Week, while Asset Number 'def' has recorded 'Tuesday'. A second altref code was defined as code '456' and appears to be a Yes/No value

Just be aware that in the case of "Reference Codes" (I am not sure about "Alternate ref") you may store an array of values against a pair <entity, ref code> in which case there will be a "sequence number"

There is a bit of info on 'Reference Codes' in FAQ here or against the MIMS forum. The tables for reference codes are MSF070 and MSF071.
Alternate References are much simpler.

 

[Archimedez]

First, the underlying tables are: MSF600 - Equipment Register; and MSF601 - Equip Reg Alt Reference. From MSF600, you need only 2 fields: "EQUIP-NO" (12-character key) AND "PLANT-NO" (human-readable name).

MSF601 holds 5 types of records, you want only 1: "WHERE REC-601-TYPE = 'EA'. Constrained within this type, the relationship between MSF600 and MSF601 is a simple parent-child.

A natural join exists: MSF600.EQUIP-NO = MSF601.ALT-REF-CODE

As you know, Each unique EQUIP-NO may have multiple ALT-REF-TYPEs and ALTERNATE-REF values, or none. You may ALSO find records that have the Type but no value for it, because Alt Reference entry is not mandatory on the front-end. I typically put in a user-prompt for the ALT-REF-TYPE and report the PLANT-NO and ALTERNATE REFERENCE value.

That last piece to this query: ALT-REF-TYPE description is held on MSF010, TABLE-TYPE = 'AR', TABLE-CODE = MSF601.ALT-REF-TYPE