Hierarchy Aggregation – A Best Practice


Another topic could be added in the goodbook of Best Practice .In the OBIEE hierarchy, starting from the top and moving down. At each level below the Grand Total level, double-click the level. In the field Number of elements at this level: enter 10 and increase in increments of 10 through all levels in the hierarchy. This is necessary to avoid the below Hierarchy errors and let BI server optimizes the aggregated query :

[nQSError: 15001] Could not load navigation space for subject area Student Enrollment.

[nQSError: 15019] Table <Logical table name> is functionally dependent upon level <Logical level>, but a more detailed child level has associated columns from that same table or a more detailed table.

This message occurs when either a key is defined under the Total level or when the first child level below the Total level contains two keys. Right-click on the Total level and select Properties. The Keys tab should be grayed out. If it is selectable, check to see what is there and delete it. Go to the next level down, right-click, select the Keys tab. Only one Key should appear. Delete the key that does not belong.

Multiple RPD & Multiple Presentation service instance on single BI Server


The objective of the thread is to understand the process of configuring multiple RPD and Presentation service in same BI server instance .Once that will be understood it can be further extended in scalable solution to integrate the approach on multiple server based instance as per business demands .The OBIEE suite documentation mentioned this functionality could be achieved but never mentioned the detail steps how to achieve this .

The initial bit could be found at below URL of Mark Rittman though not in detail :

http://www.rittmanmead.com/2007/09/11/managing-multiple-presentation-services-on-the-same-unix-box/

My goal in this thread is to clearly define the entire process however multiple Presentation Services instances on a machine is “NOT SUPPORTED BY ORACLE” .

The effective use of this approach will support multiple RPD editing online and also modifying multiple BI catalog online through two different instance .Also this would facilitate the running of failover instance in real life production environment .Apart from that customer could also ask for hosting two different (dev and test) instance(environments) in single host . 

Now lets assume that the two RPD i.e deba.rpd & test.rpd and there respective two catalogs both working properly while loaded individually and working independently .

Configuring the NQSCONFIG

Lets configure the NQSCONFIG.ini file as below :

[ REPOSITORY ] 
RPD1  =       deba.rpd , DEFAULT; 
RPD2    =     test.rpd ;

Configuring the DSN

Create 2 Oracle BI Server System DSN i.e DSN1 to point to default repository deba.rpd and DSN2 to point to test.rpd . Since we are configuring two RPD in parallel to single BI server instance hence  for both DSN we point to server Local with default Port 9703 . The 9703 port is ODBC communication port between BI Server and Presentation Services .

Multiple RPD and Presentation Instance-1Multiple RPD and Presentation Instance-2

 

 

 

 

 

N.B : whatever repository will be put under change Default repository section will be override by the DEFAULT settings in NQSCONFIG.ini .You can tick “Connect to Oracle BI Server to obtain default settings” ,so that RPD connection will be established as per the definition in .ini file after BI server restart .

Configuring the Catalog and instanceconfig.xml

For both deba.rpd and test.rpd we have catalog file under ../$OracleBIDataHome/web/catalog/” as “deba” and “test” having different sets of reports in each of them to distinguish properly.So lets make catalog “deba” online after making the below changes in instanceconfig.xml .

<WebConfig>
<ServerInstance>
   <DSN>DSN1</DSN>
   <CatalogPath>D:/OracleBIData/web/catalog/deba</CatalogPath>

Restart the services and log into catalog for “deba” and you can verify that the query running on deba.rpd and your dashboard display the reports relevant for catalog “deba” .Verify that from NQServer.log file .

Edit the xml and make the similar type of change to point to DSN2 and catalog “test” .It should work as well pointing to repo “test” .

Now till this point single BI server hosted two repository and you since the two repo available online hence you can easily make the online modification just by selecting the DSN from Admintool in online mode .

Deploying a new Presentation Service instance

As we know that there must be something different to distinguish two Web server instance . The typical URL is : http://localhost:9704/analytics where “analytics” is the context root and deployable J2EE module .My aim to create another context root by deploying a new instance of the analytics application which can be done by the Enterprise Manager as (http://localhost:9704/em). Typical default userid is “oc4jadmin” .

Lets follow the steps below to deploy the module “analytics.ear” from ..\$OracleBIHome\oc4j_bi\j2ee\home\applications .

Multiple RPD and Presentation Instance-3Multiple RPD and Presentation Instance-4

 

 

 

 

 

Multiple RPD and Presentation Instance-5

 Multiple RPD and Presentation Instance-6

 

 

 

 

Duplicating instanceconfig.xml for Presentation services

The second presentation service should use its own catalog i.e “test” and connect to the repo “test.rpd” while first presentation service would use “deba” .Hence there must be two separate xml web config file to point to different catalog .Duplicate the instanceconfig.xml file and create two xml as instanceconfig_deba.xml , instanceconfig_test.xml .

instanceconfig_test.xml

<WebConfig>   
<ServerInstance>
<Listener port=”9712″/>
<DSN>DSN2</DSN> 
<CatalogPath>../$ORACLEBIDATAHOME/web/catalog/test</CatalogPath> 

instanceconfig_deba.xml

<WebConfig>   
<ServerInstance>
<DSN>DSN1</DSN> 
<CatalogPath>../$ORACLEBIDATAHOME/web/catalog/deba</CatalogPath> 

Replace $ORACLEBIDATAHOME as per your env settings .

Configuring Presentation service communication channel for OC4J

Context root “analytics” can be communicated by OC4J using the default settings as per web.xml under “..\OracleBIHOME\oc4j_bi\j2ee\home\applications\analytics\analytics\WEB-INF” via default port 9710  .

But to communicate with context root “analytics_deba” I need to change the web.xml file under “..\OracleBIHOME\oc4j_bi\j2ee\home\applications\analytics_deba\analytics\WEB-INF” via default port 9712  .

Search for oracle.bi.presentation.sawserver.port and change the param-value into 9712.

Multiple RPD and Presentation Instance-7

 

 

 

 

 

 

After creating the new configuration files we have to re-start the BI Server , BI Presentation services. Please restart the OC4J instance also .

Running two presentation service parallelly

To start the new presentation services I create two batch file with below target shortcuts .

sawserver.exe -c $ORACLEBIDATAHOME\web\config\instanceconfig_test.xml

sawserver.exe -c $ORACLEBIDATAHOME\web\config\instanceconfig_deba.xml

First stop all service and then start BI and OC4J service and after that invoke the above two scripts .So the presentation services will be started and you can login and type the following URL  in your web browser:
http://server_name:9704/analytics
http://server_name:9704/analytics_deba

So presentation service application/context-root  “analytics” will refer to one repository file “deba.rpd”  and “analytics_deba” will refer to “test.rpd” but you now have the ability to create different reports, dashboards etc via different presentation services using single BI server env/resource .

N.B : You can define multiple catalog path in one instanceconfig.xml but only one will be active at a time and that will be the one which defined first .Hence the idea is to create multiple instanceconfig to access multiple catalog .

Logical Table Source(LTS) Override – A multiple LTS logical fact with Count and Count Distinct Measures


One of my colleague has encountered a strange problem which I even have not noticed ever because the problem is bit ridiculous for me . The problem is about applying count distinct and count aggregation rule while building a measure . The measure will be mapped to multiple logical fact table source .

The problem in nutshell is, while applying the Count on column NUM_OF_CONTACT it is doing SUM(NUM_OF_CONTACT) instead of COUNT (NUM_OF_CONTACT) whereas using Count Distinct on the same column will be driving the Distinct count on Lowest level of LTS all time(i.e query hitting single/one LTS)  irrespective of the dimension and dimensional content specification .

Initially it is bit strange for me . After investigating across metalink I got couple of such issue encountered by users . Some of them are : Doc ID : 510223.1 SR 38-1054325181, Doc ID 526854.1 SR 38-1505875337 . But it does not give me the proper info about the workaround or solution.

Re-engineering on it reveals that there are some interesting and unknown feature left behind by Oracle/Siebel to overcome such problems where measures need to be totally driven by the content specification of fact table LTS. Its nothing but “Logical Table Source Override” . I proof this I replicate the problem in my env and done a POC and outcome/workaround of it as below :

1 . Create the below tables and insert the handcrafted data .

LTS Override1

 

 

 

 

 

 

LTS Override2

 

 

 

 

 

2. Configure the repository as below :

  LTS Override3

 

 

 

3. The Logical Fact table source content has been mapped as applicable . The 2 LTS content mapping as below . Similarly other LTS has been mapped .

LTS Override4

 

 

 

 

 

4. Now see how the Count distinct has been defined .

  LTS Override5 

 

 

 

 

However the definition prove to be wrong and erroneous while you drag the measure with the dimensional column .Here I have drag LVL2 and as per the content mapping the query should be routed via “DIM_POSITION_FLM” . But strange enough whatever column you choose from dimension table the query always routing via “DIM_POSITION_KAM” .Have a look on below :

LTS Override7

 

 

 

 

 

 

5 . Definitely the above result is wrong . A correct approach is to redefine the measure by LTS overriding as below :

LTS Override8

 

 

 

 

 

 

6. And finally see the correct result and the query log .

LTS Override9

 

 

 

 

 

7. Whereas a totally different kind of behavior has been observed while you are dragging “NUM_OF_CONTACT_Count” with measure . A simple count aggregation has been defined against it . The result would be below :

LTS Override10

 

 

 

 

 

 

So this create another major problem as any dimension you choose always SUM aggregation will be applied whereas as per Repository definition it should be Count .

8. A correct approach/workaround of the problem is to redefine the measure . However for Count aggregation you haven’t any choice of LTS overriding . Here you have to go by Based On Dimensions . Lets see this and the issue has been resolved !

LTS Override11

 

 

 

 

9. So apparently it seems everything is working fine with some tweaking as LTS override and based on Dimensions funda . But wait there are some more strange thing awaiting  and I am really bit astonished how this would happened ! If any body could explain me the reason I will be grateful .

I have deleted the measure “NUM_OF_CONTACT” and “NUM_OF_CONTACT_Sum” from logical fact table as it is of no use for me for this POC . However after removing this I observed only the measure “NUM_OF_CONTACT_Count_Distinct_Override” is functional and rest of them is not behaving what it should be , as explained above .

Dragging “LVL2” and any of the measure (apart from “NUM_OF_CONTACT_Count_Distinct_Override”) leads to inconsistent query firing in DB :

select distinct T41.LVL2 as c1,
     T38.NUM_OF_CONTACT as c2
from
     DIM_POSITION_H T41,
     DIM_POSITION_FLM T38
where  ( T38.POSITION_WID = T41.POSITION_WID )
order by c1

And more astonishing fact is that for all 3 measures the query is same !!! How come this possible ? I dont know if I am missing something .But this is quite strange how removing an independent measure impact the rest of the measures query ! Still can’t find any justification ……