nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Joining Fact and Dimension in RPD

User536127

Hi All,

I have a business scenario where i want to map a fact table with a dimension table.

The dimension table SK needs to be mapped to the fact table FK however, Dimension table SK is actually a sub-string of the fact table FK.

Example:

DIMENSION FACT

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

101 101,104(2),102(1)

102

103

104

105

So in this case the analysis should pick all 3 records of dimension table.(i.e: 101,104,102) for the single record of fact table.

Moreover in DB, the Dimension table SK datatype is NUMBER and Fact Table FK is VARCHAR.

How can i design my RPD Physical Layer and BM layer to achieve this?

Find more posts tagged with

Accepted answers

All comments

Gianni Ceresa

Hi,

OBIEE ideally works on a dimensional model with 1 or many facts rows pointing to one attribute.

In your case you already have 1 fact pointing to many attributes (so you just reversed the logical relationship).

I would highly suggest you normalize your data and use real primary key = foreign key relationship (at least having same tipe on both sides and a single value = a single value).

Ideally a mapping table between your single fact row and the multiple attributes.

If you really have to do something like that, knowing it will just be bad, you must try to treat that as a string and use something fact.columng like '%' || CAST(dimension as varchar) || '%' .

It's really bad ...

FTisiot

As suggested by @Gianni Ceresa fixing your data model should be the route to follow.

You can define casting or special joins in the model but performances will be really poor.

[Deleted User]

What both of them haven't told you yet:

You'd have to do this N times where N is the number of "ID" occurrences in your fact column. So you'd have to have a fixed number of those - like always 3 for example - because otherwise even that approach doesn't work.

Physical proper solution is definitely preferable to anything RPD-based with weird joins or writing the logic into a view and having that code execute all the time. Nasty, dirty, not performant.

User536127

I suppose the basics of data modelling in this case is not proper. I got that, thanks for the suggestion Gianni.

Gianni Ceresa

Honestly it's more the data (the way you described it) looking like it's not proper.

But as said you can still make it work (I gave an example of the kind of join you can try), but as said: poor performance, possible wrong behaviour (like 101 also joining to 1015 because of the 101% kind of join).

The fact table with concatenated FK really is the source of the issues.

Thomas Dodds

Your physical model is deficient ... listing your dimensional keys in a single column/row for any given measure violates star schema principles:

DIMENSION FACT

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

101 101,104(2),102(1)

102

103

104

105

^ nearly impossible to work with for analysis ...

what measure possibly has a dimensional context of 101,104(2),102(1)?
In your example is 104 50% of the total?
In your example is 104 a parent of 104(2)?
what business requirement for analysis could that ever satisfy?
given that complicated fact grain [ 101,104(2),102(1)] how would you be able to aggregate (physically or logically)?

User536127

The Dimensions like 101,104 are KPI's

and 101,104,102 is the combination of KPI's that a particular fact possesses, occurrence of more than one is denoted by (2) or more.

Thomas Dodds

Then use a proper scorecard and strategy tool where you can create KPIs out of metrics and metrics out of measures ...