Hi all,
I'm a newbie in the Cognos world.
I have an history table where for each person exist some rows, for example:
Name | LastName | Job | Start_date | Finish_date |
Robert | De Niro | Actor | 01/01/2010 | 01/01/2011 |
Robert | De Niro | Regist | 01/01/2011 | 01/01/2012 |
Roberto | Baggio | Football player | 01/01/1989 | 01/01/2001 |
Roberto | Baggio | Coach | 01/01/2004 | 01/01/2012 |
Now i would create a query subject to find the last job of Robert De Niro and Roberto Baggio.
Does anybody can help me to solve this problem? Thanks in advance
This is less of a Cognos question and more of a general design question.
The type of table you've described is a SCD Type 2. That type is very good at finding the state of a certain element on a specific date (today for example) or against a range of dates, but it's entirely unsuited for finding the last state of the element. In terms of the query, you would need complex logic (probably in the terms of a self join) to get it.
The correct way of dealing with this is to modify the table to add a last and current state flags. This would allow you to do a simple select * from history where last = 1. The change should be done in the ETL level.
If that's not possible, then you'll need to do something like this.
Create a new data source query called EmpLastDate with: select id, max(finish_date) last_date from history group by id It might be better to make that a view in the database to give the db optimizer a better chance to work with it.
Join that table on the history table on history.id = emplastdate.id and history.finish_date=emplastdate.last_date
In the business layer create a filter that has [EmpLastDate].[id] = [EmpLastDate].[id].
When you drag that filter into the report, it will force the join to the EmpLastDate table, thus filtering the data that you need.
Hi,
above all thanks for your reply.
I can't use an ETL practice because I need to execute a query in real time on main data source.
Have you any other idea?
Thanks again
How is the main data source populated? It should be a trivial matter to modify the process to add flag fields.
If the record is being closed the update could be:
update history set finish_date = ? and last_flag = 1 and current_flag = 0 where record_id = ?
If a new record is being inserted, you just need to do a
update history set last_flag = 0 where emp_id=?;
and add last_flag =1 and current_flag = 1 to the insert statement.
Again, it is possible without modifying the source system, with the SQL I posted above. But that will be significantly slower than the correct way of doing it.