- Over 10 years of consulting experience in Business Intelligence, Database Design, Data Modeling, Data Governance, and Data Warehouse solution implementation following industry best practices.
- Experienced in Project coordination and Client collaboration with project management teams, end - users, and business analysts.
- Proven expertise in Dimensional Data Modeling in creating Star, Extended Star designs, and Normalization using Erwin and SQL Data Modeler.
- Developed data model for Online Analytical Processing (OLAP) system and Online Transaction Processing (OLTP) system.
- Developed and managed multiple data models using CA Erwin Data Modeler and SQL Data Modeler.
- Experienced in developing UML diagrams from the business process and use requirements. Developed high-level conceptual data model, logical and physical data models. Performed normalization up to 3NF for the OLTP systems and added redundancy for the OLAP models for the report performance.
- Created Erwin data model naming standards and used Erwin macros and domain objects to follow the standard across all the entities.
- Experienced in forward and reverse engineering of a data model using Erwin. Also, managed the data model by incremental forward engineering. Followed ERwin best practices to keep relationship resolutions or target objects to show only in the physical models.
- Conducted stakeholders meeting to get the data model report approvals.
- Implemented multiple BI solutions, conducted reporting requirement gathering sessions, designed and developed data models, implemented ETL designs, created report prototypes, designed and developed pixel perfect and dynamic reports, set up Unit and UAT testing environments, created design documents and best practices, and conducted training sessions.
- Modified ODI 12C objects and provided technical support for the complete ELT environment at METRA. When METRA migrated from OBIEE 11G to OBIEE 12C, I converted all the ODI 11G objects to 12C and OBIEE reports to the new environment. I created new attribute mapping for the order management and revenue accounting tables to facilitate data conversion.
- Created Mobile Usage Analytics data warehouse and data integration for the METRA (Chicago Regional Transit Authority) using ODI 12C to help businesses analyze usage data. This helped businesses find out various fraudulent activities concerning ticket usage and take strategic decisions to increase revenue.
- Converted 200 IBM COGNOS report to SAP BOBJ at Wolters Kluwer including redesign for the semantic layer.
- According to requirement implemented Type 1 and Type 2 Slowly Changing Dimensions for different domain tables, used SQL* loader to load flat files, Used WinSCP tool, and PowerShell to perform SFTP file move and process also experienced with UNC directory.
- Delivered ArcGIS map analytics using Microsoft SSIS Power BI to METRA CIO and CFO to help them analyze real-time train and station data.
- Created visualizations for the Confidential I&D team using TABLEAU for multiple project pursuits and POCs.
- Completed full life cycle implementation of Oracle BI Applications 220.127.116.11.1 with financial, order management, project, and human resources analytics for an Energy company.
- Created custom Informatica ETL objects at Cheniere to fulfill new business reporting requirements. Also provided maintenance support for the Informatica nightly job runs. Converted Informatica objects to ODI 11G interfaces when OBIA 18.104.22.168.1 was implemented. Provided development and support for the ODI environment during the OBIA 22.214.171.124.1 implementation.
- Coordinated with report stakeholders and functional analysts to design and develop new analysis requirements.
- Designed and developed Data Model and Data Integration (ETL) solutions for the US Department of Labor using Erwin and SSIS respectively from scratch. On top of the data warehouse, I created reporting solutions (SSRS) for the business to analyze unemployment insurance claim data.
- Expertise in SQL and PL/SQL programming and optimization with Oracle and Microsoft SQL Server Databases.
- Expertise in PL/SQL Dynamic SQL, Exception Handling, and Collections.
- Worked on multiple inbuilt Oracle supplied packages (DBMS * and UTL *).
- Experienced in Query Optimizer, Indexing, and Execution Plans.
- Experienced in SQL performance tuning with Cost-Based Optimization
- Extensive experience in batch jobs. Developed Shell Scripts to invoke jobs and scripts and job scheduling.
- Strong functional, business analysis, and technical capabilities in ELT/ ETL design, development, testing, migration, and implementation.
- Hands-On experience in data analysis with Python (Numpy, Scipy, Matplotlib, Scikit-learn, Pandas).
- Proven expertise across all facets of the DW/BI Project Lifecycle: requirement gathering, solution design, data analysis, data modeling, data integration, ETL development, and database programming.
- Completed full lifecycle implementation of data integration (ELT) using Oracle Data Integrator (ODI) 11G & 12C. This involves ODI installation, patching, Repositories (Master & Work (Development & Execution)) creation, maintenance, topology setup, user creation, agent setup, load plan management, version control, backup, code migration, and execution.
- Hands-on Knowledge of Oracle Application Express (APEX).
ERP System: Oracle EBS R12.1.3, Microsoft Dynamics AX 2012
Database: Oracle 11G & 12C, Microsoft SQL Server 2012, 2014 & 2017, AWS S3, Lambda
ETL/ ELT Tools: Oracle Data Integrator 10G, 11G, 12C, Microsoft SSIS, Microsoft SSAS, Azure Data Factory, Informatica PowerCenter 9.1.0
Reporting Tools: OBIEE 11G & 12C, Oracle BI Publisher, Microsoft SSRS, Microsoft Power BI, Tableau, IBM Cognos, SAP BO WEBI 3.1, Crystal Reports 2008
Languages: SQL, TSQL, PL/SQL, DAX, MDX, UNIX Shell Scripting, C# Scripting, WinSCP, Python, JS Functional Programming, C# Scripting
Data Modeling: Erwin, SQL Data Modeler
SDLC: Jira (Agile), GIT
Lead Data Warehouse Developer
- Working as a lead data warehouse developer for the METRA Revenue Accounting BI Implementation project.
- Provided maintenance support for ORACLE BI stack (OBIEE and ODI) and implemented a new Revenue Accounting data warehouse with Oracle EBS (Legacy System) and Microsoft Dynamics (New ERP System) as ERP systems for MSBI stack (SSIS, SSAS, SSRS and Power BI).
- Scaled existing ETL solution built on Informatica and converted it to SSIS ETL apckages.
- Worked on Informatica Power Center tools- Designer, Repository Manager, Workflow Manager, and Workflow Monitor.
- Managed and monitored Informatica nightly loads and modified the mappings to support new business requirements.
- Lead complete BI conversion process from Oracle EBS as the source of truth to Microsoft Dynamics AX. This involved coordinating with clients, project management teams, end-users, analysts, and third-party vendors.
- Conducted requirement gathering sessions and developed bus matrix from the input. Created a data model from the bus matrix. Developed a conceptual data model and got it approved. Developed logical and physical data models for the SQL Server 2014 database.
- Managed data models and performed incremental forward engineering to cater to changing or new business requirements.
- Performed source data analysis of the new system to make sure all the required data elements are available, checked data grain level to make sure different reporting levels are met, performed unit testing to validate the revenue and ride metric values and inventory transactions.
- Day to day tasks involve designing Database objects, coordinate with functional analysts to map new reporting objects, gathering reporting requirement, source system analysis, source to target attribute mapping, unit testing, documentation and conduct lunch and learn sessions.
- Gathered and analyzed reporting requirements and built the BUS matrix. Developed a conceptual model using Erwin based on the analysis and BUS matrix.
- Extensively used Erwin Model Mart, performed reverse engineering different database objects and features like relationships fields or attributes, and entities.
- Developed database models (Logical and Physical) for the data warehouse.
- The developed naming standard for Erwin data models.
- Developed SSIS packages, handled file operation with C# scripts, extensively used SSIS script tool, built SSIS solution in a project deployment mode, Data Conversion tool, merge, lookup, multicast, flat-file source, OLE DB source, Oracle source using attunity driver, upgraded attunity driver from V4 to V5 when we moved to SQL Server 2017, SSIS package security with a password, converted CSV file with comma delimiter to pipe delimiter and handled CRLF line ending.
- Developed an email body for different SSIS jobs using C# script.
- Developed complex PL/SQL procedures, functions, triggers, and master tables.
- Performed SQL and PL/SQL tuning using SQL*TRACE, TKPROF, Explain Plan, and Autotrace.
- Created new ad-hoc OBIEE reports and maintained existing ones to fulfill business needs.
- Created best practices documents for the ETL conversion and development projects at Metra.
- Created SQL Deploy strategies.
- Created complex stored procedures using PL/SQL to implement business requirements.
- Developed PowerShell and WinSCP scripts to facilitate SFTP file move and processing.
- Provided maintenance support for the nightly job runs. Managed and monitored ODI Load Plan design and execution. Created email alerts for both the ODI and SSIS nightly jobs.
- Installed and configured the Attunity and Oracle drivers to facilitate ETL with Oracle as a source and SQL Server as the destination.
- Performed SSIS code review and migration, code optimization, set up SQL Server jobs using an agent. Job monitored, diagnosed errors and implemented solutions, user group set up for the database and SSRS reports access, created scripts for Quality Assurance. Database backup, space management and worked with Red Gate, SQL Server Profiler, and SQL Server Agent.
- Administered user access for the Power BI using Microsoft Azure.
- Created new and ad-hoc reports, depending on its visualization and instructiveness, in SSRS and Power BI.
- Coordinated with client users and different teams to discuss reporting needs and UAT test issues.
- Performed performance tuning of the nightly ETL jobs and reports. And developed star models and aggregated tables at different grain levels to achieve faster report response.
- Developed Power BI models for multiple departments to do the end-user analysis and developed data security and masking to follow different guidelines. Created the Power BI data gateway to perform a nightly refresh of the imported data.
- Worked on Azure data factory to move SSIS packages and source data with Dynamics 365.
- Implemented Metra Oracle historical archival with AWS.
- Created a Mobile Usage Analytics solution to help business users in the strategic decision-making process.
- Created usage forecasting analytics using Python scripts and plug them into Power BI.
- Developed predictive analytics using Python SciKit library for different ticket usage.
- Developed SSAS tabular model to analyze on Power BI. Performed data manipulation using DAX and MDX.
- This helped the business user find out mobile ticket usage behavior and any fraudulent activities. This also helped business users to change different ticket prices and conductor allocation at different points in time. This not only resulted in increased revenue but also helped in cost-cutting.
- Conducted lunch and learn sessions for the business users to demo Power BI reports and how to use Power BI to the max.
- Developed a complete BI solution for the AX workflow to determine the root cause of the delay in the AX workflow approvals.
- Designed a new data model from the Workflow business process and built the logical and physical data models using ERwin.
- Provided maintenance support for the data model and developed a new ETL system using SSIS to pull data from the Dynamics AX to the SQL Data Warehouse.
- Used Microsoft SSIS, SQL Server, SSAS, and Power BI to develop the complete solution.
- Created ArcGIS Map analytics in Power BI for different departments and Operation heads to deliver daily Station and Rail Line KPIs.
- The dynamic Power BI dashboard provided real-time daily revenue, ticket sold count, operation status and gaps, inventory status. Developed a data model to satisfy these requirements and created a robust ETL solution to source data from different sources by pulling files from an SFTP server using WinSCP scripts.
- Managed data model, ETL, and report to ongoing business requirements where they needed to see more metrics or remove any.
- These dashboards have become a daily driver the CIO, CFO, and communication directors.
Sr. Consultant (Lead ETL Architect)
- Worked as an ETL lead for the Unemployment Benefits data warehouse implementation.
- My responsibilities include requirement gathering, attribute mapping, analysis, data modeling, data integration/ETL development, managing and monitoring execution, code backup, testing, data validation, migration, and documentation.
- Worked with Business Analysts (BA) in gathering reporting requirements and coordinated with different application teams and BAs in understanding business processes and OLTP metadata. I also made sure all the new CRs are accommodated on time.
- Analyzed requirements and OLTP system to design and develop star data models using Erwin Data Modeler. I strictly adhered to Erwin Data Modeler best practices while designing data models. Used Erwin domains and macros to develop naming conventions.
- Developed high-level Erwin conceptual data model from the multiple requirements gathering meetings, built logical and physical data models using Erwin data modeler.
- Conducted data model approval meetings and scaled the existing data model to support new business requirements.
- Delivered more than 250 analytical and pixel perfect report using OBIEE and BI Publisher on claims, and payment.
- Created Unit and UAT test documents and environments.
- Initially, we started with ODI 126.96.36.199 and midway we decided to upgrade it to the 12.1.3 version. Upgraded repositories schemas, performed back up and cloning, used upgrade assistant to upgrade ODI ETL objects and Java Agent. Post upgrade performed extensive testing to confirm the upgrade, Coordinated with Weblogic admin to perform user credential testing.
- Converted ODI 188.8.131.52 to ODI 12.1.3. Cloned and backed up master and work repository schemas, Installed Oracle 12C middleware products, Upgraded repositories and objects(ODI 11G interface to ODI 12C mapping, GUID, Knowledge modules) with the upgrade assistants, verified the objects with schema versions, reconfigured JAVA EE Agent environments (JDK, JDBC, RCU, credentials and key store).
- Scheduled daily load plan runs, set up execution alert mails, monitored execution, and fixed run time issues.
- Installed ODI 12C studio, created Master and Work (Dev) repositories, setup topology, agents, users, security, and worked on 2 weeks sprints to develop different ODI objects like models, mappings, variables, database sequences, database packages, ODI tools, procedures, packages, scenarios, and load plans.
- Worked on weekly ODI project backup, gap analysis, code migration using ODI smart export, coordinated user testing, and data validation.
- Created process flows, mapping, topology, ODI design, security guide, ODI process flow, and development documents to help report developers with data lineage and data integration logic.
- Created ad-hoc reports on OBIEE to perform different data checks.
- Developed federated reports using BI Publisher on the Unemployment Insurance OLTP database.
- Developed SQL Server EDW data model following Kimball Star design methodology and performed daily maintenance tasks.
- Developed complex PL/SQL objects like stored procedures, functions, triggers, and packages.
- Used supplied Oracle PL/SQL package UTL FILE for the file operation.
- Extensively worked on PL/SQL features like Dynamic SQL with IMMEDIATE EXECUTE and DBMS SQL to perform different DDL and DML operations in PL/SQL procedures.
- Worked with an Interface Team to address specific client requirements.
- Worked with BAs and the application team to analyze business needs and address data issues. This involved moving data between different technologies, specific formatting, and handle legacy data conversion.
- Implemented batch jobs using SSIS packages to create and send specific report files.
- Developed C# scripts to manage file handling in multiple SSIS packages.
- Performed data validation, coordinated with DBAs to implement various DRRs, and created SSIS design documents.
OBIA Informatica Support
- Managed and monitored Informatica PowerCenter 9.5.1 ETL environment and nightly runs.
- Provided maintenance support for the Informatica ETL and converted INFORMATICA custom mappings to ODI at Cheniere Energy OBIA upgrade.
- Managed and monitored ETL (Informatica) design and execution.
- Developed PowerCenter Workflows and sessions, logical and physical models, administered PowerCenter and PowerExchange, developed mappings and maplets, ran workflows with UNIX shell scripts, and controlled the ETL flows, performance tuning.
- Developed complex stored procedures and indices to develop robust ETL.
- Followed Informatica guidelines and best practices while developing ETL objects.
- Provided maintenance support for the workflow runs.
- Experienced with Workflow manager tasks like E-mail, Command, sessions, event raise, decision, assignment, and scheduling.
- Implemented ODI 11G ELT for Oracle BIAPPS 184.108.40.206.1 and 220.127.116.11.1 Financial Analytics, Project Analytics, Procurement, and Data Lineage with EBS R12.1.3 as source ERP systems.
- Performed OBIA customizations (RPD modification) and BIACM configuration.
- As part of the upgrade, he also performed a 3-way merge of RPDs.
- Performed in-depth source system analysis to implement specific client requirements and create a source to target mappings.
- Configured ODI 11g ELT Oracle BIAPPS and developed custom interfaces to fix the bugs and implement client-specific requirements.
- Developed ODI load plans from BIACM and monitored the full and incremental loads.
- Performed ODI object migration from Development to Test and Production environment and coordinated with business users in fixing the report reconciliation issues.
- Performed troubleshooting of the ODI ELT logic to fix the data inconsistencies and created standard documents for all the ODI object development and migration.
- Integrated Hyperion data with OBIA Financial Analytics.
- Used OBIA Universal Adaptor and file export from HFM cube (using ODI) as a source to populate the OBIA data warehouse with the financial data.
- This helped the client use one platform for their all analytics need.
- Worked with business users and key stakeholders in analyzing the business process and issues concerning customers subscribed to online content.
- Designed and developed sales-rep compensation, customer retention, and usage analytics report prototypes in SAP BO to help business users in strategic decision making.
- Converted COGNOS reports and semantic layers to BOBJ 3.1.
- Redesigned to fulfill new requirements and converted more than 200 reports.
- Created star schema in SAP BO Universe and modified warehouse data models to accommodate new client needs.
Business Intelligence Analyst
- Consulted with cross-functional teams and client users in root cause analysis of data discrepancy in SAP BW 7.1 and Oracle 11G.
- Performed QA testing and coordinated with deployment managers to migrate the successful solutions.
Graduate IT Assistant
Confidential, Confidential, OH
- Provided maintenance support for the IT system in the Psychology department.
- Provided functional training on a scheduling software Titanium Scheduler Ti 10 and a web development tool called OQ Analyst to doctors and counselors.
- Led the offshore team in banking application support and maintenance projects.
- Worked very closely with business users, IT Architect, IT support teams, and other technical teams in requirement gathering analysis and implementation to fulfill the business needs.
- Performed development, analysis, and maintenance of ODI ELT objects in different projects.