In the face of increasing demands to streamline, improve and standardise its business processes, Statistics New Zealand has integrated BI into its business. This will improve the accessibility of government statistics.
The department’s former SAS or SuperCross system used rare skills, which needed a high degree of expertise. This limited the analyses that could be performed. SuperCross also had its own proprietary database structure, which, at the time, could not be integrated with Microsoft Content Management Server (MCMS) — this was what Statistics NZ wanted.
Project manager Chris Toohey says the department compared existing internal systems with external vendor solutions. Statistics NZ is a Microsoft development shop, so it believed only Microsoft could meet both the business and IT needs of the project. Its developers are familiar with MS, so Microsoft offered a future-proofed option, with ongoing support and enhancement.
The solution chosen involves the integration of Microsoft cubes, Office Web Components, and Content Management Server (CMS). It is also designed to be scalable and re-usable in other Statistics New Zealand data-sets.
The solution went into production in July 2007. It uses an automated OLAP cube tool-builder and a metadata manager, with a browsable user interface, containing output classifications and time-series concordance mappings.
There is also a “Total Placeholder Solution” (TPS) — a multi-tiered web content creation and publishing application, which uses Microsoft Office Web Components.
“This allows authors to directly access the OLAP cube and create the tables, graphs and texts for the products. Content generated for one region is automatically generated for all regions in the country. The tool automatically generates dynamic text and numbers embedded in the text, using conditional programmable statements. For the end user, texts and facts change to match region selected,” Toohey explains.
The TPS is now been updated and will be called “The Excelerator”. This is a highly customised version of Excel 2007, using Visual Studio Tools for Office 2007, which links directly to the OLAP cube, to provide an even more advanced and flexible web-content creation and publishing tool.
“As far as we know, this is a completely novel and innovative conversion of Excel 2007 into a web publication tool,” he says.
However, Toohey reports some issues with the cube’s performance and confidentiality rules, from using an old cube structure that wasn’t optimally tuned.
“We also had to interface with an existing MCMS application that wasn’t optimally designed to handle the volume of information the BI solution delivers. We are addressing this, with the application of additional hardware and [the] storing of information in a separate indexed database accessed from MCMS. This will facilitate the production of large volumes of information in future web projects,” he says.
The system went live on September 26, although a couple of issues are still being resolved. Nevertheless, Statistics NZ is working on a second BI solution, for analysing student loan data, which, it says, is proving highly successful.
“It will replace the reliance on SAS, with a drop and drag Excel pivot table solution, and enable far more analysis, with more analysts. The solution is scalable and reusable for us and the Excelerator tool has exciting potential, as all analysts are familiar with Excel and its basic functionality,” he says.
Looking back, Toohey says he would have instigated a change management process for the Census, so everyone understood the impact of the new solution. There would also have been a prototype.
His tips for success include having a good team, discovering what information is needed and developing a prototype for project success, acceptance and promotion. Using well-known, trusted and leading-edge technologies is also essential, says Toohey.
Project managers are needed, too – to sell the project internally. Organisations should use their technology partners and not be afraid to seek external support and consultation.
Toohey cites Statistics NZ’s use of Microsoft’s Pat Martin, for peer review and analysis.