July 1, 2016

Case Studies

 

Plant Floor Data Collection

Industry: Manufacturing – Global company with multiple plants.

Business Challenge: Client wanted the ability to analyze data from plant floor equipment in order to perform continuous improvement of the manufacturing process.Client also wanted ability to perform intra-plant comparison.

Solution: Metric-X developed a Data Warehouse for collecting, storing and analyzing the plant data. The Data Warehouse contains data about raw material, equipment settings (e.g. temperature, current, gas pressure etc.), and the product characteristics. Data is collected from several factories each hour, SPC charts are updated, and alerts are generated.

Benefits: Client has a gold mine of valuable data for performing continuous improvement, conducting research, and enabling employees to make operational decisions.

Approach: Data Warehouse was designed using the Kimball Method.

Tools:

  • Database: SQL Server 2005
  • ETL: SQL Server Integration Services
  • Cubes: SQL Server Analysis Services
  • Reporting Front End: SQL Server Reporting Services, Excel, ProClarity

Ledger Transaction Analytics

Industry: Manufacturing

Business Challenge: The monthly financial close process was time consuming and labor intensive. Much energy was expended on compiling the data and reporting it. Client wanted to simplify the process and enable the Finance team to focus on detecting deviations between the budget and actual.

Solution: Metric-X developed an analysis cube containing all the ledger transactions extracted from the ERP system (Microsoft Dynamics AX). The cube was refreshed every two hours, and the data could be sliced and diced conveniently (dimensions included Department, Account, Currency, Item, Time etc.).

Benefits: The Finance team could focus on actual vs. budget analysis and make recommendations for continuous improvement. Eliminated a significant amount of time that was spent collating and cleansing numbers in spreadsheets.

Approach: A proof of concept was initially created. After the hands-on assessment by the Finance department, a waterfall process was applied for defining the requirements, designing the data model, and developing and testing the solution.

Tools:

  • Source System: Dynamics AX (ERP System) and spreadsheets
  • Database: SQL Server 2005
  • ETL: Timextender and SSIS
  • Cube: SQL Server Analysis Services
  • Front End: Excel, ProClarity, SQL Server Reporting Services

Real-Time Dashboard

Industry: Banking

Business Challenge: A bank advertised that it could process loan applications within 3 days. Each loan application goes through around 20 processing steps. The bank needed a system to track the volume and flow of applications through the process.

Solution: Metric-X designed a “real-time dashboard” that displayed how many applications were at each step of the loan evaluation process. The solution also issued alerts if the process experienced any delays.

Approach: The solution was based on the real-time dashboard technology of Celequest. Metric-X worked as a technology partner of Celequest. Celequest was subsequently acquired by Cognos.

Production Allocation and Logistics Planning

Industry: Manufacturing (Business to Business)

Business Challenge: Client was using an Excel and email based process to determine the quantity of finished goods to ship to various customers. The process could not scale and errors were costly.

Solution: Metric-X designed and developed a web application that enabled employees from each department to collaborate in the forecasting, allocation and logistics process.

Benefits: Information about the production plan, production allocation and logistics was available in a central system. Collaboration was improved, processing time was reduced, and errors were eliminated.

Approach: Metric-X designed and developed a web application based on the .NET 3.5 Framework. Our Project Manager and Systems Analyst were based in the U.S., client team members were based in the U.S. and Europe. Software development was done in India, and then deployed at the client data center in the United States.

Tools:

  • .NET Framework 3.5
  • ETL: SQL Server Integration Services
  • C#
  • SQL Server Reporting Services

Sales and Operations Planning

Industry: Manufacturing

Business Challenge: It was not easy to determine the gap between the supply and demand for a product in any given month. This made it difficult to make commitments to customers and determine if the future factory capacity was going to be adequate.

The data required to compare aggregate supply and demand was maintained in multiple systems and spreadsheets, and required much cutting/pasting before decisions could be made with the data.

Solution: Continued using Excel for managing the data, however made significant improvements to the process and spreadsheets. Reduced the number of sales spreadsheets to one. Enabled the spreadsheet data to be saved in databases. Built powerful modeling capability enabling the client team to be able to look at multiple levels of detail and multiple perspectives without duplicating data in other spreadsheets.

Benefits: The required supply and demand data is readily available. Data can be analyzed at various levels of detail. The models are flexible and can be adapted as the business processes and data requirements change.

Approach: Applied a highly collaborative process to develop the solutions. Worked closely with sales and supply chain personnel to develop spreadsheet based models that suited their way of entering and analyzing data.

Iterated the Excel models gradually to develop the desired solution.

Tools:

  • Excel 2007
  • SQL Server 2005

Rebate Processing

Industry: Manufacturing

Business Challenge: To respond to market dynamics, the client decided to offer rebates to its customers. Complex business logic needed to be applied to calculate the correct rebate amount. Furthermore, the rebates needed to be reflected accurately in the invoices and credit notes in the ERP system.

Solution: A web application containing a rules manager was developed. The application contained three main subsystems: Claims Processing, Rules Engine and ERP Interface. Validations were built in to ensure that the correct rebate amounts were applied to each rebate claim.

Benefits: The rebate rules could be maintained in a single application and new rules could be defined and tested conveniently. There was a high level of confidence that the rebates were being applied correctly.

Approach: We conducted a buy-vs-build analysis for a rules engine, and decided to proceed with a custom built solution. Developed the claims processing and rules engine as a .NET application. Initially created a pilot using Excel and cutover to a web application as requirements were better defined.

The rules engine involved fairly complex logic and a creative user interface.

Tools:

  • NET Framework 3.5
  • C#
  • SQL Server 2005

Mobile Application for Parking Violations

Industry: Real Estate / Property Management

Business Challenge: Client manages apartment communities and wanted to reduce the parking violations. Residents, guests and others who park in the wrong space cause inconvenience for other residents. Citing a vehicle for a parking violation required an employee to write down the license plate and other information, and then go to the office to type up the incident. This was time consuming, had to be done during the hours the office was open, and was a distraction to the employee who had other things to attend to (such as unlocking a resident’s door, fixing the plumbing or mowing the lawn).

Solution: Metric-X developed a mobile application for writing up the parking violation. Now, the employees of the apartment community can simply photograph the vehicle with their smart phones, enter the license plate number and other details into the phone application, and push the information to the central system.

Benefits: Parking violations have been significantly reduced because the management can respond within minutes. This leads to a higher satisfaction among residents who can be confident their parking spot will be empty when they return from work or running errands.

Approach: The parking application was developed in MVC on the .NET Framework 4.0. Using Phone Gap, the Android and iOS versions were easily generated and optimized for the mobile browsers. A .NET interface was also developed to the Rent Manager property management software (www.rentmanager.com) for pulling in the required tenant and property information. The information obtained from Rent Manager is persisted in a SQL Server database.

Tools:
SQL Server
.NET Framework 4.0 (MVC)
Phone Gap

Business Intelligence Strategy

Industry: Manufacturing / Tier 1 Automotive Supplier

Business Challenge: Key metrics were difficult to compile. Different business units, departments and plants recorded data in different ways. Compiling common metrics for financials, manufacturing and HR involved a lot of manual transformation. Multiple implementations were under way.

Solution: Metric-X worked with the client’s business and IT teams to define a roadmap for defining and capturing key metrics in a uniform manner. The roadmap called for developing a data warehouse in phases. Also delivered were a high level data architecture, and an implementation plan that included an organization structure, projects, job descriptions and effort estimates.

Benefits: The client’s IT department was able present a plan and business case to the business for delivering key metrics for the corporate performance management process.

Approach: The strategy definition process involved gathering information through interviews and reviewing existing reports and data. The key metrics were defined, source systems were identified, common dimensions were established, and gaps identified between current and desired state.

Small Business IT Management

Industry: Healthcare

Business Challenge: A growing small business with a staff of approximately 200 employees did not have a coordinated process for managing IT assets such as PCs, servers, routers and printers. They also did not have clear policies for file storage, information security, password management or email.

An assessment revealed that the company was losing productivity due to the way the current setup had involved.

Solution: Metric-X was engaged to streamline the management of IT assets. Users were identified, assets were tagged, information security guidelines were defined, software versions were upgraded where needed and other measures taken to protect information and reduce downtime.

Benefits: Less downtime. Better information protection. More effective use of IT assets.

Approach: Utilized tools to inventory the PCs and software licenses.

Small Business CIO

Industry: Manufacturing

Business Challenge: A growing small business (< $20 million in sales) wanted to leverage technology intelligently but did not want to hire a full-time CIO. They wanted to put in place systems to enable them to earn and maintain key industry certifications (SAE AS9100B, ISO-9001:2000 and MIL-I-45208A).

Solution: Metric-X was engaged as their part-time CIO. We helped define key business processes, developed technology architecture, helped create their internal IT department, select vendors for specialized tasks, and conducted periodic reviews of their IT capability.

Benefits: Client sees Metric-X as an extension of their team. They are able call on our expertise as needed without having to invest time training us on their business processes. They also don’t have to carry expense.

Approach: This has been a highly collaborative process based on trust. It required the Metric-X team to learn to walk in the client’s shoes and propose solutions that were scalable and practical to implement.