Architecture for reports
We need to create reports in our system. We cannot create them on the network using tables for two reasons:
- Complex logic
- We want to move reports to a different system (just post some facts and create reports)
So, we need to collect facts about user actions. Today we have a job that analyzes CDC changes . But this approach has some problems:
- CDC change is not a necessary fact, we have to perform some operations
- Slow processing of CDC changes
But it helps us to collect all actions (even those done by the sql update script) and it is asynchronous (does not affect user performance).
Another way is to collect facts at the Business Logic level and send them to another system or save them to a spreadsheet. But it works synchronously and it is difficult to collect facts made with sql scripts.
So what's the best way to do this? Requirements:
- Collect all changes / facts (even generated by sql scripts)
- Does not affect the performance of user operations.
source to share
You can use Change Data Capture (CDC) or a logging framework, or both.
You can embed your custom logging rules into your business logic layer and write them separately through your logging framework in your application (to a text file, separate database tables, etc.) using Log4net or any other framework.
Ideally, if this log is written to a separate database, you can create all sorts of custom reports. If you need to record every database activity, you can set up Data Capture or use SQL triggers to capture only certain events (this will require a lot of configuration and testing work). Some helpful links I found are this
source to share