Bryan Hogan | Elance
Last Sign-in: Apr 2, 2013

Bryan Hogan

SQL Server/MS Access/MS Excel Analyst/Developer
   Czech Republic
  |   Prague, Prague
  |  9:51 am Local Time


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 jobs instead of constantly 'fire-fighting' their inbox. It also makes their days more enjoyable, and therefore more productive!
Service Description
My speciality is developing database solutions using MS SQL Server or MS Access that are integrated with Office products such as MS Excel, MS Word and MS Outlook to provide a complete solution. Workflow management, document management, data analysis, all of these are 'parts of the picture' which are included in final solutions.

I also like to think outside of the box. My most recent major project was for Optus, one of the major telecommunications companies in Australia and SE Asia. They needed a database and workflow for creating pricing plans for mobile phone service providers. This market is highly volatile, and therefore new facets are continually being added to plans. Overnight, a feature such as accessing Facebook from your smart phone can become a must have for customers. In a standard normalised database, requirements such as adding new fields can add to changes to tables, stored procedures and views – even a simple change such as one new field, when put through a change management process, could take a week or more to implement. Instead, I suggested a design where the pricing plan is stored as an XML document in the database. That way, a new field – indeed, an entire new section – can be added to the XML document without a single change to the database structure. By validating the XML against an XML schema, business rules can still be strictly enforced so that data integrity can be verified. By using the XML capabilities of SQL Server the data can still be viewed as normalised data, plus it can be modified with ease. Storing the data as XML also allowed MS Excel to be used as a front end so that users can view and modify the data in a spreadsheet, allowing the project to re-use the existing templates with data validation. This saved having to use another tool such as Access or VB.Net to create the input forms from scratch, dramatically reducing the development time – and budget!

I have a proven track record delivering solutions where others have failed. For one organisation, a previous project to deliver a document management system to support the key business processes had failed. An interim solution which was only intended to last for 3 months, but which had been in place for over a year was falling apart at the seams - the database was regularly being corrupted. By talking to all of the stakeholders, producing a clear specification outlining all of the business processes and talking to users at all levels from management to the temp staff doing the data entry I was able to build a system that met all of the requirements where others had failed.

I have also been developing remotely for nearly a decade now. In 2009 I had the rare privilege of meeting my contacts at Westpac in Australia to discuss the latest set of requirements for a project that I had commenced working on in 2002 in Dublin, Ireland. I remember sitting in an internet café in Prague in 2003 using Go To My PC to run a program on a computer in Melbourne… we live in an amazing world!
South Australian College of Advanced Education
Bachelor of Education - Early Childhood Education
- 1982
SA School of Music and Audio Education
Certificate of Sound Engineering
- 1982
Adelaide College of the Arts and Education
Diploma of Teaching - Early Childhood Education
- 1979
JCN Systems Pty Ltd (Australia)
Freelance Programmer
2011 - Present
JCN Systems, is an IT Consultancy/Solutions provider based in Sydney/Bowral NSW Australia. I have provided solutions for several of JCN's clients, including designing and building a SQL Server database plus VB.Net application to load XML data received from gaming machines into a normalised database for analysis and designing and building an Access database integrated with Excel forms for data collection, enabling ALCO Pumps to distribute, receive and process inspection forms for pump inspections at client sites.
Optus Telecommunications (Australia)
Database Analyst/Programmer
2009 - 2010
Software Skills used: SQL Server 2005 with XML, Excel 2007, Excel/XML Schema integration using XSD, XML, ADO. I developed an application for the marketing team to create tariffs for clients using Excel. The data from the spreadsheets was mapped to XML schemas so the data could be stored as XML in a SQL Server database. The Excel spreadsheets act as a front end to the database as all data is stored in the database, not in the files. XML Queries allow users to search all tariffs. ADO is used to transmit data to the backend. The database integrates with other Optus systems based on the workflow so that once a tariff has been agreed with a client, the data can be submitted to the relevant systems/teams for building. Interfaces also allow for revenue assurance by comparing configured plans to the original tariffs. Reports are produced in Excel and PDFs. Role based security allows for users from Marketing, Ops and Admin to perform various tasks related to their role.
Australian Government, Regional Development
2009 - 2009
Software Skills used: SQL Server 2005/2008, Access/Word/Excel 2007, Word/Excel/XML Schema integration using XSD, XML, ADO/DAO, Team Foundation. Worked in a team of four to assist in the development of the Grants Management System for the Regional Development team in the Dept of Infrastructure, Transport, Regional Development and Local Government. Backend database was SQL Server, front end was Access 2007 using linked tables. Created reports in Word and Excel using XML data exported from stored procedures in the backend which was then imported into documents and workbooks with attached XML Schemas (XSD files). The legacy database was normalised and referential integrity was introduced where appropriate.
Thinkronicity Pty Ltd (Australia)
2008 - 2009
Software Skills used: XML/XSLT/VB.Net. As part of a project delivering reports for the New Zealand Transport Agency I developed documentation, test cases, test scripts and XSLT transformation scripts.
Westpac Bank (Australia)
2002 - 2009
Software Skills used: Access 97/XP, Excel XP, MS Chart, Adobe Acrobat PDF, VB.NET. This project commenced initially in April of 2002. Acting as an Analyst Programmer for Thinkronicity Pty Ltd (Australia) I developed a database to manage Mystery Shopper Surveys for I-View, a market research company who performed Mystery Shopping for Westpac. The initial database was released around October 2002. After that time the database was supported and enhanced through several major changes. As time went on Thinkronicity basically became an out-source for I-View so that they could send the raw data which was then processed through the database after which reports were issued. Around 2007 I-View ended their relationship with Westpac, after which Thinkronicity dealt directly with Westpac to process the data and issue the reports. In 2010 Westpac decided to deal with the reporting in-house, and so the project ended. This project involved the creation of a database for managing the results of mystery shopper surveys for Westpac. The database was used to compile survey results, calculate statistical data and produce a series of reports for distribution using Adobe Acrobat plus Excel extracts for further data analysis. The raw data was received in a flatfile format which needed to be normalised prior to processing. A significant part of the reports involved the presentation of data using graphs from MS Chart, including automation and coding to manipulate the charts graphically. VB.NET and Excel were used to create a tool for validating the flatfile data according to specified business rules prior to importing it into the database. This project was managed by THINKronicity Pty Ltd in Melbourne Australia, and the project was completed remotely in Europe by me working as a sole A/P utilising telecommuting processes including using the internet for file transfers. As a developer for THINKronicity I still handled the production of these reports on a quarterly basis using the files provided by Westpac plus occasional enhancements to the existing application up until the termination of the project in December 2009.
Post-Graduate Medical Education and Training Board
2006 - 2008
Software Skills used: SQL Server 2000/2005, Access, Excel, Word, Outlook, (all 2003), XML, ActiveX controls, SSIS, Outlook Redemption, Postcode Lookup software, SourceSafe, testing in Office 2007. The Post-Graduate Medical Education and Training Board (formerly PMETB, now part of the General Medical Council is responsible for processing applications for the registration of doctors in the UK. A Document Management system was intended to support this process, but the project failed to deliver. The requirement was to design, build and implement a solution that stored all of the applicants’ details and also handled the application workflow. The solution was a SQL Server database containing over 75 tables, 150+ stored procedures, 160+ user defined functions and secured access for 8 varying roles. Access was used to create a Data Access Layer (DAL) handling all of the communications between the presentation layer (also in Access) and the database. The front end is disconnected, all data retrieved from the database is stored in locally cached XML files and data updates are made via calls from the DAL to stored procedures. Excel is used for ad-hoc reporting and data analysis, Word is used to create documents and Outlook Redemption is used for emails. The user base is 25+ users. The database was migrated from SQL Server 2000 to 2005 and the front end was tested in Office 2007. I was the sole Analyst/Programmer for this role. I also assisted in the transformation of data collated from the National Surveys of Trainee Doctors and Trainers for the purposes of data analysis. The data from the surveys was collated and summarised using SSIS and Transact-SQL Scripts so that the web-site reporting tools could show statistical analysis of the results in tabular and chart forms. The website can be found at:
Outdoor Media Association (Ireland)
2004 - 2008
Software Skills used: Access 2000/XP, Access Snapshot Reports, Excel Pivot Tables. The OMA provides reports to its member companies in relation to revenue generated from advertising. A distributed database was created for member companies to enter their revenue and submit the data for compilation in a central database. Due to varying installation environments the application was based in Access 2000 but coded to run in 2000/XP utilising XP functionality where possible. Interfaces were created for data transfers. A Data Warehouse was created so that users could create Excel Pivot Tables/Charts for OLAP analysis of the data by Company, Region, Period, Advertiser, Brand, Agency and Category. Access Snapshot Viewer was used for distributing reports. An Excel Pivot Tables/Charts data analysis tool was created for the member companies. The data from a legacy application was migrated to the new application. I was the sole A/P on this project and all work was completed remotely. The initial project was completed around April 2004, after which I provided support and several enhancements.
Electricity Supply Board (Ireland)
2003 - 2005
Software Skills used: Access 97/2000/XP, Excel 97/XP, Oracle PL/SQL, Word XP, Outlook XP, XML 3.0. During this time I actually worked both full-time and part-time during 3 different periods. Jan 2003 - May 2003: This project involved the creation of a database to store data relating to the Special Load Readings on lines in power stations on various dates throughout the year. Data was supplied from an Oracle SCADA database using ODBC, PL/SQL and SQL Pass Through queries, as well as through manual input by the user. Reports for data analysis were produced in Excel using templates and VBA code to export the data and format it to a presentation standard. A great deal of mathematical computations was required, including trigonometrical functions. Assistance was also provided to the team responsible for converting Access 97 databases to XP, including building a tool to prepare Access 97 databases for conversion. I was the sole A/P on this project. Sep 2003 - May 2005: Several part-time projects were undertaken for ESB as a sole A/P. The previously created Special Load Readings database was upgraded to Access XP and further data analysis capabilities including Excel pivot tables were added. A new project was undertaken for the Archives section to migrate data from existing databases to newly created Access XP databases. These databases store data relating to the artefacts and documents stored in the Archives section. Query screens allow users to specify various criteria in order to return lists of matching data. The ability to add, edit and delete data was also added. Another project for Electricaid, a charity organisation within ESB, involved a database to store data related to applications for funding. This allows the applications to be tracked through the application workflow process - data gathering, evaluation, funding and payments. This database has a querying tool, the ability to add, edit and delete data, the ability to store hyperlinks to related documents and the ability to create standard letters by merging data with Word templates. May 2005 - Nov 2005: ESB PowerGen implemented Henwood's EnerPrise software (a third party system) for operational scheduling. As a member of the development team my role was to build interfaces and additional systems to support the implementation. The interfaces used XML files to transfer data between the Henwood system and existing Excel spreadsheets. This involved adding structure and data validation to existing Excel files and creating VBA code to facilitate the import/export of data in XML and CSV formats. This resulted in the creation of Excel Add-ins used by other developers in the project team. An Access XP database was designed and developed to track power station availability. The database contained records of all station outages (planned and forced), max plant capacity, min loads, forced and ambient de-rates. Complex code was developed to cross-validate outages against loads/de-rates for all half hourly periods within outages. A screen was developed to display the full availability for all power stations in Ireland at any given point in time. XML was used to for data transfers to other systems. VBA was used to output data to Excel for intranet reporting. Outlook was used for automated distribution of reports. I also participated in peer reviews of code.
Abbey College (Ireland)
2003 - 2005
Software Skills used: Access XP, Word XP, Excel XP, ActiveX Controls. Abbey College provided courses to Irish and foreign students, particularly in the teaching of English to foreign students. This project involved the creation of a database to store data pertaining to students, courses, teachers & facilities at the school. It allowed the entry and tracking of students’ attendance, the reporting of this data and the merging of data with Word templates and Excel spreadsheets to create reports and mail merges. This is a multi-user database with full Access security. The application also allows tracking of Staff Holidays. I was the sole A/P and all work was completed remotely. The initial project was completed around September 2003, after which I provided support plus several enhancements.
Various IT Roles
1990 - 2001
Software Skills used: Access 2.0/97, MS SQL Server 7.0, Excel 3.0-97, Word 6.0/97, Outlook 97, ODBC, Siebel. I completed a number of IT assignments for companies including: - AXA Investment Managers / Apr 2001 - Aug 2001 / Siebel Data Migration - Anglo Irish Bank / Feb 2000 - Dec 2000 / Analyst/Programmer - Merrill Lynch Mercury Asset Management / Jul 1999 - Sep 1999 / Small Business Solutions Developer - Prudential Assurance, UK / Aug 1997 - Apr 1999 / Y2K Development Team Leader - Chase Manhattan NA Bank (Aust) / Sep 1996 - Apr 1997 / Conversion of Lotus Gold FX Portfolio Model to Excel Further details are available on request.
Various Roles
1980 - 1989
During this time I worked in various roles including: - 1982 - 1989 / Teacher / SA Education Dept & Inner London Education Authority - 1980 - 1982 / Office Manager / Adelaide College of the Arts and Education Student Union
Payment Terms
None specified
Bryan Hogan | Elance

Bryan Hogan