I am working on to decide the best way to create repository. I do have a requirement from my client that they need to see both Current and Historical Reporting via OBIEE. I am keeping Type 2 SCD. My model consists of about 30 fact tables - each of them represents a business process/event.
Following are my options:
1) One Subejct Area to keep current/historical reporting. Here the joins would be based on PK/FK relationships with Effective Start and Effective End dates. There is a flag in all tables to find the most updated record. Users can use the flag to find the most updated record or use Effective Start and Effective End dates to go point in time.
2) Create two Subject Areas - Current and Historical reporting. Current one will include the flag logic to get the most updated record in RPD
3) Create multiple Subject Areas for each Fact table and use Combining Multiple Subject Area approach to join Subject Areas. Additionally, create one Subject Area for Historical Reporting (that will have all the tables in one SA)
I am thinking of going ahead with option 3) This will have Subject Areas for all logical facts (for Current reporting) and one Subject Area for Historical reporting.
Is this a standard design? Any inputs?