on 09-02-2010 3:25 PM
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?
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
What do you want to exactly achieve with a universe?
Des the "Table C" exists when modeling the universe?
Didier
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
User | Count |
---|---|
84 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.