Learning Management System BI reports to defend warranty claim

In Resume
Project	Learning Management System BI reports to defend warranty claim
Customer	Chrysler LLC & FIAT
Period	March 2012 - till date
Description	Chrysler Learning Management System (LMS) is getting aligned with FIAT LMS. Chrysler has their own LMS and a complex BI environment to support warranty claims. As other automotive industry their warranty policy is depending on the training status of dealerships. As SABA, a new LMS application joins this two major enterprise learning system, the BI environment also needs to be converted or simply restructured. Existing system has 600 Business Objects reports for Chrysler and 250 reports for FIAT. They need to combine and shorten the number. There is a big demand of historical data at least 25 years from present. SABA application is only holding transactional data but not the historical data. TCS needs to build a warehouse and keep all the historical data and also needs to keep history from time of new application launch. Old environment was comprised of Lattitude as learning application, SQL server 2008 as database, Microsoft BI as ETL, Business Objects as reporting tool. In new environment SABA is learning application, DB2 as Database, DataStage as ETL and Cognos as reporting tool.
Role	Business objects Analyst and Business Intelligence Analyst
Solution Environment	Windows Server 2003, XP, 7
Tools	Business Objects XI R3, SQL Server 2008, DB2, Cognos 10.2
Highlights	As a single onsite coordinator my primary task is 1.       Understanding process changes from old system to new system 2.       Analysis of existing BO reports. 3.       Requirement gathering for new reports and dashboard. 4.       Creation of BRD and get a sign off. 5.       Designing the data warehouse with help of Data Architect. 6.       Designing DataMart with data modeler for reporting. 7.       Coordinating with Cognos developer and helping them to convert the existing BO functionality in Cognos. 8.       Giving input to Cognos developer to create framework for the report. 9.       Testing of the data. 10.    As the project is still in development phase my current work is maintaining coordination between offshore developer and business.

                I moved to Auburn Hills, Michigan to join a project called Learning Management system as a Business Intelligence analyst. After merger of FIAT and Chrysler this project was the first try to merge the applications in FIAT and Chrysler. Chrysler and FIAT both had different learning management system and they want to merge both and implement a common functionality. SABA, the largest learning management system company is assigned for that, the same company created our Learning Management system in TCS and I was pretty aware of the functionality what they have.

                Chrysler had their own LMS and they had more than 3500 reports to track the learning system. That sounds weird! I also thought so and that’s why, they brought me in. Old reporting system was in BOXIR3 and new reporting system will be in DB2. My job was to analyze the Business Objects reports and gather the changed business requirement and explain to Cognos developers.

                I thought it was simple as no one can think of a complex learning management system before start working in automotive industry. LMS is directly tagged here with warranty and dealers don’t get their warranty money if they don’t complete their training. That defines the criticality and complexity in Chrysler LMS.

                I started gathering automotive industry knowledge. Majorly supply chain and dealer processing networks. The more I was diving deep, the more I found that many other systems are interlinked with training software. Reports which I started to analysis unfolded the mystery that the reporting system was not only providing learning details, but track sales volume and customer satisfaction index. Dealership employee’s evaluation has been done on basis of several parameters which are not directly coming from learning software.

                I started running through all automotive glossaries and following lots of automotive websites to get inner picture of automotive industry which was helpful to analyze the whole structure. While understanding 800 objects in present multiple universes, internet and the old documents helped me a lot. Below are the steps which I did apart of reading to analyze the system:

1.       Find out number of universe present.

2.       Sorted out reports by universe.

3.       Extracted all objects list in the universe.

4.       Remove duplicated objects and created a list of definition for all the objects.

5.       Enhanced the document with all the formula used in the universe.

6.       Understanding the universe and the joins using the existing data model.

7.       Create a list of critical reports.

8.       Create a document of report analysis where used objects are listed.

Once the above steps were done I started learning existing SABA data model and help data modeler to create the model using business rules extracted from reports and universe.

                While doing modeling I came to understand that the similar business knowledge I need to share again and again with different set of developer who doesn’t know about the functional side of the project. But I believe that doing development blindly will create lots of unnecessary error which can be avoided. So I created a 50+ page document which is a complete functional understanding document with addition of technical stuff which we already analyzed. This document is widely appreciated among clients and used by whole team. In this document I also captured how Chrysler dealership works.

                Once the model is somewhat finalized I started working with Cognos developer to build the framework. I understand that there are few differences between a Business Objects universe and Cognos framework. Functionality wise both are same. I didn’t put my hand in development as there are several analyses were still pending and I was not assigned to do this. I was closely monitoring the framework development and suggest changes in that.

                Next thing what I did is the consolidation of the report. After gathering lots of knowledge in training segments I came to understand that the numbers of reports they are using are not really needed by business and there are lots of repetition with simple filter added into it.  Also there are several reports with hard coded parameters which changes every quarter. I found several parameters where we can merge multiple reports. It requires lots of analysis and I end up with 170 reports and two dashboard design which started another project.
                As I mentioned earlier that there were several reports which were hardcoded, but there was no chance to create crosstab data driven dynamic reports due to their templates. So we came up with a design which creates the template dynamically and user can change the template dynamically while following a certain spreadsheet. That saved a lot of redundant effort for quarterly changing reports. Now all the reports reflect automatically the change in template when user uploads the spreadsheet.

                Spreadsheet loading through ETL is really back dated and not a best practice in data warehousing project. But we had 7 different spreadsheets which cannot be loaded in the database as they are user driven and there are no web portal created to feed data directly to database from GUI. I came up with a design where user can load the spreadsheet on their own and also they also can change the value with a menu driven system. I am ready with the design but still development is not done yet.

Report development and data model change is going on still now as there are 170+ report is identified.