multiple facts with Common Dimension

Rajagopal R-Oracle

Hi.,

My requirement is  One Physical fact table joined with dimensions ( around 10) in Physical layer .,

In BMM the same physical sholde divided into multiple facts tables ( ex: if total 100 column in one Physical tables, 25 columns in 4 BMM fact tables by grouping related attributes) and joined to same dimensions

In Presentation layer have presentation folder for four fact tables., Report can be created  from any of these fact tables. (Measures from more than one fact table).

Issue  observed :  1) 2 fact tables I have joined with one common dimension ., when we pull measure columns from two fact table and one dimensional attribute the report is working fine,

2) Pulled a  numeric value(no aggregation logic applied in rpd), one measure from one fact table , one measure from another fact table , one dimension attribute . getting cast (null as double precision) in the query and values are displaying as null,

How to resolve  cast (null as double precision).

Thanks

Rajagopal R

Gianni Ceresa

3357027 wrote:2) Pulled a numeric value(no aggregation logic applied in rpd),

Is that one coming for the logical fact table? If you answer yes it means you modeled things wrong. In a dimensional model a fact table only contains aggregated measures, anything not aggregated and being an attribute must be modelled as a dimension. It's what dimensional modelling call "degenerated dimension". Model it as dimension, set the required content level and things will work fine again.

Rajagopal R-Oracle

thanks for your response ,its a measure column , just to show the value without any aggregation , still we need to move it to dimension (degenerated)?

Gianni Ceresa

Do not think I'm playing with wording but a column without any aggregation isn't a measure from a dimensional modelling point of view.

It would work fine without multiple facts and conformed dimensions, but because you have these 4 facts and conformed dimension it must be moved into a dimension so you can set the appropriate content level to it.

Content levels is what allows OBIEE to work with multiple facts and conformed dimensions, everything not being aggregate must have the right content level setting or you end up with NULLs in your analysis.

Thomas Dodds

"thanks for your response ,its a measure column , just to show the value without any aggregation , still we need to move it to dimension (degenerated)?"

If it's not aggregated @Gianni Ceresa is correct ... BUT you can aggregate it if you just need a discreet value with MAX, MIN, FIRST, or LAST  Pick the one that best fits your use ... if you are planning on filtering - then it is an attribute and should be in a dimension.

[Deleted User]

tl;dr - degenerate dimension. Period. Do what Gianni and Thomas have already told you and then report back.

Souro

If you have multiple facts only measures from both facts work with the conformed dimension. Is there any way to pull attribute columns from two facts and a confirmed dimension.??