Oracle BI EE 10.1.3.3/2 – Customizing look and feel – Styles and Skins – Phase 2

I had earlier blogged here on how to go about modifying the styles in BI EE. As i said earlier, a style controls how dashboards and answers are displayed to an end user at run time. Whereas Skins are assigned to an end user while logging in. Now lets see how to go about modifying the skins. This is again just to give you a guideline. If you go to the {OracleBI}/web/app/res folder, all the folders starting sk_ are the skin folders. If you have done a default install, you would find a folder under the name sk_oracle10. oracle10 is the default skin. Our aim is to modify the preview button in BI Answers i.e
Change this->        to this->       
 Lets make a copy of the sk_oracle10 folder in {OracleBI}/web/app/res and rename it to sk_oracle10modified. Copy this sk_oracle10modified to {OracleBI}/oc4j_bi/j2ee/home/applications/analytics/analytics/res folder. Remember, for both skins and styles, custom styles and skins must be present in both {OracleBI}/web/app/res and {OracleBI}/oc4j_bi/j2ee/home/applications/analytics/analytics/res folders. Now lets modify the image file_preview.gif under sk_oracle10modified/answers folder to our desired image. Remember the modification has to be made in both the folders. Once this is done, edit the instanceconfig.xml file under {OracleBIData}/web/config folder. Add the following tag to the file
DefaultSkin oracle10modified /DefaultSkin
      
Once this is done, restart your OC4J and presentation services. Now look at the answers preview button for a report. The preview image should have changed to your custom image.
      

Reset OC4J Admin Password

Seems like a rash of questions recently about how to reset Oracle Application Server/OC4J's administrator password and for whatever reason Google doesn't index our doc (as per the feedback below, due to our robot.txt disallowing this - I don't know why this is the policy) and as a result isn't finding the entry in our doc on how to do this here:

http://download-west.oracle.com/docs/cd/B31017_01/core.1013/b28940/trouble_asc.htm#BCEDHFEI

Reproduced in its entirety below [1] and hopefully this will help Google point people to the right information.

One of the "tricks" you have to be aware of when doing this is that if you are using Application Server Control to manage a cluster of OC4J's, there is a default assumption that all the OC4J's use the same administrator password (you can override) - if you are doing this, step 5 of the instructions, which talks about removing the ASControl cached security file (passwords encrypted!) to re-enable cluster management, is important to heed when you do this.

[1]
Reset the oc4jadmin password using the following procedure while you are logged in as the user who installed the Oracle Application Server instance:

1. Stop OC4J and the Application Server Control.
   Enter the following command in the Oracle home of the application server instance:
   

   (UNIX) ORACLE_HOME/opmn/bin/opmnctl stopproc ias-component=OC4J
   (Windows) ORACLE_HOME\opmn\bin\opmnctl stopproc ias-component=OC4J
   

2. Locate and open the following file in a text editor:
   

   (UNIX)ORACLE_HOME/j2ee/home/config/system-jazn-data.xml
   (Windows)ORACLE_HOME\j2ee\home\config\system-jazn-data.xml
   

3. Locate the line that defines the credentials property for the oc4jadmin user.
   The following example shows the section of system-jazn-data.xml with the encrypted credentials entry in boldface type:
   

   
    
       jazn.com
       
        .
        .
        .
          
             oc4jadmin
             OC4J Administrator
             OC4J Administrator
               {903}4L50lHJWIFGwLgHXTub7eYK9e0AnWLUH
          
   

4. Replace the existing encrypted password with the new password.
   Be sure to prefix the password with an exclamation point (!). For example:
   

   !mynewpassword123
   

   The password for the oc4jadmin user should conform to following guidelines:
   
   • Must contain at least five characters, but not more than 30 characters.
     
   • Must begin with an alphabetic character. It cannot begin with a number, the underscore (_), the dollar sign ($), or the number sign (#).
     
   • At least one of the characters must be a number.
     
   • Can contain only the following characters; numbers, letters, and the following special characters: US dollar sign ($), number sign (#), or underscore (_).
     
   • Cannot contain any Oracle reserved words, such as VARCHAR.
     
   
   

   See Also: "The oc4jadmin User and Restrictions on its Password" in the Oracle Application Server Installation Guide

   
   
5. Delete cached password data by deleting the contents of the following directory:
   

   (UNIX)
   ORACLE_HOME/j2ee/oc4jinstance /persistence/ascontrol/ascontrol/securestore/
   (Windows)
   ORACLE_HOME\j2ee\oc4jinstance\persistence\ascontrol\ascontrol\securestore/
   

6. Start OC4J and the Application Server Control.
   After the restart, the Application Server Control will use your new Administrator (oc4jadmin) password, which will be stored in encrypted format within the system-jazn-data.xml file.

Oracle BI EE 10.1.3.3/2 – Multi User Development of Repositories (MUD)

I had some time today to put together a simple document for enabling Multi-User Development(MUD) for repositories. Typically when you have many data sources and lots of tables, it would make sense to distribute the repository development work to multiple users. MUD is basically a feature of the BI EE admin tool wherein multiple users can work on the repository at the same time. The concept of Repository Merging was actually introduced in order to facilitate MUD. Lets try to understand the concept of MUD today.
Lets start with the simple diagram below.
      
The above diagram illustrates how the MUD works. In order for the MUD to work following are the pre-requisites
1.   A shared drive to host the Master Repository
2.   All the client machines should have access to the master repository.
3.   All the client machines should have the admin tool installed.
For the MUD to work, the repository that is worked upon by all the users should be kept in a shared directory. This shared directory should be accessible to all the users. In each of the client’s Admin tool, enter the Shared Directory path.
      
Now, open the Master repository in offline mode. The entire concept of MUD revolves around objects called as Projects. So, from within the Admin tool navigate to Manage – Projects.
      
This will open up a project window. Projects are basically subsets of objects within the Admin tool that can be assigned to individual users. So, the idea is to assign different projects to different users. Also, each of these projects can contain one or more Logical Fact tables. As soon as a logical fact table is included all the other dependent objects would automatically be part of the project. Typically when we start with a repository, we would not be having any BM or presentation layers. So, it is recommended that one imports all the physical tables and creates the physical joins in the repository first before implementing MUD. After that we can create dummy BM and presentation layers so that they can be assigned to individual projects. Also, one can assign Users, Init Blocks and Variables to a project.
      
After creating and assigning objects to a project, the next step is to save the master repository in a shared drive. Now, open up a client Admin tool and navigate to File – Multiuser – Checkout. This Check out process does 2 things
1.   Copies the Master repository from the shared drive to the local drive ( This will serve as the local master repository).
2.   Gives you a screen to choose the project that you have the authority to work on.
3.   Creates a subset repository (would ask you to enter the name) which would contain only the selected project related data.
So, basically the idea is to work on the subset repository (like creating/deleting dimensions, hierarchies etc) and then merge the changes back to the local master repository. The merge process will lock the master repository in the shared drive. In order to release the lock one would have to Choose “Publish to Network” which will copy the modified and merged local master repository to the shared drive. The process would be the same for all the other users.

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

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.

view sourceprint?

1	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.

view sourceprint?

01	<?xml version="1.0" encoding="UTF-8"?>

02	<xmlpConfig xmlns="<a href="http://xmlns.oracle.com/oxp/xmlp">http://xmlns.oracle.com/oxp/xmlp</a>">

03	<property name="SAW_SERVER" value="vejanaki-lap"/>

04	<property name="SAW_SESSION_TIMEOUT" value="90"/>

05	<property name="DEBUG_LEVEL" value="exception"/>

06	<property name="SAW_PORT" value="9704"/>

07	<property name="SAW_PASSWORD" value="welcome1"/>

08	<property name="SAW_PROTOCOL" value="http"/>

09	<property name="SAW_VERSION" value="v4"/>

10	<property name="SAW_USERNAME" value="Administrator"/>

11	<property name="SAW_URL_SUFFIX" value="analytics/saw.dll"/>

12	<property name="SECURITY_MODEL" value="BI_SERVER"/>

13	<property name="BI_SERVER_SECURITY_DRIVER" value="oracle.bi.jdbc.AnaJdbcDriver"/>

14	<property name="BI_SERVER_SECURITY_URL" value="jdbc:oraclebi://vejanaki-lap:9703/"/>

15	<property name="BI_SERVER_SECURITY_ADMIN_USERNAME" value="Administrator"/>

16	<property name="BI_SERVER_SECURITY_ADMIN_PASSWORD" value="welcome1"/>

17	</xmlpConfig>

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

Oracle 10g/11g – OLAP, CUBE and ROLLUP

If you had gone through the list of features in Oracle 11g, one of the most important advancements/features in the BI space would be the introduction of Oracle OLAP as part of the DB optimizer (in the form of MV query rewrite). But for users who do not have the luxury of using Oracle OLAP 11g (or even 11g database) in their environment, dont lose hope yet . CUBE and ROLLUP functions had undergone quite a few advancements in themselves while in 10g itself and they can indeed mimic an OLAP cube starting from 10g. Let me just give an example here. All the advances done in CUBE and ROLLUP functions are documented here. If you had gone through my previous blog entry here on using OLAP 11g, i had used a specific example to create a cube. I would be using the same one here.
Dimensions:
Customers:
      Hierarchy:   All      ->      Customer Gender      ->      Customer Name
Product:
      Hierarchy:   All   ->   Product Pack Size   ->   Product Name
Times:
      Hierarchy:   All   ->   Fiscal Year   ->   Fiscal Month   -> Fiscal Day
Measures:
      Quantity Sold
      Amount Sold
Oracle 10g/11g now supports Hierarchical cubes within queries. As you would know, ROLLUP is a feature wherein one can have summed up data across a hierarchy. What is new in 10g & 11g is, one can have multiple ROLLUPs (more like hierarchies) in the group by clause which in turn would take a cartesian product of all the rollups in the group by clause. In order to get a cube like data for the above measures and dimensions, the query would look like this
SELECT CUST_GENDER, CUST_FIRST_NAME||CUST_LAST_NAME, PROD_PACK_SIZE, PROD_NAME, FISCAL_YEAR, FISCAL_MONTH_DESC, DAY_NAME, SUM(QUANTITY_SOLD) AS QUANTITY, SUM(AMOUNT_SOLD) AS SALES
FROM
SALES a, CUSTOMERS b, PRODUCTS c, TIMES d
where
a.CUST_ID = b.CUST_ID and
a.PROD_ID = c.PROD_ID and
a.TIME_ID = d.TIME_ID
GROUP BY
ROLLUP(CUST_GENDER, CUST_FIRST_NAME||CUST_LAST_NAME),
ROLLUP(PROD_PACK_SIZE, PROD_NAME),
ROLLUP(FISCAL_YEAR, FISCAL_MONTH_DESC, DAY_NAME)
               
And the plan for the above query would be like this
              
The above generates the same set of data that Oracle OLAP produces. But of course, oracle OLAP provides more features like partitioning and many more. This is just to kindle your interest on the enhancements that have been made in this space. If you want to create a MV,
CREATE MATERIALIZED VIEW SALES_ROLLUP_MV
ENABLE QUERY REWRITE AS
SELECT CUST_GENDER, CUST_FIRST_NAME||CUST_LAST_NAME as CUSTOMER_NAME, PROD_PACK_SIZE, PROD_NAME, FISCAL_YEAR, FISCAL_MONTH_DESC, DAY_NAME, SUM(QUANTITY_SOLD) AS QUANTITY, SUM(AMOUNT_SOLD) AS SALES
FROM
SALES a, CUSTOMERS b, PRODUCTS c, TIMES d
where
a.CUST_ID = b.CUST_ID and
a.PROD_ID = c.PROD_ID and
a.TIME_ID = d.TIME_ID
GROUP BY
ROLLUP(CUST_GENDER, CUST_FIRST_NAME||CUST_LAST_NAME),
ROLLUP(PROD_PACK_SIZE, PROD_NAME),
ROLLUP(FISCAL_YEAR, FISCAL_MONTH_DESC, DAY_NAME)
The above is called as a hierarchical cube. It does not store all the combinations like in a cube. Instead it stores the rollup of data in the same hierarchy across different hierarchies. For example, this cube will not generate value for sales when sales is analyzed by Customer Name, Product name and Fiscal month since their actual path in the hierarchy is not specified.
 If one wants to generate an actual cube, the query would look like this
SELECT CUST_GENDER, CUST_FIRST_NAME||CUST_LAST_NAME, PROD_PACK_SIZE, PROD_NAME, FISCAL_YEAR, FISCAL_MONTH_DESC, DAY_NAME, SUM(QUANTITY_SOLD) AS QUANTITY, SUM(AMOUNT_SOLD) AS SALES
FROM
SALES a, CUSTOMERS b, PRODUCTS c, TIMES d
where
a.CUST_ID = b.CUST_ID and
a.PROD_ID = c.PROD_ID and
a.TIME_ID = d.TIME_ID
GROUP BY
CUBE(CUST_GENDER, CUST_FIRST_NAME||CUST_LAST_NAME),
CUBE(PROD_PACK_SIZE, PROD_NAME),
CUBE(FISCAL_YEAR, FISCAL_MONTH_DESC, DAY_NAME)
The above does not take care of best practices to create MVs or the queries. But this is just to show the advancements that 10g/11g provides for an end user. Can anyone let me know whether these were available in 9i? I believe in 9i, correct me if i am wrong here, one would not be able to create MVs out of the queries using CUBE clause.

Oracle BI Publisher and BI EE – Invisible Admin Tab!!!

I had a couple of users today who had sent me an email informing me about a strange issue while installing BI EE. Call it coincidence, we had the same question pop up in the forums today as well. I do not have a complete solution to this problem yet. But i thought this is worthwhile to blog about since there are chances that other users might come across a similar issue. The problem was, after the default install of 10.1.3.3.1/0(using OC4J), the users are unable to perform any kind of administration on BI Publisher. i.e if they log into BI Publisher as Administrator, they do not see the “Admin” tab!!!. There can be multiple reasons why this can happen. But following should give you a general set of guidelines on where to look at to see where the source of the problem is.
When you install BI EE, the BI Publisher that also gets installed would be configured to use BI Server security. So, in order to access BI Publisher you need to ensure that BI Server and the Presentation Services(if you want reports from Answers as data source) are up and running. There are 3 different files that are worth a mention here.
1.   {OracleBI}\xmlp\XMLP\Admin\Configuration\xmlp-server-config.xml
2.   {OracleBI}\xmlp\XMLP\Admin\Security\principals.xml
3.   {oc4j_bi}\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\xmlp-server-config.xml
Now, each of these files store certain parameters that would be used in the security and also in identifying the reports. So, if you have an install where your BI Publisher does not show you the Admin tab, try to check the following.
1.   Open the file {OracleBI}\xmlp\XMLP\Admin\Configuration\xmlp-server-config.xml using a text editor. Go to {OracleBI}\xmlp\XMLP\Admin\Configuration\xmlp-server-config.xml. Check whether you have SUPERUSER_PASSWORD and SUPERUSER_USERNAME set. Also check whether SECURITY_MODEL property is properly set.
      
Edit the property SECURITY_MODEL and change it to XDO. i.e. for example <property name=”SECURITY_MODEL” value=”XDO”/>. This would basically change the security model from BI Server security to BI Publisher Security.
2.   Restart OC4J and see whether you are able to log in as Administrator and see the Admin tab. If you are able to see the Admin tab then there is some problem with BI Publisher not being able to connect to BI Presentation Services in order to leverage the security. By default, when we install BI EE, BI Publisher would use BI Server security. By changing it to XDO we are making it to leverage local BI Publisher security instead of BI EE security. In the {OracleBI}\xmlp\XMLP\Admin\Configuration\xmlp-server-config.xml, try placing the ip instead of the hostname. For example, <property name=”BI_SERVER_SECURITY_URL” value=”jdbc:oraclebi://vejanaki-pc:9704/”/>. Default install will take the hostname and in some cases BI Publisher does not seem to recognize the hostname.
3.   Go to {OracleBI}\xmlp\XMLP\Admin\Security\principals.xml. Check whether you the Administrator Role mapping is enabled there.
The above are some checks that should get you started. If others have any other comments feel free to post them here. In the meantime, i am off for a mini vacation to celebrate the festival of lights!!!. Should be back next week with more posts.

Oracle BI EE 10.1.3.3/2 – Level Based Measures (LBMs)

Now that i am back from quite a good weekend, i thought i would blog about something which is pretty interesting i.e. Level based Measures. Lets try to understand this first. This is what the documentation has got to say about this “A level-based measure is a column whose values are always calculated to a specific level of aggregation. For example, a company might want to measure its revenue based on the country, based on the region, and based on the city. You can set up columns to measure CountryRevenue, RegionRevenue, and CityRevenue. “ As stated above Level based measures have their aggregation set based on a level in the hierarchy. Lets try to understand this using a simple example. We will start with a simple hierarchy as shown below.
      
Basically, we have a hierarchy and we would like to have the Sales measure aggregated over 3 levels i.e. Total Level, Region Level and the Country level. Now lets start with creating 3 logical measure columns Sales By Region, Sales By Country and Total Sales. Easch of these measures are exact replica of the Sales Measure which has a default aggregation of SUM.
      
      
      
Now, in order to make the 3 columns to aggregate over Region, Country and Total levels just drag and drop the corresponding columns to their associated levels as shown below.
      
Now, try creating a report with all the 4 columns(including the base SUM aggregated Sales measure) and see what happens.
      
As shown above what happens is Sales By Country calculates the sales across all the countries. Sales By Region calculates the sales across all the regions. And Total sales gives total sales across all the regions and countries. But you would see a lot of line items because of the granularity at which the Sales By Country Works. Since there are 17 different countries you would see 17 different line item sums. Now, if you remove the Sales By Country column the number of line items gets reduced to the number of regions i.e. 5
      
This could be a lot useful in situations where you want to a division of different LBM’s for example, Sales By Region/Sales By Country

Oracle BI EE 10.1.3.3/2 – Automating Password Updates of Connection Pools and Users – Command Line Options

If you had gone through my blog entry here, i would have talked about using UDML as a way for automating migration from dev to test/prod environments. But the major problem with UDML is that one cannot update the passwords of Connection Pools, Users etc since the UDML expects them to be encoded. In such a case, for updating passwords alone we can use an undocumented command line switch option for the AdminTool. For example, look at the screenshot below.
    
This is nothing but the connection Pool property of the ORCL database. Here, we would like to change the schema names and its corresponding passwords. For example, we would like to change the schema to SH and also would like to change the password. In order to do that, open a text editor and enter the below command. Save the file in the same directory as the Admin Tool ({OracleBI}\Server\Bin). If not, then you would have to give the full path of the file.

view sourceprint?

1	Open Dev_Machine.rpd Administrator Administrator

2	SetProperty "Connection Pool" "orcl"."Connection Pool" "User" "sh"

3	SetProperty "Connection Pool" "orcl"."Connection Pool" "Password" "welcome1"

4

Save

5

Exit

Now, open up a command prompt and navigate to {OracleBI}\Server\Bin. Typein in the below command,

view sourceprint?

1	admintool /command commandlineswitch.txt

    
This would automatically update the connection pool values.
    
You can use the above to update many objects within the repository. For updating passwords for users, just use the nqschangepassword.exe tool. For more details on this, check my blog entry here. So, on a high level your entire migration process would look as shown below
    
Thanks to Phil for sharing this.

Oracle BI EE 10.1.3.3/2 – Customizing Login Screen

I thought of picking up an older forum question for my blog entry today. The question is how do we go about customizing BI EE login screen? BI EE login screen primarily works (from the UI perspective) using the following style, stylesheets and XML messages.
1.   Logon.css – Stylesheet that sets the font sizes, background color etc
2.   LogonControlMessages.xml
3.   LogonMessages.xml
4.   ProductMessages.xml
5.   Utilmessages.xml
6.   sk_oracle10 – Base skin which provides the images for the login screen.
The login image of the login screen comes from the bglogon.jpg (this would be present in your base skin i.e {OracleBI}\web\app\res\sk_oracle10\b_mozilla_4.
      
In order to modify the above image, just create a new skin and put a new image under the same name as bglogon.jpg. For creating a new skin, check my blog entry here
The word Oracle Business Intelligence comes from the kmsgProductGeneral message in ProductMessages.xml (This file would be under {OracleBI}\web\msgdb\l_en\messages )
      
“Please enter your User ID and Password below, and then press the Log In button.” – This comes from kmsgAuthenticateRequestedContentRequiresLogon message of the logonmessages.xml (This file is present under {OracleBI}\web\msgdb\l_en\messages ).
      
The UserID, Password and the Login button all come from a message called kmsgLogonLanguageForm in logoncontrolmessages.xml (This file would be present under {OracleBI}\web\msgdb\messages).
      
Lastly the copyright messages come from the message kmsgNQuireLegalCopyright in utilmessages.xml (This file would be present under {OracleBI}\web\msgdb\l_en\messages).
      
In order to customize these messages check my blog entry here and here. Just a word of note: Some images like “Powered By Oracle” logo should not be modified since they would violate the license agreement. The above should give you an idea of the list of messages/images that you need to modify to get your desired customization.

Oracle BI EE 10.1.3.3/2 – Using LDAP/OID Authentication

One of the very good features of OBI EE 10.1.3.3/2 is its ability to leverage OID/LDAP authentication. I was trying this one out today and thought i would document it. I would split this into 2 articles. In this article we will see how to setup the OID authentication. In the next article we would see how to pass on group credentials to users from OID. Lets go through the steps one by one.
1.   Open the repository in Online Mode using the Administrator. Go to Manage and click on Security. Click on Action–New–LDAP Server
      
2.   Enter the Oracle Internet Directory details like hostname and the Base DN. And test the connection.
      
      
3.   Right click on the LDAP server and click on import. You should be seeing the users that are under OID.
      
4.   Once this is done, the next step is to create an initialization block that would basically use the OID server created above and set a system session variable called USER. This USER variable would be used during authentication.
Go to Manage->Variables to open up the variable manager. Click on Action->New->Sesion->Initialization Block
      
Enter any name, say OID, and click on edit data source. Select the OID/LDAP server that we created in the 1st 3 steps. Then click on edit target and click on new variable. Enter USER as the name of the variable and click ok.
      
Edit the variable and add the uid as the LDAP variable.
      
Test the initialization block as orcladmin.
      
You must see orcladmin username set for the USER variable. If you see that then steps that you have done so far are correct. Remember to set the Required for Authentication check box.
      
Check in the changes and save the repository. Log into Answers as orcladmin. We should be able to see all the public dashboards.
      
This is the first step in enabling authentication. The next step is to get the group related info from the OID and assign it to the user which we will see in a later article.

Customizing OBI EE – GO URL Parameters

While I was working on the data mining pieces, I got a request from a customer who basically wanted to completely customize OBI EE. Well, it got me thinking about the various customization options that we have with OBI EE. In my customer’s case, they wanted to integrate OBI EE into their web application. Let’s see what options we have with OBI EE customizations.
Using OBI EE GO URL parameters
Almost most of the functionality that a customer would need or expect out of OBI EE is available for them via URL parameters. One can leverage these URL parameters into their application. What this would do is that rather than having OBI EE as a separate reporting tool, it would enable an end customer to call various reports via iframes into their application.

 A sample URL with the parameters for OBI EE would look like this
 http://hostname:port/analytics/saw.dll?GO&NQUser=Administrator&NQPassword=Administrator&
Path=/Users/administrator/GEC_DW/Regional+Sales/Profit+per+Category+Pie+Chart&Options=md
When one needs to use the URL parameters the fundamental part of the URL should include http://hostname:port/analytics/saw.dll?GO and following that options are to be entered. Lets look at the various URL parameters for OBI EE
        1. &NQUser – Username for logging into Answers
        2. &NQPassword – Password for login. One must realize that entering password directly into the URL is not very secure. Its recommended to use    the post method wherein the password is not explicitly shown. (People who know discoverer, entering password, as a parameter is no more   available. Only post method is supported)

        3. &Options=mdfr – Each of the letters in mdfr have a specific function.
            m - To include the modify report link under the report
            d - To include the download link under the report
            f - To include the printer friendly link under the report
            r - To include the refresh report link under the report
  All the above can be interchangeably used. For example &Options=md for including modify and download links alone.
        4. &Action – This allows the developers to request specific formats for the reports. For example, &Action=print (can also have excel etc)

        5. &ViewName – This argument allows one to specify a view. For example, &ViewName=Chart1
        6. &Style – Specify a style sheet For example, &Style=Lime
        7. &Format – Specify the format of the output, html or xml

        8. &Path – Specify the path for the report. For example, &Path=/Shared/administrator/testReport
        9. &SQL – Logical Sql – One can even issue logical sql via the URL parameter. For example,  &SQL=select+region+from+Sales. Here Sales is the   subject area.
The above are some of the parameters that you might find useful. But for more detailed and some parameters refer the Documentation here.
 
The above picture is actually a simple application that calls out 2 different reports based on the data entered. This kind of integration is very strong and infact the entire OBI EE is integrated into the Oracle Siebel CRM using this methodology. One can use Post methods; execute scripts etc using this type of methodology.
Following are some examples for simple HTML form elements using post method.
 Access to Answers:
     <form action=”http://hostname:port/analytics/saw.dll?” method=”post”>
     <input type=”hidden” name=”Cmd” value=”Answers”>
     <input type=”hidden” name=”nqUser” value=”Administrator”>
     <input type=”hidden” name=”nqPassword” value=”Administrator”>
     <input type=”submit” value=”Answers”>
     </form>
To open a report in Answers (for edit), include the Path parameter:
     <form action=”http://hostname:port/analytics/saw.dll?” method=”post”>
     <input type=”hidden” name=”Cmd” value=”Answers”>
     <input type=”hidden” name=”nqUser” value=”Administrator”>
     <input type=”hidden” name=”nqPassword” value=”Administrator”>
     <input type=”hidden” name=”Path” value=”/Shared/SH/Category analysis”>
     <input type=”submit” value=”Open a Report for editing in Answers”>
     </form>
To open BI Publisher use “AdvancedReports” as the Cmd value
     <form action=”http://hostname:port/analytics/saw.dll?” method=”post”>
     <input type=”hidden” name=”Cmd” value=”AdvancedReports”>
     <input type=”hidden” name=”nqUser” value=”Administrator”>
     <input type=”hidden” name=”nqPassword” value=”Administrator”>
     <input type=”submit” value=”BI Publisher”>
     </form>
 

Oracle BI EE 10.1.3.3 – Configuring Delivers – iBots

I had one OBI EE user today who had specifically asked about configuring iBots. Though it is pretty straight forward, the customer was looking out for a screenshot by screenshot tutorial on how to go about setting delivers. Well, i had some time to put this one together. Hopefully, you would find it of some use.
1.   The first step is to configure a schema that would hold all our scheduler tables. If you do not have a schema create or use an existing one. In my case i have a schema called S_NQ_SCHED wherein i would create all my scheduler tables.
2.   Navigate to your {ORACLEBI}/Server/Schema folder and run the scheduler scripts. If you are on an Oracle Database, you would have to run SAJOBS.Oracle.sql. This would basically create the scheduler tables that Oracle Delivers would use later.
3.   The next step is to open your Job Manager. Go to File – > Configuration Options and enter the schema and connection details for the scheduler schema. In my case it would be S_NQ_SCHED schema.
      
4.   Go to the General tab. If you had followed the default install, then leave everything the same. Just enter in the Administrator usernames and passwords.
   
5.   Go to the Mail tab and enter in your mail server details.
      
6.   Open your instanceconfig.xml and add in the scheduler details. This step basically lets the presentation server know about the machine and port details of the scheduler. By default the scheduler port is 9705.
      
7.   The next step is to run a cryptotools utility that would basically store the username and password of the scheduler into an XML file called credentialstore.xml.
8.   Go to {OracleBI}/web/bin from command prompt. Then enter in the following command.
cryptotools credstore -add -infile OracleBIData_HOME/web/config/credentialstore.xml
   
Ensure that you have the alias as “Admin”.
9.   The next step is to add the details of this credentialstore.xml file into the instanceconfig.xml.
   
Once this is done you should be able to schedule and create new ibots. The best and quick way to check whether the configuration has been done properly is to save a sample ibot. If you are able to properly save one then your configuration has been properly set. For more details refer the docs here.

Oracle BI EE 10.1.3.3/2 – Write Back Option – Budgeting/Planning

OBI EE has another interesting feature that would allow end users to update or insert data back into the database. For example, one can have a column of data that can be manually entered by end users, which in turn can be updated back to the database. This would make a lot of sense for users who want to do planning or budgeting on a mini scale. They can use OBI EE both as a reporting tool and also partly for entering sales quotas or budgets etc depending on how the business is performing currently. Let us see how to go about setting up this option in OBI EE. The entire process of setting this up involves changes right from the connection pool to the presentation layer. The example that i am going to follow here is based on the data from SH schema. I have added a new column called Sales_Quota to the sales table which is what i would like to update and insert. The idea is to allow end users to enter data for sales_quota and insert new records into the database.
      
1.   The first step is to enter in the relevant details in the connection pool writeback properties section. If you are on Oracle, leave them as default.
      
2.   The second step is to make the sales table not cacheable. This would ensure that we would get the results as and when we update or insert.
      
3.   Once this done, log into OBI EE Answer and give in the writeback privilege to those users who would need them. In my case, i would be giving them to Administrator since it is not enabled by default.
      
4.   Next is to create a sample report that would include the sales_quota column. I have a report with all the 5 dimension keys and sales_quota.
      
5.   Once this is done, go to the column properties of sales_quota and enable writeback.
      
6.   After enabling the write back, save the report. The next step is to specify the query that we would like the BI Server to execute while writing back to the database. The queries (insert and update) are specified in a XML Template.The XML Templates are generally placed in {ORACLEBI}/web/customMessages folder. The structure of my XML template file is


The filename of the XML file can be anything. But the WebMessage name is the actual XML template name i.e SHNEW is the XML Template name. One can have inserts as well as updates. @{cn-1} specifies the actual coulmns that i have pulled into the report.
7.   Go to the table properties of the report and click on the write back option. Enter in the template details.
      
      
8.   Once this is done, enter in some data and test out the write back.
      
      
More details can be obtained from the BI Server and Presentation Services guides.