Ability to Enable/Disable DISTINCT and ORDER BY Clause in OBIEE at Report Level

There was a requirement from my customer to be able to Configure OBIEE at the report level such that the 'DISTINCT' and 'ORDER BY ' clause added by the OBIEE generated query on dimensional attributes can be gotten rid of when desired .

One of the Reason why we wanted to have this feature in our Dashboard was PERFORMANCE . Some of the queries were taking awfully long time to fetch data because of 'DISTINCT' and 'ORDER BY ' clause.

Let us first have a look at what I am trying to achieve and then I will explain the configuration steps.
The screenshot below shows a simple adhoc request sent to OBIEE by selecting COL1

Analyzing the Query generated by OBIEE shows that DISTINCT and ORDER BY clause was added.

Now let us say if we would like to get rid of 'DISTINCT' and 'ORDER BY ' clause in this particular report query all that I am going to do is navigate to the Reports Advanced Tab and change the Value of the Subject Area from 'A' to 'B' as you can see below .

Let us re-run the same report and analyze the query . As you can notice below the 'DISTINCT' and 'ORDER BY ' clause has been removed for this particular query .

Let us now look into the steps to achieve this
1.Within the OBIEE Physical Layer>Database Features by default the value of 'DISTINCT_SUPPORTED ' is checked .

2.Unchecking 'DISTINCT_SUPPORTED' feature will ensure that all the queries that uses the Connection Pool defined within this Database to remove 'DISTINCT' and 'ORDER_BY' clause . But the requirement is to be able to change for any selected report .
3.So I Copied all the three layers of the Metadata within the existing RPD as you can see below.The Only difference between the two are that 'DISTINCT_SUPPORTED' feature in one(B) has been turned off and they are named differently in all the three layers as you can see below.

4.To Summarize the steps . For the Subject area within which you want to be able to Enable/Disable the addition of 'DISTINCT' and 'ORDER BY ' clause .
i.Append all the three layers to the existing RPD with a different name.
ii.Uncheck the 'DISTINCT_SUPPORTED' feature for one of the Physical Datbase connection.
iii.Restart your Oracle BI Server .
iv.Go to the Reports Advanced Tab and Change the Subject Area name to point to 'A' or 'B' in order to Enable/Disable the 'DISTINCT_SUPPORTED' feature for the Report.