Scott Carmody
Verified Expert in Engineering
Data Warehouse Architect and Developer
Naperville, IL, United States
Toptal member since June 18, 2020
Scott is a data warehouse architect and BI developer with 30 years of industry experience. He specializes in the Microsoft BI stack. He utilizes all aspects, including building data marts and data warehouses in SQL Server, ETL with SSIS, SSAS cubes, and creating reports and advanced analytics with SSRS and Power BI. Scott also has considerable cloud experience with Azure and has worked with Azure SQL Database and Azure Data Factory.
Portfolio
Experience
- Data Warehousing - 20 years
- Dimensional Modeling - 20 years
- SQL - 20 years
- SQL Server Analysis Services (SSAS) - 19 years
- SQL Server Reporting Services (SSRS) - 19 years
- SQL Server Integration Services (SSIS) - 14 years
- Microsoft Power BI - 4 years
- SQL Server 2016 - 3 years
Availability
Preferred Environment
MDX, Visual Studio, SQL Server Management Studio (SSMS), Windows 10, Data Visualization, Reporting, Data Engineering, Data Analytics
The most amazing...
...project I have worked on was an end-to-end BI solution for a multinational financial services company.
Work Experience
Business Intelligence Principal Engineer
Carhartt
- Enhanced and created data models crucial to the business, including point of sale.
- Handled Power BI report development, which required extensive use of DAX and data modeling concepts.
- Conducted Power BI training for internal Carhartt personnel.
- Developed new data model training for internal Carhartt personnel.
Microsoft Fabric Engineer | Power BI Expert
Three Five Two, Inc.
- Developed a conceptual and physical dimensional data model to be used in historical reporting.
- Created and implemented the dimensional model in Microsoft Fabric. Source data was pulled into the lakehouse using OneLake via Gen2 data flows and pipelines. Fact table pulls were designed for incremental refresh. Data was populated in the warehouse.
- Created Fabric capacity for the client; Researched cost analysis versus other approaches like Azure Synapse Analytics.
- Performed training and knowledge transfer of Fabric capabilities and led a walkthrough of the pieces developed with key stakeholders from the client.
Power BI Developer | Data Analyst
Trillium Beverages Pvt. Ltd.
- Developed a dataset from Neilsen survey data and other sources. Involved knowledge of Power Query to reverse-engineer some metrics that were not supplied.
- Merged queries in the Power Query editor to pass back to Microsoft Power BI.
- Developed Power BI reports from the dataset involving complex DAX to solve compound annual growth.
Senior Data Warehouse Architect | BI Developer
Callon Petroleum
- Built a SSAS Tabular cube from an existing dimensional model.
- Created multiple Power BI reports based on the SSAS cube.
- Developed data-profiling Power BI reports from a metadata mart holding Gartner-based profile table statistics (internal project). This involved showcasing data statistics and services to external clients.
Data Engineer
A-dec, Inc.
- Reworked existing SQL logic and routines for performance improvement.
- Captured EDW requirements related to an AX 2012 archiving project. Acted as the BI/data team liaison for this high-level project. Reworked ETL processes to capture the appropriate data row from prod and archive systems.
- Assisted HR with a UKG to Workday data migration project.
Senior Data Warehouse Architect | Power BI Developer
Georgia-Pacific
- Helped create Proof of Concept Power BI models for inventory projections, allowing the business to make better-informed decisions. This involved detailed knowledge of SQL, SSIS, and Power BI.
- Assisted in the development of Power BI reports, working directly with the business sponsor.
- Oversaw mentoring and training of GP personnel and mgmt.
Senior Data Warehouse Architect | BI Developer
Health Care Service Corporation
- Created a dimensional model for an enterprise data warehouse in SQL Server.
- Designed and developed a robust ETL solution using SSIS.
- Developed and maintained an Analysis Services Tabular cube.
Senior Data Warehouse Architect | Power BI Developer
Gifted Healthcare
- Designed and architected tables and stored procedures in SQL Server to serve as the source for Power BI reports.
- Created Power BI reports to assist in travel nurse billing, payroll reconciliation, client scorecard, etc. This involved in-depth knowledge of DAX, data modeling, and proper visualizations.
- Provided knowledge transfer and training for internal personnel.
Senior Data Warehouse Architect | BI Developer
Atlantic Broadband
- Created SSIS packages to connect to 3rd-party SOAP APIs to send and receive data.
- Handled the architecture of the new IVR data mart (Kimball dimensional modeling).
- Took charge of various reporting off of the ABB data warehouse.
SQL Server Analysis Services Developer
PepsiCo
- Incorporated dimension and fact changes to existing SSAS cubes.
- Integrated a corporate security model into the existing cube.
- Created a version of the existing cube on Azure Analysis Services.
- Built data build tool models to populate tables in Snowflake. These tables are being used to populate an Azure SSAS cube.
Power BI Developer
Toptal Client
- Served as a Power BI consultant for a large analytics and data science consulting company in South America.
- Created a Power BI report for their client. This was an iterative approach where the client was rapid prototyping design and functionality.
- Contributed to this project, which involved complex modeling, visualizations, and complex DAX coding.
Power BI Developer
Toptal Client
- Created multiple sales reports and dashboards in Power BI to help the company with sales and volume metrics, including market share. Multiple visualizations, slicers, bookmarks, and complex DAX coding were involved.
- Built the data model and necessary relationships for effective reporting.
- Managed the architectural design of the underlying SQL database for various initiatives within the company.
Senior Data Warehouse Architect | BI Specialist
BluBØX Security
- Solely created a data warehouse in Azure SQL Database reading from a transactional system. Developed SSIS ETL processes and successfully ported them to Azure Data Factory to service reports.
- Implemented Power BI as an analytics platform sourced from the company's data warehouse as well as all reporting needs. This involved porting the model, managing relationships, and creating visualizations for reports.
- Conducted knowledge transfer sessions among personnel.
Senior ETL Architect/Developer for Kirkland & Ellis (legal firm)
Request Technology
- Converted SSIS packages under SQL 2008 R2 to SQL 2016. Added new functionality when needed.
Senior BI Specialist for Plante Moran, Southfield, MI
Strive Consulting (contract)
- Assessed and triaged issues related to data movement and ETL among business processes within the firm. Required extensive use of SQL scripting and SSIS.
- Planned for data warehouse build-out.
Senior BI Specialist for Komatsu, Rolling Meadows, IL
Randstad Technologies (contract)
- Helped create over 180 Power BI reports and dashboards against SSAS tabular models as part of Komatsu’s New Parts System initiative program.
Senior ETL Developer for Reyes Holdings (Great Lakes Coca Cola Bottling)
Addison Group (contract)
- Supported new and existing SSIS development for the Coca Cola business unit of the beverage distribution company.
- Assisted with SSAS cube development.
Senior BI Specialist for CDW, Vernon Hills IL
Insight Global (contract)
- Oversaw all MDS (Master Data Services) and ETL development (SSIS) from the AS400 source system to the corporate DW in order to create a new categorization for products. Required extensive use of MDS and the MS BI stack of tools including SSIS.
Senior BI Specialist for Ventas, Chicago, IL; Merchant's Fleet Management, Rosemont, IL
Addison Group (contract)
- Managed all ETL development (SSIS) from the source financial system (Yardi) to the corporate DW. Also responsible for optimizing and developing MDX queries from several cubes used in reporting. Required extensive use of the MS BI stack of tools including SSIS, SSAS, and the MDX query language.
- Headed DW architecture and ETL development (SSIS). Created Data Dictionary for DW. Optimized several source queries. Responsible for production deployment procedures.
Senior BI Specialist
Aon
- Created, developed, and modified an entire BI solution to handle ad hoc requests and MDX report queries regarding comparative analysis among clients (brokerage firms) for McLagan Retail.
- Assisted in redesigning an optimal solution for the existing survey data warehouse. Helped evaluate in-memory solutions. Developed a robust ETL solution for large data loads using SSIS. Assisted with DBA tasks to host an acquired company in MS Azure.
- Architected a data warehouse according to Kimball best practices. Created robust ETL using SSIS, including email notification, auditing, and logging. Designed and developed a cube in SSAS. Created complex calculated member logic.
Senior BI Architect/Developer
SIMS Recycling (contract)
- Created a complete data warehouse and BI solution for inventory analysis. Required extensive use of the entire MS BI stack of tools including SSIS, SSAS, SSRS, and the MDX query language.
Senior BI Architect/Developer
Redbox (contract)
- Provided real-time strategic data and information to the loss prevention and games teams using a combination of T-SQL and OLAP cube queries. Created real-time alerts and reports which contributed to a reduction of fraud in game rentals from 17% to 2% and allowed for a nationwide rollout of games with an estimated $40 million increase in revenue.
- Assisted the marketing department with numerous initiatives including providing RFM (recency, frequency, and monetary) data to assist in strategic decision-making initiatives.
Senior ETL Architect/Developer for W. K. Kellogg Foundation, Battle Creek, MI
Information Technology Partners (contract)
- Managed ETL architecture using a combination of T-SQL stored procedures and SSIS, SSAS, and SSRS Reports with Dundas to create a corporate dashboard displaying trends of corporate contract, request, and grant information for a non-profit charitable foundation. Dramatically reduced the existing ETL time from a run time of around one hour to between 5-10 minutes.
Experience
Dashboard Creation
Redbox Game Rentals
Education
Bachelor's Degree in Computer Science
Siena College - Loudonville, NY
Skills
Libraries/APIs
Fabric
Tools
Microsoft Power BI, Microsoft Excel, Visual Studio, SSAS, Power BI Desktop
Languages
SQL, C#.NET, VBScript, Excel VBA, MDX
Paradigms
Dimensional Modeling, ETL, Business Intelligence (BI)
Storage
SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Services (SSRS), SQL Server 2016, Data Pipelines, SQL Server Management Studio (SSMS), Microsoft SQL Server, SQL Architecture, SSAS Tabular
Platforms
SharePoint, Azure
Other
Data Visualization, Multidimensional Expressions (MDX), Data Warehousing, Data Warehouse Design, Data Reporting, Reporting, Data Engineering, Data Analytics, Data Architecture, Dashboards, Reports, Interactive Dashboards, Documentation, Data Analysis, CSV, Business Analysis, Excel 365, DAX, APIs, Windows 10, Cisco MDS, Data Modeling
How to Work with Toptal
Toptal matches you directly with global industry experts from our network in hours—not weeks or months.
Share your needs
Choose your talent
Start your risk-free talent trial
Top talent is in high demand.
Start hiring