V/Line’s Executive HR Dashboard proof -of- concept developed by Oakton, powered by Microsoft SQL Server 2008

Background

On 1st October 2003, a new Franchise Agreement came into effect between V/Line and the Director of Public Transport to operate regional rail and rail replacement coach services throughout Victoria. The commencement of the new Franchise Agreement was simultaneous with the change in shareholding for V/Line to now having State Government as its sole shareholder via a statutory corporation, V/Line Passenger Corporation.

The Challenge

V/Line had recognised the requirement for a solution to facilitate advanced reporting capabilities with greater flexibility and efficiency that would satisfy the requirements of both the Finance and HR Departments. V/Line is currently implementing Microsoft Dynamics AX Financial and HR modules. They are also running Microsoft Dynamics GP together with a number of administrative systems for payroll management, point of sale transactions, and the management of train schedules.

V/Line’s finance department uses FRx for their report development. However, the HR department is limited to using the out of the box reports available in the Dynamics AX HR module. This makes the reporting process cumbersome and restricts the HR department’s reporting capabilities. HR and non-financial reporting is done using Reporting Services with data being consumed directly from the operational databases.

A number of management reports are compiled manually in Excel with the data being manually extracted from various production systems. For example, a monthly summary report required by management can take 3 days to prepare.

V/Line would like to move towards a more robust reporting solution. The solutions required would provide reliable and timely information with easier access to the data required by the HR department. They would like to have a data warehouse that sources its data from the operational databases. This would allow them to report on data sourced from different areas of the business. VLine would also like their power users to be able to run ad hoc queries, without touching operational databases.

The Approach

Oakton proposed using SQL Server 2008 to build a proof of concept that would demonstrate the type of solution that could be built using the Microsoft SQL Server Business Intelligence toolkit. The HR Executive Summary Report was used for the proof of concept because it is currently prepared manually. For the proof of concept the database consists of some key employee metrics like employee age and length of service as well as key reference data like department, location and employee status. The data is presented visually in a dashboard and the user can drilldown by department, geography and labour code. The user can select any of the indicators on the main dashboard and drill down to a detailed comparative report.

Most of the data is sourced from the Microsoft Dynamics AX database and the Excel spreadsheets are used to provide some head count target information. The data warehouse is implemented as a SQL Server database consisting of a number of fact and dimensional tables.

The Solution

For the Proof of Concept, Oakton developed the capability to deliver Executive Dashboards to the HR department, as this was one of the HR department’s main pain points.

The HR Dashboard uses SQL Server Analysis Services to create the OLAP cubes. SQL Server Reporting Services is used to provide a standard dashboard report and Excel 2007 is used to provide ad hoc query capabilities for the power users.

To bring data in from the different sources and apply any transformations that are required we used SQL Server Integration Services as the ETL tool.

When the application is implemented, V/Line anticipates using SharePoint Services to facilitate easy distribution of the reports, including the ability for users to subscribe to specific reports.

The Oakton advantage

Oakton provides specialist end-to-end IT services to plan, deliver and operate business systems, as well as business consulting, financial management and assurance services. For nearly 20 years, Oakton has consistently enabled some of Australia’s largest organisations to meet their technology and business needs with a flexible, cost-effective, results-driven approach to service delivery. We employ over 1300 staff, and have offices in Melbourne, Sydney, Canberra, Brisbane and Hyderabad (India), and offer a range of services across the lifecycle of systems and across a range of different technology and industry sectors

Oakton has a strong partner network with leading global and local product companies — this supports a sharp focus on the provision of services that support the successful delivery of business system solutions for customers. However, we do not develop and sell products, nor do we sell hardware or network solutions.

Oakton’s service provision combines highly skilled resources, relevant and proven service delivery methods and frameworks, and a strong focus on project delivery governance.

Oakton’s enviable history of achievement for large and medium sized organisations in the private and public sectors is reflected by the testimony of customers to the quality of our service delivery and results.