As more and more companies go to the cloud, build data warehouse solutions, and become more data-centric, we as BI Developers and Data Engineers will find ourselves migrating reports to pull data from new systems, rather than old, deprecated ones. Take a healthcare example: Say the healthcare company you work for runs their electronic medical records on Epic software, but they recently purchased several other hospitals, some of which use different software. To report at an enterprise level, they build a data warehouse solution to bring all the data from these systems to one place. Now, instead of sourcing reports out of all the systems separately, we want to pull all of the data from the data warehouse.
In this series of blog posts, I will walk through the process / some tips and tricks that I use when migrating a report from an old system to a new system. The main phases of this process are:
The first phase of this process should always be requirements gathering. The better your requirements, the smoother the rest of the process will go. I always like to start this process with a kickoff session, where I invite several different people who are all stakeholders in the success of this report migration:
- Business Users – Who are the business users who are going to be using this report regularly?
- Product / Area owners – is there a product owner who specializes in this subject matter? This person is usually an intermediary between the business owner and the BI team.
- BI Developer – If you aren’t the BI Developer yourself, you should definitely include whoever is going to be doing the front end of the report.
- Source System Data Specialist – This is rare to have, but if you do have someone like this to invite, it will definitely help!
In the kickoff session, I always try to discuss and accomplish the following:
- Timeline – roughly what is the requirement from a timeline perspective? How long will it take? Are there any hard deadlines?
- Functional explanation – get the business to explain what the report is, why they use it, and what decisions they make based off of it. The better you can understand the business function behind the report, the easier it will be to understand the data.
- What are the different views in the report? Are there multiple tabs? One Tab? Are there aggregations? Have the business walk through the report and explain it.
- What is the time period the report shows? Is it just today’s data? Is it a rolling year? Is it monthly? Yearly?
- What level is the data at? Is it aggregated at a month level? What is the lowest granularity of the report? For example: In a report about financial transactions, is the report at the item level (eg – each line will represent one item that rolls up to a single ‘order’)? Or is it at the order level?
- Parameters and filters: Are the parameters passed into the report? What filters are used in the report? These are key data points you need to make sure are accurate.
- Schedule: How often does the report need to refresh? Daily, hourly, weekly? This will determine the schedule of the ETL’s, and if there are dependencies to your ETL’s, you will also have to take these into consideration.
After the kickoff, you will have a pretty solid understanding of the report. At this point, there are some other considerations I like to explore and bring up if they seem relevant:
- Is the report definition still valid? For example, if the report was built 15 years ago, it is very likely that some things have majorly changed in the interim. Business users are often hesitant about changing reports, but it can’t hurt to ask the question.
- Is all the data in the old report still used? We don’t want to be sending / including anything extraneous. Use this as an opportunity to trim things down.
Next comes documenting all of your findings. By the end of the requirements gathering phase, I always like to have two documents in hand.
The first is a design document. Essentially, this document should consist of all the main points you gathered in the kickoff session. Keep in mind that this should be a working document – You can have the business ‘sign it off’ in the beginning, but I always keep a ‘key decisions’ section that I update if we make any changes along the way, with a description of why the changes were made. This will allow you to keep a clear history of how the report changed over time and can always be referred to if you get any questions.
Next is a data contract / mapping document. The data contract should be given to you by the business / product manager. The contract should contain a list of all the required fields, their data types, the level of the data, and the definitions of the fields. If they have mappings to old systems (tables / columns / etc), these should be provided as well. If you cannot get a data contract, you may have to revert to reverse engineering the legacy report. If this is the case, make sure to tack on extra time to your estimates!
Here is an example of what your report mapping could look like:
Note: You can always switch the source system to be on the left, and the target system on the right, it is all a matter of preference. The above is better if you might have multiple source systems going to one target system / report. But at the end of the day, feel free to pick your poison!
With your data contract in hand, you can begin the crucial exercise of gap analysis, which is essentially mapping the fields in the data contract to the new source system. Do you have all the required fields at the required granularity? Are you missing anything? If so, you will need to work with the business to either find out how to source the data, or move forward without that data. Will you need to write / modify any of the ETL to support this new report? The gap analysis is important, because if there is a lot of work involved in procuring the required data for the report, the whole process could take longer, and it is vital to set expectations with the business.
By the end of this phase, you should have your documents completed and agreed to by the business / product owner. Of course things are likely to change, but it is always a good idea to have signed off documentation, so that if things do change, you can explain why. Once you are set with your signed off documentation, you are ready for phase 2: Building the report!
I have included below some document templates to get started. Enjoy!