Creating reporting structures is expensive. Especially when you consider that someone could just make reports on the source data instead. But it is worth the cost if you can make it easier for analysts to extract information or for programmers to create reporting applications.
One benefit of creating a reporting structure is that the data will more accurate, and thus more valuable, by removing inconsistencies and ambiguities.
Programming the ETL process, in any reporting solution, is complex and represents the majority of the expense. The good news is, there are specific transformations that you will likely use more than others, and learning how to program these will make creating any ETL process easier.
In this lesson you will learn to program the following common transformations:
- Renaming columns for legibility
- Splitting and Merging columns
- Converting data types for consistency
- Reformatting data to make it more readable
- Combining Data from Multiple Tables
- Redefining nullable values for enhanced reporting
- Creating Date Lookup Tables
- Connecting foreign key to surrogate key
Note: Microsoft is the owner of the content and video in this blog post.