Data Sql Server

PHILLIP POIRIER

* ******** *****, **** *, Stamford, CT 06907

Phone: 203-***-****

E-Mail: ******@*********.***

PROFESSIONAL EXPERIENCE

Data Architect/Business Intelligence Oct 2015 – PRESENT

Preferred Mutual Insurance Company New Berlin, NY

Rebuilt Premium and Loss SSAS tabular cube (DAX intensive) from scratch as previous consultant-built cube did not allow earned and written premium measures to be queried alongside claim measures

Rewrote entire COBOL earned premium routine in T-SQL as SQL databases could not match mainframe systems of record without atomic level earned premium numbers

Created data dictionary and metadata processes to capture all SQL database tables and columns and use an Access front end for easy entry and reporting of business metadata and to pump back into SQL extended properties for storage

Designed multiple upstream checks in daily EDW ETL processing as new warehouse had none and was susceptible to orphaning and bad data due to lack of foreign key constraints and other typical unique indexing

Created PowerBI and Tableau Proof Of Concepts with goal being the replacement of existing SharePoint dashboards, company still deciding

Rearchitecting many dimension and fact tables in EDW as the current grain is not atomic enough for certain regulatory reporting and to ensure integrity with foreign key constrains, proper surrogate keys, etc.

Redesigning existing SSIS packages to populate new EDW design

Designing new strategies for testing changes to SQL/SSIS/SSAS databases, packages and cubes as all three environments (DEV/QA/PROD) are finally in place for all these important layers

Write ad-hoc complex MDX queries against cube for some business analyses that are not possible in vanilla Excel pivot and do not exist in current dashboard

Business Intelligence Architect/Data Analyst/Systems Analyst Consultant Sep 2013 – Dec 2013

Peoples United Bank Fulltime Jan 2014 – Aug 2015

Bridgeport, CT

Architected the Commercial Loan Origination Data Mart, complete from business requirements to data modeling and ETL specifications

Architected a Transaction Data Warehouse solution to pull all disparate banking transaction source systems together (deposit accounts, loan accounts and mortgages, trusts, transfers, wires, credit cards, etc.)

Built SSAS OLAP Cubes for various Warehouse subject areas for the business to do ad-hoc analysis

Documented all new data mart and warehouse entities in ErWin/PowerDesigner, complete with business definitions and ETL specifications

Created Proof Of Concept SSIS packages for all ETL specifications and data/model verification, ETL team reuses tasks and logic from these packages in DI (Business Objects Data Integrator, the company’s standard ETL tool)

Tested In-Memory tools (Tableau, QlikView), possibly for selection, for end-user analysis

Wrote an extensive Access (2010) VBA application to pull/push comprehensive metadata from PowerDesigner models

Business Intelligence Architect/Data Analyst Consultant Dec 2012 – Mar 2013

Unilever Trumbull, CT

Analyzed severely problematic data in firm’s data warehouse semantic layer to identify symptoms of the duplicated data causing OLAP cubes to fail building and reports providing erroneous data

Worked with team to correct data in the back end semantic layer

Wrote complex SQL against Teradata system tables to generate SQL scripts that would identify all problems with necessary data tables

Designed a new data mart that would provide clean data via views that would select data properly

Documented new data mart in ErWin

Data Modeler/Business Intelligence Architect Jun 2012 – Sep 2012

Bloomberg, LP New York, NY

Modeled a dimensional schema using ERStudio for various disparate text files for contact and organization information for investment, insurance, and other types of financial contacts

Created mappings for source-to-target ETL

Wrote T-SQL to load target schema using these mappings

Data Mart Architect/OLAP Consultant Jan 2012 – May 2012

XL Insurance Hartford, CT

Modeled an insurance star-schema data mart focusing on measures for policies, claims, quotes and submissions using ErWin

Extracted test data with SSIS for modeling purposes from multiple SQL Server 2008 source databases

Built complex SSAS 2008 cubes with multiple measure groups at various grains with very intricate calculations utilizing shell dimensions and advanced MDX scripts

Worked with Business Analysts to translate business specifications into new data marts

Director, Data Warehouse Architect/ETL/OLAP Apr 2009 – Jan 2012

Blackstone Group New York, NY

Architected an enterprise level financial data warehouse that was fed from multiple data sources (JDE ERP data, as well as many other existing and non-integrated investment and marketing systems), with three major subject areas in production (GL balances and transactions, financial consolidation/elimination data)

Performed source system analysis of all subject areas destined for warehouse and translated the results of the analysis into dim and fact tables for the warehouse star schemas

Created ErWin diagrams and star schemas in SQL Server 2005 for these subject areas as well as writing first versions of T-SQL and SSIS packages necessary to populate warehouse structures from the various sources

Architected full and incremental loads for warehouse, contingent upon source systems’ availability and data volatility, as well as coordinating with the warehouse processing window

Created and oversaw all unit testing of new subject areas coming into warehouse

Developed an SSAS 2005 proof of concept cube for financial reporting from data extracted from JDE using advanced structures to emulate shared members from other OLAP tools (TM1)

Developed automation to provide TM1 security based on JDE security tables

OLAP Consultant Jun 2008 – Nov 2008

Sempra Energy Trading, LLC Stamford, CT

Created SSAS 2005 cubes that had varying levels of grain combined into one, via the use of different measure groups at different levels, and MDX SCOPE statements using similarly named calculated members to provide transparency

Introduced Time Calculation Shell dimension for proof of concept to eliminate measure explosion and ease of front end use

Wrote VB.NET routines using AMO so a large amount of specific security roles and their corresponding metadata could be SQL table driven and automated

Wrote VB.NET AMO routines to dynamically set slice and filter statements of cubes’ partitions

Built Oracle tables and stored procedures to populate data mart specific data for these cubes

Business Intelligence Architect (OLAP Developer/Data Modeler) Feb 2005 – Jun 2008

US Tobacco, Inc. Greenwich, CT

Designed and implemented physical and logical OLTP database (SQL 2005) structure for the company’s new sales force automation system, including filegrouping and file sizing as well as SAN DASD requirements

Wrote a significant number of stored procedures to automate tasks for this new sales force system, as well as for reporting and ETL tasks into data warehouse

Migrated SQL 2000 Analysis Services dimensions and cubes to SQL 2005 dimensions, hierarchies and cubes

Restructured entire previous OLAP databases (AS 2000) to utilize MOLAP partitioning, formerly cubes with up to 500 million rows resided in single partition MOLAP cubes

Designed SQL Server indexes in moderately sized fact tables (130 million-500 million rows) to fit this environment and provide reasonable cube build times, as well as matching cube partitioning and parallel processing schema – processing time reduced from 16 hours to 9 hours

Rewrote a customized partition manager/processor using VB.NET and DSO since partition manager provided in MS toolkit didn’t accommodate the company’s hierarchy naming standards

Wrote and implemented customized QTD and YTD calculations that are faster than native ones in MDX

Built DTS packages to schedule these new .NET apps used for partition building, slicing, and processing

OLAP Developer/Data Modeling Consultant Aug 2004 – Dec 2004

Travelers Property And Casualty, Inc. Hartford, CT

Created multiple virtual and complex SQL schemas to build subject area specific Analysis Services cubes and to adhere to client’s desire not to build physical star schemas and datamarts, but to use existing Teradata tables with views instead

Designed Teradata partitioning indexes in moderately sized fact tables (6 million-90 million rows) to fit this environment and provide reasonable cube build times, as well as matching cube partitioning and parallel processing schema

Built cubes with extraordinary number of dimensions (15-29) for analytical flexibility via Microsoft-recommended tuning techniques

Programmed intricate calculated members

Taught staff members how to use named sets and calculated members against cubes in MDX for front end reporting

OLAP Developer/ Data Modeling Consultant Dec 2003 – Jun 2004

Nestlé Waters, Inc. Greenwich, CT

Modeled a medium sized (400-600 GIG) data warehouse's star schema from multiple, disjointed data sources after intensive research into these data sources and collaboration with client

Devised complex ETL rules and staging areas for cleansing data from these multiple sources

Built multiple medium sized OLAP cubes (30-60 GIG, 5-8 dimensions) using Analysis Services against different fact subject areas in the target SQL Server warehouse

OLAP/SQL Developer Nov 2002 – Dec 2003

Arclight Systems, LLC Las Vegas, NV

Designed an extremely large Analysis Services OLAP cube (250+ GIG, 10 dimensions) for rapid retail pharmaceutical analysis from a VLDB (2+ terabytes) with subsecond response times using radical new design idioms after working directly with Microsoft developers

Redesigned SQL fact views for this large OLAP schema with T-SQL stored procedures and fine-tuned many Analysis Services settings to accommodate faster processing and rebuilding times (entire cube can be aggregated in less than two days)

Built models for smaller, more focused cubes for datamart specific analysis

Rewrote how an entire application's MDX is spawned from web interface by placing variablized MDX statements in SQL tables, and calling them consistently with uniform T-SQL stored procedures, as opposed to previous inconsistent ASP pages

Debugged the entire Web/OLAP application for deployment, making suggestions for rewrite of SQL metadata tier, and fixing problematic legacy stored procedures and MDX

OLAP/SQL Consultant Jun 2001 – May 2002

Seafood Marketing Specialists Newport, RI

Designed all routines to pull and scrub multiple data sources out of multiple Astra Informix systems and centralize into one datamart for SQL Server 7.0/2000 and OLAP Services/Analysis Services using DTS and T-SQL

Modeled and coded the datamart’s fact and dim tables, relationships, views and stored procedures in SQL Server 7.0/2000, as well as the OLAP cubes and calculated members in the cubes themselves for analysis

Wrote customized monthly Access and Crystal reports against the datamart that use user-defined criteria

Created all scheduling jobs in SQL Server to automatically extract Astra data and build cubes

Sr. Consultant (CIBER, for both projects below, Leach & Garner and Staples) Jul 2000 – Apr 2001

Leach and Garner North Attelboro, MA

Completely designed a star schema of all dimension tables and fact table for an invoicing daily analysis data warehouse/mart in SQL Server 7, including primary and unique indices used for optimal SQL performance and adherence to business rules

Designed multiple OLAP cubes for the individual salesman to view their own data

Found problematic data and design in the source ERP (Adage) in SQL Server 7 and changed the design with foreign key constraints and unique keys to enforce referential integrity and quality of data being loaded in the Data Warehouse

Created file groups for the Data Warehouse fact and dim tables

Automated the daily load of the Data Warehouse and OLAP Cube Builds with stored procedures and DTS packages

Staples Corporation Framingham, MA

Completely designed a star schema of all dimension tables and fact partitions for a weekly analysis data warehouse/mart in SQL Server 7, including primary and unique indices used for optimal SQL performance and adherence to business rules

Designed OLAP cubes in OLAP Services using this star schema creating all hierarchies and calculated members, as well as raw MDX statements for specific reporting requirements used by front end reporting tools (Knosys Pro Clarity)

Created various SQL views to be used as different fact tables for different cubes

Designed and implemented a partitioning scheme for medium amounts of fact data (approximately 80-120 million rows) by using multiple fact tables residing on different SQL Server filegroups as well as the union-all view to have them act as one table

Designed and implemented the actual SQL Server filegroups and the specific files that compose these filegroups

Sr. Consultant (Data Dimensions, Inc.) 1998 – 2000

Bayer Corporation West Haven, CT

Built an Access database to track regional billable time and expenses per employee, client, and project as well as ad-hoc front end reporting and data entry for these time and expenses

Modeled and subsequently designed an Oracle database to track an entire company’s infrastructure and inventory as well as all client/server applications and how their components/tiers reside in the infrastructure, a system used for Y2K testing and tracking along with ongoing, daily inventory and change management

Developed proof of concept OLAP/DSS solutions using Oracle Discoverer, Express, and SQL Server 7 OLAP Services

Developed detailed Y2K test plans for all versions of Access

Independant Consultant 1998

Cliffwalk Manor Newport, RI

Built and implemented a highly customized Access application for a boutique hotel in Newport to take hotel reservations and post charges and payments to folios. This system allowed immediate room blocking upon reservation as well as customized crosstab views to mimic the old manual ledger maintained by hotel staff. Customized maid and parking reports as well as financial reporting. This system was used for three years until the sale of the property.

Sr. Developer 1996 – 1998

CVS Woonsocket, RI

Wrote a comprehensive front end application in Visual Basic that uses Oracle Objects for OLE to call PL/SQL procedures used to compose criteria for select statements that will populate task-specific datamarts for a drug compliance initiative

Developed an Access application using Access Basic and dynamically generated SQL to aggregate large volumes of data in crosstab formats for other tools (SAS) to do multivariate forecasting analysis

Wrote SAS procedures and programs in both Unix and Windows to forecast prescription counts and sales as well as other pharmacy statistics

Redesigned and programmed current Gentia applications to work with the new OLAP multi-dimensional database architecture of the new release

Rewrote a Third Party Application to use SAS MDDB for instantaneous analysis results of many key pharmacy indicators to eliminate very time consuming Business Objects queries against extremely large Oracle partitions

Rewrote a contract administration system in Access to provide better search and query capabilities

Planned the migration from Business Objects 3.1 to 4.0.x

Software Developer 1995 – 1996

Computer Associates, Inc. North Smithfield, RI

Completely designed and programmed an interface routine in Navision Financials to read an EDI purchase order and create multiple comprehensive sales order headers and line items (EDI 850) as well as the sales invoicing equivalent (EDI 810)

Modified the restrictive Navision Financial’s A/R Cash Application design to allow easier multiple invoice selection for payments from a single or multiple checks

Enhanced an existing Avista accounting database to handle client’s proprietary purchase order and inventory allocation methods

Programmed reports and created new data entry screens not in the Avista base package, including a customer and vendor letter writing program

Technical Support 1994 – 1995

Corporate Software, Inc. Canton, MA

Troubleshot procedural problems as well as design questions for both versions of Microsoft Access and made design suggestions for developers; mainly via phone but also fax

Tested errors as well as logging customers requests and comments

Technical Support 1993 – 1994

Data Plus, Inc. Concord, MA

Supported clients using back office hospitality accounting software in different environments, i.e., Unix or DOS, via phone, fax, and modem

Wrote documentation of operating procedures and upgrade enhancements

Developed a training database for new clients to use for learning and testing procedures using an empty Data Plus Database

Tested and documented new features of quarterly updates immediately after they had been programmed as well as suggesting new features based on clients' requests

EDUCATION

B.S., C.I.S. 1992 – 2000

Rhode Island College Providence, RI

Bachelor of Science, Computer Information Systems, obtained part time via evening classes while gaining experience with full time jobs during the day

B.M. 1983 – 1988

University Of Rhode Island Kingston, RI

Bachelor of Music Performance (Clarinet)

TECHNICAL SKILLS

Databases

SQL Server 4.3 – 2016

Oracle 7.3.x – 11.x

Teradata V2R5, 13.0

MySQL 5.6

PostgreSQL 9.2

Sybase ASE 6.0, IQ 15

Informix

Access 1.1 – 2013

SAS 6.12

OLAP Tools

SSAS 2005 – 2014 (multidimensional), SSAS 2014 (tabular), MSAS (SQL Server 2000), OLAP Services (SQL Server 7.0)

TM1

SAS MDDB

Gentia

ProClarity, NovaView, PowerPivot, Excel Pivot Table, PowerBI, Tableau, QlikView front ends

ETL Tools

SSIS, DTS

Data Manager

Reporting Tools

SSRS (SQL Server 2000 and 2005)

Business Objects 3.x – 4.x

Oracle Discoverer 4.1

Access 1.1 – 2013 Reports

Data Modeling Tools

ErWin

ErStudio

PowerDesigner

Programming Languages

SQL, T-SQL, PL/SQL, PL/pgSQL

MDX, DAX

AMO, DSO (SQL Server 2000), ADOMD, ADOMD.Net

VB.Net (Visual Studio 2005 – 2012), VB 4.0 – 6.0, VBA (mostly in Access), ADO.Net, Access Basic 1.1 - 2.0

PowerScript (PowerBuilder 4.0)

SAS Procs

GDL (Gentia Development Language)

Cobol (academic), C++ (academic), Java (academic), JavaScript (academic)

Operating Systems

Windows, Unix Variants, Mac

Contact this candidate