Feeds:
Posts
Comments

Filtering Presentation Hierarchy Columns From Report Navigation

May 30, 2013 by obieeone

It has been awhile, I think a year now, since my last blog.  It has just been a very busy past year.  In any event, I came across a problem that involves presentation hierarchy columns.  The ever-beloved new feature of 11g , which all users and clients love to use for pretty much all reports.

The issue: How to create a navigational report which will filter the presentation hierarchy column based on the selected record from the calling report?  This is otherwise known as report navigation via action links.

Well, there is few solutions already out there by Venkat from Rittman Mead here.  And similar others like this one, here. Pretty much, almost the same idea and way of doing it.  Each will work, but takes lots more development and maintenance.  There should be an easier way, right!  Like the “is prompted” option in filters.  But presentation hierarchy columns cannot be used in the filter section, only their corresponding level attribute counterparts can be added in the filter section.  This leads to the 2 solutions linked above to some extent.  To use any sort of filtering or pruning of the hierarchy columns however, remember it would need to be done in the  “selection steps” section.

So what is the easier way that I found, which is simple and easier to manage!?

First it all centers around selection steps and the “overridable” attribute for the xml attribute “saw:selectionStep” within the advanced tab. But you do not even have to go there to change anything, as I will show you the simple steps to be able to set this using the UI.  However, if you have an older version of 11g instance (I am using 11.1.1.6 version) you will need to modify the xml, which I will describing later.

The first step is to open up the “Selection Steps” pane in the target report.

Then “Edit” the 1 step within the presentation hierarchy column.

Step1

Third step is to switch the value of the “Action” drop down to: “Start with all members”, which will change the interface.  Now you will only see “Override with Prompt” checkbox.  Just mark this box and you are all set.

step2

Step3

This will add and set the “overridable” attribute = “true” as seen and highlighted below.

Important Advanced xml attribute setting

Now in older versions of 11g, I can not remember which version, this checkbox was added.  Before that, you did not have an easy way with the UI to set this.  Instead, what you needed to do is go into the advanced tab and set the overridable=”true” attribute.  Sometimes, you will not see the “saw:dimensionSelection” so you would need to create it.  Easiest way to do that, was to go to the  “Selection Steps” pane and select one value in the first selection step-> “Start with members”.  Then you would go back to the selection step and edit it to change back to “Start with all members”.  Now once that action is done, the xml section is already created for you and all you have to do is ensure that the “overridable” attribute = “true” for the “saw:selectionStep”.

My results can be seen here in the example:

Here is the calling, or high level report:

High level report - run

Here is what the low level or target report looks like with no filtering

Low level report - before

Here is the same exact low level, target report navigated from the high level report based on selecting the “Accounts Receivable” value.

Low level report - after

Posted in Presentation Services | Leave a Comment »

Create Environment Patch (Environmentalization)

March 13, 2012 by obieeone

During RPD migrations we constantly need to change metadata for the target environment such as physical connection pool(s) and LDAP source(s).  To do this manually is time consuming and open for error.  Thus, there needs to be a way to script or automate this process.  This document will address the automated solution using what is termed in OBI as the patching process.

The first migration from Dev->Test, Dev->Prod, or source->target will need to be done manually. Meaning, the RPD that is ready to be migrated to the target environment will need to have its physical connections and LDAP source(s) manually changed for that target environment.  That would be done using the BI Admin Tool in offline mode.  After which, we can test our changes to ensure that the connections and information we entered is correct.  Then we can create the patch that will be used for later migrations of the RPD.

The following steps will be done after any manual changes needed for the target version of the RPD (this includes the first migration to the target environment), so that we can capture the patch XML to be used for later migrations.  We will need to follow these steps after any and all manual environment changes that are needed for the target environment, such as the initial migration or password or server changes.

  1. Open up the target version of the RPD in offline mode.  (In my example, I open up the Production version of the RPD after I made the manual environmental changes).
  2. Select File->Compare…
  3. Browse and locate the development RPD that was used to create the production version, prior to the manual changes.  (This RPD should be exactly the same as the opened RPD except that the conn pools, LDAP sources are changed for the target environment)
  4. Enter the selected RPD’s password
  5. Click Create Patch…
  6. Browse to folder and name patch xml file.  Click Save. ( I try to use a patch folder within a migration folder.  Also I try to use a standard naming convention for the patch file.  For my example, I put in patch folder and named the xml bipatch_env_to_prod.xml.

Now you have your patch for just the changes needed for environmentalizing an RPD to the target environment.  Looking at the patch xml I created you will see and should see just the following:

<?xml version=”1.0″ encoding=”UTF-8″ ?>

<Repository xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”&gt;

<DECLARE>

<ConnectionPool name=”MDW InitBlock Connection Pool” parentName=”&quot;Enterprise Warehouse&quot;” parentId=”3023:70678″ parentUid=”499″ id=”3029:70680″ uid=”17734975″ user=”user_prod” password=”F3145774D0C24AB12A9FA0BC016D5497A22F1B0CF28C8E9CFE23B03DFC115AAC2184BC7AA41ACD43616084824FF37DEE51F4AF446ED2EFBAFC04732D5CCBDB0A 6177A73195FA649BC952B38CDBECBA98E822325E721B9C66E5D909EEBB3FB2C1″ timeout=”300″ maxConnDiff=”10″ maxConn=”10″ dataSource=”PROD_DB” type=”OCI10G” reqQualifedTableName=”false” isSharedLogin=”true” isConcurrentQueriesInConnection=”false” isCloseAfterEveryRequest=”true” outputType=”xml” bulkInsertBufferSize=”32768″ tempTablePrefix=”TT” transactionBoundary=”10″ xmlaUseSession=”false” isSiebelJDBSecured=”false”>

<Description></Description>

</ConnectionPool>

<ConnectionPool name=”MDW Connection Pool” parentName=”&quot;Enterprise Warehouse&quot;” parentId=”3023:70678″ parentUid=”499″ id=”3029:70681″ uid=”18022356″ user=”user_prod” password=”F3145774D0C24AB12A9FA0BC016D5497A22F1B0CF28C8E9CFE23B03DFC115AAC2184BC7AA41ACD43616084824FF37DEE51F4AF446ED2EFBAFC04732D5CCBDB0A 6177A73195FA649BC952B38CDBECBA98E822325E721B9C66E5D909EEBB3FB2C1″  timeout=”300″ maxConnDiff=”10″ maxConn=”10″ dataSource=”PROD_DB” type=”OCI10G” reqQualifedTableName=”false” isSharedLogin=”true” isConcurrentQueriesInConnection=”false” isCloseAfterEveryRequest=”false” outputType=”xml” bulkInsertBufferSize=”32768″ tempTablePrefix=”TT” transactionBoundary=”10″ xmlaUseSession=”false” isSiebelJDBSecured=”false”>

<Description></Description>

</ConnectionPool>

</DECLARE>

</Repository>

As you can see I have changed 2 connection pools and I highlighted the changes I did in the admin tool for the target prod env.  You should only see those RPD objects for which you made environmental changes, as the rest of the RPD should be the same as the dev (or source) RPD which you based the target RPD for migration.

Now the next steps detail using this patch xml in subsequent migrations.  This patch xml will be used in a script that will automate the environmentalization of the rpd.  I like to make a folder structure like the following:

/migration – This folder holds everything related to migrations including the .bat script used to environmentalize stage RPDs to target_ready

/stage – This will hold the RPD(s) ready for migration

/patch – This holds the patch files

/target_ready

/test – Folder for test ready RPDs to be uploaded to test

/prod – Folder for prod ready RPDs to be uploaded to prod

To script the environmentalization, I use the xml API biserverxmlexec.  This API executes the XML in offline mode to create or modify a repository file.  It can be found in MIDDLEWARE_HOME\Oracle_BI1\bifoundation\server\bin.  The following example modifies the rp_dev.rpd and writes to rp_prod.rpd:

biserverxmlexec -I bipatch_env_to_prod.xml -B .\rp_dev.rpd -O c:\…\target_ready\prod\rp_prod.rpd.  You will be prompted for the password of the rp_dev.rpd. Give password: my_rpd_password

The newly created RPD is now ready to be migrated.  We now just need to upload the repository into the target (production in my example) environment using the FMC (aka Enterprise manager) upload process.

Now I would make a .bat (or .sh for UNIX/LINUX) file, which would call this biserverxmlexec API command and maybe include the base repository password so that I would not be prompted.  This is up to the individual.  If done so, this process is just a quick execution of the script and the new environmentalized RPD would be sitting and ready in the target_ready folder.  Now I always double check the created RPD just to make sure the script did what it was supposed to.  But there really should not be a need as it does the same thing every time.

Posted in Uncategorized | Leave a Comment »

Selection Step Start with all members; but with override with prompt

February 16, 2012 by obieeone

There seems to always be the occasion where you want to create a report, which will need the use of selection steps to include all the available members; but you want to override the values to keep/use with those of a dashboard prompt.  Currently, as of 11.1.1.5, the UI does not make it easy enough to do this.  As the Start with all members dialog does not have the Override with prompt checkbox; like, the Start with selected member does.  Nor does the Start with selected member option have the ability to specify or check you want all members.  So you have a couple options:

  1. Use Start with selected members with the option checked; but then you would need to select all members which is not really feasible with large member datasets or dynamic members.  The dynamic members is when the dataset may grow due to added member values such as locations for example.
  2. Have the first selection step be Start with all members.  Then creating 2nd selection step to use Select Members>Keep Only and checking the override option.  This also has the same problems pointed out in #1 above with having to select the members in large member dataset and dynamic member sets.
  3. Have the first selection step be Start with all members. Then creating 2nd selection step to use the Apply a Condition>Match>Name contains ‘abc’>Keep. Where the operator is to match all members using the % to bring in all members and selecting the Override with prompt option. This option fixes the dynamic and large dataset issues from 1 and 2; but brings in another issue that may or may not affect your report.  That being that conditional selection steps are pushed into the physical SQL query.  So care needs to be taken that the filtering in the query is not affecting the outcome you expected or wanted.  As for example, a rank in a report.  The filter is done prior to the ranking, so that the ranking is done on the reduced (filtered) result set and not the entire result set before the Keep only conditional one.

Anyway there has to be a better way, and that is what this posting is about to show.  I would call it a hidden gem, at least as of version 11.1.1.5, as Oracle may fix this issue and add the option in later releases.  All props to finding this go to Kyle Guillot.

To create the Start with all Members with Override with prompt functionality you just need to do the following:

  1. In the report/Analysis, go to the Advanced tab.
  2. Look for the column you are using in the selection step and wanting to apply this property.
  3. Then look for the ‘<saw:dimensionSelection>’ tag for that column.  It will be enclosed in the ‘<saw:column…>’ tags.
  4. You will see the following for the selction steps for that column:

<saw:dimensionSelection>

<saw:selectionStep stepID=”1″ type=”startWith” category=”member”>

<saw:stepMembers xsi:type=”saw:staticMemberGroupDef”>

<saw:staticMemberGroup>

<saw:members xsi:type=”saw:specialValueMembers”>

<saw:value specialValue=”all”/></saw:members></saw:staticMemberGroup></saw:stepMembers></saw:selectionStep></saw:dimensionSelection>

   The Start with all members step is always stepID=1 and later has the following tag in the saw:members which signifies such:

<saw:value specialValue=”all”/>

      5.  If you do not see this or any selection steps of dimensionSelection tag, do the following:

a.  Go to the criteria tab.

b.  Open up the selection steps.

c.  Go to the columns selection step and flip it to Start with members with anything really and hit ok to save.

d.  Then go back and switch to Start with all Members.  This will force the create of this tag in the xml for you in the advanced tab so you can add the overridable   attribute detailed below.

6.  Now to make this Override with Prompt, all that is needed is to add one attribute to the selectionStep tag highlighted below:

<saw:dimensionSelection>

<saw:selectionStep stepID=”1″ type=”startWith” category=”member” overridable=”true”>

<saw:stepMembers xsi:type=”saw:staticMemberGroupDef”>

<saw:staticMemberGroup>

<saw:members xsi:type=”saw:specialValueMembers”>

<saw:value specialValue=”all”/></saw:members></saw:staticMemberGroup></saw:stepMembers></saw:selectionStep></saw:dimensionSelection>

So that is it, you would just need to add the overridable=”true” attribute to the selectionStep tag to make it so.

Now, on another note, one may say they could have done #2 without the need to select all the members and/or worry about taking care of dynamically added new members by also modifying the second selection step Start With Members>Keep Only by also modifying the XML in the advanced tab by adding and modifying the highlighted code below:

<saw:dimensionSelection>

<saw:selectionStep stepID=”1″ type=”startWith” category=”member” overridable=”false”>

<saw:stepMembers xsi:type=”saw:staticMemberGroupDef”>

<saw:staticMemberGroup>

<saw:members xsi:type=”saw:specialValueMembers”>

<saw:value specialValue=”all”/></saw:members></saw:staticMemberGroup></saw:stepMembers></saw:selectionStep>

<saw:selectionStep stepID=”2″ overridable=”true” type=”keep” category=”member”>

<saw:stepMembers xsi:type=”saw:staticMemberGroupDef”>

<saw:staticMemberGroup>

<saw:members xsi:type=”saw:specialValueMembers”>

<saw:value specialValue=”all”/></saw:members></saw:staticMemberGroup></saw:stepMembers></saw:selectionStep></saw:dimensionSelection>

So what we did here was remove/modified the selected member list to the ‘All members special value’.  So you really could do this both ways.  But I prefer the first one as it is all done in the first selection step instead of creating 2 selection steps.  Oh, by the way, you could also do the above in just one selection step by specifying the first selection step as Start with Members with Override with Prompt option and then changing the selected member list in the xml as stated above.  Again, I still like the first solution as it is the quickest and easiest way with just needing to add the one  attribute overridable=”true’.

So make life easier, by making things simple

-Frank

Posted in Presentation Services | Tagged | Leave a Comment »

Alternative way to set multi-value LDAP parameters as row-wise session variables

January 6, 2012 by obieeone

Problem and challenge came up recently to pull and set a multiple value parameter from OVD (or LDAP source) and set session variable (row-wise session variable to be specific) to be used by report writing.  Well in OBI 10g, I usually have done that by using my custom LDAP table function to use along with setting row-wise variable(s) in an init block.  I looked to see if anything has changed along the lines of accessing LDAP data sources and pulling parameters.  There is no problem pulling scalar parameters and mapping them to session variables, only when it is comes to trying to pull multi-value parameters and setting a row-wise session variable.  I tried the interface in the admin tool to see if I could do it with no avail.  So I was going to revert to my old approach of using the table function and setting the row-wise session var using that.  Problem is, the client wanted to see if we could do so without using this table function at all.  The challenge!!!!  So I agreed, lets do it.

First, I would need them to set a scalar parameter in the LDAP source as a delimited string of all the values (We chose the notorious ‘,’ delimiter).   And since we will not be dealing with an extensive list of values, I do not foresee length or size issues. It is easy enough to extract this delimited value string now. No problem.  Now we could theoretically stop there and use this delimited value in reports; but it is more tedious as users would need to use and perform a LIKE operator on this session variable and the column compared to.  Not so eloquent if you ask me.  I would prefer the row-wise session variables and let bi server use that to build the physical query proper.

So what now! How do we take a session variable that is a delimited string and get a row-wise session variable out of it!?  Well here is my approach and the solution:

1) I set an init block to query against the LDAP data source to extract the delimited value list string. Also if need be make sure there is default value in case the parameter is not found for that user.

2) Create a new init block, used for setting the row-wise session variable.  This new init block will need to specify the other init block (in step one above) as a dependent using the execution precedence section of the init block properties.  This ensures the proper firing of the init blocks and the dependency between the two.  Then also specify it as being row-wise session variable.  Lastly, I used a pure SQL solution to parse and pivot the string into rows of the individual elements. Now, there could be many approaches to doing this including create custom functions and looking to see if there is such a function already in another package.  But, I just wanted a pure SQL solution.  Here is that SQL query solution (using MODEL clause):

 

SELECT ‘ROW_WISE_SESS_VAR_NAME‘, SUBSTR(str, start_pos, end_pos-start_pos) as value
FROM (
SELECT *
FROM (
SELECT ‘,’||’VALUEOF(NQ_SESSION.LDAP_DELIM_STR)’||’,’ as str,
1 as start_pos,
LENGTH(‘VALUEOF(NQ_SESSION.LDAP_DELIM_STR)’) as end_pos,
LENGTH(‘VALUEOF(NQ_SESSION.LDAP_DELIM_STR)’) – LENGTH(REPLACE(‘VALUEOF(NQ_SESSION.LDAP_DELIM_STR)’,',’))+1 as elem_cnt,
0 AS elem_no,
rownum as rn
FROM dual
)
MODEL
PARTITION BY (rn,str)
DIMENSION BY (elem_no)
MEASURES (start_pos, end_pos, elem_cnt)
RULES
ITERATE (4000)
UNTIL (ITERATION_NUMBER+1 = elem_cnt[0])
(
start_pos[ITERATION_NUMBER+1] = INSTR(CV(str), ‘,’, 1, CV(elem_no)) + 1,
end_pos[ITERATION_NUMBER+1] = INSTR(CV(str), ‘,’, 1, CV(elem_no) + 1)
)
)
WHERE elem_no != 0
ORDER BY str, elem_no

This worked like a charm and can be another solution, if the ldap table function cannot be used in order to get the same results.  Which is multiple value session var from an LDAP source.  Obviously the SQL query above can be used and modified for any such way to parse and pivot delimited string into multiple values.  Note: I am using modeling clause that I believe started to be available in Oracle 10g DB, and this is strictly an Oracle based solution.

I like using the modeling clause as I like to use advanced oracle sql and learn and keep knowledge of all the capabilities available to me.  For those not too familiar with these types of clauses I am adding an alternative SQL solution:

 
SELECT ‘ROW_WISE_SESS_VAR_NAME‘
, SUBSTR(str, start_pos, (next_pos-start_pos)) AS value
FROM
(
SELECT ds.str
, ne.rn AS element_no
, INSTR(ds.str, ‘,’, DECODE(ne.rn, 1, 0, 1) , DECODE(ne.rn, 1, 1, ne.rn-1)) + 1 AS start_pos
, INSTR(ds.str, ‘,’, 1, DECODE(ne.rn, 1, 1, ne.rn)) AS next_pos
FROM
(select ‘VALUEOF(NQ_SESSION.LDAP_DELIM_STR)’||’,’ as str from DUAL) ds
, ( SELECT ROWNUM as rn
FROM dual
CONNECT BY ROWNUM <= (LENGTH(‘VALUEOF(NQ_SESSION.LDAP_DELIM_STR)’) – LENGTH(REPLACE(‘VALUEOF(NQ_SESSION.LDAP_DELIM_STR)’, ‘,’))+1)
) ne
)

 

Posted in Administration Tool, BI Server | Leave a Comment »

OBIEE 11G error: No more than 1 hierarchy from the same dimension…

July 15, 2011 by obieeone

Thought some developers may find this useful and save time trying to work around, fix or look for answers. Users of the latest OBIEE 11g release 11.1.1.5 may get this error going against OLAP source systems.

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A
general error has occurred. [nQSError: 43113] Message returned from OBIS.
[nQSError: 22068] No more than 1 hierarchy from the same dimension Instrument
can be referenced.

If so, this is a Bug (#12628039) in the latest release, and is currently fixed in a patch (#12628039) for linux-64bit.  However other platforms are in the works for the fix and should be available shortly.

Posted in BI Server | Leave a Comment »

Update to Global Go Script for dashboard page scope

June 24, 2011 by obieeone

The current global go script I have not only produces one ‘go’ button for all prompts on a page; but, it also has code to create required fields and required field validations. The one thing that was brought to my attention was that my current script treated the prompt collective on the page to have dashboard scope. To read about prompt scope, read here. So whether or not you had one or all set for page scope the global go button disregarded and made it act like dashboard scope. So that any selections on the page would carry over on other dashboard pages. So I have updated my script so that it will use the scope from the first prompt as the entire pages scope for its prompts.  I have added and updated the script on my 2nd blog posting which had the new required field validation, here. For the instructions, on how to setup look here.

-Cheers

Posted in Presentation Services | Leave a Comment »

Scope of Dashboard Prompts

June 1, 2011 by obieeone

Some may think that the scope of the prompts do not work and are broken; but, they do work and are not broken! You just need to know what it really does.  So when you specify a dashboard prompt as scope-> ‘page’, what this does is say that any selections made for this prompt will not be used to set other prompts within the dashboard.  When you specify a dashboard prompt’s scope-> ‘dashboard’, this says that any selection of this prompt will be reflected in other prompts within the dashboard.  I think people get it confused and think that the ‘page’ scope shields the prompt from receiving selections from other ‘dashboard’ scope prompts; but, it does not.  The scope is only there to specify if the selection within the prompt will be local (page) or global (dashboard).

So for any issues where your prompt is still being set by other page prompts within the dashboard, the reason is due to the other prompt having scope-> ‘dashboard’.  If you do not want this behavior within the dashboard, set all the prompts’ scope to ‘page’.  So with this knowledge you will know when to set a prompt to ‘dashboard’ or ‘page’.  ‘Overview’ or ‘Summary’ dashboard pages are a good example of using ‘dashboard’ scope for its prompts, while the other pages in the dashboard are set for  ‘page’ scope.

Posted in Presentation Services | Leave a Comment »

Older Posts »

Follow

Get every new post delivered to your Inbox.