Technical Support Data Warehousing

VIJAYALAKSHMI K.S

E-mail: ****.**********@*****.***

Phone: +1-929-***-****

LinkedIn: https://www.linkedin.com/in/vijayalakshmi-ks-38116635

Summary:

Having 14 years of total IT experience in Data Warehousing domain with the functional knowledge on ETL & BI concepts

●Having experience on all stages of a Data Warehousing project which includes Business Requirement Analysis, Project Scope, Effort Estimation, Architecture Design, Data Analysis, Dimension Modelling, Data Mapping, ETL & BI Design, Development, Build, Unit Testing, Technical Support, Report Generation, Dashboard Creation, Presentation, Data Mining and Prediction Analysis

●Having 6 years of experience in Data stage in both server and parallel edition including Requirement Analysis, Design, Development, Implementation and Testing

●Having 2 years of experience in Microsoft SQL Server 2005 Suite (SSIS, MSAS, MSRS) which includes KPI identification, Requirements gathering, Analysis, Design, Data Modelling, Development of ETL packages, OLAP Cubes, Report generation, Dashboards, Implementation, Testing, Prediction Analysis

●Knowledge in writing SQL queries, Oracle PL/SQL scripts involving procedures, triggers, cursors

●Worked on other BI tools like Cognos, Hyperion and Business Objects

●Worked in other ETL Tools like Informatica and Data modeling

●Hands-on on some of the open source tools like Pentaho, Jaspersoft and an idea on Qlikview

●Exposure to Power BI, Tableau, Amazon Web Services

●Implemented Best Practices in DWH projects, Data stage tool and provided ideas to improve the project execution

●Provided trainings to the new employees on DWH concepts and the ETL/BI tools

●Performed GAP analysis on the source systems for creating an Enterprise Data Warehouse

●Experience in creating a Decision Support System (DSS), Data Analysis & Modelling, Data Governance Policies, Data Audit and Retention Strategies, Data Quality Standards, Meta-Data Repository, Business Intelligence Modelling & Data Warehouse Architecture design

●Proficient in the integration of various data sources and design Data Warehouse using multiple relational databases like Teradata, Oracle, SQL Server, DB2 and multiple files

●Explored using Unstructured data

●Involved in creating proposals and ready build DWH solutions to suit for any manufacturing industry

●Worked for Retail, Banking, Medical and Manufacturing domain

Educational Qualifications:

●Masters in Computer Applications with distinction from Madras University, India

Technical skills:

ETL Tools

Data stage 7.5, 8 & 9.1, SSIS 2005 & 2008, Informatica 6.2 & 8

BI Tools

Business Objects, MSRS/MSAS 2005 & 2008, Cognos 7.2 & 8, Hyperion System 9, Tableau, Power BI

Database development tools

SQL Server 2005 & 2008, Oracle 9i & 10g, MS-Access, Teradata, DB2

Operating Systems

Windows NT/98/ 2000/ XP, UNIX

Open Source & Other Tools

MY SQL, Pentaho, Jaspersoft, BIRT, Qlikview

Languages

SQL, PL/SQL, JAVA, C, C++, ASP.Net, VB.Net

Application Development tools

TOAD, SQL*Loader, Putty, Powerdesigner

Web Technologies

HTML, XML

Certification:

Microsoft Certified Professional

Oracle Certified Professional

IBM Certified Solution Developer Infosphere Data stage V8.5

PROFESSIONAL EXPERIENCE

MindBridges Technologies [Apr 2021 – Dec 2022]

Project: Complaint Management System, Inhouse projects

Role: Architect

Environment: Powerdesigner, Datastage, Oracle, PowerBI, Tableau, TrackWise

Brief Profile:

The project involves a holistic picture of the complaints about the Infusion pump by considering different stages in the complaint lifecycle. Different types of complaints reported. Two different solutions formulated.

Also an Inhouse Project Monitoring Dashboard created to oversee the projects in the enterprise.

Responsibilities:

●Involved in the discussion in understanding the scope of the projects

●Contributed to the Project Management Plan, Project documents

●Architectural design of the projects worked

●Designed the high-level design and the mapping specifications.

●Designed the data model and the approach document

●Technical support to the team

●Came up with project level document templates

●SME review of technical documents

●Facilitated training on DWH concepts to the resources

●Participated in Amazon Web Services training

Infosys Technologies [Sep 2016 – Dec 2017]

Bank of America, USA

Project: Enterprise View (onsite)

Role: Architect

Environment: Powerdesigner, Informatica, Oracle, SQData

Brief Profile:

The project must provide an enterprise view of the client to the downstream system to perform due diligence on the clients who are onboarded/refreshed. Hence, the target system needs the relevant information from the ADS to perform AML intelligence. This will ensure compliance to the data and avoid financial crimes.

Responsibilities:

●Involved in analysis and profiling the data

●Prepared the high-level design and the mapping specifications

●Designed the data model and the approach document

●Technical support to the team

Bank of America, USA

Project: Reconciliation (onsite)

Role: Architect

Environment: Powerdesigner, Informatica, Oracle, SQData

Brief Profile:

The project has an authorized data store(ADS) which sources data from all systems of record namely Liquidity, Deposits, Payments, Trade etc. The solution involves reconciling the data both daily and monthly from all deposit systems in order to validate the data sent downstream. The daily involves sending an email when discrepancy occurs, monthly is to match the end of month balances.

Responsibilities:

●Involved in analysis and profiling the data

●Prepared the high-level design and the mapping specifications

●Designed the data model and the approach document

●Technical support to the team

Bank of America, USA

Project: Operational Reporting (onsite)

Role: Architect

Environment: Powerdesigner, Microstrategy, Informatica, Oracle, XML

Brief Profile:

The project is to support the operational reporting requirements for international payments. Currently, the system supports only domestic business to consumer payments. The existing solution does not offer robust client choice for managing both domestic and international payments across borders using established alias networks. Also, the current solution does not have transparency for the end customer. A solution must be built which caters to the above scenario and operational reports need to be generated on top of that to meet the needs of the corporate, commercial and public sector clients.

Responsibilities:

●Interacted with the business and the stakeholders of the project

●Involved in GAP analysis and solution design

●Prepared the high-level design and the mapping specifications

●Designed the data model and the approach document

●Technical support to the team

Wipro [Aug 2011 – Mar 2016]

Lloyds Banking Group, UK

Project: TBT SOC on boarding

Role: Project Leader, SME & Architect

Environment: UNIX, Oracle 10g, XML, Data stage 9.1

Brief Profile:

The purpose of the Soc-On boarding project is to migrate customers from legacy channels to SOC. The migration will be in tranches that may involve customers from all three legacy channels per tranche. The customers from each channel will be segmented based on migration complexity and operations support requirements. Ongoing business analysis of customer data will drive the tranche migration approach.

Responsibilities:

●Lead the teams on requirement gathering, gap analysis, estimation

●Prepared the mapping specifications

●Involved in preparing the design approach of the module Operational Reconciliation

●Interaction with the Business architects and the analysts on the issues identified

●Involved in the overall project plan and the migration approach

●Involved in the preparation of the high level design documents and mappings specifications

●Designed a reusable component using RCP and schema methodology to ease the design of Operational Reconciliation

●Identified the loading strategies and retention policies

●Participated in the review process and prepared the test plan

Lloyds Banking Group, UK

Project: Self Service Software Replacement (SSSR)

Role: Project Leader & Architect

Environment: UNIX, Oracle10g, XML, Data stage 9.1

Brief Profile:

The Self-Service Software Replacement (SSSR) project will address the manifold issues in the current self-service space, and position the Business area and the Group in a strategically sound position for the future. The objective of the project is to process and transform/load the ATM & IDM transaction details coming from external source into the MI Data mart and the Branch network system.

Responsibilities:

●Involved in the preparation of the design document and guiding the developers on the build approach

●Reviewing of build test cases and the code

●Involved in the requirements gathering and preparation of Mid-Level Platform Design

●Interacted with the Business analysts and the third-party stakeholders on analyzing the issues. Performed Gap analysis on the source extract XML file.

●Involved in giving walkthrough of the design to the review panel

●Involved in the implementation approach

Lloyds Banking Group, UK

Project: Payment Protection Insurance (PPI)

Role: Project Leader & Architect

Environment: Unix, Oracle 9i, Teradata, Data stage 9.1

Brief Profile:

The PPI (Payment Protection Insurance) program is Banking Group’s coordinated response to the FSA’s publication of assessment and redress rules regarding Payment Protection Insurance complaints. The objective of the programmer is to improve complaint handlers’ productivity by centralizing data into a single repository, reducing time spent assessing each claim and assisting the process of proactive claim management by enabling customer letter processing and tracking.

Responsibilities:

●Interacted with the customer on the mapping requirements

●Involved in preparing the high-level design documents

●Provided support on a timely basis on defects and technical support to the team

●Involved in design walkthrough, reviews and knowledge sharing to the team & mentoring

●Created reusable components to improvise the design & coding techniques and reduce the defects

●Incorporated performance tuning methods to increase the jobs performance

●Created best practices for the developers to build efficient jobs

●Had an experience of working as an SME in the portfolio

Infosys, Bangalore, India [Jan 2011 – Aug 2011]

PepsiCo, USA

Project: Enterprise Data Warehouse

Role: Project Leader

Environment: Unix-AIX, Data stage 9.1

Brief Profile:

PepsiCo is a leading global food and Beverage Company with brands that are respected household names throughout the world. The requirement is to build an enterprise data warehouse to achieve the business needs. The Data Warehouse helps accelerate business requirements gathering and data model design.

Responsibilities:

●Provide guidance and mentorship to ETL developers

●Prepare design documents

●Review Data stage jobs and test cases

L&T Infotech, India [Jul 2005 – Dec 2010]

VF Corporation, USA

Project: B2B Reporting

Role: Project Leader

Environment: Unix-AIX, Cognos 8, DB2

Brief Profile:

The B2B Reporting Solution initiative is to provide external users (Sales Agencies, Sales Representatives and account holders) with adequate information related to orders, status of orders, shipment details and other critical information enabling users to interact with the customers efficiently. Flexible reporting and the availability of daily updated data is another goal of the system. Data is sourced from SAP ODS and populated into DB2 for the cognos reporting.

Responsibilities:

●Capturing the business requirement and modeling the database design.

●Designed the loading strategy of the dimension and fact tables.

●Involved in providing technical guidance to the team

●Involved in Performance tuning and troubleshooting

●Formulated the migration and the test plan

●Involved in reconciling the Cognos reports against SAP BW queries

CITIBANK, Singapore

Project: Nexgen DWH

Role: Project Leader

Environment: Data stage V8.0.1, Oracle 10g

Brief Profile:

This module basically deals with the banking domain of the CITI. The basic purpose of this module is to generate reports and to analyze the data which deals with the banking domain to understand the business. The objective is to design and develop a model which can support the end-users who form the decision making team working mostly in Sales and Analysis. From different locations data is arrived in the form of mainframe flat files. The flat files will be processed to load into the dataset and data warehouse schema. Nexgen DWH, a standardization mechanism is provided with which a set of common standards will be followed across all regions.

Responsibilities:

●Understood the business requirement and designed the mapping documents for Star Schema

●Designed and loaded the interim tables for cards, authorization, star schemas using data stage jobs

●Used stages like SCD, Remove Duplicates and many more

●Created SQL scripts for star schema tables and DB sequences

●Inserted data into one time dimension tables through oracle PL/SQL statements

●Prepared test plans and tested the jobs through sql queries

L & T Infotech

Project: Data Driven Continuous Improvement – D2CI

Role: Project Leader & Architect

Environment: MS SQL Server 2005 suite, QlikView

Brief Profile:

The Objective is to identify the lead indicators for a project and perform trend deviations. The main goal of the project is to alert project managers and delivery heads, an early warning signal via email and SMS about the threat during the course of the project. As a phase2 approach, prediction analysis will be provided. This will enable them to take corrective action.

Responsibilities:

●Understood the business requirement and identified the metrics with lead indicators

●Prepared SRS, HLD, DLD, functional architecture, technical and report specifications

●Designed the Dimension Model and created the dimension modeling schema which involves multiple facts

●Interacting with the business team and did project monitoring and technical support

●Created prediction analysis

●Involved in showing demos

HD SUPPLY, USA

Project: Migration Project for a large retail division (Onsite)

Role: ETL Developer

Environment: Datastage 7.5, Oracle 10g, Teradata, SQL Server 2000

Brief Profile:

The objective was to convert all the Datastage jobs using or referring Teradata and SQL Server to Oracle and convert all the Teradata Bteq, MLOAD scripts to Oracle stored procedures and make all the projects up and running with Oracle as the backend.

Responsibilities:

●Identified the Data stage jobs in all the projects which uses SQL Server and Teradata

●Prepared standard checklists and designed the shell jobs with design specifications for the ETL jobs

●Modified the jobs with Teradata and SQL Server to Oracle and Bteq, MLOAD scripts in Teradata to Oracle

●Used PL/SQL stored procedures to migrate data from Teradata to oracle for one-time dimensions and created partitions for the different lines of business

●Involved in testing the jobs and assisting the quality team in resolving the issues

●Used SQL scripts to test the data loaded in the tables as per the mapping logic

HD SUPPLY, USA

Project: Automation of Staging Load Programs (onsite)

Role: ETL Developer

Environment: Datastage 7.5, Oracle 10g

Brief Profile:

The objective of this project is to automate the process of receiving files from Business Units and load into Oracle Staging by capturing deltas. The staging area maintains the history of data as partitions. From the staging, the Sales Analytics and Zilliant will pull the data to populate their data mart. The goal of this project is to minimize the time in capturing the deltas while populating the data warehouse.

Responsibilities:

●Identifying the limitations of the proposed approach of Sales Analytics and suggesting the inbound and the outbound processing logic to create a better approach

●Created the inbound processing logic document and conducted walk through to the team and devised ETL development approach pulling data from FTP to staging & capturing deltas and developed the jobs for the same

●Partitions were created on the oracle tables based on the lines of business

●Created a stored procedure for swapping the data in the partitions and developed ETL jobs needed for extracting data from FTP source, load the data to staging by capturing deltas

●Used CRC technique for capturing the deltas and automated ETL jobs to load daily data feeds coming in from various business units and unit testing was done and conducted the pilot work of automated daily feed

●Worked on profiling the data available from various business units and interacted with the offshore QA team to resolve the issues

HD Supply, USA

Project : Enterprise Sales Analytics 1.0 (Onsite)

Role: ETL Developer

Environment : Datastage 7.5, Oracle 10g, SQL Server 2000, Teradata

Brief Profile:

The goal of the Sales Analytics Release 1.0 project is to reduce the time, labor and cost required to collect and process sales data from the business units and increase the scope and accuracy of enterprise-wide sales reporting and analytics. The project will create a sustainable, standardized and optimized data repository and corresponding reporting analytics tool accessible by enterprise functional areas (e.g. Marketing, Credit), HD Supply business units, and Enterprise Sales, ultimately driving additional revenue for HD Supply.

Responsibilities:

●Business requirement understanding and analysis which involves the high level DW architecture, gap analysis and business data flow and error handling

●Designed DFD, SDD, source to target mapping document and conducted design walkthrough to the team

●SQL scripts were used to create the dimension tables

●Developed the ETL jobs to load the staging area for the extracts from various business units and create the Enterprise data mart from staging

●Interacted with the offshore QA team to resolve the issues and in profiling of data

HD Supply, USA

Project: Plumbing Sales Matrix Utilization (Onsite)

Role: ETL Developer

Environment: Datastage 7.5, Teradata, Sql Server 2000

Brief Profile:

This project was to provide the Plumbing LOB with a more effective way to access and analyze their sales/pricing data. Due to the transactional nature of the ERP system, the business data required for analysis was difficult and time-consuming to access. The existing process was, the business must physically move the transactional and detailed ERP data to other software packages to facilitate analysis and report generation. The group was looking to streamline the process, provide a common, readily available sales/pricing information source, and a robust analytical BI environment and toolset.

Responsibilities:

●Understood the business process and the Project overview

●Designed the SDD for Sales Code and developed the jobs for the same

●Unit testing of the developed ETL jobs, Code walkthrough of the developed jobs

●Designed a job to fix the item group rejections by creating custom transforms

●Worked with the QA team to review the test cases and resolve the issues

L&T Infotech, India

Project: Sales DataMart for a Leading Manufacturer

Role: ETL/BI Developer

Environment : Microsoft SQL Server 2005, SSIS 2005, Cognos 7.2

Brief Profile:

The company manufactures vacuum cleaners and has invoices raised from 10 different units. The units include 8 different branches from all over India. The head office keeps track of the data from various branches and the database maintained by the branches is in FoxPro. The head office extracts all the data from the branches into their homegrown ERP, which is in Sql server 2005. The requirement was to design and develop a sales data mart, which captures all the sales, happened in 10 different units. Dealer Wise and Customer Wise Sales were captured.

Responsibilities:

●Gathering the requirements about their ERP, database structures, head office & branch details, product hierarchy and the customer relationships

●Prepared design specifications and developed the jobs to extract the data, cleaning and transforming it. Aggregator, data conversion, script component, conditional split, slowly changing dimension were used.

●Reports which include list, cross tab, drill thru, hierarchies, prompts were developed

●Scheduling the jobs using SQL Server Agent at the production environment.

●Test the data loaded in the tables using SQL queries, sub queries

L&T Infotech, India

Project: Readymade Dashboard for a Manufacturing Industry

Role: ETL/BI Developer

Environment: Datastage 7.5, Hyperion System 9, Oracle 9i

Brief Profile:

A development project where an Enterprise Dashboard with manufacturing metrics was built. The division uses JDEdwards and totally 5 instances of ERP and 8 different servers are running. The requirement was to find out the part summary, work order and assembly summary within a period. Two and half years of data are available. Corresponding to the metrics, a dimensional model is designed which would incorporate all the attributes that would be required for their calculation. A Dimensional model was adapted to the customer’s OLTP system and reports and dashboards were created.

Responsibilities:

● Identifying the KPI’s of certain functional units in the manufacturing domain.

● Participate in designing the dimensional models for the functional units like sales, production, inventory and finance

●Developing jobs to extract the parts, assemblies and the work order details and queries were used to test the data

●Used surrogate key generator, aggregator, look up, transformer stages

● Reports were developed to show the part summary, assembly wise orders placed and the work order wise summary with prompts, pivots and charts

L&T Infotech, India

Project: Attendance Analytics

Role: ETL/BI Developer

Environment: Microsoft SQL Server 2005, SSIS 2005, MSAS / MSRS 2005

Brief Profile:

The requirement was to build an insight into the Attendance Pattern of the Employees. The data was extracted from different sources like MS Access, MS Excel to the Microsoft SQL server 2005 using SSIS after the required cleaning. The model was developed using the MSAS in the form of cubes. Various reports were generated from the cube using MSRS. The Reports were deployed and the Security settings for report viewing were done for the set of report users.

Responsibilities:

●Design the dimensional model to meet the gathered requirements

●Identify the source systems that include OLEDB, Excel and used transformations involving Merge Join, Sort, Derived Column, Slowly Changing Dimension etc.

●Developed truncate and load strategy for tables in the staging area for incremental data load and Scheduled the packages using windows batch scripts

●Developed the required cubes using analysis services and built the hierarchies in the cube dimensions. Configurations were implemented on the cube

●Design and develop the required reports which includes prompts, filters, hierarchies, highlighters for alerting the user and configurations in the report manager for web based access

●Develop test cases to test the developed artifacts to meet the business specifications

Contact this candidate