Agree with what others have said. This is *one* of the reasons that people build data warehouses, and as Preet mentioned, often star-schema ones.
One point I’d make though, is that I always like to challenge any time I hear that databases are slow because they’re large. https://blog.greglow.com/2018/02/05/sql-server-big-databases-really-slower/ and https://training.sqldownunder.com/courses/sql-server-indexing-for-developers/lectures/5803340 I regularly work with truly large databases, and they aren’t slow. If they are, I’ve got work to do. Regards, Greg Dr Greg Low 1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax SQL Down Under | Web: <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.sqldownunder.com%2F&data=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091516274&sdata=SLHeEGAMmWUY5YIwcC4oAPYr%2F9RIZdi4MNASsdzwX2I%3D&reserved=0> www.sqldownunder.com | <https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fgreglow.me%2F&data=02%7C01%7Csspahelp%40microsoft.com%7C1f0ea4d6b97e4d897f3708d666d1e890%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636809449091526278&sdata=IU8tnAITCjBxWafi3A9XpO9lF3PIwZJ8ad3t36lnxvs%3D&reserved=0> http://greglow.me From: ozdotnet-boun...@ozdotnet.com <ozdotnet-boun...@ozdotnet.com> On Behalf Of Preet Sangha Sent: Friday, 1 February 2019 11:03 PM To: ozDotNet <ozdotnet@ozdotnet.com> Subject: Re: [OT] SQL Server DB designed for reporting I spent a few years building these kind of reporting systems. Here are the key things I learned: 1. Absolutely design your reporting DB with completely different concerns to the transaction one. A Star schema is what I'd recommend. 2. Avoid SSIS unless you are doing something very high volume and requiring streaming. SSIS is opaque and a PITA to source control/diff easily.Instead use denormalised views that allow you to run near real time deltas to feed to reporting database. Using good IO and indexing can result in excellent performance using cross db/server queries. 3. If the performance of the reporting DB is not sufficient consider feeding the star schema in hypervcubes using SSAS or simillar. It's designed for reporting and is highly performant. MDX isn't hard once you get the hang of navigating these cubes.. I didn't spend much time outside of SSRS so can't comment on other reporting tools. Preet On Fri, 1 Feb 2019 at 18:05, David Burstin <david.burs...@gmail.com <mailto:david.burs...@gmail.com> > wrote: >> 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. Hi Nathan, My response is that "it depends". I would certainly not advocate a full separate database approach except in exceptional circumstances. However, CQRS is primarily an approach to having separate models for CUD vs R. I would argue that having SQL views is a form of CQRS, as it supplies a specific data model that is used for reads only. So, in the sense of having different views of the data for reading/reporting, and leveraging the experience and knowledge of CQRS practitioners, there is a good opportunity to find a solution for Tom's problem and answer any questions that come up (eg around synchronization if required). I believe that this approach is totally applicable to current LoB applications - the implementation is just a question of degree, and the minimum to meet the requirements is best. I again suggest looking at the work of Vladimir Khorikov, including the Pluralsight course, as it walks through the multiple levels of CQRS from simple views right through to separate databases. Somewhere in that range is the sweet spot for responsive reporting vs implementation work/risk. YMMV Cheers David On Fri, 1 Feb 2019 at 15:45, Nathan Schultz <milish...@gmail.com <mailto:milish...@gmail.com> > wrote: +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 <tmcgee...@gmail.com <mailto:tmcgee...@gmail.com> > 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 <tompbi...@gmail.com <mailto:tompbi...@gmail.com> 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
