Categories
- All Categories
- Oracle Analytics Learning Hub
- 29 Oracle Analytics Sharing Center
- 18 Oracle Analytics Lounge
- 236 Oracle Analytics News
- 45 Oracle Analytics Videos
- 16K Oracle Analytics Forums
- 6.2K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 88 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
OBIEE Business Modeling
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.
Answers
-
Hi,
First of all, this question should probably be in the space.
Regarding your question, it's quite easy to achieve what you need. In your Business Model Layer (BMM) you should only have star-schemas and no snowflakes. So, in order to translate your snowflaked physical schema into a star-schema in BMM, all you need to do is:
- Create 4 logical tables (1 fact and 3 dimensions)
- On the logical table that will correspond to your Dim B, in your logical table source, add an inner join between Dim B and Dim D and an inner join between Dim D and Dim C.
- Set up the dimension hierarchies and the correct content levels and you have your BMM ready to use.
0 -
Keeping it short ... what @Pedro F said
Yours is a common case and the "reduction" of the snowflake to a start is done inside the same logical table in the way Pedro said.
OBIEE will be smart enough to not make the joins to D and C if only columns of B will be requested by the analysis, so you don't have to worry about it from a performance point of view.
0
