This post is going to discuss things to consider when setting up a basic security strategy in OBIEE, focusing mainly on User Authentication.
Security in OBIEE can be thought of as a 2 step process as a user logs into the system: 1. Authentication – Do you have access? 2. Authorization – How much access do you have?
Authentication Think of this as just getting your foot in the door. During Authentication, OBIEE is simply determining if a user has access to the tool. Once someone logs into the system from the Logon screen, the Oracle BI Presentation Server (OBIEE web front end), creates two System Variables. One for the Username that was entered (:USER) and one for the Password that was entered (:PASSOWORD). These System Variables are then sent to the Oracle BI Server to check if a User has been set up with that combination of Username and Password. A User can be set up a few different ways…
Security Manager:
The easiest and most basic way to set someone up with a Username and Password is via the Security Manager. Once someone is set up in this manner, their Username (:USER) and Password (:PASSWORD) are stored on the Oracle BI Server. If the Username and Password entered in the Logon Screen match what’s stored on the Oracle BI Server, they are granted access to the tool. This approach should only be taken if just a few people need access to the tool. Adding an entire organization of people in this manner is tedious, time consuming, and difficult to maintain.
External Database:
Setting up a table with a complete set of User Information, including Username and Password, will allow OBIEE to verify security credentials using what’s called an Initialization Block. An Initialization Block is basically a SELECT statement sent to the User Information table to pull back Username and Password. It is then compared to the System Variables sent from the Logon screen. If there is a match in the User Information table, they are given access. Now the User doesn’t need to be entered through the Security Manager, and the User Information table can be maintained from the database. Additionally, a number of other Variables can be populated from your User Information table to be used during Authorization.
Care must be taken when creating the Initialization Block, as the order of the SELECT statement determines what information is applied to each Variable. For example, in the image below, the value of “full_name” will be applied to the Variable “DISPLAYNAME”.
Single Sign-On:
SSO allows Users a single point of access to all of their organization’s systems. This typically requires the use of an SSO product. View this blog entry for more information on SSO. http://oraclebiblog.blogspot.com/search/label/SSO
Once you iron out your approach with this first step in the process… You’re ready to tackle the task of setting up Authorization. I’ll cover this in a future post…
BI Publisher – OC4J crashing with “Out of Memory Error”
Repeatedly customers get the “Out of Memory Error” when they are running high volume/complex BI Publisher reports (formerly Oracle XML Publisher) on 32bit environment using OC4J. Why you get this error when you try to generate these kind of reports in BI Publisher and how to resolve this issue?
There are multiple ways you can resolve this issue.
1) Login to OC4J server and Increase the OC4J timeout in server properties because sometime it take more time to fetch the data and oc4j has timeout parameter which exceeds timeout limit and your oc4j crashes.
2) Modify/tune the SQL to avoid complex join to fetch the results faster. By doing this it will use less memory and return the data within timeout timeframe. Also since you are running on 32bit environment change the multi-threading to single threading. It will run slower than normal but you may get the results back.
3) In oc4j_cmd by default the memory is set to 256K. In 32bit environment oc4j is allowed to use maximum of 1536K memory out of 4GB which is Windows has a limit on 32bit environment. This is more than enough but sometime if you have reports which has more than 2000 pages with auto bursting reports (rare case which I have experienced). You can change the memory in oc4j to 512K to 1024K or 1536K by using following command:
Take the following steps to change the heapsize values for an OC4Jinstance: 1. Navigate to the Home page for the OC4Jinstance. 2. Click Administration. 3. If necessary, expand the Properties section of the table by clicking the Expand icon. Then, click the Go to Task icon in the Server Properties row. 4. In the Command Line Options area, change the value in the Maximum heapsize and Initial heapsize fields. 5. Click Apply. 6. Navigate to the Cluster Topology page, select the OC4Jinstance that you modified, and click Restart. On the Confirmation page, click Yes. This changes the size of the heap allocated to the OC4J process (only for that single instance). If your Oracle Application Server topology includes more than one JVM on the same system, The heapchanges must be apply to each JVM.
OR go to C:\OracleBI\oc4j_bi\bin folder and modify oc4j.cmd file, save and restart the OC4J service.
Also you can monitor the heap size by logging to OC4J and check under performance tab:
4) If you are running Presentation server on Windows server, the oc4j memory is shared by BI Presentation sever and BI Publisher. You move the presentation server from OC4J to IIS by re-installing the OBIEE on window box. During installation, it will provide an option for you to use OC4J or IIS for BI Presentation service and you should choose IIS. This way it will release the memory which is used by Presentation service. Also In future if oc4j crashes, still Users will be able to login on OBIEE and run Dashboards & Reports and won’t effect them since your presentation services configure on IIS.
What a great opportunity for me to speak freely on the OBIEE Blog site. Working with the team here at BICG - outside of direct reporting into a software vendor - inspires 'more honest' advice.
I've seen many versions of BI over the last thirty years. In the early years we had to figure out how to join VSAM files with IMS data and produce a report. Of course, this was in that funny era when we made the leap from card based editors to CRTs. Maybe thats going back a bit too far for any relevent blogging.
Anyway, I thought there might be some interest out there in knowing how to get from say Cognos to OBIEE or Hyperion along with - or - conversion to OBIEE. I am big on "How-To's" so I'll take that approach going forward.
One thing I learned to do early on is to write about things in the right order so I'll offer some dialog to the Bloggers out there. What would you like to hear about first? Moving from Hyperion into OBIEE without getting burned - or - moving from Cognos to OBIEE?
BI Publisher originated as a Java application called "XML Publisher" (hence the "xmlp" naming convention) embedded within Oracle's E-Business Suite. XML Publisher has been an integral component within E-Business Suite as a means to deliver highly refined PDF-formatted output of raw transactional data, primarily for hard-copy printing.
In its incarnation as Business Intelligence Publisher (hereinafter "BIP"), it is essentially the same application at heart but with several powerful new features. Like XML Publisher, BIP is a XML transformation engine that applies XSLT templates (created on the fly from user-generated WYSIWYG templates in RTF format) against raw data to produce reports with highly customized visual layouts in a variety of output formats.
XMLP differs from BIP in two significant ways:
First, BIP has much greater flexibility using a variety of template sources, output formats, delivery methods and scheduling options.
Second, and more importantly, BIP is not restricted to E-Business Suite as a data source - in fact it has been expanded to interface with a wide variety of applications, including JDBC-complient databases, Webservices, Hyperion... and OBIEE.
From a high-level pespective, BI Publisher...
transforms raw data from various sources (JDBC, Webservices, OBIEE, etc)
according to a layout designed using native functionality of best-of-breed layout tools (Word, Excel, Acrobat, etc)
into various file formats (HTML, PDF, Flash, CSV, XML, XLS, etc)
and delivers them via various mechanisms (Email, FTP, HTTP, WebDAV, Fax, etc)
either on demand or according to a user-defined schedule
Any organization making the investment into OBIEE should understand and take advantage of the extensive data formatting & delivery functionality provided by BI Publisher.
This discussion is divided into two parts illustrating BIP's basic functionality within the context of its two native OBIEE interfaces:
with BI Presentation Services as a specialized integration source, wherein Answers Requests are treated as a unique data type (Part 1)
with BI Server as a JDBC-compliant SQL database, wherein Presentation Tables are treated as simple database tables (Part 2)
Assumptions for this discussion:
BIP was correctly installed and configured onto a demo system during a full OBIEE installation process (full installation & configuration of BIP can be tricky, but is out of scope for this discussion - see Oracle's documentation for more info)
OBIEE on this demo system is serving the demo "Sample Sales" repository
MS Office is installed on the client system (screenshots herein represent MS Office 2007 but should adequately demonstrate functionality for previous versions)
PRE-REQ: Install "BI Publisher Desktop" (interfaces desktop client apps Excel and Word with BI Publisher)
Log in to OBIEE as Administrator
Click to "More Products - BI Publisher"
Click to "Business Intelligence"
Click on "Template Builder" (in Developer Tools) - Save BIPublisherDesktop.exe then run
PART 1: Integration with OBIEE Presentation Services (via Answers Requests)
BIP can interface with the OBIEE Presentation Services as a data source. BIP "sees" existing Answers requests and treats them as reports ready for formatting & delivery. In this exercise we will create a simple Answers request, then connect to it from BIP and build a simple template using MS Word, then observe how we can use native functionality in MS Word to modify the resulting output.
CREATE ANSWERS REQUEST
Click to Answers
Open the "Sample Sales Reduced" Subject Area
Create & save new Request titled "BI Publisher Request" in your personal folder with the following dimensions & facts: Products . Brand Time . Year Facts Revenue . Revenue
CREATE BI PUBLISHER REPORT
Open BI Publisher ("More Products" - "BI Publisher")
Click to "Shared Folders" - "Business Intelligence"
Click to "Create a New Report"
Enter Name = "Answers Request Demo"
Click "Create"
Click "Answers Request Demo" - "Edit"
Click "Data Model" = "New"
Enter Name = "BI Publisher Request"
Select Data source = "Oracle BI Answers"
Click Flashlight icon
NOTE: Directory structure matches Web Catalog structure - because BIP here is communicating with Presentation Services
Click "users" then [your username] then "BI Publisher Request"
Click "Save" (green disk icon in upper LH corner of screen)
CREATE TEMPLATE USING WORD
Open MS Word
Click to "Add-Ins"
Click "Oracle BI Publisher"
Click "Log on" - Pop up window will appear NOTE: Value for Report Server should be the local OBIEE install
Click "Login"
"Open Template" window will open
In "Workspace" drop-down, select "BI Publisher"
Expand "Shared Folders" then click "Business Intelligence"
In the "Reports" area, click on the "Answers Request Demo" (which you created in BIP above)
NOTE: Directory structure matches BIP structure - because Word here is communicating with BI Publisher
In the "Layout Templates" area, click on "New"
Click on "Open Layout Template"
Click to "Add-Ins"
Select "Insert" - "Table wizard"
Select report format = "Table" and click "Next"
Click "Next" (Data Set = "ROWSET/ROW")
Click >> (selects all available fields), then "Finish"
Select "Preview" - "PDF"
Obey prompt to save document in RTF format
PDF viewer will render output
Use "Add-ins" - "Insert" to create new objects, then experiment with native Word formatting functionality to create your desired report layout
When ready to proceed, select "Add-ins" - "Oracle BI Publisher" - "Upload Template As..."
Enter Template Name = "Answers Request Demo Template"
REVIEW NEW TEMPLATE CONFIGURATION IN BIP
Return to BI Publisher
Click to "Shared Folders" - "Business Intelligence"
The following is the third installment on the topic of High Availability within an OBIEE environment.As I mentioned in previous posts, much of what I’ll be discussing was covered in an Oracle eSeminar which I recently viewed on the subject.To quickly summarize, our basic goal within an HA implementation is to provide multiple instances of all components from the BI Server all the way to the end user, so if anything fails, we have another instance of the same component ready to go.The first two posts on the subject can be found in the blog archives if you’d like to rewind. This installment will feature the HA connections between the Presentation Servers and BI Scheduler Servers as well as between the Presentation Servers and BI Servers.We’ll also look closer at the BI Scheduler Cluster configuration.
First we’ll look at the connection between the BI Servers and the Presentation Servers.This again is handled by the Cluster Controller.In a Windows environment you’ll need to go into the Administrative Tools on each Presentation Server and set up the clustered ODBC data source.To do so, simply ensure that “Is this a clustered DSN?” is checked, then specify the primary and secondary cluster controllers and ports.On a Unix/Linux box, you’ll need to make the following changes to the odbc.ini file:
IsClusteredDSN=Yes
PrimaryCCS=<PrimaryCCS>
PrimaryCCSPort=9706
SecondaryCCS=<SecondaryCCS>
SecondaryCCSPort=9706
One important note to keep in mind is that if you have any clients from which you want to access your repository in online mode, you’ll need the clustered DSN set up on these as well.They’ll need the same connection to the BI Servers as the Presentation Servers will.
The BI Scheduler Cluster Controller assigns the active Scheduler server. In an HA environment, you would have two cluster controllers, a primary and a secondary, in an active/passive relationship. The secondary server will not be used unless the primary is unavailable. The client Controller ports are specified in the respective NQClusterConfig.INI files.The Scheduler configuration will be handled by the Cluster Controllers, so all we need to do in the instanceconfig.xml file is point to the Cluster Controllers, as shown below:
<Alerts>
<ScheduleServer>
ccsPrimary=”<Primary Cluster Controller>”
ccsPrimaryPort=”<Client ControllerPort>”
ccsSecondary=”<Secondary Cluster Controller>”
ccsSecondaryPort=”<Client Controller Port>”
</Alerts>
</ScheduleServer>
The other task you would need to complete is to add the BI Scheduler Administrator credentials to the credential store of each ps.The quickest and easiest way would probably be to copy the credential store file from one instance to all other presentation servers.
A few notes about what will occur when the Active Scheduler fails.The transition from one server to another is seamless to the user’s perspective.The users won’t receive any errors, the Cluster Controller will simply detect the failure on the active server and point to the secondary. Any jobs which didn’t complete will be picked up where they left off.One important note to remember is that once the primary server is back up, it will not automatically resume the primary role. Only after the services have been restarted will the primary Scheduler resume its proper role.If any Java, command line, or script jobs are being run during an interruption, they will be restarted when another server is activated and given a new job ID. Take a look at the diagram of the basic HA architecture for the Scheduler Servers.
To configure the Cluster Controller to talk with multiple Schedulers, you must make the following entry in the NQSClusterConfig.INI file on each Cluster Controller:
The first port number for each scheduler will be the rpc port, where the Scheduler will be listening for connections from the Scheduler.The second will be the monitor port, which simply listens for a “heartbeat” from the Scheduler, to confirm it’s still available. This is how the Cluster Controller determines that the primary server has gone down and it needs to look to the secondary server.The default ports for the rpc and monitor ports are 9705 and 9708, respectively.
Configuration of the BI Scheduler Servers themselves can be completed through the Job Manager or via command line using schconfig.All configuration settings are saved in the instanceconfig.xml file of the Scheduler folder, not to be confused with the file of the same name in the web\config folder.The Scheduler can be configured to talk with multiple Presentation Servers and Java Hosts, which will be necessary if you are setting up a true HA environment.If you are using the Scheduler to run script files, you must place them in a shared network file, so that multiple Scheduler Servers can access them.All Schedulers should have read/write access to these files.
That’s going to wrap it up for this post.I’ll finish things up next time with a closer look at the BI Server cluster and how it’s integrated into the HA environment.
Oracle will be featuring one of our customers from BI Consulting Group in a Customer Reference Forum next Wednesday, March 4th.
Discussion is set to surround the details involved with the implementation of Financial Analytics, specifically Payables, Receivables, and Profitability Analytics. The source systems accessed included Oracle eBusiness Suite (EBS) as well as some third-party data sources.
Click to the evite for more or contact your Oracle Rep to get registered (I think this is only necessary if you want to field questions during the discussion period),
SQL used be "SEQUEL" and stood for 'Structured English Query Language', but it was changed to SQL and now stands for 'Structured Query Language.' Supposedly, the change was due to a trademark infringement with another company.
SQL is what users like you and I use to access and manipulate databases.
SQL can....
execute queries against a database
retrieve data from a database
insert records in a database
update records in a database
delete records in a database
create new databases
create new tables in a database
create stored procedures in a database
create views in a database
etc.
SQL is just a general term used to describe the language used to interact with databases.
There are in fact several variations of SQL that are widely used; such as,
I won't go into detail because that is for a later date and a different post.
As the title states, this is just the basics of SQL.
Before we get started, let me explain that I will only be covering the DML part of SQL. To refresh your memory, DML stands for Data Manipulation Language. We'll probably go over the DDL (Data Definition Language) part later.
So, let's get started!
Below are two tables:Employee table and Salary table.
These tables will be used in each exercise to demonstrate how each SQL command works.
In nearly every query, the commands SELECT and FROM will always be used. Why? Because in order to retrieve data from databases, we need to SELECT what it is that we want to see and need to specify FROM which table(s) to retrieve the data.
SELECT
This statement is used to select data from a database.
The syntax for SELECT is:
SELECT table_name.column_name(s)
FROM table_name;
**NOTE:
1.SQL is not case sensitive, but it is good practice to keep all caps for easy readability.
2.'FROM' does not have to be on a separate line; however, keeping it on a separate line is considered best practice due easy readability.
3.Placing the name of the table with the column name is best practice because future queries will require joining tables and tables may have the same column name and the only way the query can distinguish between columns with similar names is by indicating what tables the columns belong to.
An example:
From the Employee table, select the last name, first name, and occupation columns.
This statement is used when selecting only distinct, meaning non-repetitive, unique, data from a table.
The syntax for SELECT DISTINCT is:
SELECT DISTINCT table_name.column_name(s)
FROM table_name
An example of SELECT DISTINCT:
From the Employee table, select distinct values from the Location’s column.
SELECT DISTINCT Employee.Location
FROM Employee;
WHERE
This clause is used to select records that meet a specific condition(s).
The syntax for WHERE is:
SELECT table_name.column_name(s)
FROM table_name
WHERE table_name.column_name 'operator value';
Examples of operator values:
'=' (equal)
'>' (greater than)
'<' (less than) '>=' (greater than or equal to)
'<=' (less than or equal to) '<>' (not equal to)
'LIKE' (string comparison test)
An example of WHERE:
From the Employee table column, select all of the columns where the Location column is equal to Minneapolis.
SELECT *
FROM Employee
WHERE Employee.Location = ‘Minneapolis’;
**NOTE: When the condition references a text value, then you must enclose the condition with single quotes; however, numerical values do not require single quotes.
AND & OR
These operators are used when there are more than 1 condition in the query. They can be used separately or together.
The syntax for each one is:
SELECT table_name.column_name(s)
FROM table_name
WHERE table_name.column_name 'operator value' AND table_name.column_name 'operator value';
(This query means that BOTH conditions must be met in order for the select value(s) to appear in the result-set. If one condition is not met, then no value(s) will appear in the result-set.)
SELECT table_name.column_name(s)
FROM table_name
WHERE table_name.column_name 'operator value' OR table_name.column_name 'operator value';
(This query will return value(s) provided that either one of the two conditions are met. For instance, if the condition is column_name = 'red' OR column_name = 'blue' and only the first condition can be met, then the result-set will show the rows that meet the first condition.)
SELECT column_name(s)
FROM table_name
WHERE table_name.column_name 'operator value' AND (table_name.column_name 'operator value' OR table_name.column_name 'operator value');
(This query combines both AND & OR resulting in a result-set that must still meet both conditions, but with the second condition containing a separate condition that does or does not have to meet the conditions.)
An example of AND & OR:
From the Employee table, select all columns that meet the following conditions: Location equals Minneapolis and Firstname equals Jane or Bob.
SELECT *
FROM Employee
WHERE Employee.Location = ‘Minneapolis’ AND (Employee.FirstName = ‘Jane’ OR Employee.FirstName = ‘Bob’);
IN & BETWEEN
The IN operator works when there are several conditions. It works the same way as the OR operator, but makes the SQL look more legible and intelligible.
The BETWEEN operator is used when selecting a range of data that is between two values.
The syntax for IN and BETWEEN:
SELECT table_name.column_name(s)
FROM table_name
WHERE table_name.column_name IN ('value', value, 'value', etc);
NOTE: The conditional portion of the query could also be written using OR, but would make the query look long: WHERE tale_name. column_name = 'value' OR table_name.column_name = value OR table_name.column_name ='value' etc. Using IN requires less typing and makes the query look more clean and intelligible.
SELECT table_name.column_name(s)
FROM table_name
WHERE table_name.column_name BETWEEN value AND value;
NOTE: The conditional portion of the query can also be rewritten using AND; for example, WHERE table_name.column_name >= value AND table_name.column_name <= value, where 'value' is a numerical value.
An example of IN and BETWEEN:
From the Employee table, select all employees with the LastName equal to Alba, Bower or Davis.
SELECT *
FROM Employee
WHERE Employee.LastName IN (‘Alba’, ‘Bower’, ‘Davis’);
From the Employee table, select all employees whose Location is between Minneapolis and Minneapolis.
SELECT *
FROM Employee
WHERE Employee.Location BETWEEN ‘Minneapolis’ AND ‘Minneapolis’;
Note:Depending on the database that you are using, the BETWEEN function will work different.It may include or exclude the test values.
ORDER BY ... ASC/DESC
This command is used to sort the result-set in ascending or descending order on a specific column. Keep in mind that to sort in ascending order, one can either use 'ORDER BY ... ASC' or just 'ORDER BY', because 'ORDER BY' defaults to ascending order anyways.
The syntax for ORDER BY:
SELECT table_name.column_name(s)
FROM table_name
ORDER BY table_name.column_name(s) ASC / DESC;
An example of ORDER BY:
Select all the employees and rank them by Location in ascending order.
SELECT *
FROM Employee
ORDERBY Employee.Location ASC;
AGGREGATION FUNCTIONS
Aggregation functions are used to calculate numerical values in a specified column.
Below are 6 commonly used aggregate functions:
MIN () - returns the smallest value
MAX() - returns the largest value
AVG() - returns the average value
SUM() - returns the sum value
COUNT() - returns the number of values
COUNT(*) - returns the total number of rows in a table
The syntax for the aggregate functions:
SELECTAVG(table_name.column_name)
FROM table_name
WHERE table_name.column_name 'operator value';
SELECTCOUNT(table_name.column_name)
FROM table_name;
SELECTCOUNT(*)
FROM table_name;
(**This will return the number of rows for the selected table**)
An example of an Aggregate Function:
SELECTAVG(Salary.Salary)
FROM Salary;
Note: In the result-set, the name of the column is AVG(Salary.Salary).To give the column name a more appropriate title, you can give the column name an alias.For instance, SELECTAVG(Salary.Salary) AS Avg. Salary.The column header will now appear as Avg. Salary.
GROUP BY
This statement is used to group the result-set by one of more columns and is used in conjunction with the aggregate functions mentioned above.
Create a list of the total salaries of employees and group them by occupation.
SELECT Salary.Occupation,SUM(Salary.Salary) as Total_Salary
FROM Salary
GROUP BY Occupation;
HAVING
This clause is used in conjunction with ORDER BY and places a condition on the column(s) in the GROUP BY clause. Also, this clause is used with ORDER BY because WHERE cannot be used with aggregate functions.
