We were working with a Market Intelligence function that was responsible for publishing research reports from a blend of internal and purchased data. The function’s work was complicated by many factors, including:
- Purchased data arrived denoted in calendar years which did not coincide with our client’s fiscal year.
- Our client has a complex geographical hierarchy where subsidiaries report into regional and area units. The Market Intelligence produced was required to show views for each subsidiary, region and area so every report necessitated a consolidation process.
By designing a database that was able to hold all types of data along with metadata to describe our client’s reporting hierarchy we were able to automate both the CY to FY conversion process and the consolidation of data.
When we launched this simple database tool it quickly started to produce up to 2,000 Market Intelligence Reports per month for a user base of 500 staff. By reviewing the audit data we soon found that users from other departments has discovered the tool and were using it for purposes not associated with the original MI goal. For example traveling executives were using the reports as a briefing to the market in subsidiaries that they were due to visit.
The system was later enhanced by linking to an OLAP cube which was designed to speed up the querying of data and it also allowed us to use the MDX language to define calculated field such as Business Ratios, Growth Rates and KPIs.
We also connected the system to security tables that were already established in other financial systems so that we could publish more sensitive data whilst ensuring compliance with Sarbanes Oxley.