FDMEE / DMS (E-PBCS Cloud) - Loading Average Value using FDMEE / DMS (E-PBCS)

Hello,

This post is about FDMEE on Cloud - DMS.

Update 26-5-18: My colleague observed issues with this approach once the record goes beyond 40K.

Update 14-7-18: We can solve this issue by splitting the mapping into smaller chunks;

for ex: Account Mapping;
Earlier: - * to #SQL
Updated:- 1* to #SQL, 2* tp #SQL...

This will ensure that less number of records are processed at a time.

One of my colleague mentioned that he had a requirement to load the Average data using DMS / FDMEE on PBCS cloud. FDMEE in general doesn't provide this option out of the box. And on cloud we are handicap to use any kind of scripting to fulfill this requirement.
However, FDMEE Cloud does provide an option to play with data once it is imported in form of #SQL, where in we can write queries to modify the data once it is imported. So, you must have got the hint how we are going to achieve this requirement.

Before going to the solution lets see how to calculate average;

Avg (a,b) = (a + b) / 2 = (a / 2 + b / 2);
Avg (a. b, c) = (a + b + c) / 3 = (a / 3 + b / 3 + c / 3).

To calculate average we need the count of the number of elements, if we get the count we can calculate the average. This is what we are going to try and find using #SQL to calculate & load the average.

Here is the solution;
Source File:

Application Definition:

Here Count (LookUp Dimension will hold the count of the combinations)

Import Format:

Location:

Data Load Rule:

Data Load Mapping:

* to * Mapping for all the dimension except for Count & Version

Count Mapping:

Below query will return the count of records where the Account & Entity is matching

Source Records and Count

Acc1,Ent1,Working,USD,100  - Count 1
Acc2,Ent1,Working,USD,100 - Count 2
Acc1,Ent2,Working,USD,150 - Count 1
Acc2,Ent1,Working,USD,150 - Count 2
Acc2,Ent2,Working,USD,150 - Count 1

Version Mapping:

Workbench

File Output

Above is the entire solution, Hope you like it :)