Sql Server Data

Tina Lee

Summary:

Over * years’ work experience in various domains like Financial Services, Insurance, and Academic Research, focusing on MS SQL Server 2008/2012, T-SQL and corresponding BI tools (SSIS, SSRS, SSAS).

Experience in analyzing and defining data structure using data modeling which includes ER diagram, star and snowflake schemas.

Expertise in creating tables, constraints, views, indexes, stored procedures, user-defined functions, triggers using T-SQL.

Experience in SQL query tuning using query optimizer, database engine tuning advisor, execution plan, SQL server profiler and DMVs.

Experience in designing dimensions and fact tables according to Kimball’s DW architecture, and implementing data warehouse and data mart with star or snowflake schema.

Expertise in understanding of OLTP/OLAP systems, and OLAP operators for cubes.

Expertise in validating and extracting data from OLTP systems and other non-relational sources, loading them into OLAP systems after cleansing and transformation by designing and implementing ETL processes.

Experience in using SSIS 2008/2012 to implement maintenance of slowly changing dimensions for historical data in data warehouse.

Experience in debugging SSIS packages using breakpoints, data viewer, and error outputs, monitoring and troubleshooting during production execution with logging information.

Experience in performance tuning using error and event handling capabilities, checkpoints and parallel execution in SSIS packages.

Experience in designing and implementing cubes using SSAS 2008/2012, defining hierarchies, attribute relationships, calculations, KPIs, and perspectives.

Experience in performing analytics through Browser of SSAS 2008/2012 in either Excel PivotTable or MDX queries, generating reports from cubes using Power View.

Experience in generating parameterized, drill down, drill through, chart, dashboard, etc. reports using different reporting tools available in SSRS 2008/2012, and PowerPivot.

Expertise in content management of published reports through the interface of report manager or SharePoint reports library.

Experience in identifying business needs and current operational procedures, defining and documenting functional specifications and levels of system access.

Knowledge of Unified Modeling Language for analyzing object oriented information systems.

Education:

MS in Information Technology Management

Skillset:

MS SQL Server 2008/2012/2014, T-SQL, SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), SQL Server Analysis Services (SSAS), MS Access, MS Visio, PowerPivot, SAS 9.3, Java, Weka 3.7, R 3.1.2, MATLAB R2010, Visual Paradigm for UML CE 11.0, Cloudera CentOS 6.2 on Linux.

Projects Summary:

Elsevier, Bridgewater, NJ 09/2013 – Present

MS SQL Server/SSIS/ETL Developer

Elsevier is a world-leading provider of web-based, digital solutions for science, health and technology professionals. This project was to create Enterprise Data Warehouse (EDW) in MS SQL Server and implement ETL processes to extract data from OLTP systems to the staging database, load into EDW after cleansing. In addition, it delivers experiments of emerging big data technology.

Responsibilities:

Gathered business requirements, design of the data sourcing and data flows, and did data quality analysis.

Worked in conjunction with the data warehouse architect to identify granularity of fact tables, conformed dimensions, and role-playing dimensions. Applied naming convention when implementing data warehouse.

Wrote primary key, foreign key, check and other applied constraints, and tuned performance of data warehouse by writing views, indexes, stored procedures and functions using T-SQL.

Designed high level ETL architecture for overall data migration from OLTP systems to EDW.

Successfully integrated data from multiple sources like Oracle, MS Access, Excel to staging database, loaded into destination after applying necessary transforms in SSIS 2012 such as Lookup, Derived Column, Union All, OLE DB Command, Merge Join etc.

Involved in text mining using Term Extraction and Term Lookup transforms.

Worked on SSIS 2012 to implement maintenance of slowly changing dimensions in order to track historical data.

Debugged packages during development by checking execution results, enabling breakpoints and data viewers, using error outputs in data flow task.

Tuned performance of SSIS packages via good logical design, knowledge of transform types, and parallel execution if possible.

Dynamic deployed the SSIS packages to SSIS catalog with parameters and various environments.

Monitored and troubleshot the execution of packages by reviewing reports in SSIS catalog, querying SSISDB database through catalog views.

Understood data storage architecture (the HDFS) of Apache Hadoop, and hands-on HBase, Hive, MapReduce in Cloudera demo platform.

Environment: MS SQL server 2012, T-SQL, SQL Server Management Studio (SSMS), SQL Server Integration Services (SSIS), Cloudera CentOS 6.2 on Linux.

State Farm Insurance, Dallas, TX 04/2012 – 07/2013

MS SQL Server/SSAS/SSRS/BI Developer

State Farm is the largest insurer of autos and properties. It also supports life, health insurance, and acts as nontraditional financial institution by offering retail banking and mutual funds. This project is responsible for creating data marts/cubes to support policy and claim analytics, and generate custom reports.

Responsibilities:

Analyzed information needs and determined analytical requirements for data processing solutions.

Developed clear definition of data elements, and captured metadata for data elements.

Designed cubes with star and snowflake schemas (logical data modeling) using SSAS 2012.

Defined hierarchies and attribute relationships within dimensions in order to improve processing performance of cubes with the help of SSAS 2012.

Extended the capabilities of cubes by defining dimension usage, calculations, KPIs, and perspectives.

Implemented SSAS Cubes, and performed data mining through Excel PivotTable with different perspectives.

Integrated SSRS 2012 with SharePoint Server 2010 by configuring a report server to run in SharePoint integrated mode.

Designed and developed reports using reporting tools available in SSRS 2012 like report designer, report builder, Excel PowerPivot, and delivered to users in HTML, CSV, XML, PDF, Excel, Word format.

Generated parameterized, drill down, dynamic matrix, linked, and dashboard reports on a regular basis, and assisted end users with development of ad hoc reports.

Performed activities like organizing, subscribing, configuring for end user access of published reports using SharePoint reports library.

Environment: MS SQL server 2012, SQL Server Management Studio (SSMS), SQL Server Analysis Services (SSAS), SQL Server Reporting Services (SSRS), SharePoint, MS Excel, PowerPivot.

Citi Group, Warren, NJ 01/2010 – 03/2012

MS SQL Server/SSIS/SSRS/ETL Developer

Citigroup is the largest financial service network in the world. It supports industry solutions, investment management, retail banking. This project was to maintain underlying data warehouse of financial services system, to execute data integration on a regular basis, and to generate reports according to users.

Responsibilities:

Created variables, stored procedures, triggers, user-defined functions, and views for handling business logic and functionality of various modules.

Involved in data element mapping between ER diagram for source data and dimensional fact model for data warehouse, and implemented this process using inner join, left or right join.

Designed and implemented complex SSIS packages to migrate data from multiple data sources.

Worked on various tasks like Data Flow Task, Execute SQL Task, Execute Package Task, Send Mail Task, Containers, File System Task, and Data Profiling Task.

Widely used SSIS transforms such as Derived Column, Lookup, Fuzzy Lookup, Data Conversion, Conditional Split, Script Component and Aggregate while loading data into destination.

Provided custom logging, auditing and error handling for ETL processing.

Created configuration files to dynamic deploy the SSIS packages across all environments.

Developed auto generated system using SQL Server Agent to schedule the packages.

Created parameterized, drill down, chart, and matrix reports using SSRS 2008.

Generated daily, weekly, monthly, quarterly and annually reports for the managers.

Identified the database tables for defining the queries and defined datasets for report generation.

Administered interface to organize reports and data sources, to schedule report execution and delivery, and to track reporting history using SSRS 2008.

Environment: MS SQL server 2008, SQL Server Management Studio (SSMS), SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), T-SQL, MS Excel.

Geico Insurance, Houston, TX 02/2009 – 11/2009

MS SQL Server (T-SQL)/ETL Developer

Geico Government Employees Insurance Company is one of the biggest insurance company in USA. It supports life insurance, auto insurance, and property insurance. The objective of the project is to help the marketing department of policies to devise a strategy towards direct sales, agency sales and brokerage company sales and to build data marts for the insurance policies.

Responsibilities:

Involved in development phase meeting for analyzing business processes and requirements.

Interacted with business users and analysts, gathered and documented the technical and business metadata.

Involved in designing star schema (Logical model) for the new data marts.

Designed database, tables and views structure of the new data marts, and data mapping source data into database objects.

Successfully transferred old data from various sources like flat files, MS Access, and Excel into MS SQL Server 2008 using SSIS packages.

Created complex stored procedures and functions for integrity checks of transferred data.

Performed unit testing and tuned T-SQL statements using indexes and stored procedures.

Helped provide end users with the ability to access and utilize the data stored in the data marts.

Developed data access queries and scripts using T-SQL Query to run in production on a regular basis and assisted end users in development of complex Ad Hoc queries.

Responsible for query optimization using MS SQL tools such as Query Analyzer, SQL Profiler, index tuning wizard.

Environment: MS SQL Server 2008, MS SQL Server Integration Services (SSIS), MS Access 2007, T-SQL, MS Excel, SQL Query Analyzer, MS SQL Profiler.

Shanghai ZhonghuiYida, Shanghai, China 03/2008 – 12/2008

Business Analyst

Shanghai ZhonghuiYida is a subsidiary of China Foreign Exchange Trade Center. They provide value added services for interbank market, deliver transactional management systems for bonds and foreign exchange to meet business needs and growing regulatory requirements. The project was to develop foreign exchange management system for local banks, performing real time trading management, Profit/Loss analysis, risk management, and to generate scheduled reports for clients.

Responsibilities:

Consulted with banks to gather, understand their business needs, documented business specifications in order to design functionality of management system, and to define rules of data sourcing.

Documented functional specifications by translating business needs into developer-ready, testable requirements, was in charge of quality inspection for all requirements.

Created conceptual and logical data models using MS Visio 2003 to fit business requirements.

Involved in development phase through discussing possible technical solutions with development team.

Worked with the project team to determine appropriate test plans, deployment plans and supports.

Tracked and fully documented updates for business and functional specifications, wrote detailed procedures for permanent records and for use in training.

Contact this candidate