Issue with stitch join with attributes from fact tables

2828715

Hello Experts,

I have a question regarding stitch join in OBIEE. I have 2  facts and 5 dimensions.

Fact Summary--> Dim 1,Dim 2,Dim 3

Fact Detail--> Dim 1,Dim 2, Dim 3,Dim 4,Dim 5

1 record in Fact Summary can pertain to multiple records in Fact detail.

Fact Summary has 5 metrics and 4 attribute columns as well defined at same grain.

Fact Detail has 8 metrics and 6 attribute columns  defined at detail grain.

I designed 2 separate stars in the physical and BMm but clubbing them under 1 sublect area in Presentation layer.

Running reports on the individual facts along with the attribute columns from facts is also fine, as its a single star.

Now if  I am trying to build a report with metrics from both the fact tables and some conformed dimension columns, BI server issues 2 separate queries and then stitches the result set together using outer join, which is fine.

Problem comes up when I am trying to add a attribute from any of the fact tables to the above anlaysis, the Bi server queries against 1 fact table and zeroes out the result from other fact.

Say My report contains the below columns Dim 1.X,Dim 2.Y,Fact_Summary.metric1,Fact_Summary.metric1,Fact_Summary.attribute1,Fact_Detail.metric1

the query is fired against only Fact Summary and Fact_Detail.metric1 is zeroed for result set , showing error nQSError: 14020 for Fact_Detail.metric1 in query log.

Please suggest what can be done

Gianni Ceresa

Hi,

I assume you have the attributes coming from the 2 fact tables in the logical fact table?

If it's the case you have a problem with your model. In a dimension model the fact table must contains only measures, any attribute must be outside in a dimension. In your case it would be a degenerated dimension (that's how dimensional modelling manage attributes stored in fact tables).

If you model it right and set the correct content levels everywhere your query will work again.

[Deleted User]

After Gianni b*tching about me beating him to the punch today he's beating me to it.

Yes: degenerate dimensions! That's the key. Attributes must NOT reside in logical fact tables unless used for calculations, counts or the likes. Attributes which are used to aggregate facts BY (keyword here) must be in an own - logical - degenerate dimenson.

2828715

Thanks for the replies Gianni and Christian, so as I said these attributes are actually coming from the physical fact table itself, so based on what you are suggesting, in the BMM layer shall I bring these attribute columns from the fact table and create a new logical table just for these attributes?

In my case I do not have a surrogate key for my fact table, so how do I create the hierarchy for this logical dimension?

Gianni Ceresa

Yes you must add them into a logical table acting as a dimension.

Worst case if you can't find a better business key just add all the columns to it...

2828715

So adding all the attribute columns to a logical dimension without having a hierarchy set, will that work? as it has no key to define.In my case I dont have a business key from single column that uniquely identifies, can I use multiple columns for business key?

Gianni Ceresa

The hierarchy is mandatory (I assume you have hierarchies for your other dimensions, right? So just add one for this new table as well: grand total and detail level right after, no need to use it or add it to the subject area).

A business key can have as many column as you want (the max is all the column of the logical table).

2828715

Yes, I did create hierarchies for all the other dimensions.

so I created 2 degenearte logical dimensions with attributes from fact A to Degen Dim A and attributes from fact B to Degen Dim B, created the businness keys for the dims and also the basic total and detail hiererachy. Created the logical join in BMM with the new dimensions, and set the content levels or fact A to detail for degen Dim A and at Total to Degen Dim B similarly for Fact B.

But still when I am trying to build the report

Dim 1.X,Dim 2.Y,Fact_Summary.metric1,Fact_Summary.metric1,Degenrate DimA.attribute1,Fact_Detail.metric1

it still NULLs the metrics from FactB and in log gives me the error converted to NULL because [nQSError: 14020] None of the fact tables are compatible with the query request

2828715

I finally got it to working by setting all the metrics from Fact A to total level for the attributes from Degenerate dimension Fact B which resolved the issue. But the downside I see from this is, every time  I try running such a scenario the other metric is aggregated fully which might throw some performance issues and manual work of adding the levels for all the metrics, Please suggest if there is any other better way to handle this.

[Deleted User]

2828715 wrote: Please suggest if there is any other better way to handle this. 

Well that's how things work. You can be glad that the tool can resolve non-conformes dimenasionalities on the fly because otherwise you would have to immediately go for the alternatives:

Physical pre-aggregation and/or total redesign of your physical sources to match your requirements.

Not sure what you expect here. There is no magic, it's software so when the sources don't perform due to their design then the tool can not speed things up.