Bridge Table

Question 1

Create a dimension which includes a weight factor and listagg.

Answer 1

CREATE TABLE leftDIM AS SELECT leftID, leftxxx, leftyyy, 1.0/count(rightID) as weightFactor, LISTAGG(rightID, ‘__’) WITHIN GROUP (Order By rightID) AS rightGroupList FROM (…) WHERE (…) GROUP BY leftID, leftxxx, leftyyy

Question 2

What is the total cost for each right?

Answer 2

Select rightID, rightXXX, sum(totalCost * weight) AS total_cost_right FROM fact, left, bridge, right where fact.id = left.id and left.id = bridge.id and bridge.id = right.id group by rightID, rightXXX, order by rightID, rightXXX

Question 3

Weightfactor

Answer 3

1.0/count(rightID) AS weightFactor

Question 4

ListAGG

Answer 4

ListAGG(rightID, ‘__’) within group (order by rightid) as rightGroupList

Question 5

ListAGG

Answer 5

ListAGG(rightID, ‘__’) within group (order by rightid) as rightGroupList

Question 6

Create another version of a dimension with listagg and weight factor that has an increased level of aggregation

Answer 6

Create table leftDim as 1.0/count(rightID) as weightFactor, LISTAGG(rightID, ‘__’) within group (order by rightID) as rightGroupList from (…) where (…)

Question 7

Create a bridge dim with increased aggregation using listagg

Answer 7

Create table bridgeDim as select listagg(rightID, ‘__’) within group (order by rightID) as rightGroupList, rightID from (…) where(…) group by rightID

Question 8

Create a bridge dim

Answer 8

Select leftID, rightID from (…) where (…) group by leftID, rightID

Question 9

Draw sample records for a ProductDim (ProdNo, ProdType) and Bridge (ProductNo, SupplierID) and SupplierDim (SupplierID, SupplierName) with weightFactor and listagg

Answer 9

draw