Business Intelligence Application Development using MicroStrategy Business Intelligence platform

Introduction and Background of ACMA

The Automotive Component Manufacturers Association of India (ACMA) has been representing the Indian Auto Component Industry for the last 46 Years. It has a membership of over 500 companies which are major contributors to the total output of the organized sector.

The association’s active involvement in trade promotion, technology upgradation, quality enhancement and collection and dissemination of information has made it a vital catalyst for the progress of this industry. ACMA is represented on a number of panels, committees and councils of the Government of India through which it helps in the formulation of policies pertaining to the Indian automotive industry. For an exchange of information and for cooperation in trade matters, ACMA has signed MoU’s with many overseas counterpart associations.

ACMA is committed to provide leadership and quality “automotive related” information services for the members. The data mart build project covered two areas viz., Sales and Production Quantity relating to analysis – India and China data.

The ETL process was built for their Central Database System using Data Transformation Services (DTS) in SQL SERVER 2000 for loading transaction data as well as history data into a common data warehouse database. For each of the Excel sheet of 47 sheets, OBSI built the OLAP and Non OLAP reports using the MicroStrategy BI platform. A central metadata was built for handling all metric and cube information. Users were given an individual profile for secure access to both canned query usage and building adhoc reports.

Solution

OBSITECH followed the proven best in class DW methodology, which it has developed over time by virtue of its expertise gained in international project delivery in manufacturing data mart applications. The approach for each business area needed to provide immediate user coverage and quick returns on investments in this BI project. We had initially developed prototype project with a data for 3 months and explained a strategic approach and detailed plan to ACMA top management.

Based on the users’ requirements, OBSITECH designed and developed specific functional focused data marts. This was validated with data for 3 month transactions and cross referenced with OLTP reports generated by the users before going in for large scale deployment for the 2 years i.e. 2004 and 2005.

The OBSITECH solution worked on ACMA’s ERP platform together with an end-to-end Business Intelligence Solution to ACMA. With this development, ACMA could significantly reduce time for reporting and decision support tool could support their strategic decision making which in turn reduced their operation cost and improved their ROI/ efficiency in the face of stiff competition in the automotive industry.

Ashok Leyland (AL), the second largest automobile manufacturer in India, has 4 manufacturing units and over 20 warehouses controlling auto parts and vehicle valued at over USD 1 Billion. AL has a highly technology savvy management team comprising production, sales, and marketing, finance and distribution departments. The project covered two areas viz Marketing Finance and Spare Parts.

• Vehicle Turnover Analysis,
• Engine Turnover Analysis,
• Cost Management Initiative.
• Excise Duty Analysis

• Service Order Analysis data mart
• Purchase Order data mart
• MITR (Mutual Interest Through Relationship) data mart
• Part Pricing data mart
• Procurement Planning / Inventory data mart
• Market Planning data mart

The ETL process was built for their Central Database system using Oracle Warehouse Builder 9.2 (OWB) for loading transaction data as well as history data into a common data warehouse database. For each of the data marts listed above OBSITECH built the OLAP and Non OLAP reports using the MicroStrategy BI platform. A central metadata was built for handling all metric and cube information. Users were given an individual profiles for secure access to both canned query usage and building adhoc reports.

OBSI followed the well proven best in class DW methodology, which it has evolved by virtue of its expertise gained from international project implementations in manufacturing data mart applications. The data mart approach for each business area was needed to provide immediate user coverage and quick returns on investments in this BI project. We had initially developed prototype project with a data for 2 months and explained a strategic approach and detailed plan to AL top management.

Based on the users’ requirements, OBSI designed and developed specific functional focused data marts. This was validated with data for 2 month transactions and cross referenced with OLTP reports generated by the users before going in for large scale web deployment. The deployment took place in multiple stages. The OBSITECH solution worked on Leyland’s ERP platform namely ALMARK and ALMAP together with an end-to-end Business Intelligence Solution to Ashok Leyland. With this development Ashok Leyland could significantly reduce time for reporting and decision support tool could support their strategic decision making which in turn reduced their operation cost and improve their ROI / efficiency in the face of stiff competition in the automotive industry.

River Rock Casino (RRC) started their business in June 2004. River Rock features massive 70,000 sq.ft. casino, state-of-the-art 1,000 slot machines, 70 table games, Canada’s largest Poker Room.

The purpose of this project was to design and implement an Operational Data Source (ODS) from two different data sources which involved extraction of required data, data integration to create linkage between them and enabling cross functional reporting and analysis, specifically with reference to identification of bus customers. Additionally, the client desired to analyze

• Primary and secondary markets – based on zip codes and coin in totals
• Trends by looking at various metrics on a time period
• Promotion effectiveness
• Analyze incomplete customer

OBSITECH Technologies were asked to perform the following as part of this engagement

• Design and implementation of business-defined extract, transform and load process
• Design and implementation of a dimensionally –modeled ODS.

Solution

We worked with the client to perform a full life-cycle of Operational Data Store that included design, implementation, deployment and testing of an ad-hoc data analysis infrastructure utilizing existing Microsoft technology. The Operational Data store solution comprised of Microsoft SQL Server 2005, Microsoft SQL Server Analysis Services 2005 (SSAS), Microsoft SQL Server Integration Services 2005 & Microsoft Reporting Services 2005 (SSRS) for analysis.

SSIS is designed to make it easier for organizations to automate the transition of data into the database from disparate sources. OLAP is a well-known analytics technology that speeds the report building and generation process, but is typically only available as an add-on database feature Benefit

The SSRS features have been honed up to create comprehensive solutions for managing, creating and delivering traditional and paper oriented reports or interactive, web based reports.

With SQL Server 2005 Business Intelligence toolset, OBSITECH could build Operational Data Store to meet the requirements of the customer.

Future Group is one of India’s leading business houses with multiple businesses spanning across the consumption space. While retail forms the core business activity of Future Group, group subsidiaries are present in consumer finance, capital, insurance, leisure and entertainment, brand development, retail real estate development, retail media and logistics.

Led by its flagship enterprise, Pantaloon Retail, the group operates over 12 million square feet of retail space in 71 cities and towns and 65 rural locations across India. Headquartered in Mumbai (Bombay), Pantaloon Retail employs around 30,000 people and is listed on the Indian stock exchanges.

They had already implemented Business Intelligence solution. With ever growing volume of business all over the country, their existing tools were not effective in generating reports quickly. This apart, they have issues relating to data loading as the ETL process consumed more time. The report generation was a time consuming process and the quality of the reports were also far from satisfactory.

With rich expertise gained in the implementation of proven Data warehousing projects over a period of time, SUN Microsystems and OBSITECH have studied their existing system and reporting requirement that covers more than 65 reports and suggested alternate BI system in place.

OBSI followed the data mart approach for each business area that was needed to provide immediate user coverage and quick returns on investments in this BI project. We had initially developed prototype project with a data for 2 months and explained a strategic approach and detailed plan to Future Group’s top management.

Based on the users’ requirements, OBSITECH designed and developed specific functional focused data marts. This was validated with data for 2 months transactions and cross referenced with current reports generated by the users by the existing systems.

There were issues relating to decision for selecting hardware requirements. The reports generated by their existing tool and new tool recommended by us were not matching and that necessitated change of logic for derived measures. The reporting requirements were complex and needed more time than assessed at the start of the project.

GOL, a leading automotive component distribution company managing auto spares dealers and distributors in India, has about 30 branches spanning West and South India with headquarters in Chennai. GOL deals with distribution of those auto components which are mostly OEM and conform to the highest quality standards. Today, it holds agency lines of 18 top manufacturer brands. Further, in order to improve the level of service to its customers- fleet operators, garage owners and the traders, the company has continuously been expanding its branch network.

GOL has been using tally software for its billing and inventory accounting at all its branch operations. The data is being transferred to their Head office via a physical medium. They required corporate data mart house to electronically transfer and consolidate the branch information in order to support GOL top management. This they wanted for maximizing the operational efficiency and stay ahead of competition in terms of customer services and support. Based on their requirements we designed Data Mart for transferring XML data from their tally systems at branches to consolidate into a sales data store on a relational database at Head office. OBSITECH collected the reporting requirements from the users. Considering the cost of operations and current size of the database, we built a data mart using the open source technologies for meeting the management needs for analytical reports at their Head office.

The Tally Data from different branches were exported into XML format and then using a FTP process transferred to the Central Raw data store. Later it was imported into a Report Database through the interface developed in Java –Python. The report DB resided in a Firebird Database. The analytical reports were generated using the Jasper BI tool interface and also using a Report Query Interface.

GOL were taking much time to consolidate the reports sent by their branches and submit to the top management. In the process, the reports submitted by the branches were not uniform in terms of data quality. Now, after implementation, they could save much time in consolidating the reports for submission to their top management to facilitate quick business decision.