Rearranging Columns in Oracle BI using nQUDNLGen and nQUDMLExec

Rearranging columns manually in rpd is a boring task along with waste of time. However Oracle BI has provided nQUDMLGen.exe and nQUDMLExec.exe which can be used to convert rpd into text format and back to rpd. We can use these applications to convert rpd to text then rearrange the columns in Excel or any text editor and convert the text file back to rpd.

The process is explained below:
1.Open rpd in Admintool and copy the table.


2. Open notepad/Excel and Paste.

3. Change the order of columns in the way you want it to be and save the file. Similar changes can be made for any object in rpd.

4. In command prompt browse to the OracleBI/server/bin folder and run the following command:

Syntax: nQUDMLExec [-U [userid]] [-P [password]] -I input_script_pathname [-B base_repository_pathname] -O output_repository_pathname [-8]
-8 is for UTF-8
Eg 1: nQUDMLExec -I testudml.txt -O rp1.rpd
create a new repository rp1. textfile should contain complete rpd definition
Eg 2: nQUDMLExec -U administrator -I testudml.txt -B rp1.rpd -O rp2.rpd
modify rp1 and write to rp2

5. Open the rpd in admintool and confirm the changes.

Just like columns we can also reorder Presentation Tables. The complete rpd can be exported to textfile using nQUDMLGen.exe.

Configuring BI Publisher to use Oracle BI Data

This task involves the following steps

1. Create administrator alias in credentialstore.xml and update instanceconfig.xml
2. Create BI Publisher groups in rpd and assign appropriate users to the groups.
3. Setting up the Datasource to point to Oracle BI Server.
4. Set up the Security Configuration to use Oracle BI Server Security Model
5. Set up the Integration – Oracle BI Presentation Services
6. Set up the Scheduler Configuration

1. Create alias in credentialstore.xml and update instanceconfig.xml

BI Publisher uses Impersonation to get authenticated with the BI Server. The first step towards it is to add an alias in credentialstore.xml.

a. Open command prompt and browse to OracleBI/web/bin directory.

b. Enter command “cryptotools credstore –add”

c. Enter the path of credentialstore.xml file. By default it is located in OracleBIData\web\config folder.

d. Enter a credential Alias which would be used in instanceconfig.xml

e. Enter Administrator’s Login ID and Password.

f. Once this is done enter the following lines in instanceconfig.xml

<advancedreporting>

<reportingengine>XmlP</reportingengine>

<volume>XmlP</volume>

<serverurl>http://localhost:9704/xmlpserver/services/XMLPService</serverurl>

<weburl>http://localhost:9704/xmlpserver</weburl>

<adminurl>http://localhost:9704/xmlpserver/servlet/admin</adminurl>

<admincredentialalias>Biadmin</admincredentialalias>

</advancedreporting>

<credentialstore><credentialstorage type="file" path="Z:\OracleBIData\web\config\credentialstore.xml"></credentialstore>

p.s. replace localhost with the servername on which oc4j is loaded.

g. Make sure the <AdminCredentialAlias> matches the one created in step d and the path of Credential Store is mentioned correctly.

h. Restart Oracle Presentation service.

2. Create BI Publisher groups in rpd and assign appropriate users to the groups

a. Create the following groups in rpd to correspond to BI Publisher functional roles

• XMLP_ADMIN – this is the administrator role for the BI Publisher server.
• XMLP_DEVELOPER – allows users to build reports in the system.
• XMLP_SCHEDULER – allows users to schedule reports.
• XMLP_ANALYZER_EXCEL – allows users to use the Excel analysis feature.
• XMLP_ANALYZER_ONLINE – allows users to use the online analysis feature.
• XMLP_TEMPLATE_DESIGNER - allows users to connect to the BI Publisher server from the Template Builder and to upload and download templates.

b. Assign users to appropriate groups. There should be at least one user in XMLP_ADMIN.

c. Restart Oracle BI Service.

3. Setting up the Datasource to point to Oracle BI Server

a. Login into the analytics application as an administrator.

b. Click on More Products -> BI Publisher

c. This should open up the BI Publisher Screen. Click on Admin tab. (If required login with the same credentials as that of Analytics).

d. Click on JDBC Connection -> Add Data Source

e. Enter a name for the Data Source.

f. Select Driver Type as Oracle BI Server.

g. Confirm that the Database Driver Class is “oracle.bi.jdbc.AnaJdbcDriver”.

h. Enter the connection string as jdbc:oraclebi://<host>:<port> where host is the server on which Oracle BI server is installed. By default the port number is 9703.

i. Enter the Administrator credentials and Click on Test Connection

j. Confirm that the connection is successful and Click Apply.

4. Set up the Security Configuration to use Oracle BI Server Security Model

a. Click on Admin tab -> Security Configuration

b. Enable Super User and set User ID/ Password for it.

c. Select Oracle BI Server in Security Model.

d. In Connection String enter the same connection string as entered in 4-h.

e. Enter the User ID/ Password of rpd Administrator.In Database Driver Class enter oracle.bi.jdbc.AnaJdbcDriver

g. Click Apply.

h. Restart the oc4j server (restarting of xmlpserver from the applications tab of oc4j admin screen is also sufficient for changes to take place)

5. Set up the Integration – Oracle BI Presentation Services

a. Login into the analytics application as an administrator.

b. Click on More Products -> BI Publisher

c. This should open up the BI Publisher Screen. Click on Admin tab.

d. Click on Integration -> Oracle BI Presentation Services

e. Select server protocol as http

f. Server Version as v4

g. Server field should contain the server on which Oracle BI Presentation service is loaded.

h. Port should be 80 if analytics is using IIS or 9704 if analytics is using oc4j

i. Enter the rpd Administrator’s User ID / Password

j. URL suffix should be analytics/saw.dll

k. Click apply.

l. Restart the oc4j server (restarting of xmlpserver from the applications tab of oc4j admin screen is also sufficient for changes to take place).

6. Set up the Scheduler Configuration

a. Login into the analytics application as an administrator.

b. Click on More Products -> BI Publisher

c. This should open up the BI Publisher Screen. Click on Admin tab.

d. Click on System Maintenance -> Scheduler Configuration

e. We need a database to use scheduler. This database will be used to store the information of various scheduler activities.

f. Select Database type

g. Connection String for Oracle and MSSQL is given below:

Oracle

• jdbc:oracle:thin:@<hostname>:<port>:<oracle SID>
  eg. jdbc:oracle:thin:@oracleserver:1520:TEST
  p.s. TEST is the SID, its alias in tnsnames should not be used here.

MS-SQL server

• jdbc:hyperion:sqlserver://<hostname>:<port>;DatabaseName=<DATABASENAME> eg. jdbc:hyperion:sqlserver://mssqlserver:1433;DatabaseName=INFA1

h. Click on Install Schema.

i. Click Apply and restart oc4j or xmlpserver.

j. Important: If you stop and start or restart the database that contains your BI Publisher Scheduler tables, you must restart you BI Publisher Enterprise server.

Outer join of Dimension and Fact in Analytics report

I have came across this situation couple of times when user wants to see 0 against the dimensions for which Fact value is not Available. i.e. we need to take a left outer join between dimension and Fact.

As this requirement is not very common its better to handle it on the webcat side. The following example elaborates this requirement.

In the Area dimension I have 10 values

Fact contains the Booked Amount. However Booked Amount values are not available for all Areas. So after including Fact column number of rows in the report decrease and we get the result as shown in image below:

As seen in the above results, Area 7 and Area 8 are missing as the Fact table does not hold any value for this value of Dimension. The requirement is to show Area7 and Area 8 in the report with Booked Amount = 0.

The Procedure to implement this is given below:

1. Include the required columns and filters in criteria.

2. Click on Combine with Similar Report button and select the same subject Area

3. Add the same dimension and fact columns again

4. Change the formula of Fact column and write 0, (ZERO) in its column Formula.

5. Change the Set Operation to Union All

6. Always show the results in Pivot Table with Aggregation rule as SUM on the Fact column

Area 7 and Area 8 are available in the results with Booked Amount = 0

Using javascripts in Analytics Reports

By using javascripts, many requirements that seems to be impossible in analytics can be implemented easily. The following report is one such example.

This report has a column selector on Time dimension that has Quarter/Month/Week and a fact "# of Service Requests". The tricky part about this report is to show 2 quarters when Quarter is selected in Column Selector, 12 months when month is selected and 8 weeks when Week is selected in the column selector.

1. Create a report with columns Quarter and # of Service Requests in criteria and sort Qaurter in descending order. Apply filter for 12 months as it is the largest span in all 3 required conditions.

2. Click on Column Properties of Open Qaurter and in Data Format select Override Default Data Format.





3. Select Custom Text Format from Drop down menu and paste the following code

@[html]"<""script"">""document.getElementById(""\"Test\""").innerHTML""=""\"3\""";"document.getElementById(""\"columnHeading\""").innerHTML""=""\"Quarter\""</"script">"@



4. Click on Results Tab.




5. Add a Column Selector View and add Month and Week columns to it.

6. Select Month from the column selector and click on Criteria.

7. Click on column properties and change the data format to custom text. Copy the following code in the Custom Text Format:

@[html]"<""script"">""document.getElementById(""\"Test\""").innerHTML""=""\"13\""";"document.getElementById(""\"columnHeading\""").innerHTML""=""\"Month\""</"script">"@

8. Repeat steps 5,6 & 7 for column Week and use the following code for Week column.

@[html]"<""script"">""document.getElementById(""\"Test\""").innerHTML""=""\"9\""";"document.getElementById(""\"columnHeading\""").innerHTML""=""\"Week\""</"script">"@

9. Go to results tab and create a narrative view.

10. In narrative view check the contains HTML Markup checkbox and add the following code

Prefix:
<a Id="Test"></a><table Id= "myTable" class="ResultsTable" onmouseover="RTOver(event)" onmouseout="RTOut(event)" title="Results" border="1" cellpadding="2" cellspacing="0"><tr><th Id="columnHeading" scope="col" class="ColumnHdgD"></th><th scope="col" class="ColumnHdgD"># of Calls</td></tr>

Narrative:
<tr><td>@1</td><td>@2</td></tr>

Postfix:
</table><Script>var counter=document.getElementById("Test").innerHTML;var numberOfRows=document.getElementById("myTable").rows.length;for (i = 0 ; i <= (numberOfRows-counter); i++ ){document.getElementById("myTable").deleteRow(counter);} </Script>

11. In Compund View delete the table view and add Narrative view.

Act As Functionality in Oracle BI

Implementing Act As functionality or the Proxy User functionality is very well explained by Venkat in his blog. This feature uses "Execute Physical" for getting the list of Target Users. That means we need to give access for executing physical requests to the database in Oracle BI Administration tool. This can be easily done for the users by assigning them to a group and adding permission to execute physical requests on the database. However when the users are coming from a different system, say Siebel for instance and we dont have control on groups then its a little tricky to get through. Its recommended to go through this blog before proceeding.

After the Proxy User functionality is set as per given in the Venkat's blog,

1. Create a new Business Model and drag the Table PROXYTABLE in BMM Layer.

2. Create a Dummy Fact using the same table.

3. Create a complex join between the two logical tables in Business Model Daigram.

4. Create Primary key for the Logical Table PROXYTABLE to make the Business model consistent.

5. Drag the Business Model in the presentation Layer and delete the Dummy Fact.

6. Change the contents of the webmessage file located at {OracleBIData}\web\msgdb\customMessages to:

<?xml version="1.0" encoding="utf-8" ?> <WebMessageTables xmlns:sawm="com.siebel.analytics.web.messageSystem"> <WebMessageTable system="SecurityTemplates" table="Messages"> <WebMessage name="LogonParamSQLTemplate"> <XML> <logonParam name="RUNAS"> <getValues> SELECT PROXYTABLE.TARGETUSER saw_0 FROM Proxy WHERE PROXYTABLE.PROXYUSER = VALUEOF(NQ_SESSION."USER") </getValues> <verifyValue>SELECT PROXYTABLE.TARGETUSER saw_0 FROM Proxy WHERE PROXYTABLE.PROXYUSER = VALUEOF(NQ_SESSION."USER") and TARGETUSER='@{VALUE}'</verifyValue> <getDelegateUsers>SELECT PROXYTABLE.PROXYUSER , PROXYTABLE.PROXYLEVEL FROM Proxy WHERE PROXYTABLE.TARGETUSER = VALUEOF(NQ_SESSION."USER") </getDelegateUsers> </logonParam> </XML> </WebMessage> </WebMessageTable> </WebMessageTables>

This approach is similar to the way SA Systems Subject Area work.