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.
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.
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?
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...
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?
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).
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
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.
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.