Reporting Practices

Every time I hang out with Steve Gray I get a little smarter. (It's probably the opposite for Steve. Sorry about that.) He made good point on Saturday and I'm going to try to summarize it. GP's tables are a pretty good balance of optimization, not tremendously favoring transaction performance or reporting. This works pretty well in the middle space that GP occupies. (If you ever want to buy me a drink and argue the pros and cons of how far to take database normalization, my drink of choice is Diet Coke and Crown Royal.)

As a contrast, I have another app at work that is highly normalized for transaction performance. Don't even think about writing a SQL based report without joining 8 tables. It won't happen. Oh, and all the unique identifiers are GUID's. Great for transactions, yucky poo poo for reporting.

Even so, we all want better performance and easier report building. Steve's new mantra is to create a table with everything he needs to report off an area in GP. So for sales, he can prebuild a table with all the data from the sales tables. Then, every time he needs a sales report he doesn't have to join the pieces and figure out the joins from scratch. The initial work is a pain, but after that reporting gets easy.

This is essentially the same principal that most data warehouses run on. Despite all the benefits of a relational database, for reporting, flat files are faster. That's why GP's temp tables in report writer are still Pervasive flat files, not SQL tables.

Another benefit is that it makes reporting a lot easier if you don't have to figure out what tables you need. Just grab the Sales Reporting Table for instance and start plugging in fields. Yes this can cause some headaches around updating the reporting table, especially if need real time (versus near real time) data but you might still be able to get good performance and instant updates with a Stored Procdure instead of a table. It just depends on how much data you're using.

If I do anything around this to make my life easier, I'll put the SQL code up on the website for your use as well. Thanks again for the idea Steve.