You don’t have a data warehouse. Maybe you have a reporting database with some de-normalized data marts. Or, at best you have a reporting database populated from transactions on your OLAP database. Now you have need to produce a report most easily calculated in a data warehouse using statistical queries rather than standard SQL. Where do you begin? What options are available to you?
The problem is that you need to perform some complicated data analysis, and you don’t want to disrupt the performance of you OLTP database. I experienced this phenomenon when an individual wrote a view that essentially joined the entire database so they could look up anything they wanted. Whenever they ran a query against the view the entire system came to its knees: literally.
Moreover, you do not have funds or time to create a well-defined data warehouse, or engineer a large Un-structured data mine. So, what is a developer supposed to do? Here’s a few ideas I have brainstormed:
- Replicate the data in some fashion so you can build reports more easily
- Modify your data access layer to duplicate work to a NoSql or similar data store
- Build views, indexed views, or even indexes to support your reports, knowing this can highly impact your OLTP performance
- Go to the well to get resources to create a more long term solution
- Create the necessary reporting, and execute it during time of reduced activity
- Build as much of the report design as you can in a reporting tool
What would you suggest? This is a real world problem. Small companies cannot afford the extra design. Larger companies may not be able to wait for better designs. How do we retrieve the data while still keeping the lights on? Share your methods in comments here, or by email to btaylor@sswug.org.
Cheers,
Ben