Select Medical Corporation

Select Medical Corporation (SMC) manages a national network of specialized, acute healthcare facilities and services including long-term acute care hospitals, inpatient and outpatient rehabilitation, and contract therapy services. In April 2001, Select Medical began a conversion of billing and collection systems, distributing processing across each of Select Medical's five Central Billing Offices regionally located through-out the country. At the same time, Select Medical Corporation management recognized a need to develop a Data Warehouse to "facilitate budgeting, forecasting, financial analysis, and financial trend reporting" from the distributed revenue information.

Avanco analysts researched, documented and received executive approval for a business requirements definition performed for the proposed analytical data environment. Select Medical management, business analysts, and associated vendors were interviewed as part of this process. Avanco assisted in the determination of hardware selection, operating system, database platform, and software as well as the end-user application development environment and analytical tools. The determination was made to support only a web enabled User Interface environment using the Oracle 9i Application Server. Avanco performed all software installation, configuration, and tuning. Based on the user requirements definition document, a data warehouse was designed and built using a relational Dimensional Modeling design (Star Schema). In addition a staging database was designed and built to house staged data in a subject oriented fashion. Avanco assisted in the design of extract routines to retrieve and load required data from the billing systems in an incremental fashion. Data cleansing, transformation, incremental loading, indexing, aggregation and performance routines were all built by the Avanco specialists to manage the data environment. Avanco created an Oracle Discoverer metadata repository supported by the dimensional database and created a web-based reporting environment. Upon deployment, Avanco assisted in the development of a training curriculum for end users to understand and fully utilize the business and technical environment supported by the Data Warehouse and reporting tools. A variety of customized reports were developed by Avanco based on management requirements. Several self-service, parameter driven analytical tools were developed by Avanco to provide intuitive just in time end user reporting.

USA Today - Gannet Corporation

Using PowerBuilder and Oracle7, Avanco designed an information system for USA Today's nationwide customer base that tracks subscriber payments and requests. The system, running on the local RS/6000 UNIX network, communicates with the AS/400 national network and performs interactive, high speed processing and data transfer.

Analyzing corporate information needs and user requirements Avanco designed a single application incorporating advanced GUI design and object oriented techniques. A user-friendly menu and toolbar provide immediate access to all application functions and screens. Metamorphic windows simplify navigation and improved user productivity. Scanning and OCR technologies reduce payment processing and input errors.

Redland/Genstar Corporation

As the largest producer of quarried stone products, concrete, asphalt, and other construction materials in Maryland, Redland/Genstar Corporation has a solid reputation for quality products and services. Redland/Genstar is the largest producer of construction materials in Maryland and one of the oldest stone, blacktop, and concrete companies in the United States. Redland was recently purchased by Lafarge Corporation, the largest stone and quarry materials company in the world. Redland/Genstar wanted to be more proactive in their sales analysis in the face of stiff competition in what is traditionally a low margin business. In 1997 there was only one problem, the traditional corporate method of parsing through enterprise data structures within the corporate accounting system searching for market demand trends from structured reports was time consuming, complicated, and it wasn’t working. In order to maintain and increase market share, Redland/Genstar management knew that they needed more timely access to information and trend analysis. They also needed to put that information in the hands of their sales managers where it could be used to increase sales, improve the business, and lower overhead. Redland turned to Avanco International, Inc. for a solution that they could use to improve their competitive edge.

Avanco introduced and successfully implemented the first Oracle Data Mart Suite implementation world wide in response to Redland’s requirement. Avanco quickly sized up the companies’ information management needs and determined what data assets were locked up in the enterprise IBM based accounting system in Denver, Colorado. Avanco analysts developed a Star Schema database structure that mirrored the company requirements for data access and the business structure of the organization. To help its Sales and Marketing representatives analyze the customer base of construction and pavement companies and to eventually assess the efficiency of its distribution network, Avanco built a Data Mart that extracted data from the ES/9000 and AS/400 systems and integrated it, utilizing Oracle Data Mart Suite for Windows NT. It fell upon the resources of Avanco in concert with Redland personnel to extract data from the corporate database structures, scrub and clean the data to enforce rigorous standards, and load the new Data Mart. Avanco helped Genstar load two year's worth of sales history into the system initially. After loading the data, Avanco set up template reports and trained Redland/Genstar personnel in the use of the Oracle Discoverer ad hoc query and reporting tool. The Data Mart was successfully completed in six months within budget and on time.

Henry Jackson Foundation for the Advancement of Military Medicine

Avanco analysts conducted software design and development for an Information System (IS) designed specifically for medical research. Avanco assisted in designing the data model using the Erwin Design tool and developed reports and forms using Oracle Developer. Analysis of the Oracle database resulted in recommendations to normalize the database, improve table structures, and reduce response time.

Cable and Wireless, Inc.

Avanco converted existing reports from SQL Report Writer 1.1 to Oracle Reports 2.5 and developed new tabular and graphical reports that capture network statistics and present critical performance data including bandwidth saturation levels. Avanco installed Developer/2000 software on VAX VMS and DEC Alpha platforms. Avanco worked closely with CWI to perform final acceptance testing and develop technical documentation.

Fairfax Hospital Neonatal Intensive Care Unit

Using PowerBuilder and ORACLE7, Avanco designed a unified medical and business information system for the Fairfax Neonatal Associates. Avanco designed a single ORACLE7 database incorporating all medical and business data and unifying all information processing and reporting, including diagnosis, procedures, medications, labs, billing, security, outcomes analysis, and statistics.

Avanco's advanced application and database design incorporate the latest OCX, Oracle, and PowerBuilder 5.0 technologies in an intuitive, full function design. Using distributed processing technology, business rules are partitioned from other system domains and all system components - visual and nonvisual - are implemented as objects, allowing the system to evolve rapidly to meet the anticipated information demands of health care reform.

Podiatry Insurance Company of America

Business Problem – Podiatry Insurance Company of America is one of the nation's leading Risk Retention services formed by and for Podiatrists to provide insurance coverage, risk management programs, and consulting support for Podiatrists nationwide. The company operates under a mainframe environment supporting Claims management, liability policies, and general online policy support. Management was interested in policy underwriting and claims trends from a profitability standpoint and the company was unable to support policy and claims trend analysis using daily online systems.

Avanco solution - Avanco developed a Decision Support system for PICA management using the Oracle Data Mart Suite, Oracle Discoverer 3i, and the Sagent ETL tools. Avanco developed a Dimensional database design and corresponding application environment developing table structures, constraints, relationships, and indexes to ensure adequate system performance. The developed application interacts on an ad hoc basis with a corporate Policy and Claims database on Windows NT that is downloaded and loaded nightly from the companies’ online productions system. Business Areas developed included Claims, Liability, and Payment Information, and a Certificate of Insurance that is available to member and the Board. Management can assess claims by customer and date and evaluate the profitability of individuals or practices. The system was well received by management and the work was done within cost constraints and on time.

Rohm Electronics

Rohm Electronics was recently listed as one of the Business Week Magazine's Top 100 technology firms in the world. As the US distributor of electronic components for its’ Japanese Parent company, Rohm Electronics USA sells and distributes literally billions of resistors, transistors, and other electronic components to companies nationwide such as Motorola, Compaq, Dell Computers, and the big three auto makers. The sales staff for Rohm is chartered with a very aggressive sales quota and needs the best possible trend information and pricing available to them to ensure success. Not all that long ago Rohm electronics was unable to give their sales force even accurate pricing information. Understanding customers buying trends was difficult. The transactional system Rohm relied upon for reports and information was geared to getting data into the system and producing standard accounting reports. Trend analysis came from pouring through large paper copy reports and using manual Excel spreadsheet entry from those reports, a time consuming and ineffective method that management found cumbersome at best.

Avanco solution – Avanco performed a three week requirements definition and scoping effort where it was determined that a Windows NT and Intel based architecture would support a solid business decision support environment with the Oracle Data Mart Suite. Avanco developed a star schema design that included a separate table for the latest company product pricing, as well as a central fact table that contained information concerning individual product sales by salesman, by product and product family, by region, state, and customer, by date, and other criteria. Avanco then began the process of cleansing and scrubbing data and loading it into the central Data Mart. Fact tables were developed for direct and indirect sales so that Rohm management could gauge the effectiveness of their channel partners as well as their own sales force. Upon completion of the data load process, recurring load procedures were developed for future daily, weekly, and monthly loads of new information in the system. Lastly, all members of the sales force as well as technical staff were trained on site to use the tools and system provided. Avanco performed a "train the trainer" initial course and installed all software on each direct salesman’s laptop to ensure no problems would occur initially and that the project was well received by the principal beneficiaries, the sales staff. After the initial training, Rohm IT staff trained the other three regional offices. Avanco was tasked to perform on-call support and software upgrades for the first year after deployment of the system on an hourly basis. The project was completed under budget and on schedule.

SATO Travel

Business Problem - Sato travel provides contract travel reservations and arrangements for government agencies and individuals alike for business and personal trips. Sato has a number of contracts with the Department of the Navy and their Navy client is interested in knowing the frequency of travel, costs, current and historical flight information, invoices by airline, and other demographic and cost information. Data is embedded in Navy's legacy systems but is not easily queried trends cannot be easily determined.

Avanco solution - Avanco worked on a team of experts to analyze legacy structures and design the target dimensional data model. Using Oracle 8i and the Sagent ETL tools, data was directly extracted out of the AS/400 mainframe legacy environment and loaded into the oracle 8i database on a large Windows NT host computer. The Data Warehouse uses the Microstrategy DSS Agent 6.0 tool for ad hoc query and reports purposes. Avanco has performed requirements definition, Warehouse design, Warehouse construction of the physical schema and partitioning of the database, extraction, cleansing, and load of the data using the Sagent ETL tool, tuning of the Warehouse database and MicroStrategy queries, and metadata definition and Warehouse standardization procedures. Implementation was a phased approach with final deployment in December 2000.