I've been tasked with a db re-design and while reviewing the current system and the business requirements I've gotten stuck deciding if I need to redo the db in a dimensional model or to go relational with it.
The database is used for comparing the close of the previous month (imported once a month), to a daily ledger (imported every day, overwriting the previous days' ledger). The database then runs stored procedures that calculate any variances between the two.
The other function of the database is to import from Excel or manually enter, reconciliation amounts to be applied to the variances to offset them.
Finally, users can modify supporting records that the imports bring in such as company, account, portfolio, and so on. The current database has an Active column in many of the supporting tables so the users can flag a record to exclude it from calculating the variances between the close and the daily ledger.
The end product is a report with all of the variances and reconciliation items that offset the variances. The users have an application on top of this database and they can "drill into" the variance to see what data was used in the calculation to comprise the variance.
I was asked to normalize this database but the more I look at it the more I wonder if I need to set it up as a dimensional model, However, in the past, any data warehousing I've done has not dealt with any direct editing to the records.
Has anyone else faced a similar dilemma or built such as system either relational or dimensional and if so, what were the pros/cons?
Thanks in advance for your input!