Put simply, we have been impressed with how quickly we were able to put the system together, and the price competitiveness was very impressive. Off-the-shelf products we looked at were expensive and inflexible. In contrast, the choice of development based on Microsoft’s SQL 7 OLAP functionality resulted in a cost-effective solution precisely tailored to our needs.
Mel Birch, Director of
Computer Services
The Defence Force’s IT Bureau has the responsibility of ensuring that the 14,000 plus individuals in New Zealand’s Army, Navy, Air force, and the various civilian support staff, have access to network-connected services wherever, and whenever, they are needed.
One of the constant challenges for the New Zealand Defence Force’s Computer Services Bureau is to meet changing service level requirements as the gap between military operational, and support systems, closes.
Another notable challenge is to enable authorised personnel to access networked services twenty-four hours a day - no matter where they are. This includes secure access to information hosted on the Defence Force’s 200 plus application and file servers from networks and workstations in remote locations such as London, Washington DC and Dili.
The dominant NZDF mail communications tool is Microsoft Exchange, and e-mail services have become mission critical to the Defence Forces. To ensure that this service could be managed effectively, the Computer Services Bureau needed to analyse the communications traffic between sites, both internally and externally, for Microsoft Exchange.
More precisely, the Bureau wanted to be able to quickly and simply measure the growing usage of email and the pressures, if any, this was likely to put on the various servers in future.
“Our ethos is one of, if you can’t measure it you cannot hope to manage it,” said Mel Birch, Director of Defence Computer Services Bureau.
“Originally we looked for off the shelf products but they proved too costly (up to $100,000), and often lacked flexibility. ”
Mel Birch set his development team the goal of finding a way of extracting the logs from the Microsoft Exchange Servers and putting them in a simple to analyse format.
The Defence Forces were already employing Microsoft OLAP in various areas of its business, such as in catering (to analyse ration consumption) and in Human Resources (to analyse career progression under the equal employment opportunities legislation).
“For us, our use of Microsoft SQL Server and OLAP has been a fortuitous evolution.
We were impressed at how easy it was to meet our challenge simply using the pre-packaged components that come with the Microsoft SQL Server,” said Mel Birch.
Thanks to SQL Server, the solution turned out to be simpler than the Defence Force had expected. All that had to be done was to extract the raw data from their Microsoft Exchange Servers’ logs and place it in a SQL Server staging file using a pre-packaged SQL Server programme - Microsoft’s Data Transformation Service. The Data Transformation Service was then used again to build the data into an OLAP data cube.
While the cube could then have been directly accessed using the pivot control in Microsoft Exchange, the NZDF used a licence free tool, provided by Microsoft, to access the data cube via a web page.
By using SQL Server 7, a product they were already licensing, the Defence Force are now able to extract data very easily from Microsoft Exchange logs and present this information by Cube in the form of a web page. Moreover, implementing this solution has proven to be very cost effective as well as time efficient.
“We set out to see if we could analyse the data between servers at a fairly high level. We were impressed by how easily we could do this, and more, just by exploiting the features of our SQL Server.”
“Put simply, we have been impressed with how quickly we were able to put together the analysis tool and the price competitiveness was very impressive. Off-the-shelf products we looked at were prohibitively expensive. In contrast, as we already licence SQL server for our enterprise the analysis tool cost us little more than a weeks worth of effort.” said Mel Birch.
The Defence Force now also has the ability to gauge the mail traffic between each of their Exchange servers, and to some extent the nature of business being transacted. The ability to do this is especially important with regard to remote sites with limited bandwidth, and for future planning generally.
“The fact that we can now analyse, in some detail, the nature of our mail-enabled work-flow, will definitely contribute to our ongoing achievement of SLA targets”, said Mel Birch.
Specifications
The Exchange servers are running Exchange 5.5 on Windows NT 4.0
The extraction and load was accomplished using Microsoft SQL Server, specifically Data Transformation Server and MS OLAP Server.
The user access to the OLAP cubes is accomplished using asp web pages running on Microsoft Internet Information Server on a Windows NT 4.0 platform. Connection to the OLAP cube is via MDX calls.
Both the SQL Server and the IIS server are shared by multiple applications.
Typical query response times are less than three seconds.
The database currently holds approximately 1.5 million transactions, which we are able to analyse by:
The available measures are: