Cognos Framework Question

[mstrpgmr]

I have a database that reuses a table for multiple foreign keys on other tables.

Example:
Account table references itself in a parent child relationship and calls it Parent Account

Sales table references both the Account and Parent Account.
The data in Sales is more of a historical copy of the parent child relationship for the Account at the time of the Sales entry.

I am looking for a way other than creating loads of Account aliases to be able to pull back the data from either the Account or Sales tables.

This is just one example. Throughout the database, the Account table is used 50 times and each one has the historical Account to Parent Account relationship at the time of the entry.

If my calculations are correct, I would need about 100 aliases for Account to set the relationships in the Framework correctly for my clients reporting needs.

Does anyone have a better solution?

 

[blom0344]

You can think of alternatives like database views, but the bottomline is that you need to establish the relationships in FM, hence the alias objects.
It is the limitation within SQL that objects can only be referenced once , otherwise you could get away with multiple paths to 1 alias

Ties Blom