Work and Training as a Data Engineer

Working as a Data Engineer as part of the Business Intelligence team was an exciting and insightful opportunity for me. I was initially surprised by the disconnect between the work I had done as part of my degree with the work expected of me in the field. In school, projects and assignments had you starting on a problem fresh, or sometimes with a small backstory, to solve a problem or manage x amount of data. In contrast, I found myself being thrown into an already operational and highly complex system where a large part of the initial learning curve was understanding the data structure and systems already in place.

Early on, when assigned a problem, I would first meet with my supervisor to ensure I had an understanding of the specific data I would be working with. Since there were several different data models used for various different reporting goals, you could have greatly varying results depending on how the same data source was broken down. Even working with an incorrect dimension table from within the correct dataset, your data could get broken down incorrectly and invalidate your report. I often sat in on meetings with upper management to expose me to how the reports were being used first hand and give context to the work I was doing.

After I had established a solid understanding of the data itself, I would then be given a more freedom to learn the platforms like Power BI to create reports and dashboards myself. This involved utilizing the company provided courses in DataCamp as well as reading documentation straight from the Microsoft website. You can see a sample of the courses I took below.

A bulk of my time was spent migrating the existing paginated reporting system in SSRS to Power BI paginated reporting. I worked with my supervisor on the first report or two, and then was given essentially free reign to tackle the remaining reports and create new ones as needed. This involved reading a lot of SQL in the SSRS repots, and translating the logic into DAX. I would often discover errors in the largely unmaintained SSRS reports, and have to create tickets for the BI IT team to remedy it. Despite having full access and the ability to adjust it myself, I was not allowed to directly modify the SSRS reports due to the way the different departments were allocated resources and the BI IT team essentially had jurisdiction over the older SQL based system.

Since I was leading the way on the Power BI paginated reporting, I also drafted several SOPs to document what I was doing and to help train others in the future. You can see some of the most basic examples below based on the formatting of the reports.

Summary of my first few months written as I was experiencing them

This year I've been given the privilege to work as a Data Engineer on Ortho Molecular Product's Business Intelligence Team.  Although I have worked relatively extensively with databases and SQL through my schooling, this was the first time I have gotten to work with "real world" data.

Having taken "Server Side Scripting" and "Web Server and Unix Administration" this past fall 2022, the basics and fundamental building blocks of databases and SQL were fresh in my mind.  The tables worked with in the classes consisted of 3 or 4 columns and maybe a dozen rows.  In the real world, the dimension tables I work with have a around a dozen columns and are millions of rows long.

This stark difference has resulted in a rapid evolution of my approaches to working with data.  I have to take into account other factors such as data refresh rate and learned how to use scripts or macros to filter out or correct data formatting errors.  In the real world, it is extremely inefficient or sometimes impossible to manually find discrepancies in dimension tables with millions of entries.  Additionally, real time refreshing of user accessed data sets is generally impractical.  This is especially important to consider when the data is stored at a remote location, as is the case with my employer.

Something entirely new to me this year was visualizing large amounts of data.  While working with the small datasets in my classes, data visualization consisted entirely of outputting the data to a table on a webpage or user terminal.  This is simply not possible when viewing large amounts of data.  In  my position, I have learned how to use tools such as Microsoft Power BI to visualize data in a meaningful way.

I created the Power BI report above, as a way to track and visualize usage of other reports accessible to employees across the company.  The goal of this project is to give those in the Business Intelligence team a way to identify and reduce unused or sparsely accessed reports.  Simply measuring the usage of each report is insufficient, as it is possible for the only viewer to be the developer of the report maintaining it.

Not only does the above report visualize the report usage in multiple ways, but it gives the user a way to easily filter the report to display the most relevant information to them.  As seen in the screenshot, selecting a row on the table adjusts the visuals to only display usage details from that report.  This dual usage allows broad usage trends across all data to be visualized on the same page as the filtered or specific reports.

As this was one of my first reports, an objective of the project was to practice visually formatting for aesthetic reasons.  Just about every possible way to modify the report visually (colors, text, rounded visual corners)

I have continued to become more comfortable with the database structure at Ortho Molecular Products.  My understanding of the measures and dimension tables are at the point where I can determine if the report I am building requires additional calculated measure during the planning phase.  I am comfortable writing my own measures, and have gotten significantly more reliable working within "best practices" framework.

I have spent a significant amount of time working within Power BI Paginated Report Builder.  These reports are sent out via weekly subscriptions, and all components must update automatically, not be "hard coded".  This experience has greatly impacted how I think about reports or measures when designing them, and has led my work to utilize concrete and well tested logic that requires no scheduled maintenance.  I utilize Matrix visuals to allow for changes within Sales Territories, active Salespeople within those territories, and rolling changes in tracked products, without breaking the format of the report.  The scope of the timeline within these paginated reports defaults to the current week of the current year, and is an adjustable parameter that the user can change to generate historic data.