Customize GL Balance Fact

Ashwin Malgaonkar

Hi,

I have GL reporting requirements to create Balance sheets and P&L reports. We also need to provide ability to end users to use all segment hierarchical prompts to analyze and group data. The idea is to transform financial statement generator reports from the source into BI reports. We designed a custom solution around the OOTB Oracle data model. However, since the out of the box data model is snow flaked, we are facing performance issues when using hierarchical prompts. Since the snowflake uses, GL Balance fact -> GL Account Dimension -> GL Segment dimension -> Hierarchy dimension. When trying to analyze this with multiple segment hierarchies selected in Prompts at one time, the report performance is very slow.

In Order to address this, we plan to customize GL Balance fact to add Segment values and attributes directly in the fact table. So the join changes to Gl Balance Fact -> Hierarchy Dimension.

This avoids the joins via GL Account dimension and GL Segment dimension. And 1 Fact row can directly be joined to all the different segment hierarchies at lowest level. I did give this option a try and it performs much better.

But I want to get some suggestions for downside of this approach, since Oracle did not create this a perfect star schema model, is there something missing in the approach ?  Can anyone please suggest if the customization said above can impact data integrity or can have impact on ETL performance. My understanding is segment values of a code combination id cannot be changed in source.

Thanks,

Ashwin