Categories
- All Categories
- 127 Oracle Analytics News
- 23 Oracle Analytics Videos
- 14.5K Oracle Analytics Forums
- 5.5K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 48 Oracle Analytics Trainings
- 7 Oracle Analytics Data Visualizations Challenge
- 4 Oracle Analytics Career
- 8 Oracle Analytics Industry
- Find Partners
- For Partners
Structure in RPD

Hi,
I have problem with my structure in rpd.
Version: Oracle Business Intelligence 11.1.1.7.131017
I have four table: Fact1 (nonconformance), Fact2(shipping), Dim1(TIME), Dim2(Item).
Fact1 has a few attribute like NC_TYPE, NC_SOURCE, NC_CODE etc. and QTY , QTY_NC
Fact2 does not have any attributes, only QTY_Sh
My goal is analysis like:
for one day (2017/12/22)
1) basic analyze looks like:
Item || Shipping || Nonconformance
W1 || 100 || 25
2) but I must create analyze with NC_TYPE (from table Fact1)
Item || Shipping || Nonconformance || Nonconformance TYPE
W1 || 100 || 5 || TYPE1
W1 || 100 || 12 || TYPE2
W1 || 100 || 8 || TYPE3
5+12+8 = 25 (value from first analyze).
And the question is: how to model this structure in rpd?
In physical table I have:
Fact1 -- ITEM_ID --> Dim2(ITEM) and Fact1 -- DAY_ID --> Dim1(TIME)
Fact2 -- ITEM_ID --> Dim2(ITEM) and Fact2 -- DAY_ID --> Dim1(TIME)
In BMM:
In this layer I have question how to model this case?
1) first idea
Maybe I create one Logical Table like: Fact_NONCONFORMANCEandSHIPPING with two source: Fact1/Fact2 with columns: QTY_NC and QTY_SH
And second Logical Table like: Dim_NONCONFORMANCEandSHIPPING with one source: Fact1 with attributes columns liek NC_TYPE, NC_SOURCE etc.
For this table (Dim) create Logical Dimension.
Join this fact with this dimension and other table like TIME and ITEM.
2) second idea
Created only one Logical Table with attrbutes and 'aggregate' columns with two source: Fact1/Fact2 --> it has columns like NC_TYPE, NC_SOURCE, QTY_NC, QTY_SH etc.
Join this table with dimension TIME and ITEM.
Which of this idea is correct and allow me to create second analyze?
Answers
-
Hi,
As a fact table must not have attributes inside but only aggregated columns (that's the theory of a dimensional model) then your idea 2) isn't really a good idea
Which means you are left with idea 1)
Depending on the relationship between NC Type and shipping you maybe also can use 2 separate fact tables defining content level in the right way for the conformed dimensions.
0 -
In addition to Gianni's comment -
If it is possible to link from Fact1 to Fact2 via a shared dimension(s) then provided you only construct reports that utilise measures from the facts and attributes from the shared (conformed) dimensions then it will work. (And set appropriate content levels on the LTS)
0 -
I created first idea.
First Logical Table is a fact with only aggregare columns from two source (Fact1/Fact2)
Second Logical Table is a dimension with attributes from one source (Fact1)
I connect this two tables in BMM. In first table set logical levels: Fact1 - Details, Fact2 - Total
And for column Shipping I set level on Total on Second Logical Table Dimension.
It's working ok.
0