Oracle Communities

Oracle Analytics and AI

Child Item

Non-standard 2 fact content

HI,

I have inherited a 'hybrid' 3NF OLAP data warehouse where sometimes fact tables join to fact tables and fact tables have attribute fields as well as measures.

My question is where there is

Header Fact (1)

Detail Fact (M)

And both are joined via the Header Fact PK to form a single logical table source is there any way to reference the measures on the header such that they still add up?

Common sense tells me no, as you would need a means to pro-rate the header fact across the detail children, or the equivalent of sum(distinct measure by PK)

Am I correct, or is there any way to do this?

thanks for any input,

Robert.

Find more posts tagged with

Accepted answers

All comments

[Deleted User]

a) Lovely...

b) You wouldn't "SUM by PK" but create level-based measures for example.

c) I'm saying "for example" because you can also equalize things by being clever with the aliasing the physical layer objects nicely and using them as you would normal header/detail facts and their dimensions. The joins will be nasty though.

Robert Angel

Thanks Christian.

On a. - agree totally!

On b, I kind of had this in mind but you don't have a dimension hierarchy for a fact, which left me with c. - where reading between the lines I guess you are suggesting that I treat the header fact like it is an agg fact(?) but I am not 'getting' the rest of it?

On joins you are correct some dimensions join to the header, some to the detail, so some of the time this is like snowflake modelling but 'within' the fact rather than within the dimension (though dimensions have children too, love, love, love this DW.... sob)

Following my thought above I am guessing that you would say to pin the measure to the total level on the dimensions that join at detail level, otherwise they are at detail level as 'normal'.

So can you explain further on C please and correct my half baked musings above where necessary?

Thanks again,

Robert.

[Deleted User]

Yes and no.

"b)" was meant for the case where you construct a degenerate dimension with that fact-based attributes across which you will then aggregate.

JustTheFacts

I think this is just a restatement of what Christian is suggesting, but I'm going to spell it out a bit for future readers who may not have the skill of either you or Christian. Mark Christian's as the correct answer, if this works.

1) Create a new logical dimension and have as its source your Header + Detail combination (just like your logical fact source)

2) Map all degenerate dimension columns into this new logical table

3) Set the PK of the logical table to the business key of your "detail" level

4) Create a hierarchy from the new dimension, with at a minimum "Total", "Header", and "Detail" levels with appropriate keys

5) Replace all of those degenerate dimension columns in your Pres layer with the ones from the new logical table

6) Set header-level measures to the "Header" level of your hierarchy

I think that's the gist. We have a few models that ended up like this and they work alright so long as you don't need to factor header-level measures across detail rows.

Robert Angel

Thanks, I have used the fact as the dimension previously so I 'get' exactly what you are saying.

However, your caveat - so long as you don't need to factor header-level measures across detail rows is the bottom line, and the crux of my question, so thanks you confirmed my thinking for me and others, and you confirmed my fundamental suspicion that there is no meaningful way to have header level at detail and it be 'correct'.

[Deleted User]

Robert Angel wrote:there is no meaningful way to have header level at detail and it be 'correct'.

With level-based measures at "Header" level there is. Your "Attribute" dimension would just have Header -> Detail levels with the header measures aggregating at "Header" level and the detail measures at "detail".

[Deleted User]

./reply.sh -cmd logicalmodelling -verbosity high

I never put the last part ;-)

Robert Angel

On the face of a report this would still yield the wrong answer if the measure was aggregated, wouldn't it?

i.e.

Dimension X Header PK (not displayed) Header Measure

A 1 5 --correct in isolation

B 1 5 --ditto

C 1 5 --ditto

D 2 20

Total 35

Desired Result - sum of distinct PK measure

Total 25

My example above assumes that Dimension X comes from a dimension that is joined at the detail level, if (say) Dimension Y was used which joins at the Header level (with no other detail level dimension members present) then the result as you describe it and I desire it would be correct, yes?

Brevity is clarity, but verbosity is oft' necessitated.

[Deleted User]

Why should the sum be 35?

Q Mth Sum Q-Total

1 Jan 10 30

1 Feb 8 30

1 Mar 12 30

--------------------------------------------

GTotal 30 30

===========================

Same logic as for time-based level-based measures but these make the behaviour nicely visible.

Robert Angel

Hi Christian, sorry web browser crashed earlier and it was a lengthy response... but I owe you a response for your courteous and considered responses.

Dimension X - a normal dimension that joins to the detail level of the combined fact

Dimension Y (based on fact details, with 3 tiers in hierarchy, Total, Header and Detail from top to bottom)

Fact (based on Fact detail and fact header in LTS)

Content Levels; -

Dimension X detail

Dimension Y header

Fact: Dim X detail; Dim Y header

Measures in logical Fact Table

A - from physical fact header

B - from physical fact detail

Measure A is pinned to HEADER level of the hierarchy for Dimension X -- I capitalise here as this answers your question why is not just one number

Measure B is not pinned

Answers report shows

Dimension X and Measure B - all values aggregate and sum as 'normal'

X B

- -

A 5

B 4

C 1

D 7

-------

Introduce Dimension Y attribute fields into the Mix and the previous query works as before, no change aside from the additional field content...

Instead run a query against Dimension Y and Measures A and or B and again all looks 'normal'

Finally Run a query against Dimension X and Measures A and B and the behaviour is as shown in my previous example

X A B

-----------

A 2 5

B 2 4
C 2 1

D 6 7

-----------

8(?) 17

Here the hidden column is the header key and in the case above A,B,C all share the same header key, but D has a different key, hence why the values are the same and why the value is different respectfully.

Phew.

Do I understand this 100% as you would wish now?

Also can you confirm what number would come out on the total of column A as above?

Million thanks,

Robert.