We provide IT Staff Augmentation Services!

Business Intelligence Architect/data Analyst/systems Analyst Resume

Bridgeport, CT

TECHNICAL SKILLS:

  • SQL Server 4.3 - 2012
  • 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 - 2010
  • SAS 6.12
  • SSAS, MSAS (SQL Server 2000), OLAP Services (SQL Server 7.0)
  • TM1
  • SAS MDDB
  • Gentia
  • Tableau, ProClarity, NovaView, PowerPivot and Excel Pivot Table front ends
  • SSIS, DTS
  • Data Manager
  • SSRS (SQL Server 2000 and 2005)
  • Business Objects 3.x - 4.x
  • Oracle Discoverer 4.1
  • Access 1.1 - 2010 Reports
  • ErWin
  • ErStudio
  • PowerDesigner
  • SQL, T-SQL, PL/SQL, PL/pgSQL
  • MDX
  • AMO, DSO (SQL Server 2000), ADOMD, ADOMD.Net
  • VB.Net (Visual Studio ), 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)
  • Windows, Unix Variants, Mac

PROFESSIONAL EXPERIENCE:

Business Intelligence Architect/Data Analyst/Systems Analyst

Confidential, Bridgeport, CT

Responsibilities:

  • Architected the Commercial Loan Origination Data Mart, complete from business requirements to data modeling and ETL specifications
  • Architecting a Transaction Data Warehouse solution to pull all disparate banking transaction source systems together (deposit accounts, loan account, trust, transfers, wires, credit cards, etc.)
  • Building 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, complete with business definitions and ETL specifications
  • Testing In-Memory tools, possibly for selection, for end-user analysis

Business Intelligence Architect/Data Analyst Consultant

Confidential, Trumbull, CT

Responsibilities:

  • 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

Confidential, New York, NY

Responsibilities:

  • 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

Confidential, Hartford, CT

Responsibilities:

  • 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

Confidential, New York, NY

Responsibilities:

  • 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

Confidential, Stamford, CT

Responsibilities:

  • 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

Confidential, Greenwich, CT

Responsibilities:

  • 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

Confidential, Hartford, CT

Responsibilities:

  • 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

Confidential, Greenwich, CT

Responsibilities:

  • Modeled a medium sized ( 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 Confidential, 5-8 dimensions) using Analysis Services against different fact subject areas in the target SQL Server warehouse

OLAP/SQL Developer

Confidential, Las Vegas, NV

Responsibilities:

  • Designed an extremely large Analysis Services OLAP cube 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

Confidential, Newport, RI

Responsibilities:

  • 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

Confidential, MA

Responsibilities:

  • 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
  • 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

Hire Now