cancel
Showing results for 
Search instead for 
Did you mean: 

Expanding data structure within a universe to include extra tables

Former Member
0 Kudos

I have a conceptual problem for the universe design.

Table A has

ID

UniqueID

...

Table B has

ID

TableAId (Link to ID field within Table A)

...

The data within these tables is maintained via one application

Via a separate application we wish to populate a new "Table C" linked to "Table B" (table C not yet defined). However, it needs to be linked via the "UniqueID" as tables A & B contain historical data. Can this be done via the universe, say via the join properties?

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

I was able to solve this problem by creating a derived table D which select all data in table C and has a where clause within the derived tables SQL select statement which links the UniqueID.

Former Member
0 Kudos

Hi,

What do you want to exactly achieve with a universe?

Des the "Table C" exists when modeling the universe?

Didier

Former Member
0 Kudos

For every record in Table A, multiple records exist on Table B.

Also, Tables A and B contain historical data. (i.e. every time data is updated new records are created and existing data left unchanged for tracking purposes)

I wish to link Table C to Table B (same level of data) so that the data is available for all versions of the data (i.e. Independent from historical data existing).

The UniqueID in Table A remains static across all historical data.

On Table B there is also an identifier of the type of data that exists. So combining these in some way would be ideal

The data in Table C will be updated by a separate application.

Table C does not yet exist so can be defined in whatever way works.

ok ... the reason.

The alternative to this is to create a separate query in the report on this table, and merge the results. However, this makes creating the report more complex and restrictive than is potentially necessary.

If it is possible to link Table C to Table B within the universe, amending the report will be much easier as they can just include the report fields in the report and not have to worry about merge dimensions.

MariannevL
Advisor
Advisor
0 Kudos

Hi Richard,

So datamodel wise the easiest solution would be to add a new column to table B containing this unique ID from table A

(as you explained there can only be one throughout the history). Then you can use a normal join to this new table C.

If you want to solve this in the universe it depends on the underlying database.

One solution would be to replace table B with a derived table as follows:

Select B.*,max(A.ID) as A_ID from A,B where (normal join between A and B)

Then you can use this new column A_ID in the join to table C.

Another is to do a lookup in the join.

(select max(A.ID) as A_ID from A,B where (normal join between A and B)) = C.ID

Hope this helps,

Marianne