Feeds:
Posts
Comments

Cross drilling! Or in star schema design, otherwise known as “Drilling Across”. This is where you want to report off metrics from 2 or more fact tables in the same report. This is easy when your report is using all conformed dimension attributes. But in most cases, the requirement is for these fact tables’ metrics and some non-conformed dimension attributes.

In OBIEE, we typically solve this by making LBMs of all a fact table’s metrics to the non-conformed dimensions total/all level. This will then allow users to be able to build reports across different fact tables with conformed and non-conformed dimension attributes. What happens behind the scene is the following:

  1. A query is issued for each fact table, aggregating them to the common level of the other fact tables.
  2. The results, or datasets, of each fact table query from #1 above is merged/joined based on the conformed dimension attributes using a full outer join.

This is the correct behavior and solution for solving cross drilling, which OBIEE has been doing forever. Now as of 11.1.1.7.1, if you are now on that release, the full outer joins are not there! Instead, it is using Left Outer joins. This will alter all the reports that you have written that required cross drilling and have different results. So what happened!?

Researching this for a client whom upgraded from 11.1.1.5 to 11.1.1.7.x, I came across this issue and investigated. It threw me for a loop.   Upon investigating, I found support doc Doc ID 1601703.1. As per that doc, “There was a change in behavior in 11.1.1.7.1. The issue matches this change. Basically there was an inconsistency in how filters were working with grand totals and subtotals. As a result, full outer joins were replaced with left outer joins.

I guess it can work with left outer joins; but, more than likely this solution will not give you what you want as a result. First of all, before I get to the solution to re-introduce full outer joins, you may want to know what table is being used left of the left outer join. From my experimentation, the table with the most conformed dimensions seems to be the one on the left. So if fact table A has the most conformity, then the SQL would be A LEFT OUTER JOIN B ON (…).

The question then is how do I get back to the FULL OUTER join. The answer lies in the session variable FOJ_GRAND_TOTAL_LBM, FOJ standing for FULL OUTER JOIN. As the Oracle doc states there are 2 ways to resolve: REPORT ONLY or INIT BLOCK approach. The REPORT ONLY solution will only solve this for an individual report, and you would have to make changes to each report in which you wanted the full outer join to be used. The INIT BLOCK solution will make this change global.

REPORT ONLY

In the advanced tab of the report, enter the following within the PREFIX box:

SET VARIABLE FOJ_GRAND_TOTAL_LBM=1;

INIT BLOCK

  1. Create a new session variable in the RPD: FOJ_GRAND_TOTAL_LBM
  2. Set the default value of this session variable: 1
  3. Under Initialization Block, click (This is to create the dummy init block)
  4. Set the init block’s name: SET_FOJ_GRAND_TOTAL_LBM
  5. Within the init block, click the Disabled check box.
  6. Click OK, within init block.
  7. Click OK, within the session variable.

(Note: the default initializer is all that is needed to set the value; but, all session variables need a init block assigned. Thus the need to create the disabled init block)

Row-level security in OBI is a very powerful tool. It is used to limit data seen by users/groups/roles based on a business’s security model. It really is not hard; but, you need to understand when and how data filters will be applied. This is the key that is not documented thoroughly (or that I have seen by searching on the web for clarification). So I did the work to clarify it for you, or I hope I am in this writing. Note: I said it is easy, because once you understand how OBI works in this regard it pretty much is. The biggest issue, from my experience, is getting the business to define their security model and how they want all the scenarios to play out.

 

What I will be showing is a bunch of scenarios and what happens behind the scenes. Based on these scenarios and outcomes, one should understand what BI Server is doing and put that knowledge to use for setting up their security based on security model requirements.

 

 

Scenario 1: User is assigned 2 app roles, ‘A’ and ‘B’, both app roles are total unrelated (meaning no hierarchy or membership of each other). One of the app roles (‘A’) has security filters, the other (‘B’) does not.

Here I am adding filter on presentation column for “Company Name” = ‘Foo’ for app role ‘A’.

security1

I built a simple report including that presentation column to test.

Outcome: The security filters for app role ‘A’ are added to the query. Here is the query produced for the report, as you can see the filter was applied!

SELECT T528077.PROFIT_CENTER_NAME AS c2,
T556965.MCAL_PERIOD_NAME         AS c3,
T556965.MCAL_PERIOD_WID         AS c4,
T528077.ROW_WID                 AS c5,
SUM(T551274.BALANCE_GLOBAL1_AMT) AS c6,
T556965.ROW_WID                 AS c7
FROM W_MCAL_DAY_D T556965,
W_LEDGER_D T551773,
W_PROFIT_CENTER_D T528077,
W_INT_ORG_D T538674,
W_GL_BALANCE_A T551274
WHERE ( T528077.ROW_WID   = T551274.PROFIT_CENTER_WID
AND T538674.ROW_WID       = T551274.COMPANY_ORG_WID
AND T538674.ORG_NAME       = ‘Foo’
AND T538674.COMPANY_FLG   = ‘Y’
AND T551274.BALANCE_DT_WID = T556965.ROW_WID
AND T551274.LEDGER_WID    = T551773.ROW_WID )
GROUP BY T528077.ROW_WID,
T528077.PROFIT_CENTER_NAME,
T556965.ROW_WID,
T556965.MCAL_PERIOD_WID,
T556965.MCAL_PERIOD_NAME
Summary: For mutually exclusive app roles, meaning no hierarchical relationship, when a security filter is placed on one role and not the other the security filter placed on the one role will be applied implicitly to the physical query.

 

 

Scenario 2: User is assigned 2 app roles, ‘A’ and ‘B’, both app roles are total unrelated (meaning no hierarchy or membership of each other). Each app role has a security filter on the same object. The data filter may be on the same object or different.

Same Object Data Filter:

App Role ‘A’ has same filter as scenario 1. Just added filter for app role ‘B’, on “Company Name” = ‘BAR’.

security2

Different Object Data Filter:

App Role ‘A’ has same filter as scenario 1. Just added filter for app role ‘B’, on “Account Name” = ‘ACME’. Please note: this is on the same presentation object but the Data Filter expression is on different logical column.

 

security3

Outcome: If the data filters are on the same logical object, it will treat them in an IN clause. If the data filters are on different logical objects, it will treat as ORs.

Same Object Data Filter:
SELECT T528077.PROFIT_CENTER_NAME AS c2,
T556965.MCAL_PERIOD_NAME         AS c3,
T556965.MCAL_PERIOD_WID         AS c4,
T528077.ROW_WID                 AS c5,
SUM(T551274.BALANCE_GLOBAL1_AMT) AS c6,
T556965.ROW_WID                 AS c7
FROM W_MCAL_DAY_D T556965,
W_LEDGER_D T551773,
W_PROFIT_CENTER_D T528077,
W_INT_ORG_D T538674,
W_GL_BALANCE_A T551274
WHERE ( T528077.ROW_WID   = T551274.PROFIT_CENTER_WID
AND T538674.ROW_WID       = T551274.COMPANY_ORG_WID
AND T538674.COMPANY_FLG   = ‘Y’
AND T551274.BALANCE_DT_WID = T556965.ROW_WID
AND T551274.LEDGER_WID     = T551773.ROW_WID
AND (T538674.ORG_NAME     IN (‘BAR’, ‘Foo’)))
GROUP BY T528077.ROW_WID,
T528077.PROFIT_CENTER_NAME,
T556965.ROW_WID,
T556965.MCAL_PERIOD_WID,
T556965.MCAL_PERIOD_NAME
Different Object Data Filter:

SELECT DISTINCT T528077.PROFIT_CENTER_NAME AS c1,
T556965.MCAL_PERIOD_NAME                 AS c2,
T556965.MCAL_PERIOD_WID                 AS c3,
T528077.ROW_WID                         AS c4
FROM W_MCAL_DAY_D T556965,
W_LEDGER_D T551773,
W_PROFIT_CENTER_D T528077,
W_INT_ORG_D T538674,
W_PARTY_D T605218,
W_PARTY_ORG_D T605644,
W_STATUS_D T529754,
W_AP_XACT_F T532384
WHERE ( T532384.LEDGER_WID       = T551773.ROW_WID
AND T528077.ROW_WID               = T532384.PROFIT_CENTER_WID
AND T532384.COMPANY_ORG_WID       = T538674.ROW_WID
AND T556965.ADJUSTMENT_PERIOD_FLG = ‘N’
AND T532384.CUSTOMER_WID         = T605644.ROW_WID
AND T529754.ROW_WID               = T532384.DOC_STATUS_WID
AND T532384.MCAL_CAL_WID         = T556965.MCAL_CAL_WID
AND T605218.PARTY_ORG_WID         = T605644.ROW_WID
AND T532384.DELETE_FLG           = ‘N’
AND T538674.COMPANY_FLG           = ‘Y’
AND T532384.POSTED_ON_DT_WID     >= T556965.MCAL_DAY_DT_WID
AND (T538674.ORG_NAME     IN (‘Foo’)
OR T605218.NAME           IN (‘ACME’))
AND (T529754.W_STATUS_CODE IN (‘POSTED’, ‘REVERSED’)))
Summary: For mutually exclusive app roles (meaning no hierarchical relationship), when security filters are applied for both app roles on the same object, OBI treats them as additive. Meaning, they will either be written as ORs or added within an IN clause.

 

 

Scenario 3: User is assigned to only the ‘A’ app role. ‘A’ app role is made a member of the ‘B’ app role. Only the ‘B’ app role has a security filter on it.

Only app role ‘B’ has a filter on the presentation column “Company Name” = ‘Foo’. All security filters on app role ‘A’ where removed. So ‘B’ looks like:

security4

Outcome: App role ‘B’s security filter is applied to the query.

SELECT T528077.PROFIT_CENTER_NAME AS c2,
T556965.MCAL_PERIOD_NAME         AS c3,
T556965.MCAL_PERIOD_WID         AS c4,
T528077.ROW_WID                 AS c5,
SUM(T551274.BALANCE_GLOBAL1_AMT) AS c6,
T556965.ROW_WID                 AS c7
FROM W_MCAL_DAY_D T556965,
W_LEDGER_D T551773,
W_PROFIT_CENTER_D T528077,
W_INT_ORG_D T538674,
W_GL_BALANCE_A T551274
WHERE ( T528077.ROW_WID   = T551274.PROFIT_CENTER_WID
AND T538674.ROW_WID      = T551274.COMPANY_ORG_WID
AND T538674.ORG_NAME       = ‘Foo’
AND T538674.COMPANY_FLG   = ‘Y’
AND T551274.BALANCE_DT_WID = T556965.ROW_WID
AND T551274.LEDGER_WID     = T551773.ROW_WID)
GROUP BY T528077.ROW_WID,
T528077.PROFIT_CENTER_NAME,
T556965.ROW_WID,
T556965.MCAL_PERIOD_WID,
T556965.MCAL_PERIOD_NAME
Summary: In app role hierarchical relationships, even though a user is not assigned to the parent app role, they will inherit the security filters from the immediate parent app role that has security filters applied to it.

 

 

Scenario 4: User is assigned 2 app roles, ‘A’ and ‘B’. ‘A’ app role is made a member of the ‘B’ app role. Each app role has security filters on the same object. We will follow the same setup as Scenario 2, except the app roles are now hierarchical. We will have 2 separate sub scenarios, the data filter may be on the same object or different.

App Role ‘A’ has the following filter: “Company Name” = ‘Foo’.

security5

Same Object Data Filter:

App role ‘B’ has the following filter: “Company Name” = ‘BAR’.

security6

Different Object Data Filter:

App role ‘B’ has the following filter: “Account Name” = ‘ACME’. Please note: this is on the same presentation object but the Data Filter expression is on different logical column.

security7

Outcome: If the data filters are on the same logical object, it will only use the lowest app role in the hierarchy. For our example that would be app role ‘A’, as ‘A’ is a member of ‘B’. If the data filters are on different logical objects, it also only uses the data filter on the lowest app role.

Same Object Data Filter:
SELECT T528077.PROFIT_CENTER_NAME AS c2,
T556965.MCAL_PERIOD_NAME         AS c3,
T556965.MCAL_PERIOD_WID         AS c4,
T528077.ROW_WID                 AS c5,
SUM(T551274.BALANCE_GLOBAL1_AMT) AS c6,
T556965.ROW_WID                 AS c7
FROM W_MCAL_DAY_D T556965,
W_LEDGER_D T551773,
W_PROFIT_CENTER_D T528077,
W_INT_ORG_D T538674,
W_GL_BALANCE_A T551274
WHERE ( T528077.ROW_WID   = T551274.PROFIT_CENTER_WID
AND T538674.ROW_WID       = T551274.COMPANY_ORG_WID
AND T538674.ORG_NAME       = ‘Foo’
AND T538674.COMPANY_FLG   = ‘Y’
AND T551274.BALANCE_DT_WID = T556965.ROW_WID
AND T551274.LEDGER_WID     = T551773.ROW_WID)
GROUP BY T528077.ROW_WID,
T528077.PROFIT_CENTER_NAME,
T556965.ROW_WID,
T556965.MCAL_PERIOD_WID,
T556965.MCAL_PERIOD_NAME
Different Object Data Filter:

SELECT T528077.PROFIT_CENTER_NAME AS c2,
T556965.MCAL_PERIOD_NAME         AS c3,
T556965.MCAL_PERIOD_WID         AS c4,
T528077.ROW_WID                 AS c5,
SUM(T551274.BALANCE_GLOBAL1_AMT) AS c6,
T556965.ROW_WID                 AS c7
FROM W_MCAL_DAY_D T556965,
W_LEDGER_D T551773,
W_PROFIT_CENTER_D T528077,
W_INT_ORG_D T538674,
W_GL_BALANCE_A T551274
WHERE ( T528077.ROW_WID   = T551274.PROFIT_CENTER_WID
AND T538674.ROW_WID       = T551274.COMPANY_ORG_WID
AND T538674.ORG_NAME       = ‘Foo’
AND T538674.COMPANY_FLG   = ‘Y’
AND T551274.BALANCE_DT_WID = T556965.ROW_WID
AND T551274.LEDGER_WID     = T551773.ROW_WID )
GROUP BY T528077.ROW_WID,
T528077.PROFIT_CENTER_NAME,
T556965.ROW_WID,
T556965.MCAL_PERIOD_WID,
T556965.MCAL_PERIOD_NAME
Summary: In app role hierarchical relationships, the security filter that will be used will be the first one with a data filter going bottom up in the app role hierarchy. So let’s say you have three app roles A, B and C where A is a member of B, and B is a member of C. If A has security placed on it, it will use A’s security. If A does not have security on it nor does B; but, C has security C’s security will be used.

 

 

Now, knowing these behaviors and how OBI treats data filters on app roles and the app roles hierarchy, one can pretty much setup OBI to how they want security to act. For example, you have a user who belongs to N number of app roles. One of the app roles is very restrictive. What will happen? Well since the user is part of the restricted group he will be restricted. But, what if they belong to a group which should have no restrictions like a super user? This can be fixed in one or two ways.

Option 1: If there is no app role hierarchy setup, or you do not want app roles to be setup in a hierarchical manor, you can just add a simple data filter for the superuser app role on the same object(s) as the restricted app role but just put the expression: 1=1! This will always return to true. So looking back at scenario 2, OBI will make the security filters additive in OR logic. OBI is smart enough that it understands that 1=1 is always true. So if you add that to another relation via ORs it is always true. So OBI does not add any filters whatsoever. NO FILTER APPLIED!

Option 2: You can setup the superuser app role as a member of the restricted app role. Then add simple security filter on the same objects that are restricted with the expression: 1=1. As we saw with hierarchies from Scenario 4, the security filter at the lowest app role will be used. So in this case since superuser app role is a member of the restricted role and we placed an overriding security filter on it with 1=1, OBI will not place the filter. NO FILTER APPLIED! Now if we don’t place any security on the superuser role it will use the next security filter applied up the app role hierarchy. So in this case the restricted app roles security will be applied.

OBIA comes with a set of domain values, dependent on the bi app that will be implemented.  These domain values are distinct sets of values/codes that act as conformed warehouse code sets.  In the sense, that multiple sources can map their codes to warehouse specific codes (source independence); thus, giving the ability to utilize multiple sources with one code set.  The OOTB domain value sets are used to create and calculate prepackaged metrics which are used in OOTB reports.  Now there are cases where the source data can not be easily mapped to a domain value or the list of available domain values does not fit your model and you wish to extend the list of available domain values to map to.  In prior OBIA implementations, this was done via files. Starting with OBIA 11.1.1.7+ which uses ODI this is now controlled and done within CM (Configuration Manager) and/or FSM (Functional Setup Manager).  However, some domain value sets do not allow you to extend or add new domain codes.  This leaves you with just the OOTB list of values which you may or may not like.  The solution I propose will allow you to add values to these none extensible domain value sets.

 

NOTE: This is not supported by Oracle. Use at your own risk. Basically there might be OOTB measures that are using these DW code/value pairs in pre-built metrics. With changes to these non-extensible domain values, one can potentially break these pre-built metrics and OOTB reports. Any changes will need the RPD metrics and OOTB reports to be analyzed and modified accordingly.

 

The RCU schema XXX_BIACOMP houses these domains and their members (or value lists). The workaround to make a non-extensible domain extensible, so that it’s value list can be changed and used in Functional Setup Manager, is the following:

  1. Launch Configuration Manager in browser: http://<host&gt;:9704/biacm
  2. Log in as bi apps admin user: (ie. biadmin)
  3. Navigate to Domains Administration -> Manage Warehouse Domains
  4. Search for your Offering and Domain in question
  5. Note the Code for the Warehouse Domain that needs to be extensible.
  6. Now log into the XXX_BIACOMP schema, using the XXX_BIACOMP user.
  7. Run the following DML to enable the domain. Use the Code noted from step 5:

UPDATE C_DOMAIN

SET EXTENSIBILITY_TYPE_CODE = ‘E’

WHERE DOMAIN_CODE = ‘<WAREHOUSE DOMAIN CODE>‘;

COMMIT;

8. Log back into Configuration Manager, and navigate to Domains Administration -> Manage Warehouse Domains.

9. Search for the warehouse domain to modify. Select the warehouse domain within the Warehouse Domains pane.

10. In the lower pane, Warehouse Members, click the (+) icon to create the new domain member code(s). This icon will now be enabled. Note: At this time, there are no easy ways to delete/remove members from domains, without hacking into the database. So it is not advised. 

11. Now, these new members are available for mapping to source codes using either one of the following two ways

a.  Navigate to Domains Administration -> Manage Domain Mappings and Hierarchies. Search for the domain(s) that were changed. You will observe existing OOTB source domain codes mapped to corresponding warehouse codes and also any new source codes, which have not been mapped. You can finish mapping source to warehouse domains here.

b. Change/modify source to warehouse domain mappings in Functional Setup Manager via implementation projects .

 

-Frank

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

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.

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

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
)

 

Follow

Get every new post delivered to your Inbox.