September 13, 2018 at 8:25 pm
Hi all,
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!
HawkeyeDBA
September 13, 2018 at 9:06 pm
I guess in a perfect world, you would have a operational database that functions as the main source for the application. In your case, this would be a normalized database where you users are uploading and correcting data via the application. Application in this sense, would be Excel or manually editing the records.
Then you would have a data warehouse, essentially a separate database, where you would take those records from the normalize database and then denormalize them for reporting purposes and do so on a daily basis. Thus, as users edit the normalized database, it does not directly impact the denormalized database. You split the use cases -- one for the application and one for the reporting. As users update the normalized dataset, you reflect those as snapshots to the denormalized world, which is completely separate and unified as one consistent and conformed model.
September 14, 2018 at 5:50 am
Everything xsevensinzx says...
However, if you get stuck to where you can't do both, the newer versions of SQL Server offer a bit of an out. With the ability in 2016/2017/Azure SQL DB to have either a clustered index with nonclustered columnstore indexes, or clustered columnstore indexes with nonclustered rowstore indexes, you can satisfy the needs of reporting through a normalized structure, or, some limited OLTP behavior in a warehouse structure. There are issues with this of course, but it does open up your choices some.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 14, 2018 at 6:26 am
Thank you both for your input and ideas. I've never encountered a relational db that just imported data and performed calculations lol
I just changed from DBA'ing the last 10 years back to development so I guess I have some adjusting to do 🙂
Thanks again,
HawkeyeDBA (Sandy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply