Timespan accumulating fact table

I have a reporting need where we have the following scenario. Every year there is an operational risk review done for business processes/applications. The life cycle goes as follows:

Creation of an Assessment (AKey)
Associating the relevant Processes/Activities (P Key)
Associating the relevant Risks (R Key)
Associating the relevant Controls (C Key)
After evaluating the controls, if there is an Issue then Issues are created (I Key)
For each issue one/more Action Plans are created (AP Key)


All of these activities are done over weeks and in some cases months.  As one goes down the hierarchy from step 1 to 6 the relationships are typically one to many each level down. Business is interested to see the life cycle of an Assessment during various stages in near real time manner as any of the business entities are getting created/modified under an assessment. I will be creating a dimension for each of the above underlined entities.



Since the changes are happening in near real time in the OLTP system, the period will be at a time grain. The reporting requirements are:

Show me how an Assessment looks at 10 a.m. on 1/2/2016
Show me a snapshot for all Assessments as of 1/10/2016


I plan to go with a Factless fact table approach since the accumulating fact table will not meet the reporting need in my opinion. The ETL will load the fact through inserts as the Assessment is being assembled. I will therefore end up with something like shown in the table below. For reporting, I am suggesting to constrain each dimension between the effective start and end dates to report and then join to the Fact table.



Am I on the right Fact table design for the stated requirements? OR is there a better design alternative:




PERIOD AKEY PKEY RKEY CKEY IKEY APKEY Assess ID Process ID Risk ID Control ID Issue ID Action Plan ID Change Type
1 1 -1 -1 -1 -1 -1 A1 -1 -1 -1 -1 -1 A1 - New Assessment Created
2 11 -1 -1 -1 -1 -1 A1 -1 -1 -1 -1 -1 A1 - Assessment name changed
3 11 1 -1 -1 -1 -1 A1 P1 -1 -1 -1 -1 P1 - Process/Activity added to Assessment
3 11 2 -1 -1 -1 -1 A1 P2 -1 -1 -1 -1 P2 - Process/Activity added to Assessment
3 11 3 -1 -1 -1 -1 A1 P3 -1 -1 -1 -1 P3 - Process/Activity added to Assessment
4 11 21 -1 -1 -1 -1 A1 P2 -1 -1 -1 -1 P2 - Some process attribute undwerwent SCD 2
5 11 1 1 -1 -1 -1 A1 P1 R1 -1 -1 -1 R1 - Risk added for the Process P1
5 11 1 2 -1 -1 -1 A1 P1 R2 -1 -1 -1 R2 - Risk added for the Process P1
6 11 1 1 1 -1 -1 A1 P1 R1 C1 -1 -1 C1 - Control added for the Risk R1
6 11 1 1 2 -1 -1 A1 P1 R1 C2 -1 -1 C2 - Control added for the Risk R1
7 11 11 1 1 -1 -1 A1 P1 R1 C1 -1 -1 P1 - Process Description updated
7 11 11 1 2 -1 -1 A2 P2 R1 C2 -1 -1 P1 - Process Description updated


Thanks and Regards,
Sadashiv

Hi - could you provide a bit more detail around your reporting requirements:
Show me how an Assessment looks at 10 a.m. on 1/2/2016
Show me a snapshot for all Assessments as of 1/10/2016


What does an assessment look like? Do you want to know whether it has Processes/Ricks/Controls etc associated to it and if so do you need to know just that it has them, or how many of each one, or information about each one?
Regarding SCDs, when you want a snapshot of a particular date do you need to know that state of its associated Process(es) (or any other dimensional records) at that date or their current state or both?

Are you trying to do analytical queries (e.g. show me how many of x, grouped by y and filtered by z; what was the average time between an Assessment being created and and a Control being added) or are you just trying to display textual/transactional information about an assessment?