Joris Laperre

Developed a C# application to effortlessly create multi-dimensional SSAS cubes using dimensions, measures, calculations, and KPIs. The dimensional model can be entered through the UI or using a "database adapter": an XML document describing the model that can be developed as part of a customer implementation. Year-month-day dynamic partitioning and management of such partitions are supported out of the box, as well as automatic partition invalidation, type I and II slowly changing dimensions, parent-child dimensions, incremental dimension processing, date range expansion, and conversions (e.g., multi-currency), all requiring no coding. The product ships with an extensible library of 60+ MDX calculations in several categories: period-to-date values, moving averages, projections, ratios, parallel period values, etc. Projects can be deployed onto an SSAS server from the application or through a command-line utility.

Designed and implemented a regional ministry extracts solution for the New Zealand Ministry of Health within the scope of the South Island project.

The solution consists of an extract database built on top of the data warehouse, with a master data set for each of the five extract types. These extracts are populated from a layered set of views, separating the business logic of the source system from the mapping to the target system. Other components are a journal of data sent, responses received, queues for outgoing data (extract files), incoming files (the Ministry of Health response files), and write-backs to the source system. A client application, also developed by me, allows end-users to manage the entire process: create, validate and send extracts, receive and process response files, view file content, and query extract and response records. Administrators can build and test pre-submission validation rules and edit the mapping functions that translate coded values from the source to target systems—code is generated behind the scenes. At the end of this 12-month project, the solution replaced three failed attempts by two internal teams and an external vendor over a period of seven years.

Designed and implemented a continuous data warehouse feed solution based on SQL Server change data capture (CDC).

A graphical fact view builder (also developed by me) lets developers create complex de-normalized views for the data warehouse with zero coding. The SQL view and meta-data are then scripted for the online transaction processing (OLTP) database. A SQL Server Integration Services (SSIS) ETL package on the DW server retrieves the meta-data, creates tables with a structure derived from the source view, and dynamically generates another SSIS package to stream the initial data from the source database to the data warehouse for each new table. At the OLTP source, changes are tracked using CDC and translated into one or more changes for the DW. These changes are applied through another SSIS package that packs the data as XML, sends it down the wire, and merges the changes in the DW. Whereas traditional ETL solutions consist of hand-crafted packages, one for each DW table, this implementation allows new views to be added in minutes without any coding. The system is currently used for 125 views, some of the more complex ones consisting of over 50 joins.

I authored a set of scripts, command-line utilities, and Salesforce Data Loader configuration files to migrate data from a legacy residential real estate CRM application to a Salesforce/Propertybase implementation. The migration included accounts, contacts, tasks, emails, and three custom objects.

I took over where a previous in-house migration effort had failed or was at least incomplete. Unfortunately, the scripts for the initial migration were no longer available, so I started from scratch and based my work on the inspection of the already migrated data and input from the customer's team.

The project was completed successfully and on time.