I am working on a model in Framework Manager (v 10.2.1) based on a relational Oracle database. I am looking for advice on the best way to do the following:
I have two tables - the first table is comprised of list of unique combination of foreign keys TABLE_A. I want to join this table to another table where the foreign key from the first table is repeated TABLE_B.
In TABLE_B, each time a record is modified, the original record is left intact and a new one is created. The database uses an effective year column in the table to determine which is the most recent. So what I want is my join to use only the record from TABLE_B with the maximum effective year.
Can I do this in framework manager? The only thing I can think of is to create a database function that I can call in my join which will return the maximum effective year for the given foreign key. Then my join expression could say ....AND FunctionCall(Parameters) = TABLE_B_EFFECTIVE_YEAR....
Any feedback is greatly appreciated and if I am missing something obvious I apologize in advance.
Decided to use a summary filter with maximum function in report studio for the moment. I am going to review how heavily this will be used in the future (how many reports) and if it is worth it, will build a database function.
Thanks
What you describe sounds very much like table B is a slowly changing dimension. I would expect that the database design of table B would have both an effective and an expiration date. The latest unexpired record would carry a date far into the future or a null making it easily identifiable as the latest. Trying to determine it on the fly with max functions is asking for performance problems right out of the gate.
Yes I would agree that TABLE_B is a slowly changing dimension and thank-you.