Joris Laperre, Developer in Bangkok, Thailand
Joris is available for hire
Hire Joris

Joris Laperre

Verified Expert  in Engineering

Database Developer

Bangkok, Thailand

Toptal member since January 27, 2022

Bio

Joris is a passionate problem solver with a wealth of experience in relational database design and development, and .NET coding. He was the principal database architect for Microsoft's extensive enterprise healthcare system for several years. More recently, he has been building business intelligence solutions focusing on metadata-driven solution generation. Joris prides himself on delivering exceptional quality work at record speed.

Portfolio

Freelance
Business Intelligence (BI), SQL, C#, MongoDB, JSON, BSON, NoSQL...
Orion Health
Microsoft SQL Server, C#, Database Design, SQL, Database Modeling, ETL...
Microsoft
Microsoft SQL Server, erwin Data Modeler, Data Modeling, Dynamic SQL, XML...

Experience

  • OLTP - 20 years
  • Microsoft SQL Server - 20 years
  • Database Performance - 18 years
  • Database Modeling - 18 years
  • C# - 15 years
  • Data Warehousing - 8 years
  • Business Intelligence (BI) - 8 years
  • ETL - 8 years

Availability

Part-time

Preferred Environment

Windows, Microsoft SQL Server, C#, Database Modeling, Business Intelligence (BI), Data Visualization, ETL, Data Warehousing, OLTP, Databases

The most amazing...

...accomplishment to date was a reporting project I single-handedly designed and implemented in 11 months, where three different teams had failed over seven years.

Work Experience

Senior Database Engineer and Developer

2021 - PRESENT
Freelance
  • Developed a Cube Builder for multidimensional SSAS enabling users to create complex cubes with zero coding. Included built-in calendar; parent-child and type I and II slowly-changing dimensions; multi-currency support; automated dynamic partitioning.
  • Built an extensible library of 60 calculated measures for the Cube Builder, allowing a user to generate complex MDX expressions without coding for calculated measures, such as period to date, rolling averages, projections, and growth.
  • Created an infrastructure to facilitate migrating the SQL Server to MongoDB. An input JSON document describing the structure generates an extended JSON file for mongoimport, the SQL select for it, a JSON validation schema, and a set of C# classes.
  • Ingested 17 million XML-based radiology reports from a legacy database as HTML in a new system. Wrote the conversion app, capable of running dozens of sessions side-by-side during the 7 day run-time. Also created a report viewer for QA purposes.
Technologies: Business Intelligence (BI), SQL, C#, MongoDB, JSON, BSON, NoSQL, Multidimensional Expressions (MDX), MDX, Data Modeling, Dynamic SQL, XML, Microsoft Power BI, SQL Server Reporting Services (SSRS), Data Visualization, ETL, Data Warehousing, OLTP, Code Generators, SQL Server Analysis Services (SSAS), Database Design, Database Performance, C#.NET WinForms, SQL Server Integration Services (SSIS), Databases, SQL Stored Procedures, .NET, Data Architecture, Database Architecture, SQL Server DBA, Relational Databases, Architecture, Data Engineering, Dashboards

Principal Database Architect

2013 - 2021
Orion Health
  • Took responsibility for the 4000-table design of the Orion Health Enterprise product. Modeled several subject areas myself, led developer and work design meetings, and reviewed minor changes. Applied changes to the Erwin model and scripted changes.
  • Oversaw query performance for the product. Authored the most critical queries myself and reviewed all others. Educated senior developers on how to read execution plans and evaluate performance. Developed tools for the services teams in the field.
  • Designed and implemented a near real-time data warehouse (DW) solution with several components, such as change tracking (CDC), a graphical fact view builder, and a metadata-driven ETL solution to populate fact tables with zero coding required.
  • Developed and implemented a versioning framework to store historical row versions as compressed XML using autogenerated database triggers. A UI lets developers create hierarchies of related changes and generate data retrieval stored procedures.
  • Conceived and implemented an asynchronous query infrastructure. It allowed any regular database query to be executed asynchronously with the first page returned to the caller immediately and the subsequent pages retrieved from the cache.
  • Designed and implemented the enterprise database manager to deploy and upgrade Orion Health databases—OLTP, DW, and Monitoring—deploying and scheduling SSIS packages. A complete installation could be performed in minutes and without any scripting.
  • Created and implemented the New Zealand Ministry of Health's regional ministry extracts solution. Completed it within 11 months, after three failed attempts over the past seven years by two internal teams and an external vendor.
Technologies: Microsoft SQL Server, C#, Database Design, SQL, Database Modeling, ETL, Database Performance, SQL Server Integration Services (SSIS), Data Modeling, erwin Data Modeler, Dynamic SQL, XML, Microsoft Team Foundation Server, SQL Server Reporting Services (SSRS), Data Warehousing, OLTP, VB.NET, Code Generators, SQL Server Analysis Services (SSAS), C#.NET WinForms, Databases, SQL Stored Procedures, .NET, Data Architecture, Database Architecture, SQL Server DBA, Relational Databases, Architecture, Data Engineering, Orion

Principal Database Architect

2007 - 2013
Microsoft
  • Worked on the 4,000-table design of the Microsoft Amalga Hospital Information System (HIS) product, modeling several subject areas, leading design meetings, reviewing and scripting changes, and applying them to the Erwin model.
  • Took charge of query performance for the product, authored the most critical queries, and reviewed others.
  • Educated senior developers on how to read execution plans and evaluate performance and developed tools for the services teams in the field.
  • Designed and implemented data load tools, including a template editor and an upload tool for customer sites that supported both Excel and SQL staging database uploads. Templates were version-controlled, and documentation was auto-generated.
  • Created and implemented the query plan cache viewer to query the plan cache by several metrics, identify potential problems, and generate plan guides. This tool became invaluable during performance troubleshooting on customer sites.
Technologies: Microsoft SQL Server, erwin Data Modeler, Data Modeling, Dynamic SQL, XML, Microsoft Team Foundation Server, OLTP, VB.NET, Code Generators, SQL, Database Design, Database Performance, C#.NET WinForms, SQL Server Integration Services (SSIS), C#, Databases, SQL Stored Procedures, .NET, Data Architecture, Database Architecture, SQL Server DBA, Relational Databases, Architecture, Data Engineering, Microsoft Excel

Senior Database Developer

2000 - 2007
Global Care Solutions
  • Took responsibility for the 4000-table design of the Hospital 2000 product. Modeled several subject areas myself, led developer and work design meetings, and reviewed minor changes. Applied changes to the Erwin model and scripted changes.
  • Took charge of query performance for the product. Authored the most critical queries myself and reviewed all others. Educated senior developers on how to read execution plans and evaluate performance. Developed tools for the services teams in the field.
  • Oversaw and took responsibility for database migration between software builds. Developed web-based tools to automate the upgrade scripts, monitor database performance, and publish results analysis.
  • Implemented the infrastructure code and database design to support multiple languages, code pages and calendars, software distribution, and automatic upgrades.
  • Designed and developed the human resources and payroll application as part of the Hospital 2000 software suite. The initial implementation in Microsoft Access was later ported to Visual Basic.
Technologies: SQL, Microsoft SQL Server, Data Modeling, erwin Data Modeler, Microsoft Access, OLTP, Database Performance, Databases, SQL Stored Procedures, .NET, Data Architecture, Database Architecture, SQL Server DBA, Relational Databases, Architecture, Data Engineering

Analysis Services Cube Builder

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.

Data Extract Solution for the NZ Ministry of Health

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.

Continuous Data Warehouse Feed

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.

Migration from Legacy Application to Salesforce

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.
1991 - 1992

Master's Degree in Software Engineering

University of Manchester, Institute for Science and Technology - Manchester, UK

1987 - 1991

Bachelor's Degree in Electronics Engineering

University of Ghent - Ghent, Belgium

Tools

C#.NET WinForms, Microsoft Excel, Microsoft Team Foundation Server, Microsoft Access, Salesforce Dataloader, Microsoft Power BI

Languages

C#, VB.NET, SQL, XML, MDX

Frameworks

.NET

Paradigms

Business Intelligence (BI), ETL, Database Design

Storage

Microsoft SQL Server, Database Modeling, OLTP, SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), Database Performance, Dynamic SQL, Databases, SQL Stored Procedures, Database Architecture, SQL Server DBA, Relational Databases, SQL Server Reporting Services (SSRS), MongoDB, JSON, NoSQL

Platforms

Salesforce, Salesforce SOQL/SOSL

Other

Data Warehousing, Code Generators, Data Modeling, erwin Data Modeler, CDC, Change Data Capture, Data Architecture, Architecture, Data Engineering, Data Migration, Data Visualization, Multidimensional Expressions (MDX), Migration, BSON, Dashboards, Orion

Collaboration That Works

How to Work with Toptal

Toptal matches you directly with global industry experts from our network in hours—not weeks or months.

1

Share your needs

Discuss your requirements and refine your scope in a call with a Toptal domain expert.
2

Choose your talent

Get a short list of expertly matched talent within 24 hours to review, interview, and choose from.
3

Start your risk-free talent trial

Work with your chosen talent on a trial basis for up to two weeks. Pay only if you decide to hire them.

Top talent is in high demand.

Start hiring