cube.png

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.

chart1.png

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.

powerbi.png

PowerBI Prototyping

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.

etf.png

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.

Features include:

  • 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

  • Ad-hoc Reporting

  • Web-based, client-side reports in Visual Studio (RDLC)

Crystal Reports:

  • Existing report maintenance and updates

  • Web-based, client-side reports in Visual Studio

Tableau:

  • Server: viewing and loading reports

  • Desktop: report creation, maintenance, and updates

crystal.png

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)

  • Static files

  • Office documents (Excel, Access)

  • XML

  • JSON

  • SharePoint

  • 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

Pluralsight

  • Introduction to Data Warehousing and Business Intelligence

  • Analysis Services Fundamentals

  • Advanced Analysis Services

  • Understanding Machine Learning

LinkedIn Learning

  • Learning Path: Become a Business Intelligence Specialist