OBIEE with Key / Name / Value table, the Union solution

A customer asked me to create an analysis on top of a Name - Value table. A table where instead of having one row for each key, there are multiple rows with the structure of Key_Value / Attribute_Name/ Attribute_Value. This structure is very nice for developers. They can add any attribute they want, but it's a nightmare for reporting. 

For example the following simple table:

They are few methods to deal with it both on database level (Pivot / Unpivot functions) and in the RPD. Actually, it should be done in the RPD, but just for this time...

If you prefer, you might use Logical SQL with Joins as described in the post "OBIEE Power to the people - joining the unjoinable".

But I decided to use the method of Unions at Analysis level.

For example, with the above table I need to create an analysis with the columns:

Key  |  Name  |  Surname  |  Blog

How do I do it? Something similar to the described at "OBIEE - Calculation between results of a union".

Create am Analysis that returns only the values of a specific Attribute, "Name", in this case. Add 2 empty columns with formula '' and press Union (top right).

 Now Copy / Paste or recreate manually the same with Filters of Attribute='Surname' and Attribute='Blog'.

In surname line change the value column (third) formula to '' and place the actual Value column instead the first ''

In the Blog line place it third.

 

If you can't see the filter, press the filter sign on top left.

Now we have the following result:

In the Criteria go to "Result Columns",

And Press "Add result Column"

Lets name the new column "Name" and it's formula should be Max("value" by "key"), but OBIEE will write MAX(saw_2 by saw_0), where saw_0 stands for the first column and saw_2 is the third.

 

In similar way create a Surname column - MAX(saw_3 by saw_0) and the Blog column - MAX(saw_4 by saw_0).

This is the result:

Exclude the 3 original Value columns and the Attribute column, and it's working:

You might have noticed that only key 'yyy' has a row of data for 'Security'.
What happens if we replace the filter of the third part of the union and set it to  Attribute='Security' (and change the column name accordingly)?
All is fine:

It can be dynamic as well. Just replace the filter with presentation variables, set the column heading to accept presentation variable and place the Analysis with Dashboard Prompt that updates those variables in a dashboard.

 

 

Just remember to use default values in the prompt.

OBIEE BUNDLE PATCH 11.1.1.7.141014 AKA 11.1.1.7.9 (?) is available

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

OBIEE 11.1.1.7 new general bundle patch (number 9?), 11.1.1.7.141014 is available in Patch 19261194 it can be installed on any previous 11.1.1.7 release.

I had no problems downloading the patch this time. If you have problems, try an add-on to Firefox "DownloadThemAll".

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 patches
19190053	Oracle Business Intelligence Publisher (BIP)
18657616	Enterprise Performance Management Components (BIFNDNEPM) - same as previous
19190025	Oracle Business Intelligence Server (BISERVER)
19190010	Oracle Business Intelligence Presentation Services (BIPS)
17300045	Oracle Real-Time Decisions (RTD) - same as previous patches
16997936	Oracle Business Intelligence ADF Components (BIADFCOMPS) - same as previous patches
19190111	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.

According to "Oracle EPM BI proactive support" here additional patch Patch 18277370 (MERGE REQUEST ON TOP OF 11.1.1.7.0 FOR BUGS 18071063 18176711) is required for IE11 users.

In the ReadMe here there are special instructions for upgrading from 11.1.1.6.X to 11.1.1.7.141014 at  "Section 3.1: For Customers Moving From 11.1.1.6.x to 11.1.1.7.141014". 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:

19685263:       QA:SEARCH OPTION FOR SINGLE SELECT PARAMETER NOT WORKING

19236186:         SEARCH VALUES NOT REFLECTED CORRECTLY WHEN CANCEL SELECTED IN SEARCH BOX

19550559:       PARAMETER VALUES GETTING DUPLICATED WHEN CLICK ON REMOVE OR REMOVE ALL

16364586:       MULTIPLE VALUE PARAMETERS ARE NOT PASSED TO NEW ENGINE.

17412119:       JKF - FUSION BI PUBLISHER PAGE HAS STOPPED WORKING ON PRODUCTION

17846765:       BIP - BUG 17463451 - QA: SECTION BACKGROUND COLOR DOES NOT SHOW IN THE EXCEL

17979086:       DEFAULT SELECTED VALUE IN PROMPT NOT APPEARING IN SEARCH WINDOW(ISSUE WHEN   PROMP

17979327:       REPORTSERVICE WEB SERVICE ALWAYS RETURNS FALSE FOR MULTIVALUESALLOWED

18025050:       BIP RUNS OUT OF MEMORY WHEN OS IS SET TO NON EN_US LOCALE

18077559:       IYA - MOZILLA FIREFOX CRASHES WHEN EDITING DATA SET

18113617:       R8BP: HTTP 500 ERROR WHILE TRYING TO EDIT PAYABLES TEMPLAT UNDER CUSTOM REPORT

18173394:       Fix for Bug 18173394

18306507:       QA: NO LINE BREAK SHOWS ON EXCEL NARRATIVE VIEW

18332362:       Fix for Bug 18332362

18336186:       11G:SCHEDULE PAGE IS NOT TRANSLATED ACCORDING TO LANGUAGE

18429472:       NLS:BIP WEB UI PAGE CAN NOT BE LOADED IN FA USER LANG IN CERTAIN SCENARIOS

18515334:       SCHEDULING REPORTS FAILS WITH SPECIAL CHARACTERS IN SEARCH CRITERIA FOR BIP 11G

18539339:       PARAMETERS PASSED FROM PROMPT TO BIP CONVERT COMMA (

18542946:       'FIRST WEEKDAY' NOT WORKING CORRECTLY IN SCHEDULER

18609529:       2:PSR:PERF:BIP: NEED OPTION TO SET DATA MODEL SCALABILITY MODE AT INSTANCE LEVEL

18622456:       LAYOUT EDITOR 'REPEATING SECTIONS' DESCENDING SORT NOT WORKING FOR PDF OUTPUT

18632799:       BI REPORT JOBS STOPPED RUNNING AFTER THE POD WAS TAKEN DOWN FOR MAINTENANCE

18665704:       LOV DISPLAYS PRODUCT ID INSTEAD OF PRODUCT NAME FOR SEARCH BEYOND LOV LIMIT

18712052:       PREVIOUSLY SELECTED VALUES NOT REFRESHED WITH DEPENDENT LOVS WITH 1000+ VALUES

18745662:       OPEN FILE HANDLES NOT BEING RELEASED

18816952:       PSR:PERF:BI BIP PLSQL CALL AT THE ROW LEVEL IN THE JDBC LAYER CAUSES OOM

18889966:       EXCEL FOUND UNREADABLE CONTENT IN 'NAME OF EXCEL FILE.XLSX'

18967973:       FOPROCESSOR GENERATES HUGE *.FO FILES UNDER /TMP

19032857:       ONCE CACHE TIMEOUT HAS EXPIRED, ANY OTHER REPORT RUNS ARE NOT FETCHED FROM CACHE

19131461:       SEARCH VALUES NOT REFRESHED IN DM EDITOR WHEN SELECTING LOV FROM SEARCH DIALOG

19214907:       WHEN LOV IS OF FIXEDDATA ,REPORT IS THROWING CLASSCASTEXCEPTION.

BISERVER

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

17804119:       OBIEE/BIP: UNABLE TO SEARCH LIST OF VALUES WHERE LOV QUERY USES A PARAMETER

18504891:       AFTER UPGRADE FROM 11.1.1.6, %AGE CALCULATION IN PIVOT TABLE IS INCORRECT 

18968751:       BI SERVER TAKES LONG TIME TO PROCESS OTBI LOGICAL REPORTS AFTER REL7->REL8 UPG

15845044:       NEWLY ADDED PRESENTATION TABLE ALWAYS GOT PUSHED TO THE BOTTOM IN SA

16371472:       UNEXPECTED RESULTS WITH LOCATE AND SUBSTRING FUNCTION

16796272:       ENABLE AUTOCOMMIT AND ADD TIMESTEN OPTIMIZER HINTS TO QUERIES

17407252:       REPORT WITH HFM CONNECTOR CRASHES HFM WITH MORE THAN 50 CHOICES

17442448:       FILTER ON HIERARCHY COLUMN WITH HFM CONNECTOR ERRORS:OPR4ONWY:U9IM8TAC:OI2DL65P

17503750:       EXTREMELY SLOW MUD CHECKINS - PARTLY DUE TO CONSITENCY CHECKS

17651328:       REPORT ERRORS OUT WHEN DRILLED DOWN ON A HIERARCHICAL COLUMN.

17820357:       EQUALIZERPDS VERSION STRING STORED IN .DATA SECTION INSTEAD OF .ORACLEVERSION

17831621:       INTERNAL ERROR WHEN TRYING TO DO "KEEP ONLY RELATED PARENTS" FROM ASO APPLICATIO

18160174:       CUSTOMGROUPMEMBERS IN MDX SHOWS NO RESULT BY ESSBASE-METHODS LIKE MEMBER.CURRENT

18171844:       REPORT ERRORS OUT WITH ESSBASE ERROR: REPEATED DIMENSION [ANALYSISCODE] IN MDX

18178380:       DELETING PHYSICAL COLUMNS IS NOT REFLECTED IN MASTER REPOSITORY

18234853:       AGGREGATE DATA INCORRECT FROM SAP BW DATASOURCE

18475438:       CSSTOKEN CODE PATH BROKEN

18615083:       RPD ACCESS CONTROL CHANGES BETWEEN 10G AND 11G

19076537:       OBIEE11.1.1.7.140415 [NQSERROR: 46008] INTERNAL ERROR: SQOIMDXGENERATORHYPERION.

18763373:       TIME DIM CUSTOM GROUP WITH SUM/+ GIVES WRONG RESULT ON TIME BALANCE MEASURE 

Presentation Services

New bug fixes in this patch are shown in the following list:19546808:       TRACKING BUG TO DELIVER BUG 16068402 FIX TO 11.1.1.7.141014 (OCT 2014 PATCHSET) 

17311027:       PROMPTING ON GROUPS CAUSES ERRORS WHEN EXPORTING REPORTS.

17220399:       CANNOT GET BACK TO TOTALS USING PIVOT TABLE PROMPT 

19277857:       PSUEDO HTML TAGS IN COLUMN FORMULA FAIL TO EXPORT IN JULY BP

18826493:       OBIEE 11G: NARRATIVE VIEW  EXPORT TO PDF CAUSES SAX PARSER ERROR:UH6MBRBC 

14782647:       CUSTOM CSS STYLE OF COLUMN HEADING DOES NOT WORK ON IE BROWSER

14803272:       QA:EXCEPTION ON EXPORTING A REPORT WITH CUSTOM NUMBER FORMAT EXCEL 2007

16556274:       QA: VALUE MENU NO POPULATED IN CONDITIONAL FORMT>NEW CONDITION IF BINS CASE STMT

16875993:       CHANGING SLIDER VALUE-YEAR,VERTICAL AXIS VALUES DISAPPEAR IN CHART WITH STYLING

16907102:       11G DISPLAY A PRESENTATION VARIABLE IN COLUMN TITLE FOR GRAND TOTALS SHOWS HTML

16919893:       RETURN LINK CLEARS DASHBOARD FILTERS

17057555:       UNABLE TO USE ESCAPE CHARACTERS IN CALCULATED ITEM - FAILS WITH SYNTAX ERROR

17308853:       SSO CANNOT WORK FOR EPM WORKSPACE

17342813:       PARTIAL DATA SHOWN AT PARENT LEVEL WHEN USING HIERARCHY OBJECT IN TABLE/PIVOT

17449621:       SELECTION  ON HIERARCHY COLUMN : CAN'T SELECT MEMBER  'OK GREYED OUT'

17515912:       'NO EDGE DEF: SAW_8834_B' ERROR AFTER CLICKING ON DRILLABLE COLUMN. IE7.

17653504:       HIERARCHICAL PRESENTATION VARIABLE NOT SET WHEN NAVIGATED VIA ACTION LINK.

17719891:       OBIEE 11G: SAVED RESULT SETS NOT PHYSICALLY DELETED

17734913:       CHOPPY AND PAUSED SCROLLING ON 11.1.1.7 SERVER VS 11.1.1.6 SERVER.

18191433:       OBIEE 11G - MULTIPLE DEVICE PROFILE NOT WORKING

18289005:       QA:FATAL ERRORS ARE THROWN WHEN TRYING TO EXPORT COLUMN PROMPT WITHIN DB PAGE.

18299341:       PRINT TO PDF ONLY SENDS FIRST 25 ROWS WHEN USING VIEW SELECTOR

18421243:       RELEASE LABEL VERSION EXTRACTION SHOULD USE UNDERLYING DATED LABEL

18430260:       PSR:FUNC:BI:BETTER THREAD QUOTA ERROR MESSAGE THAN STD::BAD_ALLOC

18512339:       INSERT PAGE BREAK NOT WORKING WHEN USED TOGETHER PLACE VALUE IN NEW ROW

18531410:       AUTO COMPLETE PROMPT BOX NOT DISAPPEARING WHEN TABBING

18601947:       PM: RESOLVE BASIC HTML IN VIEWS CONTAINING FMAP AND PAGENUMBER

18677382:       ADDING CALCULATED ITEM WITH FORMULA $N FAILS IN DIRECT DB REQUEST ANALYSIS

18699238:       LOG REASON FOR SESSION LOGOFF

18770064:       [1602241] SAWSERVER CRASHED ON 11.1.1.7.140225

18787874:       CLICKING ON 'NEXT 25 ROWS' ALWAYS RETURNS COMPOUND VIEW

18828701:       GETCOLUMNVALIDVALUE THROWS AN EXCEPTION WHEN A SEARCH EXPRESSION IS GIVEN

18896901:       PASSING VARIABLES TO THE HTMLVIEWSERVICE WEB SERVICE

18957880:       PAGE BREAKS NO LONGER WORKING IN 11.1.1.7.1.X

19065395:       COPYITEM SHOULD ALLOW COPYING ALL CONTENT OF A READONLY FOLDER TO A NEW FOLDER

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

You might want to look at note OBIEE 11g: How To Improve the Functionality of Exporting Data from Analyses, Dashboards, and other Oracle BI Presentation Catalog Objects into Microsoft Excel with OBIEE 11.1.1.7.131017 and Later (Doc ID 1595671.1)

Thank You Eira for the update.

OBIEE: NonEmpty MDX for Essbase (and Microsoft) - the secret life of PERF_PREFER_SUPPRESS_EMPTY_TUPLES

PERF_PREFER_SUPPRESS_EMPTY_TUPLES option in OBIEE Database features reminds me of the Japanese Fugu dish. It might be very tasty or deadly, if prepared without appropriate understanding. I have good news, unlike Fugu, you don't have to learn 3 years to use PERF_PREFER_SUPPRESS_EMPTY_TUPLES.

So what is it? If you Google, you will not find much, except the following description:
"PERF_PREFER_SUPPRESS_EMPTY_TUPLES: This is for Essbase only. If enabled, instead of applying non empty on the axis, which may contains a very sparse set. Each cross-join of two dimensions will have empty tuples suppressed before cross-joining another dimension. "

(You might also find out that in 10g, it used to be "environment variable OBIS_Essbase_NonEmptyTuples_Generation.Database.Catalog.CubeTable".)

 

This is no longer 100% correct. One of my customers (well done Dov Ziskin) persuaded Oracle Support to release this feature for Microsoft Analysis Services as well, as patch 18669600 (For OBIEE 11.1.1.7.140415 only).   

So what is PERF_PREFER_SUPPRESS_EMPTY_TUPLES? 

Before I continue, a confession: because of internal reasons, I never worked with Essbase, So I hope that what I describe for OBIEE with Microsoft Analytical Services is correct for Essbase as well.

When running MDX, the default code produced by OBIEE, creates an AxisSet that crossjoins all relevant dimensions and only then uses NONEMPTY function on the combination of the AxisSet and the relevant mesures. As a result, when combining large Dimensions there might be performance degradation and on Microsoft, sometimes the error: "The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples." (As I said, Very large dimensions). Enabling the PERF_PREFER_SUPPRESS_EMPTY_TUPLES adds the NONEMPTY to each crossjoin. So we use only combinations that has values in measures. It gives a performance boost in those cases. (1-2 seconds instead of 3-4 minutes or the above error in my cases).

If it's so great, why the Fugu allegory?
Because the MDX doesn't "know" what measures to use when using the "NONEMPTY" in the early stage of the AxisSet creation on the dimensions and uses the "Default Measure / Default Member". If your Default Measure covers all cases of data in the cube, life is great. If not, you will have incorrect results. So you must be sure you have a good default measure or create a synthetic one, that might be combination of all possible cube values. (for example on Microsoft: ALTER CUBE [Your Cube Name] UPDATE DIMENSION [Measures], DEFAULT_MEMBER = [Measures].[Your New Default Measure])

 

OBIEE - Direct Database Request and Presentation Variables

Kevin McGinley from Red Pill Analytics wrote nice 2 posts about OBIEE and Direct Database requests (Part1 and Part2 links). In case you don't have 17 minutes to read both, here is his "Readers Digest" on YouTube. Since the contexts of the posts was his OOW14 presentation on Self Service BI, he didn't cover my favorite part of OBIEE's  Direct Database request - the ability to use Presentation Variables in the select statement, and as a result natural integration of the result in a dashboard. This is the topic of the following post.

While the analysis itself can be based on Direct Database Request, the prompt must be based on a Subject Area. I created one based on the default Oracle database SH schema.
And created a simple Dashboard Prompt with a Presentation Variable P_Class:

The simple analysis will include a where statement with the Presentation Variable.

select Channel_desc, Channel_class, '@{P_Class}' PV  from sh.channels
where Channel_class ='@{P_Class}{Direct}'

but since I want the Presentation Variable to handle Multiple Values, I will change the code slightly (see OBIEE 11g Tips and Tricks blog for explanation)

select Channel_desc, Channel_class, '@{P_Class}' PV  from sh.channels
where Channel_class IN (@{P_Class}['@']{'Direct'})

The result in the dashboard is working fine:

This post doesn't cover the "All column values" option in prompt. 

OBIEE - GO URL, the old (Px) and the new (COLx) way

OBIEE had always the ability to run GO URL links and pass parameters. It is documented in many blogs (here, here, here...) and Oracle Documentation (6.3.2). Since version 11 there is additional option that is less documented. we will talk about both in this post.

(Done on OBIEE 11.1.1.7.140527) 

Px

The best know option uses Px parameters. For example, the basic URL looks like:

 saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate

or 

http://SERVERNAME:9704/analytics/saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate

When we want to pass parameters in the URL it comes in groups of 3:

1. Operator
2. Column
3. Value/s

The P0 parameter states how many such groups are passed.

So we might have one group:  p0=1&p1=opp&p2="column"&p3=value

Or 2 groups: p0=2&p1=opp1&p2="column1"&p3=value1&p4=opp2&p5="column2"&p6=value2

p0 is limited to 1-6.

From Oracle Documentation:

3.2.1 Navigation Parameters

The basic syntax of the navigation command is the same as presented in the section Section 6.2.2, "Structure of the Basic Oracle BI Presentation Services Dashboard URL", but with the addition of the Action=Navigate parameter, and then population of the P1 - Pn parameters, as necessary.

By default, you can add up to 100 parameters to the URL. However, you can adjust the number of parameters by modifying the Prompts/MaxPromptedURLParams setting in instanceconfig.xml. For more information see "Using a Text Editor to Update Configuration Settings" in the Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

&Action=Navigate

&P0=n  where n is the number of columns you wish to filter, currently 1 - 6.

&P1=op  where op is one of the following operators.

Operator	Meaning
eq	Equal to or in.
neq	Not equal to or not in.
lt	Less than.
gt	Greater than.
ge	Greater than or equal to.
le	Less than or equal to.
bwith	Begins with.
ewith	Ends with.
cany	Contains any (of the values in &P3).
call	Contains all (of the values in &P3).
like	You need to type %25 in place of the usual % wildcard. See the examples that follow.
top	&P3 contains 1+n, where n is the number of top items to display.
bottom	&P3 contains 1+n, where n is the number of bottom items to display.
bet	Between (&P3 must have two values).
null	Is null (&P3 must be 0 or omitted).
nnul	Is not null (&P3 must be 0 or omitted).
&P2=ttt.ccc	In this parameter, ttt is the table name and ccc is the column name. If the table or column contains spaces, it must be quoted with double-quotes. Spaces should be escaped as %20, for example, Measures."Dollar%20Sales".
&P3=n+xxx+yyy+...+zzz	In this parameter, n is the number of values, and xxx, yyy, and zzz are the actual values.

Note: If the value of P3 begins with a numeric character, the entire value must be enclosed in quotes. For example:

saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate&P0=1&P1=top&P2=Customers.Region&P3="7West"

Examples:

This returns records for the East and Central regions:
Saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate&P0=1&P1=eq&P2=Customers.Region&P3=2+Central+East
This returns records for like Regions E....t:
saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate&P0=1&P1=like&P2=Customers.Region&P3=1+E%25t
This returns the top two regions by dollars sold (1+2 means 1 value that is 2):
saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate&P0=1&P1=top&P2="Sales%20Facts".Dollars&P3=1+2
This is an example where the number of arguments is not included in the syntax:
saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate&P0=1&P1=top&P2=Customers.Region&P3=Central

COLx

Since OBIEE 11 there is additional method, that to the best of my knowledge is the recommended one.  It's not limited to 6 prompts and covers additional things such as hierarchies and Variable Prompts.

Basically we have a simpler solution: we can work in triplets of (with possible additional psa parameter):

• col - column
• op - operator
• val - value/values

with no P0 equivalent.
Since we can use this method to express much more, it might look a bit more complicated. It isn't.


For example lets take a sample dashboard and fill few parameters:

 Lets even select prompt value from the hierarchical column (the one that you won't find in criteria filter, but rather as Override with Prompt in Selection Step):

Now in dashboard menu I will select Create Prompted Link:

What is the result (with extra line breaks for readability and %20, %2F and %22 replaced by space, / and ")?  

http://MYSERVER:9704/analytics/saw.dll?Dashboard
&PortalPath=/shared/Sample Lite/_portal/QuickStart
&Page=Overview
&Action=Navigate
 
&col1="Products"."Products Hierarchy"
&hierid1=Products 
&dimid1=H1 Products
&tblnm1="Products"
&val1="Brands"+"FunPod"+"Product Types"+"Cell Phones"
&psa1="Sample Sales Lite"
 
&col2="Time"."Per Name Year"
&val2="2008"+"2009"+"2010"
&psa2="Sample Sales Lite"
 
&col3="Offices"."Office"
&val3="Foster Office"+"Tellaro Office"+"Eden Office"
&psa3="Sample Sales Lite"

Lets see few more examples:

Hierarchical Prompt without value:

&col1="Products"."Products Hierarchy"&hierid1=Products Hierarchy
&dimid1=H1 Products
&tblnm1="Products"
&op1=prompted
&val1="*"+"all"
&psa1="Sample Sales Lite"

Day of Year between 1 and 365:
&col2="More Time Objects"."Day Of Year"
&op2=bet
&val2="1"+"365"
&psa2="Sample Sales Lite"


What happens when using Prompt that is based on a column with Descriptor ID? We add the code &formulaUse1=code, where 1 is the same number as in col1, and in val we use the Descriptor ID value (in our case 18 instead of the product name).

&formulaUse1=code
&col1="Products"."Product"
&val1="18"
&psa1='Sample Sales Lite"

We see few interesting things:

• There is no P0 equivalent, the groups are numbered sequentially. 
• When using several values, we don't have to set the number of values.
• For non hierarchical prompts, the basic set is of &col, &op, &val, &psa.
• For Hierarchical prompts, the basic set alo includes &hierid, &dimid, &tblnm.
• When the operator is equal, it is omitted.
• The code runs fine (in standard cases) without &psa as well. 

 So to summarize:

For x, the number of the parameter in GoURL:
&colx - Table_Name.Column_Name
&opx - operator (the table in the Px section above is useful for possible values). Omitted for op1=eq. 
&valx - the value of prompt. Unlike p3 we don't have to define the number of values. Enclose the value (") and if you have few values use the plus sign (+). Same is relevant for between values as well. Example "1"+"3"
&psax - the parameter Subject Area.

 Only for Hierarchies we use also:
&colx - Table_Name.Hierarchy_Name (in presentation)
&hieridx - ???
&dimidx - The Hierarchy name in Business Model
&tblnmx - The Table Name in Presentation
&valx - the values can be from different hierarchy levels

Example:

 

&col1="Products"."Products Hierarchy"
&hierid1=Products 
&dimid1=H1 Products
&tblnm1="Products"
&val1="Brands"+"FunPod"+"Product Types"+"Cell Phones"
&psa1="Sample Sales Lite"


Now what happens with Variable Prompt?

We have only &var and &val  (and &psa, if you insist):
 &var1=dashboard.variables['KUKIU']
&val1="1"
&psa1="Sample Sales Lite"

So the value of &var is: dashboard.variables['var_name']
 (%27 = ')