+1 to Tony's answer. Using SSIS (integration services) to export to a reporting database or data warehouse. >From there you can use Power BI (for Dashboards) or something like SSRS (SQL Server Reporting Services) for traditional print invoices and things.
It's only if you were building a green-fields application that you'd look into CQRS - it's not something practical for your existing line of business apps. On Fri, 1 Feb 2019 at 07:55, Tony McGee <[email protected]> wrote: > One way to do this is to have a regular job that uses SQL Server > Integration Services package(s) to extract data from the source system, > transform it, cleanse, etc then load it into a destination database (ETL) > > Often, but not always, the destination database has a star schema with > fact and dimension tables, that a data warehouse can be built upon. Tools > like SQL Server Analysis Services or Excel/Power BI can understand and > slice the data for the user interface > > For large volumes of data there's usually an initial load, then subsequent > runs capture any changed data from the source system and load it > incrementally > > > On Fri, 1 Feb 2019, 08:27 Tom P <[email protected] wrote: > >> Hi Folks >> >> I have a normalised database for an application that is working well but >> is not performant for reporting. I’ve tried and tried to optimise my >> reporting queries but they are just too slow as the data volume is large in >> the database. What techniques are there to tackle this? >> >> I was thinking of creating denormalised tables for the reports which >> would work but how to automate this so that whenever the main normalised >> tables are updated then the changes “flow into” the denormalised reporting >> tables and stay in synch? >> >> I’m sure this is not a new problem so surely some of the people here have >> been in this situation. Any advice would be appreciated. >> >> Cheer >> Tom >> -- >> Thanks >> Tom >> >
