OBIEE cast null as double precision

dilek

Hello everybody,

I try to create a business model and there are 2 facts and 3 dims.

Connections are here;

- Fact A --> Dim A, Dim B

- Fact B --> Dim A, Dim C

Also, I create logical level based dimensions for Dim A, Dim B, and Dim C. Then set content level of fact tables as;

Fact A --> Dim A, Dim B in detail level, Dim C total level

Fact B --> Dim A, Dim C in detail level, Dim B total level

After these settings, I create an analysis and put columns as;

Dim A (which is month), Fact A (one measure: sum aggregated), Fact B (one measure sum aggregate)

This works! I can see the results.

However, when I add an extra attribute column from Fact A (for example TYPE columns), Fact B column seen as NULL

When I see the physical code, it says: cast null as double precision

What can be the problem here?

Regards,

Dilek

Gianni Ceresa

Hi Dilek,

An attribute in a Fact table? It's not supposed to be there, if it's an attribute (not aggregate mesure) it's supposed to be in a dimension.

So model your fact as a degenerate dimension, set the content levels etc. as your did for your other dims and things are supposed to work fine.

[Deleted User]

dileraco wrote:However, when I add an extra attribute column from Fact A (for example TYPE columns), Fact B column seen as NULL

THOU SHALT NOT BASE ATTRIBUTE ON A FACT TABLE!

Seriously: Create a proper degenerate dimension. Attributes don't belong into a fact.

[Deleted User]

Quit copying me

Gianni Ceresa

As mine is before yours .... it's you copying me... But I don't mind, not your fault if a bit slow

[Deleted User]

:-P I probably clicked "Post" after you because you kept me occupied in the chat

dilek

Hi thx for your answers

Yes it is an attribute columns but it shows table data version. Meaning, fact table data has 3 versions, first, second and last. So, when I want to compare first version of Fact A, and Fact B I got the error.

For ex;

Fact A: ID joined to Dim B, YEAR joined to Dim A

VERSION     YEAR     ID     MEASURE

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

1                 2015        100          500

2                 2014        200          450

3                 2011        300          345

Fact B: ID joined to Dim C, YEAR joined to Dim A

YEAR     ID     MEASURE

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

2011      45     120

2010     48      56

So, in analysis my goal is to compare first version of Fact A, and Fact B for same years.

Can't I do this?

Regards,

[Deleted User]

dileraco wrote:Yes it is an attribute columns but it shows table data version. Meaning, fact table data has 3 versions, first, second and last. 

Sorry I'm not following that train of thought there?

What we're saying is a very basic concept of dimensional modelling stating simply that you must not place dimensional attributes into a fact table - for OBIEE that means a logical fact table only contains measures and keys (which are never exposed and used further than the logical layer) AND an alias of the fact table (or the physical object itself again a second time but explicitly modeled) as a logical dimension holding every single column which is to be used as an attribute which can mean IDs used as attributes and measures used as attributes

Example: HR analysis for "Age" - now "Age" can mean 2 things: once a measure meaning "Average Age" across whatever dimension you analyze it or "Age" as the attribute of the specific person you are looking at. No need for 2 physical tables holding that information in a redundant way.

Gianni Ceresa

To make it short:

Yes you have an attribute meaning the data version in your database on the same row as your measures, but in OBIEE you must model it for what it is: an attribute and not a measure.

According to Ralph Kimball,

^[1] in a data warehouse, a degenerate dimension is a dimension key in the fact table that does not have its own dimension table, because all the interesting attributes have been placed in analytic dimensions. The term "degenerate dimension" was originated by Ralph Kimball

(from Wikipedia)

So it's just something you must model in a different way, not that you can't use it or something else...

[Deleted User]

So basically you +1 what I wrote above :-P

Gianni Ceresa

Of course +1 what you (Christian) wrote above

But I'm also translating what you said in a different language so everybody understand (it's Friday)