OLAP Cubes and SQL Server Analysis Services
The company collected data about the events associated with an online community. This included details about the number of message board posts, the number of discussions read, and the total time spent online. Over a billion rows of data going back more than 15 years were available in the SQL Server relational database.
Working alone and as part of a development pair I built out a solution to de-normalize the data and populate a collection of OLAP cubes. The solution included significant ETL processes and complex querying and could be kicked off manually or via SQL Server Integration Services.
Board of Director’s Reports and Presentation
Each quarter a complex series of reports, charts, and key performance indicators were required for presentation to the company’s Board of Directors.
The effort included the generation and updating of more than 50 reports using relational SQL, Pivot Charts and other Excel tools, SSAS, and third-party data sources.
The Azure DevOps tools used to manage the department’s project management and ticketing system provided analytical data via web services. In order to capture these stats and report the results in an easy-to-understand manner PowerBI was used.
The prototype system included a series of PowerBI reports describing project management activities over the course of a year.
Exploring the Future
Built as one part of a larger master’s project, this online tool was designed to provide data management and query tools for data scientists exploring the “Monitoring the Future” survey data collected each year by the National Institute on Drug Abuse.
Data Dictionary management
Online SQL Query tool
Drag-and-drop querying interface
Key performance indicators
Data load and purging tools
Exporting to Excel or SPSS formats
Regression and Logistical Analysis
Video tutorial and sample data
Additional 3rd Party Reporting Tools
SQL Server Reporting Services:
Installation and Configuration (versions 2005 and 2008)
Server reports and data sets
Web-based, client-side reports in Visual Studio (RDLC)
Existing report maintenance and updates
Web-based, client-side reports in Visual Studio
Server: viewing and loading reports
Desktop: report creation, maintenance, and updates
Custom Reporting Infrastructure
The department had a demonstrated need to generate and manage dozens of operational and historical reports. In order to serve this need without putting an undue burden on the technical support team a new reporting infrastructure was needed.
This tool supports the ability to define a SQL query along with end-user adjustable parameters and included sharing and exporting features. It was also possible to define aggregate fields.
Extract, Transform, Load
ETL experience includes managing and manipulating data from:
Relational database management systems (SQL Server, Oracle)
Analysis and Integration services (SSAS, SSIS)
Web services (Rest, SOAP, WCF, XML via POST, Secured/Unsecured)
Office documents (Excel, Access)
Some minor exposure to BizTalk and Microsoft Dynamics
Data Science and Business Intelligence Education, Training, and Certifications
Cal State Fullerton (Graduate Level)
Business Databases: Design and Processing
Principals of Business Intelligence
Statistics for Data Science
Data Warehousing and Foundations of Business Intelligence
Business Data Transformation
Applied Business Regression Analysis
Data Mining for Business Applications
Forecasting for Analytical Decision Making
Microsoft Certified Professional
Certified Solutions Expert: Data Management and Analytics
Certified Solutions Associate: SQL Server 2012/2014
Certified Solutions Developer: App Builder
Certified IT Professional: Database Administrator 2008
Introduction to Data Warehousing and Business Intelligence
Analysis Services Fundamentals
Advanced Analysis Services
Understanding Machine Learning
Learning Path: Become a Business Intelligence Specialist