I was just asked to create a report that slices and dices data. We are using postgres. Our schema is setup for OLTP.
The system contains orders, they have one status (out of 15+). Orders are also decorated with various properties (8+). The properties are stored in a key/value child table (because they change all the time).
End users need to know how many orders are in a given status+propertyX+propertyY. The two properties are the rows, the columns are the statuses, and the value in each cell is the number of orders matching all three criteria. The report does not have to be up to the minute, they would like to run it at the end of the day.
There will be more of these kinds of requests to come.
Doing it with SQL calls against the OLTP schema is not feasible.
A data cube might work. Does anyone know of a package in the open source world that might work in terms of BI, OLPA, or data warehousing?