How to freeze historical reporting structures?
Posted: Mon Jul 15, 2013 12:33 am
Ok, so the context of this question is for a financial reporting system covering both management and regulatory reporting ...let's just say you have data is stored at the leaf level GL, which may map to multiple reporting lines e.g.
+Repline A1
....GL12345
+ReplineB1
....GL12345
+ReplineC1
....GL12345
...then there is a remapping in a subsequent month from Repline B1 to Repline B3, so the hierarchy then becomes:
+Repline A1
....GL12345
+ReplineB3
....GL12345
+ReplineC1
....GL12345
Question: how to reflect current changes in mapping structures, without impacting historicals?
Some possible solutions I can think of are:
Option A - Split the Account and Reporting Line into 2 x dimensions
PROS: historicals frozen, simple design
CONS: annoying for users having to navigate 2 dimensions
Option B - Create a concatenation between Account and Reporting Line)
e.g. GL12345-ReplineB1, GL12345-ReplineB2 etc
+Repline A1
....GL12345-Repline A1
+ReplineB3
....GL12345-ReplineB3
PROS: historicals frozen, easy to browse
CONS: duplication of leaf data may impact storage/performance
Option C - Create a dummy "Linking" element between Account and Reporting Line
e.g. GL12345-Link1 rolls to Repline A1, B1, C1 etc
+ReplineA1
....Link02
.......GL12345-Link02
+ReplineB3
....Link02
.......GL12345-Link02
PROS: historicals frozen, easy to browse
CONS: additional overhead to maintain separate mapping structures, Link must be unique, Link to Reporting Lines may become complex (e.g. there may be 6+ Reporting Lines to link to)
Option D - Create a cube for frozen data & structures
PROS: historicals frozen
CONS: additional overhead to maintain separate cube, less efficient workflow for users, questions on "which cube has the real source of truth"? etc
Option E - ???
...anyone have any other ideas? Also am curious to know how people have handled this situation if you'd like to share your experiences
Many thanks!
Matt
+Repline A1
....GL12345
+ReplineB1
....GL12345
+ReplineC1
....GL12345
...then there is a remapping in a subsequent month from Repline B1 to Repline B3, so the hierarchy then becomes:
+Repline A1
....GL12345
+ReplineB3
....GL12345
+ReplineC1
....GL12345
Question: how to reflect current changes in mapping structures, without impacting historicals?
Some possible solutions I can think of are:
Option A - Split the Account and Reporting Line into 2 x dimensions
PROS: historicals frozen, simple design
CONS: annoying for users having to navigate 2 dimensions
Option B - Create a concatenation between Account and Reporting Line)
e.g. GL12345-ReplineB1, GL12345-ReplineB2 etc
+Repline A1
....GL12345-Repline A1
+ReplineB3
....GL12345-ReplineB3
PROS: historicals frozen, easy to browse
CONS: duplication of leaf data may impact storage/performance
Option C - Create a dummy "Linking" element between Account and Reporting Line
e.g. GL12345-Link1 rolls to Repline A1, B1, C1 etc
+ReplineA1
....Link02
.......GL12345-Link02
+ReplineB3
....Link02
.......GL12345-Link02
PROS: historicals frozen, easy to browse
CONS: additional overhead to maintain separate mapping structures, Link must be unique, Link to Reporting Lines may become complex (e.g. there may be 6+ Reporting Lines to link to)
Option D - Create a cube for frozen data & structures
PROS: historicals frozen
CONS: additional overhead to maintain separate cube, less efficient workflow for users, questions on "which cube has the real source of truth"? etc
Option E - ???
...anyone have any other ideas? Also am curious to know how people have handled this situation if you'd like to share your experiences
Many thanks!
Matt
