bermudalamb/strataspheredatamanagement
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
strataspheredatamanagement/tests/Strata.Stratasphere.Biz.UnitTests/Parser/DirectedAcyclicGraphTests.cs
T
Thom Lamb 1e042cc5e8 feat: initial commit
2026-06-23 11:26:55 -05:00

862 lines
30 KiB
C#
Raw Permalink Blame History

using Newtonsoft.Json;
using NUnit.Framework;
using Strata.Stratasphere.Biz.DataManagement.Models;
using Strata.Stratasphere.Biz.Parser;
using System;
using System.Collections.Generic;
namespace Strata.Stratasphere.Biz.UnitTests.Parser
{
[TestFixture]
public class DirectedAcyclicGraphTests
{
[Test]
public void AddEdgesWithCycles()
{
var graph = new DirectedAcyclicGraph<int>(new[] { 1, 2, 3, 4 });
Assert.DoesNotThrow(() => graph.AddEdge(1, 2));
Assert.DoesNotThrow(() => graph.AddEdge(2, 3));
Assert.DoesNotThrow(() => graph.AddEdge(3, 4));
Assert.DoesNotThrow(() => graph.AddEdge(3, 2));
Assert.That(() => graph.IsCyclicTest(),
Throws.TypeOf<InvalidOperationException>()
.With.Message.EqualTo("Cannot add this edge 3->2 because it would create a cycle"));
}
[Test]
public void AddEdgesWithoutCycles()
{
var graph = new DirectedAcyclicGraph<int>(new[] { 1, 2, 3, 4 });
Assert.DoesNotThrow(() => graph.AddEdge(1, 2));
Assert.DoesNotThrow(() => graph.AddEdge(2, 3));
Assert.DoesNotThrow(() => graph.AddEdge(3, 4));
Assert.DoesNotThrow(() => graph.IsCyclicTest());
}
[Test]
public void AddFullDAGWithoutCycles()
{
var graph = new DirectedAcyclicGraph<int>(new[] { 1, 2, 3, 4, 5, 6, 7, 8 });
Assert.DoesNotThrow(() => graph.AddEdge(1, 2));
Assert.DoesNotThrow(() => graph.AddEdge(3, 4));
Assert.DoesNotThrow(() => graph.AddEdge(2, 5));
Assert.DoesNotThrow(() => graph.AddEdge(5, 6));
Assert.DoesNotThrow(() => graph.AddEdge(5, 7));
Assert.DoesNotThrow(() => graph.AddEdge(6, 7));
Assert.DoesNotThrow(() => graph.AddEdge(7, 8));
Assert.DoesNotThrow(() => graph.AddEdge(4, 1));
Assert.DoesNotThrow(() => graph.IsCyclicTest());
}
[Test]
public void AddEdgeWithUnknownTargetNode()
{
var graph = new DirectedAcyclicGraph<int>(new[] { 1, 2 });
Assert.That(() => graph.AddEdge(1, 3),
Throws.TypeOf<InvalidOperationException>()
.With.Message.EqualTo("Node 3 not found"));
}
[Test]
public void AddEdgeWithUnknownSourceNode()
{
var graph = new DirectedAcyclicGraph<int>(new[] { 1, 2 });
Assert.That(() => graph.AddEdge(3, 2),
Throws.TypeOf<InvalidOperationException>()
.With.Message.EqualTo("Node 3 not found"));
}
[Test]
public void GetTopologicallyOrderedNodes()
{
var graph = new DirectedAcyclicGraph<int>(new[] { 1, 2, 3, 4, 5, 6, 7, 8 });
graph.AddEdge(1, 3);
graph.AddEdge(2, 3);
graph.AddEdge(3, 4);
graph.AddEdge(4, 8);
graph.AddEdge(4, 5);
graph.AddEdge(2, 6);
graph.AddEdge(5, 6);
graph.AddEdge(6, 7);
Assert.DoesNotThrow(() => graph.IsCyclicTest());
var topOrdered = graph.TopologicalSort();
CollectionAssert.AreEqual(new[] { 1, 2, 3, 4, 8, 5, 6, 7 }, topOrdered);
}
[Test]
public void GenerationCalc_EmptyGraph()
{
var graph = new DirectedAcyclicGraph<int>(new int[0]);
var generationLookup = graph.GetNodeGenerationLookup();
CollectionAssert.AreEqual(new Dictionary<int, int>(), generationLookup);
}
[Test]
public void GenerationCalc_SingleNode()
{
var graph = new DirectedAcyclicGraph<string>(new[] { "Node_1_0" });
var expected = new Dictionary<string, int>
{
{ "Node_1_0", 0 }
};
CollectionAssert.AreEqual(expected, graph.GetNodeGenerationLookup());
}
[Test]
public void GenerationCalc_SingleEdge()
{
var graph = new DirectedAcyclicGraph<string>(new[] { "Node_1_0", "Node_2_1" });
graph.AddEdge("Node_1_0", "Node_2_1");
var expected = new Dictionary<string, int>
{
{ "Node_1_0", 0 },
{ "Node_2_1", 1 }
};
var actual = graph.GetNodeGenerationLookup();
CollectionAssert.AreEqual(expected, actual);
}
[Test]
public void GenerationCalc_TwoNodesInGen1()
{
var graph = new DirectedAcyclicGraph<string>(new[] { "Node_1_0", "Node_2_1", "Node_3_1" });
graph.AddEdge("Node_1_0", "Node_2_1");
graph.AddEdge("Node_1_0", "Node_3_1");
var expected = new Dictionary<string, int>
{
{ "Node_1_0", 0 },
{ "Node_2_1", 1 },
{ "Node_3_1", 1 }
};
var actual = graph.GetNodeGenerationLookup();
CollectionAssert.AreEqual(expected, actual);
}
[Test]
public void GenerationCalc_MultipleGenerations()
{
var graph = new DirectedAcyclicGraph<string>(new[] { "Node_1_0", "Node_2_1", "Node_3_1", "Node_4_2", "Node_5_2", "Node_6_2" });
graph.AddEdge("Node_1_0", "Node_2_1");
graph.AddEdge("Node_2_1", "Node_4_2");
graph.AddEdge("Node_2_1", "Node_5_2");
graph.AddEdge("Node_1_0", "Node_3_1");
graph.AddEdge("Node_3_1", "Node_6_2");
var expected = new Dictionary<string, int>
{
{ "Node_1_0", 0 },
{ "Node_2_1", 1 },
{ "Node_3_1", 1 },
{ "Node_4_2", 2 },
{ "Node_5_2", 2 },
{ "Node_6_2", 2 }
};
var actual = graph.GetNodeGenerationLookup();
CollectionAssert.AreEqual(expected, actual);
}
[Test]
public void GenerationCalc_ChildNodesReferenceEachOther()
{
var graph = new DirectedAcyclicGraph<string>(new[] { "Node_1_0", "Node_2_1", "Node_3_1", "Node_4_3", "Node_5_2", "Node_6_2" });
graph.AddEdge("Node_1_0", "Node_2_1");
graph.AddEdge("Node_2_1", "Node_4_3");
graph.AddEdge("Node_2_1", "Node_5_2");
graph.AddEdge("Node_5_2", "Node_4_3");
graph.AddEdge("Node_1_0", "Node_3_1");
graph.AddEdge("Node_3_1", "Node_6_2");
var expected = new Dictionary<string, int>
{
{ "Node_1_0", 0 },
{ "Node_2_1", 1 },
{ "Node_3_1", 1 },
{ "Node_4_3", 3 },
{ "Node_5_2", 2 },
{ "Node_6_2", 2 }
};
var actual = graph.GetNodeGenerationLookup();
CollectionAssert.AreEqual(expected, actual);
}
[Test]
public void GenerationCalc_MultipleOriginsAndChildNodesReferenceEachOther()
{
var nodes = new[]
{
"Node_1_0",
"Node_2_1",
"Node_3_1",
"Node_4_3",
"Node_5_2",
"Node_6_2",
"Node_7_0",
"Node_8_1",
"Node_9_1",
"Node_10_3",
"Node_11_2",
"Node_12_2",
};
var graph = new DirectedAcyclicGraph<string>(nodes);
graph.AddEdge("Node_1_0", "Node_2_1");
graph.AddEdge("Node_2_1", "Node_4_3");
graph.AddEdge("Node_2_1", "Node_5_2");
graph.AddEdge("Node_5_2", "Node_4_3");
graph.AddEdge("Node_1_0", "Node_3_1");
graph.AddEdge("Node_3_1", "Node_6_2");
graph.AddEdge("Node_7_0", "Node_8_1");
graph.AddEdge("Node_8_1", "Node_10_3");
graph.AddEdge("Node_8_1", "Node_11_2");
graph.AddEdge("Node_11_2", "Node_10_3");
graph.AddEdge("Node_7_0", "Node_9_1");
graph.AddEdge("Node_9_1", "Node_12_2");
var expected = new Dictionary<string, int>
{
{ "Node_1_0", 0 },
{ "Node_2_1", 1 },
{ "Node_3_1", 1 },
{ "Node_4_3", 3 },
{ "Node_5_2", 2 },
{ "Node_6_2", 2 },
{ "Node_7_0", 0 },
{ "Node_8_1", 1 },
{ "Node_9_1", 1 },
{ "Node_10_3", 3 },
{ "Node_11_2", 2 },
{ "Node_12_2", 2 }
};
var actual = graph.GetNodeGenerationLookup();
CollectionAssert.AreEqual(expected, actual);
}
[Test]
public void TestFunctionalDAG()
{
var json = @"[
{
""queryId"": 2058,
""queryText"": ""SELECT sum(Value) as GLDollars, FiscalYearCode,FiscalMonthCode,CalendarYear,CalendarMonth,
AccountID,DepartmentID,DepartmentCode,DepartmentType
,ClientEntityID, SPHEntityID, SPHisVariableAcctDept
,SPHDepartmentRollup,SPHCategory,SPHLineItem,SPHSection,SPHStatement,ORGPIN
FROM {{ref('GL_Initial_Stage')}}
WHERE FiscalMonthCode != 'Not Specified'
GROUP BY FiscalYearCode,FiscalMonthCode,CalendarYear,CalendarMonth,AccountID
,DepartmentID,DepartmentCode,DepartmentType,
ClientEntityID,SPHEntityID,SPHisVariableAcctDept
,SPHDepartmentRollup,SPHCategory,SPHLineItem,SPHSection,SPHStatement,ORGPIN"",
""targetTable"": ""DepartmentGL"",
""description"": ""sum GL dollars, final Dept GL table"",
""materializationType"": 1,
""processId"": 161,
""displayOrder"": 2,
""clientQueries"": [],
""queryTags"": [
{
""queryTagId"": 11084,
""tagId"": 21,
""queryId"": 2058
}
]
},
{
""queryId"": 2059,
""queryText"": ""select distinct
entMap.SPHEntityID,
entMap.ClientEntityID,
entmap.SourceTable,
entmap.StrataID as em_StrataID,
entmap.ORGPIN
from datalake_sandbox.config.Entity_MAPPING as entmap
WHERE entmap.strataid = $StrataID
and entmap.EntityType = 'GL'
"",
""targetTable"": ""Org_EntityMapping"",
""description"": ""get entity mappings"",
""materializationType"": 1,
""processId"": 161,
""displayOrder"": 0,
""clientQueries"": [],
""queryTags"": [
{
""queryTagId"": 11060,
""tagId"": 21,
""queryId"": 2059
}
]
},
{
""queryId"": 2454,
""queryText"": ""SELECT DISTINCT
/* Custom GL Dollars */
CASE WHEN dgl.OrgPin in (2962) and dgl.AccountID in (1838, 1545, 1122, 1128) THEN GLDollars * -1
WHEN dgl.OrgPin in (3120, 2980) and dgl.SPHCategory in ('Revenue Deduction') THEN GLDollars * -1
WHEN dgl.OrgPin in (1170) and dgl.AccountID in (4809) THEN GLDollars * -1
WHEN dgl.OrgPin in (1975) and dgl.AccountID in (7742) THEN GLDollars * -1
WHEN dgl.OrgPin in (2724) and dgl.AccountID in (1346, 1394, 1395, 1443) THEN GLDollars * -1
ELSE GLDollars END as GLDollars,
FiscalYearCode,
FiscalMonthCode,
CalendarYear,
CalendarMonth,
dgl.AccountID,
da.AccountCode,
da.Description as GLDescription,
dgl.DepartmentID,
dgl.DepartmentCode,
dgl.ClientEntityID,
dgl.SPHisVariableAcctDept,
dgl.SPHDepartmentRollup,
dgl.SPHCategory,
dgl.SPHLineItem,
dgl.SPHSection,
dgl.SPHStatement,
dgl.DepartmentType,
dgl.SPHEntityID,
dgl.orgpin as orgpin_sys,
CASE WHEN dd.SPHDEPARTMENTROLLUPCONFIDENCESCORE=1 THEN 1 ELSE 0 END AS IsValidatedSPHDepartmentRollup,
CASE WHEN da.SPHACCOUNTROLLUPCONFIDENCESCORE=1 THEN 1 ELSE 0 END AS IsValidatedSPHLineItem
FROM {{ref('DepartmentGL')}} dgl
LEFT JOIN FW.DIMACCOUNT da ON dgl.AccountID=da.AccountID
LEFT JOIN FW.DIMDEPARTMENT dd ON dgl.DepartmentID=dd.DepartmentID"",
""targetTable"": ""Gl_DEPARTMENTGL_stage"",
""description"": ""custom GL Dollar, DepartmentType"",
""materializationType"": 1,
""processId"": 161,
""displayOrder"": 3,
""clientQueries"": [],
""queryTags"": [
{
""queryTagId"": 11080,
""tagId"": 21,
""queryId"": 2454
}
]
},
{
""queryId"": 2455,
""queryText"": ""SELECT gl.*
FROM {{ref('Gl_DEPARTMENTGL_stage')}} as gl inner join DATALAKE_SANDBOX.CONFIG.SYSTEM_CRITERIA as s on s.orgpin = gl.orgpin_sys
WHERE
/* DATA TO BE FILTERED OUT */
(s.orgpin = 1178 AND gl.AccountID IN (13238,13239,13461,14102,13240,13241,13242,
13236,13237,13243,14722,15227))
OR (s.orgpin = 3120 AND gl.AccountID IN (97))
OR (s.orgpin = 3196 AND gl.AccountID IN (1031))
OR ( s.orgpin = 2973 AND gl.AccountID IN (485, 486, 488, 490, 493, 494, 1901, 1909))
OR ( s.orgpin = 3167 AND gl.AccountID IN (257,7369))
OR ( s.orgpin = 2951 AND gl.AccountID IN (247))
OR ( s.orgpin = 2717 AND gl.AccountID IN (7))
OR ( s.orgpin = 3109 AND gl.AccountID IN (1352))
OR ( s.orgpin = 3113 AND gl.AccountID IN (70272))
OR ( s.orgpin = 3157 AND gl.AccountID IN (20415))
OR ( s.orgpin = 3202 AND gl.AccountID IN (4085, 2984))
OR ( s.orgpin = 1975 and (
(AccountID IN (734) and gl.ClientEntityID=17 /*SYSTEM SERVICES Entity*/) or
(AccountCode like '%0090%' or GLDescription like 'SS-%') or
(AccountID IN (2310, 2313, 2318,2319, 2320, 2321, 2322, 2324, 2328, 2294,
2295, 2296, 2298, 2299, 2302, 5832, 2306, 3284, 3286, 3287, 3288, 3290, 12434, 3295,
3297, 5838, 2291, 5841, 16174, 16873, 16874, 16875, 16876, 16877, 16879, 13349,
22261, 22256, 22309, 22288, 22270, 22280, 22513, 23954, 22291, 22301, 22294, 22264,
22353,22283, 22308, 22297, 23082, 22282, 22278, 22292, 22281, 22277, 22510, 23083,
12327, 23950, 12957, 20308, 25753, 16306, 12963, 11391, 16992, 14101, 14304, 16993,
17213, 14333, 13749, 973, 974, 975, 976, 977, 978, 983, 1041, 2672, 3576, 12448,
12793, 12798, 19985, 21969))
))
OR ( s.orgpin = 1137 AND CalendarYear= 2021 and CalendarMonth = 4 AND gl.AccountID IN (613))
OR ( s.orgpin = 3179 AND gl.AccountID IN (9828))
OR ( s.orgpin = 3228 AND gl.AccountID IN (778))
OR ( s.orgpin = 3266 AND gl.AccountID IN (518))
OR ( s.orgpin = 1475 AND gl.DepartmentCode IN ('1.10201199'))
OR ( s.orgpin = 3196 AND gl.AccountID IN (517) and DepartmentCode IN ('01.9000'))
OR ( s.orgpin = 2052 and CalendarYear = 2021 and CalendarMonth = 6 AND gl.AccountID IN (710, 711, 712))"",
""targetTable"": ""Gl_DEPARTMENTGL_stage_filter"",
""description"": ""data to be filtered out via customizations"",
""materializationType"": 1,
""processId"": 161,
""displayOrder"": 4,
""clientQueries"": [],
""queryTags"": [
{
""queryTagId"": 11066,
""tagId"": 21,
""queryId"": 2455
}
]
},
{
""queryId"": 2456,
""queryText"": ""select
s.*
from {{ref('Gl_DEPARTMENTGL_stage')}} as s
except (
select *
from {{ref('Gl_DEPARTMENTGL_stage_filter')}}
)"",
""targetTable"": ""GL_Summary_Stage"",
""description"": ""Handle custom exclusions - last step before data is ready"",
""materializationType"": 1,
""processId"": 161,
""displayOrder"": 5,
""clientQueries"": [],
""queryTags"": [
{
""queryTagId"": 11076,
""tagId"": 21,
""queryId"": 2456
}
]
},
{
""queryId"": 2457,
""queryText"": ""SELECT
AccountID,
CalendarMonth,
CalendarYear,
ClientEntityID,
DepartmentCode,
DepartmentID,
DepartmentType,
FiscalMonthCode,
FiscalYearCode,
GLDollars,
IsValidatedSPHDepartmentRollup,
IsValidatedSPHLineItem,
SPHCategory,
SPHDepartmentRollup,
SPHEntityID,
SPHisVariableAcctDept,
SPHLineItem,
SPHSection,
SPHStatement
FROM {{ref('GL_Summary_Stage')}}"",
""targetTable"": ""GL_Summary"",
""description"": ""Final output of GL Datamart"",
""materializationType"": 0,
""processId"": 161,
""displayOrder"": 6,
""clientQueries"": [],
""queryTags"": [
{
""queryTagId"": 11081,
""tagId"": 21,
""queryId"": 2457
}
]
},
{
""queryId"": 2458,
""queryText"": ""SELECT
(CASE
WHEN acct.isinverted = 1 THEN data.Value * -1
ELSE data.Value
END) AS Value,
data.AccountID,
data.FiscalYearCode,
data.FiscalMonthCode,
dt.CalendarYear,
dt.CalendarMonth,
dt.CalendarQuarter,
data.TimeClassCode,
acct.SPAccountRollupCategory,
acct.SPAccountRollupName,
acct.OBDollarsFinancialReporting,
acct.OBDollarsFinancialReportingCategory,
acct.OBDollarsFinancialReportingLineName,
acct.DSSAccountRollup1Name,
acct.Variability,
dd.DepartmentID,
dd.DepartmentCode,
dd.DepartmentType,
dd.IsMRPlan,
dd.IsVariable,
COALESCE(
emap.CLIENTENTITYID,
dr1map.CLIENTENTITYID,
NULL
) as ClientEntityID,
COALESCE(
emap.SPHEntityID,
dr1map.SPHEntityID
) as SPHEntityID,
(CASE WHEN (acct.Variability=1 and dd.IsVariable=1) THEN '1' ELSE '0' END) AS SPHisVariableAcctDept,
sphdr.Name as SPHDepartmentRollup,
sphar.Category as SPHCategory,
sphar.LineItem as SPHLineItem,
sphar.Section as SPHSection,
sphar.Statement as SPHStatement,
$OrgPin as ORGPIN
FROM int.FactGL data
INNER JOIN fw.DimAccount acct ON acct.AccountID=data.AccountID
INNER JOIN (SELECT distinct CalendarYear,CalendarMonth,CalendarQuarter,FiscalYear,FiscalMonth
FROM fw.DimDate
WHERE FiscalMonth != 0 or FiscalMonth != '0') dt on dt.FiscalYear=data.FiscalYearCode and dt.FiscalMonth=data.FiscalMonthCode
INNER JOIN fw.DimDepartment dd ON dd.DepartmentID=data.DepartmentID
INNER JOIN fw.DimFiscalMonth fm ON fm.FiscalMonthID=data.FiscalMonthID
LEFT JOIN fw.DimEntity e on dd.EntityID=e.EntityID
INNER JOIN fw.DimSPHDepartmentRollup as sphdr on sphdr.SPHDepartmentRollupID = dd.SPHDepartmentRollupID
INNER JOIN fw.DimSPHAccountRollup as sphar on sphar.SPHAccountRollupID = acct.SPHAccountRollupID
LEFT JOIN {{ref('Org_EntityMapping')}} emap on
emap.SourceTable in ('fw_dimEntity')
and e.EntityID = emap.CLIENTENTITYID
and emap.em_StrataID=$StrataID
LEFT JOIN {{ref('Org_EntityMapping')}} dr1map on
dr1map.SourceTable in ('fw_dimDepartment_DepartmentRollup1')
and dd.DepartmentRollup1ID = dr1map.CLIENTENTITYID
and dr1map.em_StrataID=$StrataID
WHERE dt.CalendarYear >= $StartDate_CalendarYear
and fm.FiscalMonthCode <> '0'
and data.TimeClassCode = 'A'"",
""targetTable"": ""GL_Initial_Stage"",
""description"": ""Preaggregated FactGL stage"",
""materializationType"": 1,
""processId"": 161,
""displayOrder"": 1,
""clientQueries"": [],
""queryTags"": [
{
""queryTagId"": 11100,
""tagId"": 21,
""queryId"": 2458
}
]
}
]";
var query = JsonConvert.DeserializeObject<List<Query>>(json);
Assert.DoesNotThrow(() => query.ToDirectedAcyclicGraph());
}
[Test]
public void TestFunctionalDAGReorder()
{
var json = @"[
{
""queryId"": 2458,
""queryText"": ""SELECT
(CASE
WHEN acct.isinverted = 1 THEN data.Value * -1
ELSE data.Value
END) AS Value,
data.AccountID,
data.FiscalYearCode,
data.FiscalMonthCode,
dt.CalendarYear,
dt.CalendarMonth,
dt.CalendarQuarter,
data.TimeClassCode,
acct.SPAccountRollupCategory,
acct.SPAccountRollupName,
acct.OBDollarsFinancialReporting,
acct.OBDollarsFinancialReportingCategory,
acct.OBDollarsFinancialReportingLineName,
acct.DSSAccountRollup1Name,
acct.Variability,
dd.DepartmentID,
dd.DepartmentCode,
dd.DepartmentType,
dd.IsMRPlan,
dd.IsVariable,
COALESCE(
emap.CLIENTENTITYID,
dr1map.CLIENTENTITYID,
NULL
) as ClientEntityID,
COALESCE(
emap.SPHEntityID,
dr1map.SPHEntityID
) as SPHEntityID,
(CASE WHEN (acct.Variability=1 and dd.IsVariable=1) THEN '1' ELSE '0' END) AS SPHisVariableAcctDept,
sphdr.Name as SPHDepartmentRollup,
sphar.Category as SPHCategory,
sphar.LineItem as SPHLineItem,
sphar.Section as SPHSection,
sphar.Statement as SPHStatement,
$OrgPin as ORGPIN
FROM int.FactGL data
INNER JOIN fw.DimAccount acct ON acct.AccountID=data.AccountID
INNER JOIN (SELECT distinct CalendarYear,CalendarMonth,CalendarQuarter,FiscalYear,FiscalMonth
FROM fw.DimDate
WHERE FiscalMonth != 0 or FiscalMonth != '0') dt on dt.FiscalYear=data.FiscalYearCode and dt.FiscalMonth=data.FiscalMonthCode
INNER JOIN fw.DimDepartment dd ON dd.DepartmentID=data.DepartmentID
INNER JOIN fw.DimFiscalMonth fm ON fm.FiscalMonthID=data.FiscalMonthID
LEFT JOIN fw.DimEntity e on dd.EntityID=e.EntityID
INNER JOIN fw.DimSPHDepartmentRollup as sphdr on sphdr.SPHDepartmentRollupID = dd.SPHDepartmentRollupID
INNER JOIN fw.DimSPHAccountRollup as sphar on sphar.SPHAccountRollupID = acct.SPHAccountRollupID
LEFT JOIN {{ref('Org_EntityMapping')}} emap on
emap.SourceTable in ('fw_dimEntity')
and e.EntityID = emap.CLIENTENTITYID
and emap.em_StrataID=$StrataID
LEFT JOIN {{ref('Org_EntityMapping')}} dr1map on
dr1map.SourceTable in ('fw_dimDepartment_DepartmentRollup1')
and dd.DepartmentRollup1ID = dr1map.CLIENTENTITYID
and dr1map.em_StrataID=$StrataID
WHERE dt.CalendarYear >= $StartDate_CalendarYear
and fm.FiscalMonthCode <> '0'
and data.TimeClassCode = 'A'"",
""targetTable"": ""GL_Initial_Stage"",
""description"": ""Preaggregated FactGL stage"",
""materializationType"": 1,
""processId"": 161,
""displayOrder"": 1,
""clientQueries"": [],
""queryTags"": [
{
""queryTagId"": 11100,
""tagId"": 21,
""queryId"": 2458
}
]
},
{
""queryId"": 2058,
""queryText"": ""SELECT sum(Value) as GLDollars, FiscalYearCode,FiscalMonthCode,CalendarYear,CalendarMonth,
AccountID,DepartmentID,DepartmentCode,DepartmentType
,ClientEntityID, SPHEntityID, SPHisVariableAcctDept
,SPHDepartmentRollup,SPHCategory,SPHLineItem,SPHSection,SPHStatement,ORGPIN
FROM {{ref('GL_Initial_Stage')}}
WHERE FiscalMonthCode != 'Not Specified'
GROUP BY FiscalYearCode,FiscalMonthCode,CalendarYear,CalendarMonth,AccountID
,DepartmentID,DepartmentCode,DepartmentType,
ClientEntityID,SPHEntityID,SPHisVariableAcctDept
,SPHDepartmentRollup,SPHCategory,SPHLineItem,SPHSection,SPHStatement,ORGPIN"",
""targetTable"": ""DepartmentGL"",
""description"": ""sum GL dollars, final Dept GL table"",
""materializationType"": 1,
""processId"": 161,
""displayOrder"": 2,
""clientQueries"": [],
""queryTags"": [
{
""queryTagId"": 11084,
""tagId"": 21,
""queryId"": 2058
}
]
},
{
""queryId"": 2059,
""queryText"": ""select distinct
entMap.SPHEntityID,
entMap.ClientEntityID,
entmap.SourceTable,
entmap.StrataID as em_StrataID,
entmap.ORGPIN
from datalake_sandbox.config.Entity_MAPPING as entmap
WHERE entmap.strataid = $StrataID
and entmap.EntityType = 'GL'
"",
""targetTable"": ""Org_EntityMapping"",
""description"": ""get entity mappings"",
""materializationType"": 1,
""processId"": 161,
""displayOrder"": 0,
""clientQueries"": [],
""queryTags"": [
{
""queryTagId"": 11060,
""tagId"": 21,
""queryId"": 2059
}
]
},
{
""queryId"": 2454,
""queryText"": ""SELECT DISTINCT
/* Custom GL Dollars */
CASE WHEN dgl.OrgPin in (2962) and dgl.AccountID in (1838, 1545, 1122, 1128) THEN GLDollars * -1
WHEN dgl.OrgPin in (3120, 2980) and dgl.SPHCategory in ('Revenue Deduction') THEN GLDollars * -1
WHEN dgl.OrgPin in (1170) and dgl.AccountID in (4809) THEN GLDollars * -1
WHEN dgl.OrgPin in (1975) and dgl.AccountID in (7742) THEN GLDollars * -1
WHEN dgl.OrgPin in (2724) and dgl.AccountID in (1346, 1394, 1395, 1443) THEN GLDollars * -1
ELSE GLDollars END as GLDollars,
FiscalYearCode,
FiscalMonthCode,
CalendarYear,
CalendarMonth,
dgl.AccountID,
da.AccountCode,
da.Description as GLDescription,
dgl.DepartmentID,
dgl.DepartmentCode,
dgl.ClientEntityID,
dgl.SPHisVariableAcctDept,
dgl.SPHDepartmentRollup,
dgl.SPHCategory,
dgl.SPHLineItem,
dgl.SPHSection,
dgl.SPHStatement,
dgl.DepartmentType,
dgl.SPHEntityID,
dgl.orgpin as orgpin_sys,
CASE WHEN dd.SPHDEPARTMENTROLLUPCONFIDENCESCORE=1 THEN 1 ELSE 0 END AS IsValidatedSPHDepartmentRollup,
CASE WHEN da.SPHACCOUNTROLLUPCONFIDENCESCORE=1 THEN 1 ELSE 0 END AS IsValidatedSPHLineItem
FROM {{ref('DepartmentGL')}} dgl
LEFT JOIN FW.DIMACCOUNT da ON dgl.AccountID=da.AccountID
LEFT JOIN FW.DIMDEPARTMENT dd ON dgl.DepartmentID=dd.DepartmentID"",
""targetTable"": ""Gl_DEPARTMENTGL_stage"",
""description"": ""custom GL Dollar, DepartmentType"",
""materializationType"": 1,
""processId"": 161,
""displayOrder"": 3,
""clientQueries"": [],
""queryTags"": [
{
""queryTagId"": 11080,
""tagId"": 21,
""queryId"": 2454
}
]
},
{
""queryId"": 2455,
""queryText"": ""SELECT gl.*
FROM {{ref('Gl_DEPARTMENTGL_stage')}} as gl inner join DATALAKE_SANDBOX.CONFIG.SYSTEM_CRITERIA as s on s.orgpin = gl.orgpin_sys
WHERE
/* DATA TO BE FILTERED OUT */
(s.orgpin = 1178 AND gl.AccountID IN (13238,13239,13461,14102,13240,13241,13242,
13236,13237,13243,14722,15227))
OR (s.orgpin = 3120 AND gl.AccountID IN (97))
OR (s.orgpin = 3196 AND gl.AccountID IN (1031))
OR ( s.orgpin = 2973 AND gl.AccountID IN (485, 486, 488, 490, 493, 494, 1901, 1909))
OR ( s.orgpin = 3167 AND gl.AccountID IN (257,7369))
OR ( s.orgpin = 2951 AND gl.AccountID IN (247))
OR ( s.orgpin = 2717 AND gl.AccountID IN (7))
OR ( s.orgpin = 3109 AND gl.AccountID IN (1352))
OR ( s.orgpin = 3113 AND gl.AccountID IN (70272))
OR ( s.orgpin = 3157 AND gl.AccountID IN (20415))
OR ( s.orgpin = 3202 AND gl.AccountID IN (4085, 2984))
OR ( s.orgpin = 1975 and (
(AccountID IN (734) and gl.ClientEntityID=17 /*SYSTEM SERVICES Entity*/) or
(AccountCode like '%0090%' or GLDescription like 'SS-%') or
(AccountID IN (2310, 2313, 2318,2319, 2320, 2321, 2322, 2324, 2328, 2294,
2295, 2296, 2298, 2299, 2302, 5832, 2306, 3284, 3286, 3287, 3288, 3290, 12434, 3295,
3297, 5838, 2291, 5841, 16174, 16873, 16874, 16875, 16876, 16877, 16879, 13349,
22261, 22256, 22309, 22288, 22270, 22280, 22513, 23954, 22291, 22301, 22294, 22264,
22353,22283, 22308, 22297, 23082, 22282, 22278, 22292, 22281, 22277, 22510, 23083,
12327, 23950, 12957, 20308, 25753, 16306, 12963, 11391, 16992, 14101, 14304, 16993,
17213, 14333, 13749, 973, 974, 975, 976, 977, 978, 983, 1041, 2672, 3576, 12448,
12793, 12798, 19985, 21969))
))
OR ( s.orgpin = 1137 AND CalendarYear= 2021 and CalendarMonth = 4 AND gl.AccountID IN (613))
OR ( s.orgpin = 3179 AND gl.AccountID IN (9828))
OR ( s.orgpin = 3228 AND gl.AccountID IN (778))
OR ( s.orgpin = 3266 AND gl.AccountID IN (518))
OR ( s.orgpin = 1475 AND gl.DepartmentCode IN ('1.10201199'))
OR ( s.orgpin = 3196 AND gl.AccountID IN (517) and DepartmentCode IN ('01.9000'))
OR ( s.orgpin = 2052 and CalendarYear = 2021 and CalendarMonth = 6 AND gl.AccountID IN (710, 711, 712))"",
""targetTable"": ""Gl_DEPARTMENTGL_stage_filter"",
""description"": ""data to be filtered out via customizations"",
""materializationType"": 1,
""processId"": 161,
""displayOrder"": 4,
""clientQueries"": [],
""queryTags"": [
{
""queryTagId"": 11066,
""tagId"": 21,
""queryId"": 2455
}
]
},
{
""queryId"": 2456,
""queryText"": ""select
s.*
from {{ref('Gl_DEPARTMENTGL_stage')}} as s
except (
select *
from {{ref('Gl_DEPARTMENTGL_stage_filter')}}
)"",
""targetTable"": ""GL_Summary_Stage"",
""description"": ""Handle custom exclusions - last step before data is ready"",
""materializationType"": 1,
""processId"": 161,
""displayOrder"": 5,
""clientQueries"": [],
""queryTags"": [
{
""queryTagId"": 11076,
""tagId"": 21,
""queryId"": 2456
}
]
},
{
""queryId"": 2457,
""queryText"": ""SELECT
AccountID,
CalendarMonth,
CalendarYear,
ClientEntityID,
DepartmentCode,
DepartmentID,
DepartmentType,
FiscalMonthCode,
FiscalYearCode,
GLDollars,
IsValidatedSPHDepartmentRollup,
IsValidatedSPHLineItem,
SPHCategory,
SPHDepartmentRollup,
SPHEntityID,
SPHisVariableAcctDept,
SPHLineItem,
SPHSection,
SPHStatement
FROM {{ref('GL_Summary_Stage')}}"",
""targetTable"": ""GL_Summary"",
""description"": ""Final output of GL Datamart"",
""materializationType"": 0,
""processId"": 161,
""displayOrder"": 6,
""clientQueries"": [],
""queryTags"": [
{
""queryTagId"": 11081,
""tagId"": 21,
""queryId"": 2457
}
]
}
]";
var query = JsonConvert.DeserializeObject<List<Query>>(json);
Assert.DoesNotThrow(() => query.ToDirectedAcyclicGraph());
}
}
}
Reference in New Issue View Git Blame Copy Permalink