Before one can fill in the blanks and create a data store in any report writer product, someone has to go back to the source and do analysis, data migrations, perhaps multiple transformations, and then document it all so that the user can learn how to use it.

Recently while watching a Jaspersoft demonstration it was difficult to remain objective as to the simplicity of using the product. Of course it appeared as click, click and the results were returned; but, that’s not where I typically come in… the backend work done getting to that data store may very well be as complex as my example.

While working with a local medical equipment vendor from 2005 to 2006, we created a reporting system which was delivered as a beta product to one large customer in their European market. The product was pulled off the market after a year of beta testing. It was not simple enough to be used by the end-user. I noticed a team being formed last year which seemed to be duplicating that effort. I do not know whether product advancement, specifically the user interface, could be improved enough between 2005 and 2012. It might be worth a try to repeat it.

The development team was as small or as large as it needed to be at any one time. It most often consisted of six people, with meetings attended by 20 to 30. It was not a small team. It was a highly customized effort.

The system being created contained three types of devices called stations. Each of the stations potentially contained one of three versions of Sybase. Each version of Sybase contained a different version of the station application schema. The operating systems were different, as well, predicated by the version of Sybase and the application.

The relational databases in each of nine possible environments were replicated into a central version in Sybase which contained all nine schemas and replicated data from the stations. From there, the merged Sybase station data was replicated with transformations into a Microsoft SQL Server 2005 schema which was designed with complete audit trails and tools for reviewing.

Medical devices must report everything anyone might want to know… always. The relational SQL Server database between Sybase and the star schema was heavily customized. It was shadowed in such a way that very detailed records of every transaction where made and duplicated along with reporting abilities. The original Crystal Reports from each of the nine station versions were also all supported on this server. Reporting ability was also rolled up for multiple stations. There were years of development in this schema.

Data was then extracted into a modified star schema for reporting purposes. The end product was based on Microsoft SQL Server Reporting Services. This schema was developed with the user community and the data moved this last time with SQL Server Integration Service.

Schema, data dictionary and pictures were delivered for the “reporting database”. Only then can one go click, click, click and create a data store. The data source comes first, then the data store. Not until. If one has no data source they have little business picking a report writing tool.

Leave a Reply

Your email address will not be published.