OBIEE in IL: February 2014

Thursday, February 27, 2014

OBIEE BUNDLE PATCH 11.1.1.7.140225 AKA 11.1.1.7.5 (?) is available

Please note OBIEE Bundle Patch 11.1.1.7.151020 is available. You might prefer it.

OBIEE 11.1.1.7 fifth/forth general bundle patch, 11.1.1.7.140225 is available in Patch 18057570:

This time my download failed both on Chrome and Firefox. Thanks to a tip from a colleague I used an add-on to Firefox "DownloadThemAll" that did the job (on the second attempt).

The Patch is platform specific and is available for:

HP-UX Itanium
IBM AIX on POWER Systems (64-bit)
Linux x86
Linux x86-64
MS Windows (32-bit)
MS Windows (64-bit)
Oracle Solaris on SPARC (64-bit)
Oracle Solaris on x86-64 (64-bit)

You can read the readme file here.

Unlike 11.1.1.6 patches, this patchset is downloaded in a single big file+one bonus patch. So just for the record (since you don't need to download them separately):

Patch	Abstract
16913445	Oracle Business Intelligence Installer (BIINST) - same as previous patch
18057680	Oracle Business Intelligence Publisher (BIP)
17891247	Enterprise Performance Management Components Installed from BI Installer 11.1.1.7.0 (BIFNDNEPM)
18057654	Oracle Business Intelligence Server (BISERVER)
18057646	Oracle Business Intelligence Presentation Services (BIPS)
17300045	Oracle Real-Time Decisions (RTD) - same as previous patch
16997936	Oracle Business Intelligence ADF Components (BIADFCOMPS) - same as previous patch
18057667	Oracle Business Intelligence Platform Client Installers and MapViewer

As usual there is a bonus patch: Patch 16569379 (1111770)- Dynamic Monitoring Service patch. It is platform dependent. If you installed it with 11.1.1.7.1 or later, that's enough. It's the same.

Oracle BI EE Suite Bundle Patch 11.1.1.7.140225 contains no new features.

In the ReadMe here there are special instructions for upgrading from 11.1.1.6.X to 11.1.1.7.140225 at "Section 3.1: For Customers Moving From 11.1.1.6.x to 11.1.1.7.140225". DON'T upgrade before you read and understand it.

Bugs fixes

BI Publisher

New bug fixes in this patch are shown in the following list:

14826266: LOV SEARCH DOES NOT WORK FOR NON-DEFAULT REFRESHED LOV

17009309: CUSTOM REPORTS ALSO REQUIRE BIAUTHOR PRIVILEGES

17773119: BI REPORT END WITH XDODMSEXCEPTION ERROR AFTER UPGRADING TO REL7

17813460: JOB MANAGEMENT PAGE NEEDS TO HANDLE NULL CONTEXT CASE

17813520: BIP JOBS SUBMITTED TO ESS SCHEDULER VIA EJBS ONLY RUN ONCE

18025730: VIRUS SCANNER GETS CALLED MANY TIMES DURING CREATE/EDIT REPORTS

18132650: LINUX.X64_MATS_BI_SFTWARE_CONFIGONLY_WLS1036 - 33 DIFFS

18177497: AFTER REL-7 UPGRADE, ALL BIP REPORTS ARE NOT FETCHING ANY DATA WITH DEFAULT PARA

BIFNDNEPM

New bug fixes in this patch are shown in the following list:

14686164: ESSBASE JAPI IESSMEMBERSELECTION.EXECUTEQUERY() FAILED

16425650: GETTING BINARY TOKEN ERROR WITH SMART VIEW RETRIEVALS.

16464161: REGRESSION: UNABLE TO SCHEDULE FR BATCH WITH ANY OPTION

17074868: ESSBASE CORRUPTED NODE PG IN B+TREE GENERATED DURING DENSE RESTRUCT

17238206: RUNNING MAXL SCRIPT ERROR IN PS3

17250533: WL CONFIG CHECK ERROR IN DTE AUTOMATION

17276881: SIZE OF SUMMATION OF ESSBASE INDEX FILES GROWS AFTER DENSE RESTRUCTURE

17375500: ESSBASE TIMEOUT/CRASH - FORMUALA PRESERVATION SUPPRESS MISSING EMPTY OUTLINE

17435798: AN INCREASE OF THE RETRIVAL BUFFER LEADS TO APPLICATION CRASH

17592672: PERFORMANCE ISSUE WITH TRANSPARENT PARTITION WITH THE ESSBASE ASO CUBE

17635070: CORE APPLICATION HANGS WHEN RETRIEVING DATA

17866589: ESSBASE MEMBERS DISPLAYED IN ALLOCATIONS HAVE CHANGED

BISERVER

New bug fixes in this patch are shown in the following list:

16239288: FIX FOR BUG 16239288

16416993: INSTALL SIGNAL HANDLER FOR BISERVER ON LINUX FOR EXTRACTING DIAGNOSTICS

16587885: CRASH IN NQSSERVER (SEGMENTATION FAULT)

17294458: REPORT PERFORMANCE DEGRADED AFTER 11.1.1.5-> 11.1.1.7.1 UPGRADE

17512831: NQSERVER IS CONSUMING HIGH CPU AND GETTING CRASHED WHILE WORKING WITH ESSBASE

17636761: PHYSICAL QUERY IS MISSING TABLES IN FROM CLAUSE WITH TALEO RPD

17728439: BISERVERXMLGEN TAKES MUCH MORE MEMORY THAN NEEDED BY RPD

17783679: PERFORMANCE ISSUES ON MDX QUERY FOR OBIEE 11.1.1.7.1 ON EXALYTIC SAME IS NOT PR

17791002: SR:PERF:BI OBIS LOSES CONNECTION TO CLUSTER CONTROLLER WHEN PSTACK IS EXECUTED

17941395: NLS:QABLK:CUSTOM DISPLAY NAME FOR CRM EXTN LOV COLUMN SHOULD HONOR WEB LOCALE

17967785: CROSS FACT QUERIES WITH ENABLE_DIMENSIONALITY SET ARE FAILING

Presentation Services

New bug fixes in this patch are shown in the following list:

14843606: HIERARCHY WITH "SKIPPED LEVELS" EXPANDS TO LOWEST LEVEL WITH TABLE PROMPTS

15954420: ASSESSMENT ROLL-UP DOESN'T WORK PROPERLY WHEN KPIS REPORT NO_DATA

16604360: WEBCAT HANDLING OF STALE LOCK FILES BROKEN ON LINUX NOT WINDOWS

16682358: ERROR ON CREATING A TABLE VIEW WITH OVER 250 COLUMNS

16685997: SMART WATCHLIST RESULTS IN ERROR: UNABLE TO FIND BUSINESS OWNER ID

16823838: ERROR MESSAGE WON'T SHOW WHEN THE EXPORTED PDF DOESN'T CONTAIN ALL THE DATA

16832299: SMARTVIEW IS UNABLE TO IMPORT LARGE REPORTS FROM ANSWERS IN OBIEE

16923308: LOST BUSINESS OWNER IN SCORECARD AFTER MIGRATING FROM DEVELOPMENT TO PRODUCTION

16989567: NULL POINTER EXCEPTION WHILE COPYING OBJECT FROM ONE CATMAN TO ANOTHER

17009066: OBIEE 11G: SCROLLBAR DISAPPEAR IN THE EDIT MEMBERS

17071481: REPORT PROMPTS SHOWS INCONSISTENT WHEN CLEARING VALUE OF LIMITED PROMPT

17160361: SIEBEL_MEMORY_THREADALLOC_QUOTA DOESN'T WORK WITH SETTING >= 2GB

17291534: PAGE BREAKS NO LONGER WORKING IN 11.1.1.7-- WORKED CORRECTLY IN 11.1.1.6.X

17296905: UNABLE TO CHANGE THE DEFAULT OPTION FOR DATA VIEWING TO CONTENT PAGING

17383081: EXPANDING SHARED FOLDER SHOWS NO CONTENT

17574803: API TO EXTRACT AND APPLY FLEX FIELD CONTENT FROM CUSTOMER RPD TO NEW RPD

17576695: BISERVEREXTENDER INTRODUCES CORRUPTED UPGRADE ID'S IN THE RESULTANT RPD

17597985: SAVED FILTER WITH APOSTROPHE (') SHOWS AS ' IN FILTER VIEW

17605135: SUPPORT FOR BIEXTENDER SIMULATION VIA ADF METADATA XML FILES

17713855: INCREASE JRF/JMX MAXIMUM MESSAGE SIZE TO 200MB

17737937: BUG 14527683 IS STILL REPRODUCED IN 11.1.1.7.131115

17739814: APPLYING PATCH FAILS IF WEB CATALOG IS MISSING PARENT FOLDER OF PATCHED OBJECT

17767636: SET CURRENT REPOSITORY PROPERLY DURING THE ADMINTOOL UI PATCHING

17859387: RESYNC API FILE MATCH MAY BE INCORRECT

17916818: SYSMAN WRONGLY REPORTS COMMIT FAILED WHEN 2 MGD SERVERS DEPLOYED TO SAME NODE

18027196: 7.X SPECIFIC FIX FOR BASE BUG 17509190

18057453: REPORT PARAMETER AND BUTTON NAMES DO NOT SHOW FIRST TIME AND APPEAR SECOND TIME

Remember to upgrade the BI Administration Clients with the 18057667 patch content that comes in the bundle patch (not relevant for Windows servers).

Wednesday, February 26, 2014

OBIEE - Custom Export to Excel link

This post (done in OBIEE 11.1.1.7.140114) is about creating links to export a specific analysis to excel. One option is using URL, the other is with Java Script.

Why do I want this anyway? By default I have an export to excel option on the dashboard (report links). There are few specific reasons:

Sometimes I want to get a URL that immediately returns excel.
In my case a customer wanted a slightly different version of the analysis to be exported to excel. So the dashboard has analysis A and the export is done on Analysis B.

Using URL

The GO URL options are documented in the integrator's guide chapter 6, unfortunately there is no option of exporting to excel there (just mentioning of the &Action=Extract option where "Results are displayed in an application-friendly format, such as for Microsoft Excel, without the paging control, hot links, and so on.") The only export options mentioned there are XML, HTML, or CSV:

saw.dll?Go&Path=/Shared/Test/SB2&Format=xx

Example:

saw.dll?Go&Path=/Shared/Test/SB2&Format=XML

Unfortunatly the Excel option is missing in the documentation.

The GO URL that export in EXCEL 2007 format is:

saw.dll?Go&path=/shared/Test/SB2&Format=excel2007&Extension=.xlsx

The GO URL that export in EXCEL 2003 format is:

saw.dll?Go&path=/shared/Test/SB2&Format=excel2003&Extension=.xls

If you prefer a complete URL, this one is working on my computer:

http://bdahab-lap:9704/analytics/saw.dll?Go&path=/shared/Components/test1/Overview1&Format=excel2007&Extension=.xlsx

For example the above link (starting saw.dll) in the dashboard:

The drawback of this option, if you want it in a dashboard: it always opens an extra tab in browser, when executed.

Using Java Script

This option is described here by Satya Ranki, from http://satyaobieesolutions.blogspot.com. A very nice solution where he gets the java script by viewing the Dashboard source code (right click, and View Source). Next you search a relevant string such as xls or xlsx. I'll take it from here and do some extra cleaning and understanding of the result, so the code will be easily reused.

The code i got was:

a class="NQWMenuItem NQWMenuItemWIconMixin" name="SectionElements" href="javascript:void(null);" onclick="NQWClearActiveMenu();Download('saw.dll?Go&ViewID=o%3ago%7er%3areport&Action=Download&SearchID=c2pa7kfl2gm6qvamee34fbbkmq&Style=FusionFx&Options=d&ViewState=egpscuvajonthfs8esaug5qhku&ItemName=Overview1&path=%2fshared%2fComponents%2ftest1%2fOverview1&Format=excel2007&Extension=.xlsx');"></a>

Lets create something useful from it:

<a href="javascript:void(null);" onclick="NQWClearActiveMenu();Download('saw.dll?Go&ViewID=o%3ago%7er%3areport&Action=Download&SearchID=c2pa7kfl2gm6qvamee34fbbkmq&Style=FusionFx&Options=d&ViewState=egpscuvajonthfs8esaug5qhku&ItemName=Overview1&path=%2fshared%2fComponents%2ftest1%2fOverview1&Format=excel2007&Extension=.xlsx');return false">Export</a>

Next I'll break it to lines for readability (code will stop working).

a href="javascript:void(null);" onclick="NQWClearActiveMenu();Download('saw.dll?Go

&ViewID=o%3ago%7er%3areport

&Action=Download

&SearchID=c2pa7kfl2gm6qvamee34fbbkmq

&Style=FusionFx

&Options=d

&ItemName=Overview1

&path=%2fshared%2fComponents%2ftest1%2fOverview1

&Format=excel2007

&Extension=.xlsx'

);return false">Export2</a>

I don't want the ViewID and SearchID in the code. Similarly, the style part will have to go.

The result is not only cleaner, but better transportable:

<a href="javascript:void(null);" onclick="NQWClearActiveMenu();Download('saw.dll?Go

&Action=Download

&Options=d

&ItemName=Overview1

&path=%2fshared%2fComponents%2ftest1%2fOverview1

&Format=excel2007

&Extension=.xlsx'

);return false">Export2</a>

I'll remove the line breaks

<a href="javascript:void(null);" onclick="NQWClearActiveMenu();Download('saw.dll?Go&Action=Download&Options=d&ItemName=Overview1&path=%2fshared%2fComponents%2ftest1%2fOverview1&Format=excel2007&Extension=.xlsx'

);return false">Export2</a>

All this division to ItemName and Path seemed cumbersome to so I united them (in path) and removed ItemName.

<a href="javascript:void(null);" onclick="NQWClearActiveMenu();Download('saw.dll?Go&Action=Download&Options=d&path=%2fshared%2fComponents%2ftest1%2fOverview1&Format=excel2007&Extension=.xlsx'

);return false">Export3</a>
I'll mark in yellow the parts you need to change, if you want to use it:
1. The Path
2. The string the user sees in the link.

<a href="javascript:void(null);" onclick="NQWClearActiveMenu();Download('saw.dll?Go&Action=Download&Options=d&path=%2fshared%2fComponents%2ftest1%2fOverview1&Format=excel2007&Extension=.xlsx'

);return false">Export3</a>

Now, as Satya advised, you can place it in a Text Object in Dashboard or Narrative in an Analysis (remember the Contains HTML mark, in both cases).

For example the following code:
<a href="javascript:void(null);" onclick="NQWClearActiveMenu();Download('saw.dll?Go&Action=Download&Options=d&path=/shared/Components/test1/Overview1&Format=excel2007&Extension=.xlsx');return false">Export to Excel</a>

Used in Dashboard text:

Used in Narrative:

Please note, the Java-Script option is sometimes sensitive and not as stable as the URL.

Tuesday, February 18, 2014

OBIEE Prompts - Last Year, Last Month and Custom Dates

This post (done in OBIEE 11.1.1.7.1.140114) is about creating specific type of prompt for periods: First part is about fixed periods (Last Year and Last Month) and in second part I will add Custom Dates option.

I follow Adrian Ward (@Addidici) on Twitter. He often posts links to different blogs and articles related to OBIEE. This way I found 2 things:

1. The last post of bidirect.blogspot.com OBIEE 11g – Advanced Prompts for Financial and Other Data such as ‘Last Year’, Last Month’ or ‘Last 6 Month’

2. The annoying fact: the link to BI Direct I have on the side bar was not updated for a long time, despite some interesting new posts there.

In this post Pravin Khadakkar describes a solution for a prompt when user can select from a list of period descriptions and the relevant dates are used in the analysis.

In this specific case, I wasn't happy about the solution. Since it's a very common case, I decided to write my version.

There were few things I didn't like:

Pravin asks why Oracle doesn't have this option prepackaged. It might be a good idea, unfortunately, there is no end to this list. Only in the last year I've seen over 30 different requests for such list (Last Year, Last month, Last 3 days, Last week, Previous Month, MTD, Yesterday, Week before last....).
The most common request is to add "Custom Dates".
In most cases there are few analysis in such dashboard and it might be better to calculate all the dates outside the analysis.

The first part will be about Fixed periods list. The second will add 'Custom Dates' option.

Fixed Periods List

Create a Dashboard Prompt. In this Prompt create a Variable Prompt with custom Values

(in this case Last Month, Last Year and Custom Date). We will use Custom Date later. The default is 'Last Month'. I named the Presentation Variable PV_Date_Group.

A second Dashboard Prompt (I named Hidden Prompt) will have 2 dates and will be hidden. (Actually for Last Year ad Month one date is enough, since the second date is Current Date, but for the general case...)

In this prompt there are 2 Variable Prompts

1. Presentation Variable P_Act_From_Date. Date Data type and Default Value:

SELECT case when '@{PV_Date_Group}{Last Month}'='Last Year' then TIMESTAMPADD(SQL_TSI_Year, -1,CURRENT_DATE) else TIMESTAMPADD(SQL_TSI_Month, -1,CURRENT_DATE) end saw_0 FROM "Sample Sales Lite"

2. Presentation Variable P_Act_Till_Date. Date Data type and Default Value:

SELECT CURRENT_DATE saw_0 FROM "Sample Sales Lite"

I can use the two Presentation Variables in any analysis for filtering.

The test Analysis is based on Sample Sales with Date, Revenue and Billed Quantity.
Since the data is somewhat obsolete, I added 4 years to each date.
I need the analysis to be "aware" of prompt changes, so I also have the column with the formula '@{PV_Date_Group}' (it's a string, don't forget the brackets. I always do.) I'll mark hide for it in Column Properties -> Column Format.

As I said, the filter is between the two Presentation Variables, with some default dates, so I can test the analysis.

Next task is to hide the date prompt.
I covered it in the past here. (Edit Dashboard->dashboard properties->Filters and Variables, add the hidden prompt).

(yes, there is a spelling mistake in the name).

The result is working:

Next step is adding the Custom Date option.

Period List + Custom Date

We have 2 prompts, One hidden and one selects the period from list. When user selects there 'Custom Date' I want to be able to open additional prompt (third) and select dates there.
I'll create a third prompt with two Variable Prompts based on calendar and Data type Date.
(see here about forcing Variable Prompt to be Calendar input).

I want some date default for the Prompt, I don't mind what, as long it's a date. In this case, P_From_Date default is:SELECT TIMESTAMPADD(SQL_TSI_Month, -1,CURRENT_DATE) saw_0 FROM "Sample Sales Lite" (last month) and P_Till_Date default is: SELECT CURRENT_DATE saw_0 FROM "Sample Sales Lite".

I want to hide this part when it's not custom date selected. So I created the following analysis used as condition for this prompt section:

(The column formula is '@{PV_Date_Group}{Custom Date}' and the filter tests if it's equal to the string 'Custom Date'. This returns value only when 'Custom Date' is selected in the prompt).

Last part is to change the formula of the Hidden Prompt, so it returns the P_From_Date and P_Till_Date values when 'Custom Date' is selected, and previous values when not.

The new default SQL of the P_Act_From_Date is:
SELECT
CASE '@{PV_Date_Group}{Last Month}'
WHEN 'Last Year' THEN TIMESTAMPADD(SQL_TSI_Year, -1,CURRENT_DATE)
WHEN 'Last Month' THEN TIMESTAMPADD(SQL_TSI_Month, -1,CURRENT_DATE)
ELSE DATE'@{P_From_Date}' END saw_0
FROM "Sample Sales Lite"

(Meaning: when Last Month selected Current Date minus Month, When Last Year selected Current Date minus Year, Else return the P_From_Date of Custom Date).

In Similar Fashion, P_Act_Till_Date is:
SELECT CASE '@{PV_Date_Group}{Last Month}'
WHEN 'Custom Date' then DATE'@{P_Till_Date}'
ELSE CURRENT_DATE END
saw_0 FROM "Sample Sales Lite"

(Meaning: When Custom Date selected return P_Till_Date, else return Current Date).

Now, it's working both for Custom Dates:

And Set Dates:

You are not happy with the two Apply buttons? See here.

ADDENDUM (19-Feb-2014)

I implemented the above at customer site. To save the trouble with two dashboard prompts for Custom Date, We added the 2 Prompt Variables of dates to the first Dashboard Prompt, assuming the users are intelligent enough to understand the dates are relevant only if you select the "Custom Date" option. (And you might add a note about it in the dashboard).
It looks like this. Now I don't need the conditional section. All the rest is the same.

ADDENDUM 2 (16-Apr-2014)

Currently there is a limitation of passing presentation and session variables values with "Analyze" link. On the other hand, "Is Prompted" works fine.

In that case we still used hidden prompts, but connected them to 2 real columns. For example "Greater then" on "date1" and "Less then or equal" on TIMESTAMPADD(SQL_TSI_DAY, 0,date1) with the relevant defaults. In the analysis we used is prompted on each.

ADDENDUM 3 (19-Dec-2015)

The solution described here uses the date level in date hierarchy. It might be problematic when using aggregation awareness. I wrote another post about OBIEE - Selecting Date range from a Prompt Without breaking aggregation awareness.

Monday, February 3, 2014

OBIEE Prompts - 2 prompts on same column - Part 3

This is the third part on the "2 prompts on same column" issue. I will be dealing with 2 character / string prompts. (There will be no "number prompts" post. It is mostly trivial). Specifically comparing specific lists of values.
You might want to see the first 2 parts:

First part (here) was prompt options background.
The second (here) was about 2 date prompts.

I had the following request:

A prompt to select possible values from a column.A prompt to select one of them as the focal (the other should be used to compare).

There is similar, but much easier solution at the end.

In this example I will select several Years in first prompt. They will be used for filtering the report and for selecting values.
In the second prompt I will select one of the previously selected years.
In the analysis I will compare results of the selected Year with the other years in the first prompt.
Reminder: "Year" in OBIEE Sample is defined as Varchar 4.

First I will create a Column Prompt on "Time"."Year" with Multiple Selection and Presentation Variable P_Selected_Years:

Since it's a Column Prompt, the "Is Prompted" option should work.

Next I want to create a prompt for selecting a single value from the selected years.

This post explains first why several "logical" options don't work. You can skip to the ***** line if you want the practical solution.

Wrong idea is to create a Variable Prompt on the column "Time"."Year" and mark "limit values by" option. Of course it wouldn't work here, since it's the same column.

Instead I will use List Values, based on SQL Results:

The SQL will be the result of this Analysis:

From Advanced Tab I will get the following SQL: SELECT "Time"."Per Name Year" saw_0 FROM "Sample Sales Lite" WHERE "Time"."Per Name Year" IN ('P_Selected_Years')

as expected we don't see the presentation Variable in the SQL. My favorite foolproof method to return it, is to copy the relevant part from the filter description:

and pasting it in the SQL instead of the P_Selected_Years string:

SELECT "Time"."Per Name Year" saw_0 FROM "Sample Sales Lite" WHERE "Time"."Per Name Year" IN (' @{P_Selected_Years}')
this way I don't make mistakes with apostrophes.

This seems to work with one value in the Presentation Variable P_Selected_Years:

and fails with two or more:

Why does it happen? Because OBIEE expects '2008','2009' as the content of the Variable, but actually gets '2008;2009' as one string. We don't have to deal with it in Analysis filters (it works well), but here it's a problem.
There is a solution for this problem here, by Deepak.

**********************

As a result, what I actually do is to create a Variable Prompt, with Presentation Variable P_YEAR, User Input = Choice List, Choice List Values = SQL Results and the SQL:

SELECT "Time"."Per Name Year" saw_0 FROM "Sample Sales Lite" WHERE "Time"."Per Name Year" IN (@{P_Selected_Years}['@'])

Exactly as before, in my previous attempt, only with ['@'] after the prompt definition and no apostrophes around @{P_Selected_Years}.

Now it works:

Naturally I will not allow multiple values in this part.

My sample analysis is quite simple: Comparing the Revenue of selected Year with Average Revenue of all the other years in the list.

I can use "is prompted" on the YEAR column in Filters, I don't have to bother with the presentation variable "P_Selected_Years".

To calculate the Selected Year Revenue I will use Formula Filter.
Revenue of Selected Year is:
FILTER("Base Facts"."Revenue" USING ("Time"."Per Name Year" = '@{P_YEAR}{2009}'))

Revenue of compared Year is:
("Base Facts"."Revenue"-FILTER("Base Facts"."Revenue" USING ("Time"."Per Name Year" = '@{P_YEAR}{2009}')))
/
(count(DISTINCT "Time"."Per Name Year")-1)

This means: Total revenue minus Revenue of Selected Year divided by (number of selected Years minus 1).

Now I can subtract, divide or do any calculation I want with the columns or I can add Regular Revenue column and calculate % of total...

It's almost ready, but I had 2 more problems:

The 2 Apply buttons
They wanted the selected Year and Compare Years in the Column Header.

I will deal with the Apply buttons later in the post. Lets start with the header.

I have no problem with selected year:

I change my Column Heading to be: Revenue selected year @{P_YEAR}, as long a there is a value in the Variable, all is fine:

It's the Compared Years that give me the headache. For them I have to do the following:
I need a third presentation Variable that would actually contain P_Selected_Years minus P_YEAR.
The SQL of this prompt is:

SELECT saw_0 FROM ((
SELECT "Time"."Per Name Year" saw_0 FROM "Sample Sales Lite" WHERE "Time"."Per Name Year" IN (@{P_Selected_Years}['@'])
) EXCEPT (
SELECT "Time"."Per Name Year" saw_0 FROM "Sample Sales Lite" WHERE "Time"."Per Name Year" IN (@{P_YEAR}['@'])
)) t1

Both as Select and Default:

As usual, I was too lazy to write the code by hand and first created an analysis where I had One part of the date (protected filter Per Name Year is equal to / is in @{P_Selected_Years}) Minus the other (protected filter Per Name Year is equal to / is in @{P_YEAR}):

It works fine:

But I want the third Prompt to be hidden.

In Dashboard Properties (Edit Dashboard first).

In Filters and Variables

I will add the third dashboard prompt (and remove it from the dashboard)

As we did before, I'll change the header of column, to include the Presentation Variable: Revenue Compared @{P_YEAR2}

The result is:

Now back to the Apply buttons.

Since the presentation Variable is updated only after the APPLY, I can't have both in the same Dashboard Prompt.

The solution is to Hide the APPLY and Reset options, at least for the first prompt (otherwise the users will be annoyed for pressing APPLY twice).

That might be a problem because the analysis is run each time we update values in prompts. In my case, I have an irrelevant result before selecting Main Year.

There is a nice solution by M&S Consulting described here.

Much easier solution

Lets change the requirements just a bit: a prompt for focal Year and a prompt for Years to compare (if same Year selected in both I want a warning instead of analysis).
It seems almost identical to the complex solution above.

I have One Prompt, with 2 parts (this way I don't have the multiple Apply problem):
First is a Variable Prompt with Presentation Variable based on the Year column.
The other is regular Column Prompt on Year that set another presentation variable.

If you want the message when Years to compare contains the Main Year. Create a condition analysis. In both parts the column would be "Time"."Per Name Year" the set operation is Intersect and the filter would be "is prompted" for the first part and Per Name Year is equal to / is in @{P_Main_Year}{2010}in the second. This analysis will return data only if same year selected in both prompts, and can be used for section condition (to show the error message):

The actual Analysis is based on Union with Revenue for Product Type. I decided the default would be comparing Year 2010 to 2009:
The column Headers are:

Product Type
Revenue Year @{P_Main_Year}{2010}
Compare Revenue @{P_Compared_Years}{2009}

First part of the union is the Compared Years content (based on the second part of the Prompt, so "is prompted" will work here).

The second column is placeholder with formula sum(0).
The third is the Revenue divided by number of years ("Base Facts"."Revenue"/COUNT(DISTINCT "Time"."Per Name Year")).

Now a union with similar content to the above. This time the second column will be "Base Facts"."Revenue" and third columns sum(0). The filter would be Per Name Year is equal to / is in @{P_Main_Year}{2010}