- A team-oriented Sr Technical Consultant with over 15 years of experience in System analysis, Design and Development of Databases, Data warehouses, Datamarts and Business Intelligence using SQL, PL/SQL, Oracle 11g/10g/9i/8i, SQL Server, SSIS, SSAS, SSRS, Business Objects XIR2, Universe Development, Web Intelligence, Desktop Intelligence, OWB, Oracle CC B, Informatica, DB2, MySQL and SQL Loader.
- Extensive experience in Logical and Physical Data Modeling using ERWin and Visio, practical hands on experience in designing and developing ETL processes, star schemas and transformations.
- Strong database skills, enhanced with multiple language programming capabilities. Thorough knowledge of many applications and debugging tools, Solid understanding of Kimball Inmon Architecture and Design concepts.
- Highly motivated individual with a clear understanding of Banking and Financial, Insurance Property/Auto, Policy and Claims, Retail-Supply Chain Management, Logistics, Manufacturing, Market/Media Research, Energy and Utilities Oracle CC B 2.2/2.3 Customer Care and Billing, CIS System industry requirements.
- Expertise in creating Oracle Tables, Table Partitions, Views and Materialized Views applying referential integrity constraints. Highly Proficient in OWB, Export, Import, SQL Loader and Job Scheduler Utilities.
- Expertise in developing PL/SQL Packages, Stored Procedures, Functions and Triggers. Developed various applications using Oracle built-in packages such as DBMS SQL, UTL FILE, UTL MAIL and DBMS XML.
- Worked extensively with Oracle Forms/Reports 6i/9i Triggers, Canvases, Menus and PL/SQL subprograms.
- Expertise in Performance Tuning and Query optimization using Explain Plan and Tkprof utilities.
- Highly proficient in MS SQL Server 7.0/2000/2005/2008 tools like Enterprise Manager, Management Studio, DTS, SSIS, SSAS, SSRS and SQL Query Analyzer, experienced in developing Stored Procedures, Functions and Triggers in Transact SQL T-SQL .
- Thorough knowledge of multiple operating systems like Linux, Unix-AIX, HP-UX, Sun Solaris and Windows.
- Experienced in administration and use of SVN, Visual Sourcesafe VSS and Perforce code repository.
- Solid experience with various ERP Systems like i2, SAP, expertise in setting up Data warehouse's for Curam.
- Oracle Utilities, Oracle CC B 2.2/2.3 Customer Care and Billing, CIS Systems , PeopleSoft CIS, Oracle RICE components
- Ontario Government Security Clearance obtained.
Languages: Oracle 11g/10g/9i/8i, SQL Server, DB2, MySQL, PL/SQL, SQL PLUS, Shell Scripting,
Transact-SQL T-SQL , Pro C, Visual Basic 6.0/5.0, Visual Basic.Net, C, C , Java, J2EE, C , HTML, UML
Databases: Oracle 11g/10g/9i/8i, SQL Server 2000/2005/2008, IBM DB2 UDB, MySQL, MS Access. ETL Tools: SQL Server Integration Services-SSIS/DTS, Informatica, Business Objects DS/DQ/DI Data Services, Data Quality, Data Integrator, Oracle Warehouse Builder-OWB. Analysis Tools: SQL Server Analysis Services-SSAS, MDX, Business Objects XIR2, Universe Development, Xcelsius, Dashboard Design, Infoview. Reporting Tools: SQL Server Reporting Services SSRS, Crystal Reports 9/10/XI, Web Intelligence, Desktop Intelligence, Oracle Reports.
Operating Systems: Linux, Unix- AIX, HP-UX, Sun Solaris, Windows 95/98/2000/NT/XP and MS-DOS.
Applications and Tools: ERWin 3.0/4.0, Visio, TOAD 9.0, Oracle Forms/Reports 6i/9i, DTS, SSIS, SSAS, SSRS, OOAD, OOP, ODBC, SAP, i2, COTS, Curam 5.0/5.2, Oracle Utilities, Oracle CC B 2.2/2.3, OWB, OEM, Informatica, SharePoint, Performance Point, XML, XST, Visual Studio.NET
Sr Oracle Functional Consultant
- Oracle CC B Techno Functional Participated in Oracle CC B Functional Design Activities, Requirements Definition and Analysis, supported Client Training, System Adoption, Production Support and Issue Resolution Hands-on Techno-Functional experience in setting up Oracle CC B 2.3.1 Configurations Control, Master and Transaction , Config Lab, Billing, Rates, Rate Versions, Rate Components, Oracle CC B Financials like Financial Transactions, Accounts Receivable, General Ledger, Distribution Codes, Payments and Adjustments. Thoroughly proficient with the Oracle CC B 2.3.1 V and developing Oracle RICE components - Reporting, Integration, Conversion and Enhancements
- Designed, implemented and supported Oracle CC B Functional Business Processes, Data Conversion/Data Migration solution to migrate data from a legacy PeopleSoft CIS system to Oracle CC B 2.2/2.3.1 Customer Information System.
- Designed, Developed and supported Oracle CC B 2.3.1/MDM 2.0 Data Conversion and Functional programs and processes for Accenture's client the Duquesne Light Company with Residential, Commercial and Industrial service to over 750,000 active CIS accounts.
- Sound experience in setting up Oracle CC B Batch Stream Configuration, Batch Job Scheduling, Monitoring and Tuning.
- Participated in designing Oracle CC B custom Enhancements and Integration.
- Oracle CC B Data Conversion/ETL and Reporting
- Analyzed and understood the Oracle CC B 2.2/2.3.1 Architecture, Data Model and Functional Components like Person, Accounts, Service Agreement, Service Point, Premise, Rates and Rate Components.
- Developed a Staging environment created Oracle Tables, Indexes, SQL, PL/SQL Stored Procedures and Functions to load 25 months of data for Bills, Payments, Adjustments, Meter Reads and Meter Data Management from PeopleSoft 8.8 into Oracle CC B database.
- Used the SPL Conversion Mapper/Conversion Central to define Mappings and Transformations between the source PeopleSoft system and Oracle CC B system.
- Developed ETL-Extract Transform Load routines using SPL Conversion Mapper/Conversion Central which generates SQL, PL/SQL scripts to load the Config or Control Tables for the ConfigLab and Master/Transaction scripts for loading transactional data.
- Developed Validation scripts in SQL and PL/SQL to validate the Config tables which involved fixing Required Data and Foreign Key Data errors.
- Utilized the CC B Conversion Toolkit to execute Validation, Key Generation and Insert to Production batch jobs.
- Analyzed and reviewed slow performing SQL Queries and Stored Procedures used Performance Tuning and Optimization techniques to fix them.
- Developed Data Cubes in Oracle 11g to be used for reporting by Business Objects BOXI and Crystal Reports.
- Used SharePoint and Tortoise, SVN as the code and document repository.
Environment: Oracle CC B 2.2/2.3.1 Customer Care and Billing/CIS Customer Information System, Oracle 11g/9i, Oracle RICE components, SQL, PL/SQL, SQL Plus, ETL-Extract Transform Load, Data Conversion, Data Migration, PeopleSoft CIS 8.8, TOAD, Business Objects BOXI, Crystal Reports, SharePoint, SQL Developer, PL/SQL Debugger.
Business Intelligence/Data warehouse/Reporting Consultant
- Analyzed the HP Asset Center source system in Oracle 10g/11g to understand the Oracle Tables and relationships, designed and developed brand new SQL Server 2005/2008 Dimension and Fact Tables for the Datamart.
- Wrote SQL, PL/SQL and TSQL Queries, Stored Procedures and Packages to join multiple tables to be used by the SQL and Query Transforms.
- Developed ETL routines using SQL, PL/SQL, TSQL and Business Objects Data Services/Data Quality/Data Integrator ETL Tool to load data into Staging environment and the Datamart.
- Defined Datastores which connect to Oracle and SQL Server Databases, designed Batch Jobs and Workflows which contain Dataflows and Transforms to Extract Transform Load incoming data into dimensional tables for Asset, Asset State, Cost Center, Supplier and Location.
- Developed Business Objects XIR2 Universes which contain Classes, Objects for Dimensions, Measures and Universe joins to link the Dimension and Fact tables, defined Conditions and List of Values for filtering data.
- Developed Crystal Reports XI reports, Web Intelligence and Desktop Intelligence documents/reports which Query data from Business Objects Universes and contain Prompts, Drilling and Filtering functionality.
- Scheduled and administered Web Intelligence reports using Business Objects Central Management Console.
- Used SVN as the code repository and Infoview as the portal for hosting Web Intelligence documents.
Environment: SQL, PL/SQL, SQL Plus, Oracle 11g/10g, Business Objects DS/DQ/DI Data Services/Data Integrator/Data Quality, Business Objects XIR2, Universes, Crystal Reports XI, TOAD, Transact SQL T-SQL , Datawarehouse, Business Intelligence, ETL, OLAP, OLTP, Erwin 7.0, UNIX Shell Scripting, Visio, UML, XML, Linux, PL/SQL Debugger, SVN, OEM.
Sr ETL/Business Intelligence/Data warehouse/Reporting Consultant
- Analyzed the Curam Integrated Case Management and Curam Financials Oracle 10g database architecture to understand the relationships between various entities, designed the Oracle 11g Datawarehouse from scratch by creating several Dimensions like Participant, Service Providers, Products, Evidence and Service Plans, developed Fact tables and Star schemas for data analysis and reporting, used ERWin as the Data Modeling tool.
- Designed the Staging environment to store data from various source systems like Curam, IFIS, LMDA and PIE, created Oracle 11g Tables and ETL routines using SQL, PL/SQL Stored Procedures and BO Data Services/Data Integrator to load the data.
- Developed SQL, PL/SQL Stored procedures ETL with complex SQL Joins on several source tables to load Hierarchical Dimension tables in the Datawarehouse, developed ETL Procedures in PL/SQL to load Financial Fact tables which comprise of Financial Components, Instructions, Instruction Line Items and Evidence.
- Developed Ministry and Service Provider specific Datamarts, created Materialized Views using Oracle SQL and PL/SQL to summarize data on Provincial, Regional, Product, Service Delivery Site and Case Worker level.
- Used Oracle DBMS Scheduler to schedule Batch jobs and PL/SQL Packages on a daily and weekly basis.
- Designed and developed Business Objects Universes, Business Views, List of Values using Business Objects XI and Adhoc and scheduled reports using Crystal Reports XI, Web Intelligence, and Desktop Intelligence which have cascading parameters and filters.
- Used SVN as the code repository and Curam 5.0/5.2 DocCenter as the document repository.
Environment: SQL, PL/SQL, SQL Plus, Oracle 11g/10g, Business Objects DS/DQ/DI Data Services/Data Integrator/Data Quality, Business Objects XIR2, Universes, Crystal Reports XI, Infoview, TOAD, Transact SQL T-SQL , Datawarehouse, Business Intelligence, ETL, OLAP, OLTP, Erwin 7.0, UNIX Shell Scripting, Visio, UML, XML, Linux, PL/SQL Debugger,
Data warehouse/Reporting Consultant
- Designed and Architected the entire Data Model for the Ticketing Data warehouse/Business Intelligence solution, analyzed the OLTP system developed in Oracle 10g, developed the Dimension, Fact Tables and the Star schema in SQL Server which constitute data related to the Line of Business, Workgroups, Branch's and Inquiries.
- Developed the initial load and delta load ETL - Extract Transform Load packages using SSIS -SQL Server Integration Services 2005/2008, T-SQL, PL/SQL, to extract data from Oracle 10g and load it into a SQL Server Data warehouse, developed automated SQL Server Agent Jobs scheduled to run daily or on a weekly basis.
- Developed several Oracle PL/SQL and SQL Server T-SQL Stored Procedures and Functions for the Order Management application which accepts XML as parameters and filters the data based on the XML elements passed.
- Developed an Import and Export routine using Oracle PL/SQL and SQL Server T-SQL Stored Procedures, UDF, and Packages to import data from flat files and load it into SQL Server tables.
- Designed and developed the SSAS - SQL Server Analysis Services Cubes which allow the user to slice and dice the data, developed MDX Queries for complex business analysis. Deployed the SSAS Cubes on the database server to be further used by SSRS for reporting.
- Developed several SSRS SQL Server Reporting Services ad-hoc reports which are parameterized and allow the user to filter the data on demand, the SSRS reports were developed in tabular, chart and matrix formats and can be published in Excel, PDF, CSV or XML formats.
- Used Visual Source Safe, Share point Performance Point as the code/document repository and publishing tools.
Environment: SQL, PL/SQL, SQL Plus, Oracle 11g/10g, SQL Server 2005/2008, DTS, SSIS, SSAS, SSRS, TOAD, Transact SQL T-SQL , Datawarehouse, Business Intelligence, ETL, OLAP, OLTP, Erwin, Unix Shell Scripting, Visio, UML, XML, Linux, PL/SQL Debugger, VSS, SharePoint, Performance Point, OEM. CGI
Sr ETL/Business Intelligence/Data warehouse/Reporting Consultant
- Developed and architected the Data Model to extend the GIOS framework and the generic OPUS Core schema by creating new Tables, Views, SQL, PL/SQL Packages, Stored Procedures and User Defined Functions.
- Developed Oracle 9i/10g Forms using Oracle Forms Designer which consisted of Forms triggers, Oracle 10g/11g SQL, PL/SQL Packages, Stored Procedures and Functions for the Policy Details and Claims module which captures information related to Policyholders, Interested Parties and Loss History for Risks.
- Developed several ETL - Extract Transform Load routines/packages using T-SQL and SSIS - SQL Server Integration Services 2005/2008 to extract data from the online system and load into the Datawarehouse to be further used by Datamarts for developing Cubes using SSAS - SQL Server Analysis Services 2005/2008.
- Utilized DBMS XML and DBMS XMLDOM Oracle 10g/11g built-in packages with PL/SQL to improve Performance of the Rating, Quoting Binding Logic on the Coverage's and Premiums screen.
- Developed the Automatic Property and Auto Policy Renewal process which interacts with the Billing module and sends out Memos and Diaries to Brokers and Underwriters using Oracle 10g/11g SQL, PL/SQL Stored Procedures, Functions and Packages to be run as automated batch jobs overnight via the Scheduler.
- Developed and designed Tabular, Matrix and Drilldown parameterized reports using SSRS - SQL Server Reporting Services 2005/2008 which are automated and based on SSAS Cubes.
- Created and deployed builds which constituted compiling Oracle Forms 10g, PL/SQL Packages, Object Libraries, Functions and Stored Procedures on the Oracle Application Server 10g.
- Developed UNIX Shell scripts for automation, used SVN and SharePoint as the code/document repository software.
Environment: SQL, PL/SQL, SQL Plus, Oracle 11g/10g, SQL Server 2005/2008, DTS, SSIS, SSAS, SSRS, TOAD, Oracle Forms 11g/10g/9i, Oracle Warehouse Builder OWB , Oracle Application Server 10g, ERWin, Unix Shell Scripting, T-SQL, Crystal Reports XI, Visio, UML, XML, Linux, SVN, Sharepoint, OEM
Sr ETL/Business Intelligence/Data warehouse/Reporting Consultant
- Designed a Hybrid Data Model using ERWin which combined the principles of Relational and Dimensional Modeling by reverse engineering the current OLTP system from Oracle 9i/10g to SQL Server 2005.
- Reviewed thousands of lines of code in Oracle 9i/10g SQL, PL/SQL Packages, Stored Procedures, Triggers and Oracle Forms to extract the Business Rules to be ported to the new system, worked with the Business Analyst and QA to document the same.
- Developed a normalized Database from scratch in SQL Server 2005 Management Studio created the required Users, Tables, Views, Indexes, Constraints, SQL/T-SQL Queries, Stored Procedures and Triggers.
- Developed workflow based ETL Extract Transform Load routines using SQL Server Integration Services SSIS packages to Import data from Oracle 9i/10g database to SQL Server 2005 database. Developed several TSQL Stored procedures and Queries for the User Interface developed in .Net
- Developed DTS/SSIS Packages, Stored Procedures and Triggers in MS SQL Server 2000/2005 to perform bulk inserts which load data from a client database and maintains an audit log table.
- Designed the Research and the Demographic Datamart for the specific business units, designed and developed the Business Intelligence and Reporting strategies using SQL Server Analysis/Reporting Services SSAS/SSRS .
- Wrote Enhancements and provided support for existing Oracle Forms 6i/9i applications, PL/SQL Packages and Stored Procedures which consisted of complex multidimensional PL/SQL Tables Arrays , Types and Records.
- Worked with the Business Intelligence Group to deploy automated parameter based SSRS reports
- Developed UNIX Shell scripts for automation, used Perforce as the version and source control tool.
- Highly proficient with Performance Tuning Strategies and Tools like SQLTrace, TKPROF and Statspack utility.
- Defined DBMS Jobs via the Oracle Job Scheduler to run Procedures/Packages at scheduled intervals.
Environment: SQL, PL/SQL, Oracle 10g/9i/8i, SQL Server 2000/2005/2008, DTS, SSIS, SSAS, SSRS, TOAD, Oracle Forms/Reports 6i/9i, Oracle Warehouse Builder OWB , MySQL, ERWin, Visio, Unix Shell Scripting, T-SQL, UML, MS Access, C , VB.Net, Crystal Reports, Linux, XML, XSL, OEM, MapInfo - GIS. BED BATH AND BEYOND, Union, New Jersey 2001 2006 Sr ETL/Business Intelligence/Data warehouse/Reporting Consultant
- Designed the Physical Data Model using ERWin and Visio, created the Enterprise Data Warehouse, Entity Relationship Diagrams, Data Dictionary and Metadata documentation for all the data attributes created.
- Developed and implemented the OLAP Database, Datamarts and the Star Schema.
- Used Table Partitioning to partition a huge Sales Fiscal table, built an aggregation and partition plan to assist in faster loading and reporting, thorough knowledge of Indexing, Tablespaces, and Rollback segments.
- Developed complex SQL and PL/SQL queries which created DDL objects on the fly using Dynamic SQL, used Analytic Functions and Bulk Binding to improve slow performing queries. Application performance tuning .
- Developed PL/SQL Packages, Stored Procedures and Functions for Data Retrieval and Data Storage, developed Batch processes to be run overnight which constituted of number crunching algorithms.
- Designed, developed and implemented Data migration activities including the Extraction, Transformation and Loading ETL between operational and legacy systems using PL/SQL, Oracle SQL Loader and DB2.
- Developed several Stored Procedures and T-SQL queries in MS SQL Server for retrieving transactional data.
- Created an MS Access 2003 database to track product returns previously lost in transit from retailer to manufacturer.
- Developed procedures and routines in C and Pro C, which embedded SQL statements.
- Wrote Unix- AIX Shell Scripts to automate tasks, to monitor processes and outputs using Korn shell.
- Scheduled Automated Jobs to be run by the Oracle/TOAD Job Scheduler daily, weekly or at specified intervals.
Environment: SQL, PL/SQL, SQL Plus, Oracle 10g/9i/8i, SQL Server 7.0/2000, DB2, MySQL, ERWin 3.0/4.0, Visio, Unix-AIX Shell Scripting, TOAD, Transact SQL T-SQL , VB.Net, Visual Basic 6.0, ADO.Net, PL/SQL Debugger, Oracle Warehouse Builder OWB , ERP, i2, Linux, Informatica, XML, XST, OEM, C, Pro C, Crystal Reports 7.0, Active X CISCO SYSTEMS