Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Calculating Factors in ETL Process

  • 29 March 2013
  • Author: CraigLove
  • Number of views: 6500
  • 0 Comments

Most of us have gleefully used the GROUP BY function to perform summary operations such as SUM, AVERAGE, MAX and MIN on sets of values.  A typical example of this would be the following where the ETL developer needs to determine the sum of each Group’s ItemValue column.

Image of Table with Sum Data

Using a simple T-SQL query, we can find the answer:

Sum Final

 

However, how can determine the factor of a set of numbers?  This was the challenge I faced with an ETL project where the ultimate value was determined by a set of 1 to many adjustments.  For example, a payment due of $100 was to be adjusted to 90% of the original value.  That adjusted value would be adjusted again to 95% of the adjusted value.  Mathematically this could be expressed as:

$100 x 90% = $90   $90 x 95% = $85.50 

This can be expressed as a factor of the two adjustments:

$100 x (90% x 95%) = $85.50

Transact-SQL provides the following aggregate functions.  Note there is no Factor fuction!!

T-SQL Aggregates

The answer is to use natural log function LOG() and then the exponential EXP() function!  Here is a table similar to the one shown above:

Table with factor values

Here, using Excel, I have provided an example of the desired results.

Desired Factor Results

Finally, here is how you would perform the same action using T-SQL. 

Calculated Factors using T-SQL

 

Note that in SQL Server Integration Services, the expression function equivalents for natural log and exponential are LN() and EXP().

SSIS Derived Column with Natural Log

 

Happy ETLing!

Print
Categories: Analysis Services
Tags:
Rate this article:
No rating

CraigLoveCraigLove

Other posts by CraigLove

Please login or register to post comments.