Recently when loading a snapshot fact table I was required to populate one of the columns with the the most recent revenue generating activity from all activities. The catch? This information was stored in about 12 different fact tables. I was already storing the last revenue generating activity for each fact table in it’s own individual column, but now I needed to figure out how to take the greatest date from all 12 columns and populate the last revenue generating activity across all fact tables. Make sense? Fear not, I have pictures!

To simulate the problem lets pretend that we run an internet based company and we have a separate fact table for item purchases, gift cards purchases, and services. The first screenshot below shows how the last revenue generated activity can differ for each customer.

This screenshot is the desired result. Notice how the new column has taken the MAX date from the correct column for each customer!

So how did we get accomplish this goal in less than a minute?? Awesome question, hold on, we are almost there.

I decided to take advantage of one of the 194 functions offered in the Task Factory Advanced Derived Column transform (Greatest). Oh and this works for more than just dates.

The two screenshots below demonstrate just how easy it is to set this up. Thanks and enjoy!