We provide IT Staff Augmentation Services!

Data Warehousing Resume Profile

SUMMARY

  • confidential with Ten 10 years of experience as Sr.Oracle Developer/ DBA/ Team Lead / Consultant.
  • Certified Oracle 10g DBA and Project Management Professional PMP .
  • Experience in Retail, Banking, Insurance, Pharmaceutical, Brokerage, Manufacturing and Oil industries.
  • Expertise in PL/SQL Stored Procedures/Functions/Triggers/Packages ,
  • Proficient in Unix - Shell and Perl scripting.
  • Expertise in Oracle Performance Tuning, SQL Loader, Data Pump, Export/Import
  • Expertise with Partitioning, Materialized Views, External Tables, Database Links and Indexes.
  • Experience in Data Warehousing, Data Modeling and Data Mart projects.
  • Experience as DBA in Database Resource Management including allocating and managing physical storage structures data files, redo logs, control files and logical structures tablespaces, schema and extents .
  • Working knowledge of Oracle Business Intelligence Enterprise Edition OBIEE Physical, BMM and Presentation Layers, Answers, Dashboards .
  • Working knowledge of Workflow Processes, Attributes, Notifications, Functions, Messages, Lookup types .
  • Knowledge of Extract, Transform and Load ETL tool Informatica and metadata management.
  • Experience of complete Project Life Cycle development including Requirement analysis, System Analysis, Design, Development, Testing, Implementation, Production Support and Maintenance.
  • Excellent interpersonal, communication, team-training and team-collaboration skills.
  • Experience with User group interaction, User requirement gathering and analysis

SKILLS

  • Databases ORACLE 10g/9i/8x/7x, MS Access
  • Oracle Tools SQL Plus, SQL Loader, Data Pump, Export/Import, XML-Publisher, OBIEE, OEM, Discoverer
  • ERP ORACLE Applications 11.5.10
  • Programming PL/SQL, SQL, Java, Perl, Korn Shell, Sed, Awk, VBA, PRO C, XML, HTML
  • GUI Oracle Developer/2000, Oracle Designer/2000, ERWIN, Visual Basic
  • Op. Systems UNIX Sun Solaris, IBM AIX, HP , LINUX, Windows 7/2003/NT/XP
  • Hardware RS6000, HP 9000/869, PCs Pentium
  • Reports Oracle Reports, Business Objects, Crystal Reports, SQR
  • Software TOAD, SQL Developer, SQL Navigator, SQL Studio, Informatica, IMPROMPTU, MPAC/SQL, PVCS, Dimensions, AUTOSYS, MS Office, MS Project, RS3 Discovery, MANUGISTICS.

CREDENTIALS

  • Project Management Professional PMP
  • Oracle Certified Professional OCP Program for DBA:
  • SQL and PL/SQL Database Administration Performance Tuning Backup Recovery

PROFESSIONAL EXPERIENCE

confidential

  • Ascena Retail Group, Inc. NASDAQ - ASNA , is a leading national specialty retailer of apparel for women and teen girls, operating, through its wholly-owned subsidiaries, the Dressbarn, Maurices,Justice, Lane Bryant and Catherines brands. The Company operates through its subsidiaries nearly 3,800 stores throughout the United States and Canada, with revenues of over 4.5 billion. Dressbarn stores offer casual, career and special occasion fashion apparel and accessories for women, operating 827 stores in 48 states.
  • Performed Daily Weekly Maintenance Procedures to keep the Customer Relationship Management CRM MarketWorks Datawarehouse database clean, up-to-date, and to perform at an optimal level. This includes flow of all data feeds into the database on a daily basis, and that all errors are corrected in a timely manner to prevent cascading errors over time.
  • Verified and loaded all daily imports successfully using ORACLE PL/SQL, SQL Loader, Java, XML and Unix shell and Perl scripts.
  • Loaded and corrected transaction, customer, alternate key, attribute, merchandise errors and posted rejected data. Performed application tuning of PL/SQL Stored Procedures, Functions, Triggers and Packages. Implemented Partitioning, Parallel query, Indexes, Bulk Collect, Dynamic SQL, Autonomous Transactions features.
  • Designed and Developed Best Customer application for Smart Rewards, Annual Gifts, Suits, and Dresses. Performed Reactivation and Seasonal events using ORACLE PL/SQL and Unix Shell and Perl scripts.
  • Designed and Developed Reverse Phone Append RPA procedures to retrieve and send phone number lists to Allant Group vendor on a weekly basis to update Dressbarn and Maurices customer address tables using PL/SQL Stored Procedures, Functions and Packages, SQL Loader, and FTP.
  • Extracted Oracle table data into XML format for other applications. Converted and modified several XML files into flat files and vice versa using both DOM and SAX parsers in Java.
  • Supported the Analytical Group in various analysis including Margin Analysis, Coupon posting, Promotional analysis.

Environment: Oracle 10g, PL/SQL, SQL Loader, SQL, TOAD, PERL, JAVA, XML, UNIX Shell/Sed/Awk .

Confidential

  • CitiDirect Pheonix - Barclays Project is a Global Cash Management e-banking platform based on CitiDirect and developed for Barclays Bank PLC, a major world-wide financial services provider, based in the UK. It provides Payment Processing, Payment Imports, Exports, Reports and Inquiries to Barclays clients and employees.
  • Performed SQL Performance Tuning to LRQs Long Running Queries and other queries using Explain Plan, TKPROF, SQL Developer and Perl. Created and modified Tables, Indexes, Materialized Views, Stored Procedures, Functions and Packages. Resolved day-to-day issues and supported user-interface developer's team.
  • Designed and developed a Package to load huge volume of current and historical data for Performance Testing Environment. Used Partitioning, UTL FILE, Autonomous Transaction features.
  • Designed and developed a Package to generate reports. Implemented UTL FILE, Dynamic SQL features.
  • Designed and developed a stored procedure to load data into multiple tables from external flat file.

Environment: Oracle 10g, PL/SQL, SQL Developer, SQL, Perl, Clear Case, PERL, UNIX, Win XP, Excel VBA.

Confidential

  • Implemented Oracle Applications 11.5.10.2 for Ferring Pharmaceuticals. It is a research-driven biopharmaceutical company dedicated to identify, develop and market innovative products in the fields of infertility, obstetrics, urology, gastroenterology, endocrinology and osteoarthritis. Ferring owns production facilities in several European countries, in South America, Israel and China.
  • Designed and developed Inbound and Outbound Interfaces using SQL LOADER and UTL FILE. Interfaces include GL Interface, GL Daily Conversion Rates, Item Conversion, Item Categories, PO Inbound Interface. Created several control files for SQL LOADER to load data into multiple tables both by Conventional and Direct methods.
  • Created Executables, Concurrent Programs set Execution methods, Parameters and passed appropriate Tokens using FND PROGRAM. Registered Reports, PL/SQL Stored Procedures. Registered Tables and Columns using AD DD Package. Moved AOL objects from one instance to another using FND LOAD.
  • Designed and developed several Reports including Cleared invoices, Credit transfer, Customer Shipment details, Vendor received quantity, Invoice payment status, Supplier invoice details. Worked with User exits, Profiles, Flexfields, Valuesets.
  • Performed Application Tuning using Explain Plan, SQL Trace, TKPROF and Oracle Hints.

Environment: Oracle Applications 11.5.10, PL/SQL, SQL, TOAD 9, OBIEE, Workflow, Linux/Win 2003.

Confidential

  • FGIC's PCAP Policy Capture system is designed to support a variety of users as they enter and process policies in FGIC's public, structured, and international finance businesses. Users of PCAP include analysts, underwriters, closing coordinators, surveillance, legal personnel and team leaders. PCAP is used to create policies and policy line items, and calculate and review debt service and premium schedules. Deal types covered by the system include municipal bonds, global utilities, mortgage- and asset-backed securities, collateralized debt obligations, and international deals. PCAP also facilitates transaction sign-off, reinsurance, installment reconciliation, closing documentation preparation and other policy-related activities.
  • Designed, Developed PCAP system and production support using SQL, PL/SQL, TOAD. Performed application tuning of PL/SQL Stored Procedures, Functions, Triggers and Packages. Implemented Partitioning, Parallel query, Indexes, Bulk Collect, Dynamic SQL, Autonomous Transactions features.
  • Performed maintenance and enhancements to the PCAP application. Interacted with the users for change requests and new requirement gathering analysis. Identified and resolved user-issues and change-requests efficiently. Performed unit and integrated testing before promoting to QA and Production environments.

Environment: Oracle 10g/9i, PL/SQL, SQL, TOAD 9, ASP, Perl, MS Visual SourceSafe 6, UNIX, Win 2003.

confidential

  • Designed and developed stored procedures, functions, packages for CSAR Client Sales Activity Reporting System project. CSAR will be used by the GCIB Global Corporate and Investment Banking as a reporting capability system for institutional sales across all products credit, liquid, structured and equities . Populated client dimension and hierarchy tables using SQL, PL/SQL, TOAD, SQL Tuning. Used Bulk Collect, Dynamic SQL, Parallel query, Autonomous Transactions features and enhanced performance tuning.
  • Created Korn Shell scripts to control and automate job schedules in Unix. Performed unit and integrated testing and promoted from Dev to QA and Production environments. Supported ETL Informatica team for reconciliation processes during Extraction, Staging, Cleansing and Loading.

Environment: Oracle 10g/9i, PL/SQL, SQL, TOAD 8, SQL Tuning, EXPLAIN PLAN, AUTOSYS, Informatica 8.1, Unix SUN Solaris , Korn Shell, Win 2003.

confidential

  • Designed and developed stored procedures, functions, packages for AP2PLE Accounts Payable Procure-2-Pay Leadership Execution project. It supports the setup of a new Shared Services Center for Customer Service tables, Supported ETL Informatica team for reconciliation, rollback, audit processes during Extraction, Staging, Cleansing and Loading.
  • Created and loaded data into audit, error and control tables for various workflows, sessions and mappings of Informatica using SQL, PL/SQL, SQL Navigator, SQL Tuning. Created and dropped indexes for performance.

Environment: Oracle 10g/9i, PL/SQL, SQL, SQL Navigator 5, SQL Tuning, EXPLAIN PLAN, Informatica 8.1, Win 2000.

confidential

  • Data Warehouse Application Tuning
  • Intensively involved in application tuning. Improved query performance and database availability by using Partitioned tables, Materialized Views and Parallelism. Created Partitioned tables, External tables and Indexes. Modified PL/SQL and SQL scripts to get optimized results by including bind variables.
  • Coordinated with DBA and reorganized schema objects. Created two separate database instances one for production data and a test database. Assisted DBA to refresh this test database. Extensively used EXPLAIN PLAN, SQLTRACE, AUTOTRACE and TKPROF tuning tools. Loaded data from more than one flat file to more than one table using SQL LOADER.
  • Designed and created a staging area for input into the Data Warehouse. The data is transformed to populate the destination enterprise Data Warehouse where it is maintained and refreshed for future use.

Environment: Oracle 9i, PL/SQL, SQL, SQL LOADER, SQL Tuning, EXPLAIN PLAN, AUTOTRACE, TKPROF, Unix SUN Solaris , Perl, Korn Shell, Windows 2000.

confidential

Govt. Pricing Data Warehouse

  • GP Legacy Data Mapping/Loading. Mapped and loaded Government Pricing data related to Transaction, Rebates, Medicaid PGUnits, Orders, Chargebacks, Products NDC9 and NDC11 , Customers and Contracts using ORACLE PL/SQL and Unix Shell scripts. Used XML-SQL utility to select, insert and update XML values.
  • Designed and developed stored procedures, functions and packages to extract data from SAP Data Warehouse. Created database links to access data Loaded data into database using Direct Path and Unrecoverable load options of SQL Loader. Used Export/Import to move data across development to production servers by options of user, table and tablespace.
  • Environment: Oracle 9i, PL/SQL, SQL, SQL Loader, Export/Import, SQL PLUS, SQL Tuning, XML, XML-SQL, TOAD, Unix HP , Korn Shell, Windows 2000, MSOffice.

Confidential

Govt. Pricing Data Warehouse and Data Management System

  • Schering I-Many/Govt. Pricing Integration project. Designed and developed complex implementation, data validation and data transfer scripts using ORACLE PL/SQL and Unix Shell scripts. Created several Stored Procedures, Functions and Packages. Extensively utilized Oracle's supplied UTL FILE, DBMS SQL and DBMS OUTPUT packages. Implemented Autonomous Transactions, Native Bulk Binds and parallel query PDML . Executed DML and DDL SQL dynamically using DBMS SQL package.
  • Designed and developed a Master Job Execution process which include several sub-routines to handle data validation and process controls in Unix using Korn Shell, Perl, sed, awk as needed. Used Dimensions for version control. Migrated data from test to development and to production environments using Direct Path and Unrecoverable load options of SQL Loader and Export Import.
  • Coordinated with Application DBA in implementing Database security, fine-grained access control and Object Privileges for security. Created Partitioned tables. Built a job-process schema to monitor all the activities of data transfer from SAP tables into Stage Data Warehouse.
  • Utilized Performance Tuning Tools: Explain Plan, Autotrace and TKPROF. Created and removed indexes as needed for load process. Created database links between two databases and across servers to access the tables from local to remote database using distributed database feature. Implemented PL/SQL Coding standards.
  • Worked closely with the user and testing group. Coordinated and scheduled with the user-group. Provided on-call support and performed problem resolution.

Environment: Oracle 9i, PL/SQL, SQL, SQL Loader, Export/Import, SQL PLUS, SQL Tuning, TKPROF, TOAD, XML, XML-SQL, Unix IBM AIX , Korn Shell, Perl, Dimensions, Win 2000.

Confidential

Designed and developed Mass Product Load MPL project using Perl, ORACLE 9i, SQL, SQL Navigaotor. Extensively used Perl modules including DBI, DBD::Oracle, CGI, Date::Calc, File::Basename and FileHandle. Loaded data into VWR Scientific Products Information Management Database. Inserted new products information and updated product pricing. This involved data checking, complex data validation and data intigration before loading into the database. Used CVS for version control. Optimized SQL queries.

Environment: Oracle 9i, SQL, SQL Plus, SQL Tuning, SQL Navigator, Perl, Unix HP , Windows 2000.

Confidential

Data Warehouse System

  • Designed and developed data validation and data transfer scripts using ORACLE PL/SQL, SQL Loader and Unix shell scripts to transform data. Developed various Stored Procedures, Functions, Packages and database Triggers using PL/SQL. Used PVCS for version control.
  • Involved in preparation of Extraction, Transformation, and Loading ETL specifications and cleansing of data using INFORMATICA PowerMart/PowerCenter, PL/SQL stored procedures and loading into the common dimensions data marts. Created Unix shell scripts to automate the processes.
  • Involved in design and development of the entire back-end data extraction, cleansing and loading into the warehouse and data aggregation and extraction from the warehouse into the data marts on a periodic basis. Transferred data from test to development and to production environments using Direct Path and Unrecoverable load options of SQL Loader and Export Import.
  • Analyzed user requirements and prepared data security plans. Prepared data extraction, data conversion and data partitioning plans. Coordinated and scheduled with the user-group and then implemented database schema and design changes. Provided on-call support and performed troubleshooting.
  • Tuned the application, optimized SQL and PL/SQL Tuning using Explain Plans, Oracle Hints, SQL Trace and TKPROF. Created and removed indexes as needed for load process. Created database links between two databases to access the tables from local to remote database using distributed database feature

Environment: Oracle 9i/8i, PL/SQL, SQL Loader, Export/Import, Informatica PowerMart/PowerCenter 5, SQL Plus, SQL Tuning, SQL Trace, TKPROF, Perl, Unix Sun Solaris 2.6 , Windows 2000.

Confidential

World Headquarters IT division

  • Designed and developed Away From Home AFH eKettle Data Transfer project using ORACLE 8i/UNIX, SQL, PL/SQL and Korn Shell. Wrote code to extract eKettle data from Oracle to flat files and then send these files to a LAN file server for import into MSAccess database. Coordinated the structure, timing and content of these files with the user group and programmers. Prepared detailed specifications and documentation.
  • Performed data import and export operations using SQL Loader. Created and maintained Partitioned tables, Indexes, Materialized Views and implemented constraints. Helped developers to test/debug the applications.
  • Converted Manugistics batch jobs to UJC standard before the migration using ORACLE 8i/UNIX, Perl5, Korn Shell, SQL, PL/SQL and SQL Loader. Modified several Korn Shell and SQL scripts and Autosys job definitions. Used Perl for global modifications. Removed multiple embedded SQL statements and created new SQL files. Prepared design, steps and time estimate for Manugistics upgrade.
  • Designed and developed a menu driven Autosys Job Support Tracer tool using Perl5 and Korn Shell in UNIX. This helps the support group to a generate reports of Who supports what by job or application or node or user and b update the support list with current active production jobs that they need to support.
  • Production support of Manugistics for NASS and AFH using Manugistics Supply Chain Planning System , Korn Shell, SQL Plus and TOAD under ORACLE 8i/UNIX/Windows NT environment. Investigated, resolved and documented all production job failures. Interacted with the users group.
  • Made enhancements to AFH Minor Changes Project. Modified several SQL, PL/SQL, SQL Loader and Perl Shell programs and Autosys job definitions under ORACLE 8x/UNIX environment. Performed SQL and PL/SQL Tuning using TKPROF and Explain Plan utilities.
  • Converted over 70 Oracle reports from 2.5 to 6.0 using Oracle Reports 6.0 under ORACLE 8i/Windows NT. Transferred these to UNIX, tested and then promoted to production. Created new reports as needed.

Environment: Oracle 8i, SQL Plus, SQL, PL/SQL, SQL Loader, MSAccess, Manugistics, Autosys, TOAD, Oracle Reports, TKPROF, Explain Plan, Perl, Korn Shell, Unix Sun Solaris 2.5 , Windows NT.

Confidential

A division of American Home Products

Supported Discovery Research Applications group and Ford Dodge scientists to retrieve data from RS3-Discovery database in ORACLE 8.0.5/Windows NT using SQL, PL/SQL and SQL Navigator. The database contains information on chemical compounds, structures, their physical, chemical and biological data, etc. Developed user-friendly tools in Visual Basic for Applications in Excel. Supported the user group with RS3 Discovery 2 which integrates searching, reporting and retrieval of Discovery research information.

Environment: Oralce 8x, SQL, PL/SQL, SQL Navigator, RS3, Visual Basic for Application, Windows NT.

confidential

Generated reports for Integrated Bracco Information System IBIS using ORACLE 8.0.5/UNIX, SQR 3.5, SQL and PL/SQL. IBIS database system contains information on pharmaceutical products, hospitals, customers, sales, accounts and territories for the past 10 years. The extracted reports are used by the Accounting, Sales, Marketing and Research departments. Some of the reports include GPO Member Sales Trends, GPO Membership with Sales and GPO Field Sales Performance. Created tables, indexes and loaded data as needed for the reports using SQL and stored procedures in PL/SQL. Performed SQL statement tuning to optimize queries. Worked with users and supported them using IMPRPOMPTU 3.5 to extract data.

Environment: Oracle 8x, SQL, PL/SQL, SQR, Impromptu, Unix IBM , Windows NT.

confidential

  • Designed, Created, Monitored and Maintained Data Warehoue Database using ORACLE 8x/UNIX, ORACLE ENTERPRISE MANAGER OEM 1.6, ORACLE DESIGNER/2000, SQL NAVIGATOR, and EXPORT/IMPORT. Responsible for startup and shutdown, created users, granted and revoked roles/ privileges, planned and scheduled automated backups, export and import. Edited database initialization parameter files, tuned database, reduced database fragmentation. Reduced disk I/O, sized tables and indexes, re-indexed. Added data files, altered rollback segments, tablespace storage, extents, segments, and enabled/ disabled constraints.
  • Created the database in test and development environment from production. Loaded data from production environment to test and development using SQL Loader and created normal, bitmap and hash indexes. Performed SQL tuning for the application. Pinned large procedures in Library Cache. Created and maintained Partitioned tables, Materialized Views and implemented constraints.
  • Wrote shell scripts for backups and to generate regular reports for database activities, logs, datafiles, tablespaces and extents. Scheduled batch jobs on weekends and holidays. Performed backup and recovery of database, tablespace and transaction logs. Created database links and synonyms on production and report servers to make data transfer without constraint violation.
  • Performed database tuning using explain plan, Utlbstat, Utlestat, TKPROF and other custom utilites. Developed PL/SQL code to dynamically generate scripts for database administration using execute, immediate and DBMS SQL. Developed logical models, implemented design changes into physical schema/instance. Performed Normalization and De-Normalization on data to achieve high performance.
  • Maintained Strategic Solutions for Marketing Database SSMDB to support Marketing and Research Information department using ORACLE 8.0.5/UNIX, SQL, PL/SQL, Pro C and Perl. SSMDB is used to cross-sell various promotional products to existing and new customers. Uploaded monthly loads from mainframe applications into Oracle using Pro C. Modified code to reflect the new requests from the user group. Generated extracts periodically for shipment to several mailing distributors.
  • As an Oracle Discoverer Administrator, created end user layers and business areas, administered multiple end user layers and created conditions, LOVs, and calculated items. Supported users with queries from DISCOVERER 3.1. Created several stored procedures for their predictive modeling needs for cost-effective targeted direct mail in SQL and PL/SQL. Worked closely with users with P/CIS a third-party GUI tool and CRYSTAL REPORTS in generating reports.
  • Resolved day-to-day issues related to database including tablespaces, rollback segments and indexes. Replicated production database in development and testing environments. Provided guidance for developers in writing and tuning SQL and PL/SQL stored procedures. Supported the users during the testing phase and after the conversion. Loaded, Exported and Imported huge data. Performed unit and system testing.

Environment: Oracle 8x, SQL, SQL Plus, PL/SQL, Pro C, SQL Loader, Export/Import, Discoverer, SQL Navigator, OEM, Designer 2000, Crystal Reports, Unix Sun Solaris , Perl, Korn Shell, Windows NT.

Confidential

Performed Year 2000 Y2K modifications to several forms and reports to be Y2K compliant for the Merrill Lynch Asset Management MLAM Group using ORACLE and SQR under UNIX/Window NT environment. Modified forms including APL's Operations Main Menu, Setup Accounts, Maintain Accounts, Securities Maintenance, Account Extract, Daily Download, Managers Maintenance, and Securities Daily/Weekly/Monthly Uploads using ORACLE 7x, FORMS 3.0 and PL/SQL. Generated and modified several reports for APL including Account Master Extracts, Broker/Custodian Audit, Rebate, Billing, Performance, Account Address, Security Prices and Positions using SQR 3.0. Performed unit and control testing for both forms and reports.

Environment: Oracle 7x, SQL, SQL Plus, PL/SQL, Oracle Forms, SQR, Unix IBM AIX , Windows NT.

Confidential

  • As an Oracle DBA, Created, Reconfigured, Monitored and Maintained Database. Performed physical and logical database Backup and Recovery. Performed Database Tuning using TKPROF, Explain Plan utilities. Observed execution paths and performed SQL tuning. Monitored growth of data files, tablespaces, and extents. Tuned Library cache, Data dictionary cache and Database buffer cache.
  • Created and managed Tables, Constrains, Indexes, Procedures, Functions, Packages, Database Triggers, Synonyms, Tablespaces, Database Links, Access Permissions, User Roles, Privileges, Accounts and granted access in a multi-server environment. Created several shell scripts for Backup and database monitoring. Granted roles and privileges to users. Performed regular hot and cold backups. Resolved day-to-day issues.
  • Analyzed, designed and developed Account Block Conversion Project in ORACLE 7.3/UNIX, SQL, SQL PLUS and PL/SQL for Maintenance Planning And Control MPAC software. MPAC is used for accounting information to track and record charges for materials, labor and services against Work Orders, Equipment, Components and Projects. Converted old accounting information for numerous MPAC tables.
  • Modified and maintained several interfaces between MPAC and General Ledger GL and Accounts Payable AP of ORACLE FINANCIALS including Vouchers, Accruals, Work Order Journal Vouchers and Vendor Information in ORACLE/UNIX, SQL, PL/SQL and PRO C. Generated several reports including warehouse accrual and stores activity reports using SQR 3.0, REPORTS 2.x, and SQL PLUS.

Environment: Oracle 7x, SQL, SQL Plus, PL/SQL, Oracle Reports, TKPROF, Explain Plan, SQR, MPAC, Unix HP , Shell scripts, Windows NT.

Hire Now