OBIEE: Import Excel File as Source

  OBIEE: Import Excel File as Source

You would have thought this would have been as easy as the text file or xml file...but it's not. One very

Everything else you need can be found on your local BI Server.

It's been awhile, so I'll run through the steps quickly.

1. Open up your ODBC Data Sources.
2. Select the driver you want to use.



3. Name your ODBC Data Source



4. Select your workbook.



5. In the Admin tool, go to File -> Import (10g) and select your data source. Just ignore those neoview connections, thankfully I don't have to use that one much anymore (but when I do...argh, it kills me).



6. Import that file/folder



7. What you'll have in your physical layer is this:



That doesn't do me much good does it? I tried to manually create a table, called Sheet1 like this:



And this is where Mr. Minkjan's article came in handy...you have to name the ranges. Ah, yeah...makes sense? I had assumed (I know, I know what they say about that) it would be named sheets.

So how do you name ranges?

8. Highlight your selected rows/columns, right click, "Name a Range"



9. That brings up a box like this, now just call it LOB.



10. Now repeat steps 1-5 and when you get to the Import step, you should see something like this:



Thanks,
Satya Ranki Reddy

OBIEE Directing a user to a default dashboard

PORTALPATH Variable – OBIEE 10g

 

 

 

It identifies the default dashboard    the user sees when logging in (the user can override this preference after logged on here: Settings → My Account → Preferences → “Default Dashboard”).
Steps to create PORTALPATH system session variable
Find the path of the dashboard where you want to default it for every user.
You can find it in Oracle Business Intelligence – Catalog Manager

Open in online mode, provide the answer URL path
Provide the Administrator user and password
The catalog will open the current catalog folder list.

Navigate to shared folder, open the dashboard folder group and open the dashboard page which you want to default the page.
Copy the path above
Create a system session variable with initialization like below

Give the statement as select ‘/shared/India/_portal/Satya' from dual
Single quote is mandatory.
Create the variable target as PORTALPATH (can’t give any other name as it is reserved)
Test it
Save and close the Admin tool
Logon to Answers (presentation services) with any users, the default page will open as given in the variable.

The users can change it on the settings – my account to set any default page.


OR,

Are you using External table authentication then try the  below method.

OBIEE Directing a user to a default dashboard

One of our customers wanted the OBIEE users after login directed to a default dashboard based on the department they work in. First we created a view containing P_USER, DEPARTMENT and PORTALPATH.

In the variable manager we created an initialization block


We connected this to a session variable called PORTALPATH. This is a OBIEE reserved word so will get a warning, just ignore it.

You need to restart the BI-server and the presentationserver!
Let’s try it:
USER1

USER2

USER3

Thanks,
Satya Ranki Reddy

BI Publisher Bursting Configuration on OBIEE 11g 


This article gives a complete example of BI Publisher Bursting using Oracle BI Publisher which comes bundled with OBIEE 11g (11.1.1.5.0).
Create a schema/user called bischeduler on the database.



CREATE USER bischeduler IDENTIFIED BY bischeduler DEFAULT TABLESPACE USERS;
GRANT CONNECT, RESOURCE, CREATE DATABASE LINK, CREATE MATERIALIZED VIEW, CREATE PROCEDURE,
CREATE PUBLIC SYNONYM, CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM,



CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE VIEW, CREATE SESSION To bischeduler;

Scheduler Configuration

After you login to the BI publisher, go to Administration (right-hand top). Under System Maintenance, click Scheduler Configuration.

Give connection details of the database and user you just created and do “Test Connection” and if it succeeds, do “Install Schema”. This will create the necessary database object in the schema needed by Quartz Scheduler used by BI Publisher engine.

Data Source Configuration

Make sure “demo” database is correctly configured under Administration->Data Sources-> JDBC Connection.

Point to any Oracle database which comes with HR and OE schemas. Most of the reports which come pre-packaged with BI Publisher fetch data from OE schema. Click on “demo” hyperlink shown below

And set it properties as shown below

Report which we are going to use for Bursting is going to be fetching data from HR schema.

Will start with the report creation.
Click on ‘Catalog’ to see all the files and folder. Click on ‘New’ icon and select ‘Folder’.

Next click on ‘New’ icon (Next to Catalog link) and select ‘Data Model’.

You will get a page as below.

You can give the Description for your Data Model and the Default Data Source. In XML Output Options, make sure to check Include Parameter Tags and the other two are unchecked. Select ‘Data Sets’ tab, click the ‘New’ icon and select ‘SQL Query’ as shown in the image below.

You will get a screen similar as shown below.

Here you can directly enter the SQL statement or can generate the SQL statement using Query Builder. Make sure that you select the right Data Source.
select EMPLOYEES.EMPLOYEE_ID as EMPLOYEE_ID,
EMPLOYEES.FIRST_NAME as FIRST_NAME,
EMPLOYEES.LAST_NAME as LAST_NAME,
EMPLOYEES.EMAIL as EMAIL,
EMPLOYEES.PHONE_NUMBER as PHONE_NUMBER,
EMPLOYEES.HIRE_DATE as HIRE_DATE,
EMPLOYEES.JOB_ID as JOB_ID,
EMPLOYEES.SALARY as SALARY,
EMPLOYEES.COMMISSION_PCT as COMMISSION_PCT,

EMPLOYEES.MANAGER_ID as MANAGER_ID,
EMPLOYEES.DEPARTMENT_ID as DEPARTMENT_ID,
JOBS.JOB_TITLE as JOB_TITLE,
DEPARTMENTS.DEPARTMENT_NAME as DEPARTMENT_NAME
from HR.JOBS JOBS,
HR.DEPARTMENTS DEPARTMENTS,
HR.EMPLOYEES EMPLOYEES
where DEPARTMENTS.DEPARTMENT_ID=EMPLOYEES.DEPARTMENT_ID
and EMPLOYEES.JOB_ID=JOBS.JOB_ID








order by DEPARTMENTS.DEPARTMENT_ID
Click on ‘XML’ icon on right-hand top.

Select ‘Number of rows to return’ to ‘All’ and select Run. On the click of Run you will see an XML output as shown below.

Besides ‘Return’ button on the right-hand top, there is an image. On the click of this image, select the option of ‘Save As Sample Data’.

After this step, in ‘Data Model’ you can see ‘sample.xml’ across ‘Sample Data’.

Save the work by clicking on ‘Save’ icon (right-hand top).
Creation of Report
Create a new layout called “Employee Template 1” and upload the rtf template file for the report. Figure below shows contents of layout file. This was created in Microsoft Word.
<?call:iWareCompanyHeader?> <?xdoxslt:set_variable($_XDOCTX,’RTotalSalary’,0)?>

Emp ID	First Name	Phone Number	Department	Salary
<?for-each: ROW?><?EMPLOYEE_ID?>	<?FIRST_NAME?>	<?PHONE_NUMBER?>	<?DEPARTMENT_NAME?><?end for-each?>	<?SALARY?> <?xdoxslt:set_variable($_XDOCTX, ’RTotalSalary’, xdoxslt:get_variable($_XDOCTX, ’RTotalSalary’) +SALARY)?><?xdoxslt:get_variable ($_XDOCTX, ’RTotalSalary’)?>

<?template:iWareCompanyHeader?>
iWareLogic
iWareLogic Technologies Pvt. Ltd.
Aditi Samruddhi,
Baner, Pune 411045
Email: info@iwarelogic.com
<?end template?>

Next click on ‘New’ icon (Next to Catalog link) and select ‘Report’.

You will see a page similar as shown below. You have to select the Data Model you have created in the previous step.

Select ‘Upload’ from region ‘Upload or Generate Layout’.

Give the path of the template in ‘Template File’ and the other details. Once the template is uploaded, save the work and you can now view the report. Click on ‘View Report’ icon.

You will see an output as shown below.

The report generated list of employees and “sum of salary” as the last row.
Bursting
Before we start with bursting configuration, let us say that we want this employee report to be generated on per department basis, i.e. there should be as many reports as there are department and each report should show list of employees only in that department along with sum of their salary. For Bursting to work, scheduler has to be correctly configured which we already covered in section “Scheduler Configuration”.
Now select the Data Model file and click on ‘Bursting’ tab. Suppose this option is disabled, make sure you have checked the option ‘Enable Bursting’. It comes under Properties on edit of the report created.

“Split by” is set as “/DATA_DS/G_1/DEPARTMENT_ID”. This is XPath expression for choosing DEPARTMENT_ID from the XML. In Order to see how this XML data, which is fed to Layout Template, just go click on ‘XML’ icon.
“Deliver by” is also set to “/DATA_DS/G_1/DEPARTMENT_ID”. Value chosen by this XPath expression acts as a co-relation KEY between the report data and the SQL Query which we are going to be using for specifying bursting delivery details.
SELECT distinct(department_id) KEY,
‘Employee Template 1′ TEMPLATE,
‘RTF’ TEMPLATE_FORMAT,
‘en-US’ LOCALE,
‘PDF’ OUTPUT_FORMAT,
‘FILE’ DEL_CHANNEL,
‘C:\temp\yogesh\publisher’ PARAMETER1,
(select department_name from hr.departments d
where d.department_id = e.department_id) || ‘.pdf’ PARAMETER2

FROM hr.employees e








where e.department_id is not null

Make sure you specify the right path for PARAMETER1.

If you run this query, you will see output like.

This query specifies all the parameters necessary for bursting the report. Column names of this query have to be “KEY”, “TEMPLATE”, “DEL_CHANNEL” etc. DEL_CHANNEL stands for Delivery Channel. Following table lists all possible delivery channels and additional parameters which you have to specify depending upon the delivery channel you choose. Example above is using “FILE” delivery channel and it needs Parameter1 to be Directory where reports will be saved and Parameter2 will be name of the report.

Channel	Parameter1	Parameter2	Parameter3	Parameter4	Parameter5
Email	Email address	CC	From	Subject	Message body
Printer	Printer Group	Printer	Number of copies	Sides	Tray
Fax	Fax Server Name	Fax Server Number
WEBDAV	Server Name	Username	Password	Remote Directory	Remote File Name
File	Directory	File Name
FTP	Server Name	Username	Password	Remote Directory	Remote File Name

Parameter6 and Parameter7 are applicable only when delivery channel is EMAIL and their values mean “Attachment true/false(For PDF, always set this parameter to true)” and “Reply-To” respectively.
Once Bursting parameters are set, click on ‘Save’ to save your work.
To schedule a job to generate the reports, select ‘Catalog’ and navigate to the report you have created. Below the report name and details, there is a link ‘Schedule’.

In ‘Output’ tab, make sure to select ‘Use Bursting Definition to Determine Output & Delivery Destination’. In ‘Schedule’ tab, select ‘Run now’ option. Click on Submit.
Important options are “Run Immediately” and “Burst Report”, click on Submit. Once you submit this schedule, you will contents of folder c:\temp\yogesh\publisher. In my case, I saw 11 reports generated as there were 11 distinct departments. Please note, if you don’t put where e.department_id is not null in the SQL Query given for bursting, scheduler will not run and you won’t get the reports. In the HR.EMPLOYEES table there is one employee with NULL DEPARTMENT_ID and bursting SQL query does not like NULL to be present in the KEY column.

And see below contents of Accounting.pdf. As you can see this report only contains employees belonging to accounting department.

Thanks,

Satya Ranki Reddy

BI Publisher Scheduler Configuring

 BI Publisher Scheduler Configuring - 10G

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.


(OR)

OBIEE - Configuring the publisher/scheduler for MySql

Publisher can be used to share and distribute reports. Scheduler is a quartz based scheduler for reporting jobs. In this post we will look at sending a report to a user via mail using the scheduler and publisher.
Steps
1. Login to BI publisher. If you face any problems during login, use the following troubleshooting options
http://forums.oracle.com/forums/thread.jspa?threadID=582633&start=0&tstart=60
http://oraclebizint.wordpress.com/2007/11/06/oracle-bi-publisher-and-bi-ee-invisible-admin-tab/
http://onlineappsdba.com/index.php/2009/01/15/oracle-bi-publisher-admin-console-xmlpserver-login-issue-administratoradministrator/
2. Note that the scheduler tab will be inactive.
3. To configure the scheduler. Open the Administration tool. Connect to the repository. Click on Manage->Jobs to launch the Job Manager.
4. In the job manager, Click on File -> configuration options.
5. Click on the Scheduler tab on the tab.
6. Click on the Database tab on the second row. Fill in the relevant information as shown in the screenshot. The DSN can be created as shown in earlier posts.

7. Click on the general tab on the second row and fill in the username and password. Check the scheduler script path and default script path (the default values should be ok).

8. Click on OK to close the job manager dialog.
9. Open BI publisher. Click on the Admin tab (If admin tab is not visible look at the links posted in step 1).
10. In the datasources section of the admin tab click on the link that says 'JDBC connection'.
11. Click on the tab that says JDBC.
12. The default jdbc connections will be visible. We need to add the mysql connection to this list. This link explains how to do that
http://www.iwarelogic.com/blog/how-to-configure-mysql-database-connectivity-in-bi-publisher-485
13. Once that is done, go to the jdbc tab and create a new datasource for mysql.
. The radio button that says 'user proxy authentication' should not be checked.
14. Next, click on the Admin tab in BI publisher and select that link that says 'Scheduler configuration' in System maintenance section. Click on the tab that says scheduler configuration. Fill in the mysql jdbc connection settings as shown.

15. click on 'test connection'. Once the connection is successful. click on install schema. This will install the scheduler related schema in the foodmart database of mysql.
16. We need to create some more tables so that the scheduler service works. go to /server/Schema location. There will be a file called SAJOBS.Oracle.sql Modify this file to suit Mysql and use this file to create tables in the foodmart schema of mysql.
17. we will now attempt to start the scheduler service. stop the BI server . start the scheduler service followed by the BI server (make sure mysql is running). It is important to start the scheduler service before BI server otherwise the schedule tab in BI publisher will not be enabled.
18. We will now create the report. Click on the Reports tab in BI publisher.
19. Click on create a new report. Once the report is created, open it in edit mode.
20. Add a data model to the report.
21. In the new data model page, select SQL query as the Type. User oracle BI EE as the datasource. click on query builder to build the report query.

22. click on layout and create a new layout based on default data model.
23. save the report. click on view. the report should be visible.
24. To schedule the report, click on schedule when the report is in view or edit mode.
25. Fill in the relavant details. Use E-mail as notification channel. enter the destination e-mail address in e-mail delivery.
26. Before scheduling the report, however, the e-mail options need to be set in BI publisher. To do so, click on the admin tab and click the link that says Email in the delivery section. Click on Add server and add the connection info for the smtp server that will be used for sending the mail.
27. once scheduling is complete, look at the schedules tab for a list of jobs that have been scheduled.

Thanks,

Satya Ranki Reddy