How to make objects incompatible

[nisha_18]

How can I make 2 objects(2 dimensions or 1 measure&1 dimension) coming from 2 different tables(they are not aggregate tables) incompatible.

Hope this is much clear than the question above.

Any help appreciated.

Thanks,
Nisha

[Moderator Edit - Duplicate post in this topic removed]

[Duff]

Again, it sounds like a context issue, but I would need more information to be sure. Are you saying that two dimensions objects from different tables are incompatible, but all the other attributes in those tables are compatible? That would be pretty strange.
Could you explain the situation a little better? Maybe provide your Universe?

[nisha_18]

Hi Yo,

I already have a universe in place and right now the objects are compatible but the users wants them to be incompatible.This is how it is:

ObjectA(dimension) from Table A
ObjectB(dimension) from TAble B

Right now if I run a report it gives me results.
SELECT
Table A.Column_name1,
TAble B.Column_name1
FROM
Table A,
Table B,
Table C
WHERE
TAble A.key = TAble C.key
and TAbleC.key1 = TAble B.key1

Now I have to make these objects incompatible..How do I do this?If you need more info,Please let me know

Thanks

[Duff]

Hi Nisha,
I can give you a quick answer - You can define one of your dimensions as Aggregate Aware with only one Aggregate option, like...
@Aggregate_Aware(Table A.Column_name1)
that will force the report builder to look at Aggregate Navigation for the object. In Aggregate Navigation, you make Table A incombatible with ObjectB. Now all Aggregates in table A will be incompatible with ObjectB, including ObjectA.
But I don't think this is sound design and I don't think it's the root of your issue. But I hope it helps.

[Anita Craig]

So, should you only be able to report on Table A - Table B at one time, and Table B - Table C at another? If so, it's like Yo was saying all along -- you then need two different contexts.

[nisha_18]

Hi,

How can I determine that 2 different objects from 2 tables are being used in the same context or in 2 different contexts.Is there a method to determine that or should I just look at the universe(Becos I have almost 250 contexts in the universe)

Thanks,
Nisha

[Anita Craig]

[nisha_18]

Hi,

I know thats a lot..This is a huge universe with almost 1000 joins.Anyways,I was thinking if I can make a measure from one table(not aggregate table) incompatible with a dimension(not from aggregate table) using the following syntax as told by Yo before:

Define Measure as @Aggregate_Aware(sum(nvl(decode(TableA.Column_name1, 1002, TableA.Column_name2,0),0)))

The same measure is defined as follows right now:
(sum(nvl(decode(TableA.Column_name1, 1002, TableA.Column_name2,0),0)))

And then set the TableA incompatible to the dimensions from TableB in Aggregation Navigation

It works if I do it in the above way...But Is this a good way to define incompatibility? I am just worried if I define incompatibility as above,would it effect any other tables/objects in the universe.

Appreciate ur response.

Thanks,
Nisha

[nisha_18]

Any help appreciated regarding the above.

Thanks

[Duff]

The direct impacts of your change is -
Any objects that are aggregate aware and use TABLEA will not be compatible with ANY objects that you defined TABLEA to be incompatible with in Aggregate Navigation.
I noticed you said "Dimensions" (plural)

[nisha_18]

Hi Yo,

Thanks a lot for your response. If I have to work this problem around using contexts,how do I go about it. I mean if I run a report using those 2 objects from 2 different tables,how do I know which context it is using and what do I have to change in the context.

Thanks,
Nisha

[Duff]

Hi Nisha,
To determine the context(s), check this post >
http://www.forumtopics.com/busobj/viewtopic.php?t=17431&highlight=triangles
To change it, double click the context. That will bring you to the Edit Context dialog box. All the Joins in the context will be highlighted. just click the one you want to remove to un-highlight it. Then click OK.

A couple of things you should know -
If 2 objects are put on a report that are not in the same context, BO may still allow them to be put on the same report by building multiple queries. This is a setting in the Universe parameters > SQL tab > Multiple SQL Statements for each Context. Typically this is a good thing because it allows different Facts to be reported by conformed dimensions on the same report.

If you have any reports that currently use the Join in the Context that you are removing, it will change how the reports are built, so you'll have to test them. Of course, you would only be doing this ...

[nisha_18]

Thanks a lot Yo.That helps in solving my issues.This is a big procedure as we have tons of joins and tables.

Appreciate your help again.

Thanks,
Nisha