Continue in 2 seconds

SAND Analytic Server Accelerates Analytics on Traffic and Financial Data for 407 ETR

  • July 01 2004, 1:00am EDT

REVIEWER: Dave Allred, CIO of 407 International Inc.

BACKGROUND: 407 International Inc. owns and operates 407 Express Toll Route (ETR), the world's first all-electronic open-access toll highway, which runs 108 kilometers east-west just north of Toronto. Traffic is monitored by overhead gantries, which query the transponders installed in vehicles as they pass below or photograph vehicles' license plates as they begin and end their trips on the road. Customers are then billed a toll based on distance traveled, time of day, class of vehicle and whether or not the vehicle had a transponder.

PLATFORMS: 4x1.5 GHz IBM e-server with 5GB RAM and 540GB of RAID 5 storage, running Windows 2000 Advanced Server.

PROBLEM SOLVED: When 407 ETR purchased the highway from the Government of Ontario, it inherited a legacy billing and reporting environment with fairly limited functionality. Users were limited to static financial and traffic-related reports and had to either download them to Excel for further analysis or request assistance from IT resources in order to execute ad hoc requests. Also, the warehouse was designed to accommodate only 12 months of data. We required a platform that would accommodate a rolling 25 months of steadily increasing data volumes, allow users direct access to any of that data for ad hoc requests or "drill to detail" analyses, satisfy the reporting requirements imposed by the provincial Ministry of Transportation and be completed within the time frames imposed by other project schedules. The SAND Analytic Server solution was implemented within three months and allows our business analysts to get fast answers to the questions they need to ask, without IT assistance. We are also now able to store a minimum of 25 months of traffic information in less space than the previous data warehouse required for one year.

PRODUCT FUNCTIONALITY: Historical data from 12 tables was loaded and denormalized into a single table of approximately 220 million rows. Daily data for an average of 350,000 trips is loaded from our Trip Rating system, rates are allocated across links and summary tables are created - all in less than four hours. Two years of data is stored: each month, the data from the earliest (25th) month is dropped from the database. Business users can now create their own queries and reports without depending on the systems department. SAND Analytic Server's rapid response makes it possible to execute complex ad hoc queries on the fly. Canned reports are also available for management reporting, with the ability to drill down into details when required. The SAND system is highly adaptable; we have already commenced adding data from our PeopleSoft system to provide an integrated view of the business from a customer perspective. It also provides a solid foundation for planned corporate initiatives such as CRM.

STRENGTHS: SAND Analytic Server is extremely simple to set up and use. No indexes need to be built or maintained. Implementation, loading and query performance are very fast.

WEAKNESSES: Users expected the same level of performance for ad hoc analytics on the full two years of data as had been demonstrated in the initial proof of concept, which used only eight days of data. In order to deliver equivalent performance, SAND had to create summary tables aggregating trip data for each 15-minute period. Performance objectives have been met using the summary table, but users who want to perform ad hoc analytics involving a more precise time element must be directed to a larger table holding the granular data.

SELECTION CRITERIA: Our selection criteria included speed to implementation, ease of use, adaptability, price and fixed-cost contract.

DELIVERABLES: The solution provided a data warehouse maintaining 24 months of traffic and financial data online with daily updates, delivering high-performance ad hoc query capability as well as a number of standard reports, on time and on budget.

VENDOR SUPPORT: A SAND Technology consultant was present throughout the implementation to help set up tables and data loads. A second consultant assisted in creating the required reports and in training. SAND Technology support has been very helpful in responding quickly to requests for assistance.

DOCUMENTATION: The documentation supplied with SAND Analytic Server is complete and clear. The NucleusQuery training guide provides a comprehensive reference manual for the front-end query and reporting tool supplied with the solution.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access