Job Title: Mobile Phone Pricing Plans Database
Company: Optus (Australia)
Duration: 1 Year
Role: Sole Database Analyst/Developer
Software Skills used: SQL Server 2005 with XML, Excel 2007, Excel/XML Schema integration using XSD, XML, ADO
This application allows for the creation of Pricing Plans for mobile Service Providers (SP). These plans start off as offers, then once they have been agreed with SPs they are submitted to other systems within Optus for configuration. The system interfaces with Excel forms for input, and the data is stored as XML in SQL Server. XML Schemas (XSD files) were created and then mapped to Excel so that Excel could be used as a front end for editing the data. The schemas are also used for the validation of the data prior to saving. The SQL Server database uses XPath for querying, returning normalised views of the XML data. This multi-user, role-based secured database stores not only the data from the input sheets, but the master Excel templates as well so as to enforce version control. The Excel input forms are secured and use validation lists and VBA to control data entry and enforce data integrity. The system produces Statements Of Work (SOW) for submission to other departments for plan configuration, and interfaces allow for revenue assurance to be confirmed by comparing the finalised plan to the configured plan in other databases and reporting any errors. Due to the dynamic nature of the market, the structure of the mobile plans could change at any time. By using Excel input forms and storing the data as XML these dynamic changes could be incorporated into the system with minimal work and change management. Prior to my involvement the data was stored in hundreds of spreadsheets and the SOW were filled in manually which led to errors and billing disputes. The result of the project was that all of the plans could be stored in one centralised database, the SOW were automated, and revenue assurance could be provided.
Many office workers and managers spend their days on repetitive tasks, trying to make sense of their workload and attempting to glean some information about their business from seemingly meaningless data. This is usually due to a lack of software systems, or poorly developed ones. With over 20 years of experience, my goal is to provide integrated systems that automate manual repetitive tasks and make sense of complicated data. This frees up people so that they can apply intelligence to their job...