For 1 year in 2007/08 I contracted with Unisys who had a number of contracts with various government departments and other public bodies to support Oracle E-Business Suite implementations. One of the clients was the London Metropolitan Police. I carried out a variety of work in relatoin to interface enhancements for the Oracle HR system. I also designed and delivered a set of Oracle Discoverer workbooks to assist with identifying officers with matching (or close to matching) sets of skills for a variety of roles related to the London 2012 Olympics.
The challenge was to provide a capability to the Met Police to be able to ask the following types of questions:
“I need 200 officers for role X. role X is identified as requiring officers to be trained in a number of areas. Which of my officers, given the current training they have received, are the best fits for these roles, and for each officer what additional training is required.”
At this time I had not previously worked with Oracle Discoverer - but had a good familiarity with Business Intelligence tools and OLAP principles in general. I quickly acquired the Discoverer specific skills necessary to generate the reports. The complexity was to generate a data model and SQL that was sufficiently performant given the complexity of the queries being issues. A number of advanced SQL techniques were used to improve the performance so that it was acceptable and the delivered tool was fit for purpose.