Joris Laperre
Verified Expert in Engineering
Database Developer
Bangkok, Thailand
Toptal member since January 27, 2022
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
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
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
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.
Principal Database Architect
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.
Principal Database Architect
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.
Senior Database Developer
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.
Experience
Analysis Services Cube Builder
Data Extract Solution for the NZ Ministry of Health
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
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 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.
Education
Master's Degree in Software Engineering
University of Manchester, Institute for Science and Technology - Manchester, UK
Bachelor's Degree in Electronics Engineering
University of Ghent - Ghent, Belgium
Skills
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
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