I just commented on a fascinating article http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/25147 by Jamie Oswald over on the SAP Community Network that relates to my earlier blog on BI and complex data environments.
He amusingly points up the data model optimisation “battle lines” between the BI and developer communities. I quote… “Why are transactional data models really hard to report off of you say? Because application developers build (currently out of necessity) really crappy data models. Data is stored a hundred times in a hundred ways so that the user experience is fast but the master data management is a painful process.”
He goes on, “Data is stored in painfully normalized (or denormalized – typically whichever makes the least sense from an analytics perspective) which makes ugly, inefficient, and often not-reliably-correct multipass queries necessary when trying to actually analyze the data. Finally, data is stored in [please feel free to insert your own “trying to report off of a transactional database” horror story in here]. The bottom line is typically that app guys/girls need to design their database in such a way that reporting folks throw up in their mouths a little bit when they see the schema because no matter what the application needs to respond FAST.”
Methinks it’s slightly harsh to consider the SAP operational data model in these terms, but the BI design challenge on top of this complex and optimised data model is very real. As I say in my comment to his blog we spend our lives helping BI developers interpret this OLTP optimised data model via the Saphir toolset and advising on methods of data discovery as input to the BI data design.
This issue really is not going away in any short timescale given the investment that SAP (and ORACLE Applications) have in their sophisticated and complex data models. Particularly as this complexity is increased in the majority of their customer sites by the practice of adding more tables to meet individual design needs.
Jamie floats the idea that HANA may offer some way forward in optimising the data model/design for both operational and BI needs. Personally I can’t see SAP rushing to fundamentally change things for the above reasons but interested to hear back from anybody with views on this.
In the meantime we continue to expound the use of good data modelling techniques for BI design and refining of our tools and methods in support of the deep dive data discovery from complex data sources such as SAP and Oracle Applications.