Business Intelligence – Oracle

Archive for April, 2008

Oracle BI EE 10.1.3.3.2 – Hyperion Essbase 9.3.1 Value Based Hierachies (Recursive Dimensions) and handling them in BI EE

Posted by Venkatakrishnan J on April 14, 2008

As you would probably know, Hyperion Essbase is one of those Multi-Dimensional data sources which can handle a different types of hierarchies. One of the common hierarchies that one might encounter is the value based hierarchy (in Essbase’s terms it is called as a recursive dimension). A value-based hierarchy is a hierarchy where the member levels are determined on the fly based on the parent-child relationship between 2 columns. For example, if you take the EMP table of the SCOTT schema, you would notice 2 columns EMPNO and MGR where MGR basically would have a reference to EMPNO. In this case, both the manager and employee details are stored in the same table. Manager A for example, himself being an employee, will be reporting to his Manager say B. So, this self-reference can be used to obtain a hierarchy which is generally known as a value based hierarchy. Lets try creating a simple value based hierarchy in Essbase and see how BI EE handles this hierarchy. We shall be creating 2 simple tables to test out the connectivity. One table would have the parent-child relationship and the other would be a simple fact table.

 create table ParentChild(Parent Varchar2(2), Child Varchar2(2));
insert into parentchild values ('B', 'D');
insert into parentchild values ('B', 'E');
insert into parentchild values ('A', 'B');
insert into parentchild values ('A', 'C');
commit;
create table parentchild_Fact( Child Varchar2(2), SimpleMetric Number);
insert into parentchild_fact values('D', 100);
insert into parentchild_fact values('E', 100);
insert into parentchild_fact values('C', 200);
commit;

So, basically the above parentchild table would be giving an hierarchy as shown below

    

Now, open up integration services and pull in the fact table to create a simple Account Dimension. Also, pull the parentchild table as a dimension in the OLAP Model. Click on the dimension and go to Edit – Properties – Table and click on Physical Joins.

    

Add a join between the Parent and Child columns and make it recursive.

    

Once this is done, save the OLAP model and then start creating a MetaOutline. In the metaoutline, just include the Accounts Dimension. Also, include the ParentChild Dimension. While creating a hierarchy for this dimension just include the PARENT column.

    

Now, build this cube and import this cube into BI EE. After the import into BI EE, you would notice that BI EE automatically would have columns assigned to each level (equivalent to the depth of the hierarchy). Now change the hierarchy type to “Unbalanced” and then start creating a report after creating the BM and Presentation Layers.

    

    

But lets check what would happen if we go and delete 2 rows (no third level) or 2 childs D and E from the dimension and rebuild the cube.

delete parentchild where child = 'D';
delete parentchild where child = 'E';

    

We would not get any data for the 4 th row. So, whenever having value based hierarchies in Essbase and if you are reporting on that using BI EE always ensure that the depth of the hierarchies do not change. If they do, then you would have to import the cube metadata once again into the repository. But still the above feature is very good considering the fact that BI EE does not support value based hierarchies for relational sources.

Advertisements

Posted in All Posts, Hyperion Essbase, OBI EE Plus | 2 Comments »

Oracle BI EE 10.1.3.3/2 – Changing Administrator Passwords – Impact on BI Scheduler and BI Publisher – Understanding Impersonation

Posted by Venkatakrishnan J on April 11, 2008

Another common question that keeps circulating in the mailing lists is “How do we ensure that changing Administrator password in BI Server does not impact BI Scheduler or BI Publisher?”. In order to answer this question we need to first understand how the integration between BI Scheduler and BI Server, and BI Publisher and BI Server works. Lets first start with BI Scheduler and BI Server. As you would probably know, BI Server repository contains all the users. Whenever we configure BI Scheduler, we would have to create a BI Scheduler administrator user, by running the cryptotools utility. This user (should be an user within the repository who is part of the Administrators group) details like username and password would be stored in the credentialstore.xml file. So, whenever any user logs into delivers and tries creating a schedule, then BI scheduler uses the user details stored inside the credentialstore.xml file to authenticate/impersonate into the BI Server. Once authenticated by the impersonation user, then the corresponding logging in user’s privilege details are returned back from the BI Server. This is explained in the below diagram

    

Now, what would happen when we change the Administrator user password in the repository? When we change the Administrator user password in the repository, the crendialstore.xml file would still contain the older password. Hence, whenever any user tries to run a schedule using the BI Scheduler, he would not be able to do so since BI Scheduler will still be using the older password of the Administrator user. So, in order to keep both the passwords in sync one would have to run the cryptotools utility again with the new password as sepecified in the repository.
The integration between BI Server and BI Publisher also works using the same logic. But the general confusion is why do we have to run the cryptotools? By default when we install BI EE, the cryptotools utility would automatically be run for the Administrator user for BI Publisher. But this is not the case for BI Scheduler. Hence, we would have to manually run the cryptotools for scheduler to bring it up.

    

As shown, whenever the password for the Administrator user alone is changed, then and only then should the cryptotools be run again. For all other users, there is no need for running the cryptotools. For example, lets try changing the password for Administrator in the repository to say “welcome1”. Once that is done, we need to run the cryptotools twice. One for the admin role (used by Bi Scheduler) and the other for the bipublisheradmin role (used by BI Publisher). Run the cryptotools with the new password.

 cryptotools credstore -add -infile D:\oracle\OracleBIData\web\config\credentialstore.xml

Also, open up the Job Manager and update the Administrator Password there.

    

Similarly, run it for BI Publisher using the bipublisheradmin alias.

    

The next step is to go to {OracleBI}\xmlp\XMLP\Admin\Configuration and open up the xmlp-server-config.xml. Update the Administrator passwords here.

 <?xml version="1.0" encoding="UTF-8"?>
<xmlpConfig xmlns="<a href="http://xmlns.oracle.com/oxp/xmlp">http://xmlns.oracle.com/oxp/xmlp</a>">
<property name="SAW_SERVER" value="vejanaki-lap"/>
<property name="SAW_SESSION_TIMEOUT" value="90"/>
<property name="DEBUG_LEVEL" value="exception"/>
<property name="SAW_PORT" value="9704"/>
<property name="SAW_PASSWORD" value="welcome1"/>
<property name="SAW_PROTOCOL" value="http"/>
<property name="SAW_VERSION" value="v4"/>
<property name="SAW_USERNAME" value="Administrator"/>
<property name="SAW_URL_SUFFIX" value="analytics/saw.dll"/>
<property name="SECURITY_MODEL" value="BI_SERVER"/>
<property name="BI_SERVER_SECURITY_DRIVER" value="oracle.bi.jdbc.AnaJdbcDriver"/>
<property name="BI_SERVER_SECURITY_URL" value="jdbc:oraclebi://vejanaki-lap:9703/"/>
<property name="BI_SERVER_SECURITY_ADMIN_USERNAME" value="Administrator"/>
<property name="BI_SERVER_SECURITY_ADMIN_PASSWORD" value="welcome1"/>
</xmlpConfig>

Then restart Presentation Services, BI Scheduler Service and OC4J. This would sync up all the passwords.

 

Posted in All Posts | 19 Comments »

Hyperion Essbase 9.3.1 and BI Publisher Connectivity – Using BI EE as a data Bridge

Posted by Venkatakrishnan J on April 10, 2008

As i was testing the BI EE and Hyperion Essbase connectivity yesterday, i thought i would test out the connectivity of BI Publisher and Hyperion Essbase through BI EE metadata and Answers. I started out under the impression that the performance would either be slow or there would be some hiccups on the way. But to my surprise, the connectivity was so seamless that it gave me the impression that BIP itself is using XMLA to connect to Essbase( i am not sure whether there are plans for supporting XMLA directly from BIP). I used the same cube that i had built yesterday here which contained a ragged hierarchy on the products dimension. As you would probably know, there are 2 ways of connectivity from BI Publisher to BI EE. One is by firing a query on the BI Server and the other is by using an existing Answers report. Lets try both of them today to find out how the connectivity actually works. Lets start first with the BI Server connectivity. So, create a simple BI Publisher report and in the data source use “Oracle BI EE”. And in the query use “Sql Query”.

    

Click on the Query builder and choose your Subject Area ( in our case the SA containing the Hyperion objects). Choose the 3rd level and the measure

    

If you notice, you would get a query like this

 select  PRODUCTS.Gen3,PRODUCTS as Gen3,PRODUCTS,
  Shblog.AMOUNT_SOLD as AMOUNT_SOLD
 from  SH_BSO1.Shblog Shblog,
  SH_BSO1.PRODUCTS PRODUCTS

Unfortunately, BI Publisher is not aware of Hyperion Essbase modelling in BI Server and hence does not encapsulate all the columns within double quotes. The above query would only give an error. So, change the above query to the one below

 select  "PRODUCTS"."Gen3,PRODUCTS" as "Gen3,PRODUCTS",
  Shblog.AMOUNT_SOLD as AMOUNT_SOLD
 from  SH_BSO1.Shblog Shblog,
  SH_BSO1.PRODUCTS PRODUCTS
 where "PRODUCTS"."Gen4,PRODUCTS" is not null

Now, try viewing the data. So far so good.

    

Now all we need to do is to add templates to this report and it works like a breeze.

    

    

Lets try the other means of connectivity which is by using an Answers request as the data source.

    

Add a template to this data source and there you go. A simple BIP report using Essbase as the data source.

    

Very nice!!!

Posted in All Posts, Bi Publisher, Hyperion Essbase, OBI EE Plus | 1 Comment »

Oracle BI EE 10.1.3.3.2 – Handling Ragged Hierarchies in Hyperion Essbase

Posted by Venkatakrishnan J on April 9, 2008

I am currently at a client location wherein we are testing out the BI EE and Hyperion Essbase connectivity. One of the questions that i got from the client was how does BI EE handle the ragged hierarchies of Hyperion Essbase. This was a pretty interesting question considering the fact that BI EE in itself cannot handle ragged hierarchies by default. Hence, i thought of testing this out with a simple Essbase cube. Lets start with a simple cube containing the following dimensions

1. Product Dimension (This would contain a simple ragged hierarchy).
2. Channel Dimension
3. Account Dimension

Hierarchies in the above dimensions is given below

    

So, the first step is to create a OLAP Model using Hyperion Integration Services. The model would look something like the one shown below

    

Ensure that you have created the account dimension. Save the OLAP Model. Now, create a metaoutline as shown below.

······

Once this is done, load the cube.

······

······

Once this is done, import this cube using BI EE.

······

······

Once imported, you will notice that BI EE would have imported the ragged product hierarchy as though it is a normal hierarchy.

······

Now, drag and drop this data source into the BM and Presentation layers. Then create a simple report out of this cube.

    

If you see above, i have included the first 2 levels of the hierarchy in the report. If you click on any of the Categories like Photo, you would get the corresponding sub-categories.

    

But if you click on any Product name which has no third level then it will give you no data which should not be the case.

    

Also, if you create a simple report containing all the levels, then you would see that all values which have all the 4 levels i.e all product names would be filtered which should not be the case again.

    

Well, one of the main reasons why the above happens is that BI EE thinks that all hierarchies are balanced by default. Hence when you pull in all the columns it removes the unbalanced columns out of the report. In order to circumvent this, go to the Administrator and change the Hierarchy type to “Unbalanced” in the physical layer.

    

Now, if you go back and check the report, you would be able to even drill on the product names. But the lower levels would all be null as expected.

    

    

Very nice indeed.


AddThis social bookmarking image button

Posted in All Posts, Hyperion Essbase, OBI EE Plus | 2 Comments »

Oracle BI EE 10.1.3.3/2 – Automating import of Users/Groups into Repository using UDML

Posted by Venkatakrishnan J on April 8, 2008

In my last blog entry here, we saw how to go about using UDML to automate the process of repository migration from Development to Test/Prod instances. Along, the same lines lets look at another interesting feature of UDML that would allow us to automate the process of importing Users/Groups automatically into the Repository. For example, consider a case wherein you have a list of users stored inside a database table. But you would like these users to be imported into the repository since you are planning to use repository authentication instead of external table authentication. Also, you do not want to create each of these users manually one by one. In such a case, UDML can come in very handy. The other scenario where this can be useful is when you are using LDAP/OID authentication. In such a scenario, you would need all the LDAP groups to be manually defined inside the repository. UDML again can come in very handy to automatically import all the groups into the repository. Lets try out the first example (importing users from a database table).

Lets start with a simple table containing only the list of users whom we need inside the repository.

······

This is nothing but a simple table containing around 10 users. Now, the idea is to import these users automatically into the repository. In order to do this, lets start with the below script (Thanks to Gerard Braat for this nice little simple tip in one of our internal email lists).

SELECT ‘DECLARE USER “‘||username||'” AS “‘||username||'” UPGRADE ID 1121 FULL NAME {} PASSWORD ”welcome1” NEVER EXPIRES
 PRIVILEGES ( READ);’ from rep_users

Just fire this from sql developer or from sqlplus with spool on to extract the resultant output into a physical file. You should get the output as shown below

······

If you want you can have different random passwords for each of the users. But this should give you an idea. Similarly, you can write a simple function which can retrive the groups from within OID. Now save the above file and fire the below command.

D:\oracle\OracleBI\server\Bin>nqudmlexec -U Administrator -P Administrator -I “D:\oracle\OracleBI\server\Sample\nqUDMLExec\User Import\userImport.udml” -B “D:\oracle\OracleBI\server\Sample\nqUDMLExec\User Import\BeforeUserImport.rpd” -O “D:\oracle\OracleBI\server\SamplenqUDMLExec\User Import\AfterUserImport.rpd”

······

Now you should get a repository with all the users automatically imported into the repository.

······

Another simple feature but can be very useful in a lot of situations.

Posted in All Posts, OBI EE Plus | 13 Comments »

Oracle BI EE 10.1.3.3/2 – UDML to automate repository Updates – Migration of Repositories from Development to Test/Production Environment

Posted by Venkatakrishnan J on April 4, 2008

One of the very important features of BI EE is its ability to expose the entire repository in the form of UDML. There are 2 utilities that are available for any user to expose the repository as UDML. They are nqudmlgen.exe and nqudmlexec.exe. Though the use of UDML is not supported by Oracle support, this can come in very handly in many situations. Before proceeding further i would recommend everyone to go through Mark’s blog entry here first which has details on what these utilities can do. One of the major advantages of these utilities is to streamline the process of migrating from Development to Test or Production environments. For example, when we migrate from Development to Test or Production there would be changes in Variable default values, Users, Connection Pool entries etc. Typically the methodology to update these values is to open up the Admin tool(from a windows box) and make the changes manually. But this can become very cumbersome and error prone as and when the number of changes involved become more and more. In order to automate this process of repository migration, the above utiltity nqudmlexec.exe can be very handy. Lets look at an example of how to automate the process of repository migration.

Consider the following scenario,

······

As you see above, there are 2 instances of BI EE. I have oversimplified the differences in the repositories of the Dev and Test machines. But this should give you an idea of the changes that you might encounter while moving from a dev environment to a test environment. So the changes that we have to make on the Development Machine’s repository (to move it to a test instance) are as follows

1.···Change the default value of repository variable BI_EE_HOME.
2.···Remove users B and C.
3.···Update the tnsnames entry of the connection Pool from ORCL to SALES.

In order to achieve this lets start with the first one (to change the repositor variable BI_EE_HOME value. In order to do this, first generate the UDML of the development repository using nqUDMLgen.exe.

nqudmlgen -U Administrator -P Administrator -R D:\oracle\OracleBI\server\Sample\nqUDMLExec\Dev_Machine.rpd -O D:\oracle\OracleBI\server\Sample\nqUDMLExec\Dev_Machine.UDML

······

Open this UDML file and search for CREATE RP VARIABLE “BI_EE_HOME”. This will give you the line containing the command for updating/creating a repository variable. So your command would look like this

DECLARE RP VARIABLE “BI_EE_HOME” AS “BI_EE_HOME” UPGRADE ID 817 EXPRESSION {‘D:\\oracle\\OracleBI’} PRIVILEGES ( READ);

······

Change the above command to reflect the values in the test machine. i.e.

DECLARE RP VARIABLE “BI_EE_HOME” AS “BI_EE_HOME” UPGRADE ID 817 EXPRESSION {‘/u01/oracle/OracleBI’} PRIVILEGES ( READ);

Copy this command into a text editor. Now, open the development repository and click on Tools – Query Repository. Query all the users and find the ids of the users of B and C (which we do not need in the test repository).

······

Now, add the below 2 commands to the above DECLARE command in the text editor

DELETE 1501:1019;
DELETE 1501:1020;

Once this is done, open the UDML file generated in the first step and search for DECLARE CONNECTION POOL “ORCL”. This will give you the command for updating/creating the desired connection pool.

DECLARE CONNECTION POOL “ORCL”.”Connection Pool” AS “Connection Pool” UPGRADE ID 831
DATA SOURCE {ORCL}
TIME OUT 300
MAX CONNECTIONS 10
TYPE ‘OCI10G’
USER ‘sh’
PASSWORD ‘D7EDED84BC624A917F5B462A4DCA05CDCE256EEEEEDC97D54A286E822D97C35C7AD5C43AD4F2A09EAC4D07C3A079829F’
SHARED LOGIN
CONNECTIONS TO SAME URI 10
OUTPUT TYPE XML
HEADER PATH {D:\\oracle\\OracleBI\\server\\config\\NQSQueryHeader.xml}
TRAILER PATH {D:\\oracle\\OracleBI\\server\\config\\NQSQueryTrailer.xml}
BULK INSERT BUFFER SIZE 32768 TRANSACTION BOUNDARY 10
TEMP TABLE PREFIX {TT} OWNER {}
PRIVILEGES ( READ);

Change the above command according the values in the test machine and append it to the other commands that we used above. So, our command file would look like this

DECLARE RP VARIABLE “BI_EE_HOME” AS “BI_EE_HOME” UPGRADE ID 817 EXPRESSION {‘/u01/oracle/OracleBI’} PRIVILEGES ( READ);

DELETE 1501:1019;
DELETE 1501:1020;

DECLARE CONNECTION POOL “ORCL”.”Connection Pool” AS “Connection Pool” UPGRADE ID 831
DATA SOURCE {SALES}
TIME OUT 300
MAX CONNECTIONS 10
TYPE ‘OCI10G’
USER ‘sh’
PASSWORD ‘D7EDED84BC624A917F5B462A4DCA05CDCE256EEEEEDC97D54A286E822D97C35C7AD5C43AD4F2A09EAC4D07C3A079829F’
SHARED LOGIN
CONNECTIONS TO SAME URI 10
OUTPUT TYPE XML
HEADER PATH {D:\\oracle\\OracleBI\\server\\config\\NQSQueryHeader.xml}
TRAILER PATH {D:\\oracle\\OracleBI\\server\\config\\NQSQueryTrailer.xml}
BULK INSERT BUFFER SIZE 32768 TRANSACTION BOUNDARY 10
TEMP TABLE PREFIX {TT} OWNER {}
PRIVILEGES ( READ);

Save this file. Now, invoke the nqudmlexec to update the Development repository with the UDML command file.

nqudmlexec -U Administrator -P Administrator -I D:\oracle\OracleBI\server\Sample\nqUDMLExec\Migration_UDML.UDML -B D:\oracle\OracleBI\server\Sample\nqUDMLExec\Dev_Machine.rpd -O D:\oracle\OracleBI\server\Sample\nqUDMLExec\Test_Machine.rpd

······

Now, after the execution of the above command, you would see a new repository called as Test_Repository which would contain the changes.

······

······

······

Posted in All Posts, OBI EE Plus | 9 Comments »

Oracle BI EE 10.1.3.3/2 – Changing Sort Order of Dashboards

Posted by Venkatakrishnan J on April 2, 2008

I have seen this question floating around in many of the forums and thought i shall blog about it. The question is how do we change the sort order of the dashboards that is shown in the Dashboards Banner. Well, currently there is no out of the box way to change the default sorting of the dashboards. If you are not sure what i mean, look at the screenshot below.

As you see above whenever you create a dashboard that would be automatically sorted alphabetically. So, what if you want to sort it in the reverse order. Unfortunately there is no out of the box way to achieve this. But lets look at one approach today which would be leveraging the fact that the dashboard display ignores the trailing and leading spaces. So, open your catalog manager in online mode and go to Shared\Paint Demo\_portal. This is the directory which would contain all the dashboards.

Now, first decide on the order in which you want the dashboards to be displayed. In our case we need the following order

1.···Test
2.···Sample
3.···Paint Dashboard

Always start from the last but one (in the above order). In our case, right click on the Sample dashboard from the catalog manager and click on Smart Rename. Just add a leading space to the name Sample i.e ” Sample” (without quotes).

Next, right click on the Test dashboard and click on Smart Rename. Just add 2 leading spaces to the name Test i.e ” Test” (without quotes).

Now, if you go to the dashboards screen you would notice that the dashboards would be sorted in the descending order. Dont worry about the spaces since they would be automatically truncated while doing the display.

You can use this to have any display order by adding spaces accordingly. As more and more spaces are added that dashboard would be moved to the front. A simple solution but can be quite useful if your users are very particular about dashboard display order.

Posted in All Posts, OBI EE Plus | 3 Comments »