Hi experts,
I need your assistance in knowing how to join a certain set of tables in the Business Model Layer. Here is my scenario:
I have 1 Fact Table and 5 Dimension tables...but my issue revolve around DimB,C&D
To get total revenue per region a direct select query to the database will be:
select DimC.region name, sum (FactA.Revenue)
from
FactA, DimB, DimC, DimD
where
FactA.BranchCode=DimB.BranchCode
and DimB.BranchCode=DimC.BranchCode
and DimD.RegionCode=DimC.RegionCode;
How do I create this same relationship in my rpd?
I have tried what I've depicted in the image above and whenever I try to create a simple analysis of Region Name and Revenue, it only displays the Regions only without the aggregated revenues.
Even an analysis of DimC.RegionName and DimB.BranchName fails with an error.
NOTE:
DimC table has only 5 Regions.
DimD table lists all the BranchCodes and the Regions they belong to.
Please assist.
Thank you.