Re-normalization

Re-normalization is important in denormalized data models in the Endeca Server, as well as when analyzing multi-value attributes.

In a sample data set, Employees source records were de-normalized onto Transactions, as shown in the following example:

DimEmployee_FullName:	Tsvi Michael Reiter
DimEmployee_HireDate:	2005-07-01T04:00:00.000Z
DimEmployee_Title:	Sales Representative
FactSales_RecordSpec:	SO49122-2
FactSales_SalesAmount:	939.588

Incorrect

The following EQL code double-counts the tenure of Employees with multiple transactions:

RETURN AvgTenure AS
SELECT
   AVG(CURRENT_DATE - DimEmployee_HireDate) AS AvgTenure
GROUP BY DimEmployee_Title

Correct

In this example, you re-normalize each Employee, and then operate over them using FROM:

DEFINE Employees AS 
SELECT
   ARB(DimEmployee_HireDate) AS DimEmployee_HireDate,
   ARB(DimEmployee_Title) AS DimEmployee_Title
GROUP BY DimEmployee_EmployeeKey;

RETURN AvgTenure AS 
SELECT
   AVG(CURRENT_DATE - DimEmployee_HireDate) AS AvgTenure
FROM Employees 
GROUP BY DimEmployee_Title