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.
Some authentication methods used by Oracle BI server are
1. Database
2. LDAP
3. Oracle BI server (repository users) – I do not recommend this method for medium to large implementations. It will be difficult to manage.
I will discuss on setting up LDAP in this article.
Setting up LDAP or Windows ADSI in OBIEE
Microsoft ADSI (Active Directory Service Interface) is Microsoft version of LDAP server. Most of the steps to setup of either Microsoft ADSI or LDAP server are similar. In either case, you would need help from your network security group/admin to configure LDAP. They should provide you with the following information regarding the LDAP server
1. LDAP server host name
2. LDAP Server port number
3. Base DN
4. Bind DN
5. Bind Password
6. LDAP version
7. Domain identifier, if any
8. User name attribute type (in most cases this is default)
Registering an LDAP server in OBIEE
In Oracle BI repository, go to manage security.
Create a new LDAP server in OBIEE Security Manager
With the help from your network security group/administration, fill out the following information
Next in the Advanced tab, based on the kind of LDAP server you have and its configuration, make the necessary changes.
For Microsoft ADSI (Active Directory Service Interface), choose ADSI and for all others leave it unchecked.
Most of the times, Username attribute would be automatically generated. For Microsoft ADSI It is sAMAccountName; for most of the LDAP servers it is uid or cn. Check with your network security group/administrator on what is the username attribute for your LDAP server. Make a note of the user name attribute you will need it later.
Now we need to create an Authentication initialization block. In administration tool, under Manage go to Variables.
Under Action, go to New -> Session -> Initialization Block
Configure the session initialization block. Give it a name and click on Edit Data Source. In the pop up window, choose LDAP from the drop down box and then click on Browse. You can also configure a LDAP server here by clicking on “New”. In the browse pop up window choose the LDAP server you would like to use.
Next we need to create variables. User and Email are the common variables normally in play.
Upon clicking on OK, a warning pops up on the usage of User session variable (User session variable has a special purpose. Are you sure you want to use this name). Click yes.
Next enter the LDAP variable for username. sAMAccountName in the case of ADSI as configured in the LDAP.
Next following similar steps create a variable for Email. In addition, depending on you need, you can bring additional variables from the LDAP server.
This is the second installment in a series of posts in which I’ve been discussing the implementation of High Availability within an OBIEE environment.Much of what we’ll be discussing was in included in an Oracle eSeminar which I recently viewed on the topic.In my original post, I gave the broad strokes in regards to HA and provided the basic overall architecture of a High Availability deployment.This time, we’ll start to dive into some of the specifics regarding configuration which will be necessary to implement a true “shared nothing” HA environment.
Each Presentation Server can be configured to talk with multiple web servers,Java hosts, BI Servers, and BI Schedulers.In this installment we’ll cover the Presentation Catalog, web server, and Java host connections to the Presentation Servers as well as how the user is affected when a Presentation Server fails. The diagram below is a subset of the one shown in my original post on the subject.This figure shows only the components of the HA architecture which we’ll be looking at today.
First, let’s discuss the web client behavior in a High Availability environment.When a user begins a session, the web client is bound to a specific Presentation Service and subsequent requests will be sent to that same service. When a Presentation Service failure occurs, the error is relayed back to the browser and any unsaved data will be lost.Upon logging in again, the user will be bound to another available Presentation Service.Two exceptions to this rule would be if the user is using SSO or if the Presentation Services plug-in is configured to automatically reconnect to another server.In these cases, there may still be a loss of session state. There will also be a time lag to recognize the failed server. This lag will be dependent on plug-in ping settings which we’ll get to eventually.
Any iBots which fail to complete as a result of a Presentation Service failure will result in an error being passed to the BI Scheduler Server and will be included in the log file. When the next available Presentation Service becomes available, the job is rerun without impact and will start again at the step in which it originally failed.
Next, we’ll look at how we would like our Presentation Services to share the Presentation Catalog. There are two basic options which can be deployed.The first option is to use a shared file system. All presentation servers have access to the same shared files.This is the simplest approach and, as I mentioned in my last post, is recommended by Oracle.Alternatively, a more complex method of catalog replication can be deployed through the use of replication agents on each instance which will monitor a single instance for changes and sync other copies as necessary.Two-way replication, which involves making changes to multiple copies of the Presentation Catalog and attempting to keep them all in sync, is highly discouraged and should be avoided.As you can imagine, this method would make maintaining data integrity much more difficult and complicated.
If you’ll be using the shared file approach, the first step necessary will be to point each presentation server to the shared file path by editing the <Catalog> element of the instanceconfig.xml file.In addition, we should also make changes the Presentation Service cache settings.Keep in mind that each instance will have its own cache, which we’ll want to configure to ensure it won’t get stale.Oracle recommends adding the following settings to each configuration file:
Another piece of the puzzle will be to configure the presentation servers to work with multiple Java hosts. Once again, we must edit instanceconfig.xml to complete this task.This will involve listing the java host instances as shown below. The default Java Host port is 9810, but you can verify this by checking the OracleBI_Home\web\javahost\config\config.xml file.Simple load balancing will be performed in a round robin fashion between all instances listed in the config file.
<JavaHostProxy>
<Hosts>
<Host address=”<Javahost Machine1>” port=”9810”/>
<Host address=”<Javahost Machine2>” port=”9810”/>
</Hosts>
</JavaHostProxy>
You may also add an optional LoadBalance/Ping element. This element specifies the criteria for determining whether a Java Host is reachable.The ping element is not necessary if you wish to keep the default, which is 5 pings at 20 second intervals.
The final component we’ll look at today is the BI pres Services plug-in, which sits on the web servers.Here I’ll outline the changes necessary on each web server instance both for IIS and Java-based servers.IIS web servers will use the ISAPI plug-in, and the config file for this plug-in can be found in the OracleBIData_Home\web\config directory.The only element you must configure is the Hosts element, in which you will list the host and port of all Presentation Service instances.You may also optionally configure the LoadBalancer element which controls the autoroute feature. The default setting is false, which means that the user will receive an error if the current Presentation Server goes down.Setting this option to true would cause the server to attempt to connect to the next available Presentation Server without impact to the user.You also have the option of adding the ping element, which is the same element we just discussed when examining the Java host configuration.
The Java Servlet changes necessary for Java-based web server configuration are very similar to the ISAPI configuration mentioned above.You’ll need to edit the config file found in the OracleBI_Home\web\app\WEB-INF directory to include all Presentation Server host and port pairs. The <oracle.bi.presentation.sawconnect.loadbalance.AlwaysKeepSessionAffiliation> element is equivalent to the <LoadBalancer> element with the ISAPI plug-in and should be set to “Y” or “N”.
Next time we’ll continue to discuss the BI Presentation Server and how it will be configured to talk with the BI Server and Scheduler…
Almost every OBIEE implementation includes some kind of location dimension. Whether you’re reporting on a customer location, a sales territory, or a geographic relationship between a supplier and manufacturer… the significance of location, as it applies to business, is undeniable.
By nature I am a visual person. If you give me a list of directions on a piece of paper, and I will most likely get lost. Let me look at a map for 2 minutes, and I probably won’t have to look at it again. I think this is how most people in the world function. Ok, most men… some women.
Regardless, imagine being able to harness the power of a map within a tool like OBIEE… well, you can. Utilizing a free service, and a tool as dynamic as Google Maps, you can turn any basic table or graph into an interactive map. Additionally, you can get actual satellite and street view photo’s of specific locations… ever want to see what your boss’s house looks like???
Here’s how: In Answers, within the “Criteria” tab, modify the formula for your Location Column. You’ll need to create an HTML link, and then include some SQL to send specific location information to Google Maps. You'll also need to set the column data format to HTML (properties > Data Format). Depending on how the location data is being stored in the dimension, you may have to get creative with this formula… but this should give you a general idea. In this example, we are assuming City, State, and Country are each stored as separate data columns.
This sends a combined version of (ADDRESS, CITY, STATE, ZIP CODE) to google maps. You may need to add some additional formatting to clean up extra comma’s and spaces when it’s sent to Google, or displayed in your report. Give it a shot!
Though query logging has immeasurable development value, do not use this for regular production users as the runtime logging cost is extremely high. Every log item is flushed to the disk, which in turn hurts query response. Also, note that the query log files are not created on per user or query basis, there is only one query log per OBIEE server and it would have exclusive lock on the log file, which kills concurrent performance.
On the other hand, usage tracking has a very low runtime cost and is preferred to monitor the queries being used.
