We have one to many relationship in our oltp system. Let's suppose one service request and many service request activities.
For service request activities there are huge data.
One requirement is user wants to see the only latest activity of the request and analysis it with service request together. What's the best practice to modeling this kind of requirement?
Currently I have a fact table which hold the service request activities data, and service request will he the dimension. One way I can figure out is I can set a flag for the latest activity daily bases which will be used to filter out other records. But because the service request activities fact is huge volume data. There may be performance issue.
Latest means current date(or last day)? The below would be the best practice
I would try this;
I'll go with custom activity table and load based on the change capture to fee latest records based on sr_sra_id (column name might be wrong) and I truncate(from Informatica) this on daily basis.
This would have any point of time latest records.
Edited by: Srini VEERAVALLI on Apr 2, 2013 6:26 AM
Thanks, Srini & 806756.
@806756, Actually, we are trying to use fragmentation to filter activities fact table by latest flag which will be marked by ETL.
But not sure it is a good way or not.
@Srini, we have a flag to identify the latest activity in our fact table, but can not delete the rest data because we still need them in report.
Any more ideas will be appreciated.