Structure in RPD

Question

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?

choracy69 · Answer

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.

Robert Angel · Answer

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)

Gianni Ceresa · Answer

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.