ODI other New features in OBIEE 11.1.1.7.1

Oracle is moving ahead with the ODI integration to the BI Package, in the words of Oracle,

"With the 11.1.1.7.1 release of the Oracle BI Applications, you can now use Oracle Data Integrator 11g (ODI11g) as the embedded data integration engine within the product. Along with the introduction of several new BI applications and extensions to existing ones, the introduction of ODI11g has allowed Oracle to simplify the configuration process for the BI Applications as well as leveraging ODI's high-performance "ELT" (Extraction, Load and Transform) for data loading."

Apart from this other features include:

Visualization Enhancements: Performance Tiles, 100% Stacked Bar, Freeze Headers, Waterfall  Chart, View Suggestions, Trellis Chart Grid

User Interaction Improvements: Navigation/Action from Totals, Breadcrumbs, Pass context

to ADF, Search with Endeca MDEX, Save as Analysis on Dashboard, Trellis Actions

BI Mobile HD Enhancements: Analytics available on mobile without additional development,  Data dense visualizations enabled by in memory analytics, enhanced right click interactions, better interactivity with native gestures, offline access to content with fine grained security to grant or revoke privileges

Port Setting in BIPublisher 10 G standalone

 This article is dealing only with BIP Standalone and reader should not get confused with OBIEE+BIPublisher Port setting.


1. Locate the default-web-site.xml file.

Note:  Usually the path is like oc4j home/j2ee/home/config/default-web-site.xml. OC4J reads this file when it starts up.

2. Open the file in text editor. In this file you can see the default port which had been set during the installation. Change the port value to thee desired port number.

3. Stop then Start the OC4J server.

4. Log into the application with new port number.

I came across this when I wanted to set my BIP's port to 80 but OC4J was not starting up when I tried to start after editing the port number. When I gave port number as 8080 it started up and I was able to access the application.

The possible reasons is, there are some other applications in my machine which requires a server and it might be creating a clash. Anyway my experiment continues.

Clone the bi-server in OBIEE 11G

To clone your biserver in OBIEE 11G login to your Weblogic Admin Console, from bifoundation_domain select Environments and follow the steps.

1) In the change center Click "Lock and Edit", this would make servers available for selection.

 

2) By clicking in the check box Select the server you want to clone from the provided server list and click clone.

3) Provide the server name for your all new server and assign a port for it, the administrator should notice that server name and port(Here I am Using 9705) should be unique. Click ok to make the clone.

4) Go to Change Center and Activate the changes.

5) Move to the control tab in the Summary of Servers.

6) Select the new server and click Start button. (Due to some memory limitation in my machine I am giving a Force Shutdown for my default Server.)

7) Wait till it starts up.

8) You can observe the status of the server and satus of last action. If your process is complete status of last action would show "TASK COMPLETED"

9) The new server can be accessed through the new port assigned (9705)

10) New Server is also listed in the Enterprise Manager.

Thus the cloning of biserver is completed.

 Thank you for sparing your time with my blog.

Local Temporary tables

Local temporary tables are valid within Modules/Schema and are bound to active SQL sessions.
The syntax to create a Local Temporary table is:

DECLARE LOCAL TEMPORARY TABLE table_name ( fields);

Example

DECLARE LOCAL TEMPORARY TABLE employee_temp
( employee_id number(10) not null,
  employee_name varchar2(50) not null,
  employee_designation  varchar2(50)
);

The above command will create a temporary "employee_temp" table.

Substring in Oracle SQL

The basic syntax for Sub-String in oracle sql is

"SUBSTR(string/column/field,start_position,length)"

and this function is available in all versions from Oracle DB 8i till Oracle DB 12 C (Oracle 12c)

If start_position is 0, then the SUBSTR function treats start_position as 1 (ie: the first position in the string).

If start_position is a positive number, then the SUBSTR function starts from the beginning of the string.

If start_position is a negative number, then the SUBSTR function starts from the end of the string and counts backwards.

If length is a negative number, then the SUBSTR function will return a NULL value.

Examples:

SUBSTR('This is a test', 6, 2)	return 'is'
SUBSTR('This is a test', 6)	return 'is a test'
SUBSTR('IntelBusi', 1, 5)	return 'Intel'
SUBSTR('IntelBusi', -4, 4)	return 'Busi'
SUBSTR('IntelBusi', -4, 3)	return 'Bus'

Siebel Client Setup (Installation) Hangs at 92% or just after browser Opens up

This is an which usually occur during the installation of Siebel Client in XP/WIN Server 2003. While installing the client siebel Opens up your default browser and requests you to close it. Even if you close the the browser if you find the installation is showing no progress, and seems to be a installer hang when it is around 92% of the setup be sure that a windows "Process" is blocking the installation. To take a closer look you can refer the installation log but it wont be fruitful.

The solution to this issue is open the "Task Manager" move to process tab, select "regserver32" click End Process button. Now you have done it !!! Now you can see that Installer completes by itself.

SSACommonDllInit failed for enu(SBL-GEN-02505)

SSACommonDllInit failed for enu(SBL-GEN-02505) is an error which I got while opening Siebel tools. Let's look into the issue, it is an Initialization error for the ENU (English) Client. So it can be either a configuration issue or a file missing issue.

You can try searching installation directory for the .cfg file. but there wont be a result...!!! How this could happen? The answer is simple. Installation got corrupt. Even if we copy files from a working environment the client wont function properly. And a common issue is internet explorer gets close automatically.
The best practice is to Re-Install Siebel thick client.

In case of configuration error try editing the "Target" in your shortcut as follows
C:\Siebel\8.1\Client_1\BIN\siebel.exe /c C:\Siebel\8.1\Client_1\bin\enu\cs.cfg  /u sadmin /p sadmin /s c:\spool.sql

Each part of the above line can be explained as
C:\Siebel\8.1\Client_1\BIN\siebel.exe > Path to siebel.exe
/c C:\Siebel\8.1\Client_1\bin\enu\cs.cfg > Path to cfg file (Configuration)
/u sadmin /p sadmin >User name and password, This section is optional.
/s c:\spool.sql > Spooling, enable spooling to get the siebel generated queries, use this feature for debugging,This is also an optional parameter.

BI Publisher java.lang.reflect.InvocationTargetException

When I was doing some crazy experiments with XSL variables in BI Publisher,
came across the error "java.lang.reflect.InvocationTargetException".
The error was generated by the MS Word Addin of BIP while creating a pdf preview. as shown.

The error screen was the following.

A little googling saved me!!! the reason for this error in java is mainly due to incorrect datatype.
Say a variable of type "Number" is getting assigned with a value of type "Text".

Bingo... the error might be in the variable I decalred as below..!!!

<?xdoxslt:set_variable($_XDOCTX,'V_NA',0)?>

and what generated the error should be,

<?xdoxslt:set_variable($_XDOCTX,'V_NA',NAME)?>

The error was resolved by changing the code to

<?xdoxslt:set_variable($_XDOCTX,'V_NA','xyz')?>

And tried again to get a preview.... Ta..da... It Worked.

Best Visualization and Recommended Visualization for - OBIEE 11.1.1.7

Why developers love Oracle products? Answer is pretty simple, they stand with the developer and makes their life easier. "Best Visualization and Recommended Visualization for" might be the result of such a thought.
Through the steps and screenshots below I wish to show you how it is.

1. Login to your OBIEE, then go to New Analysis> Your Subject Area (Here am using vanilla rpd)
2. Pull in some dimensions and facts.

3. Click Results tab.
4. Click New View>Best Visualization

   For my selection of dimensions and facts this was the result

 5. We can also try with the "Recommended Visualization for" if we are not satisfied with the best !!!!

Here oracle is helping the developer without putting hands into their freedom. This is a very exciting feature in 11.1.1.7. Hope you will enjoy it....!!!

Performance Tiles with OBIEE 11.1.1.7

In this post we are going throuugh an obiee 11.1.1.7 feature, that is performance tiles.
Performance Tile is a single aggregate measure value displayed in a simple way. It further simplifies the view of aggregated measures.
Feeling interested? Eager to create one?? then follow the steps!!!

1. Login to your OBIEE, then go to New Analysis> Your Subject Area (Here am using vanilla rpd)
2. Pull in some dimensions and facts.

3. Click Results tab.
4. Click New View>Performance Tile

 5. So here is your Performance Tile !!!

 6. You can customize your Tile  As Usual with "Edit View" Button

 7. Its interesting that oracle gave us some really good Performance Tile styles

Thank you for visiting intelbusi

ORA-01922: CASCADE must be specified to drop 'username'

Hi, this is a common issue when you try to delete OBIEE11G Schema (DEV_BIPLATFORM and DEV_MDS) from Database. The best way to avoid it is by using RCU itself. But if you want to delete it manually, read further.

This error is issued if someone tries to drop a user when the user to be dropped still has objects. In order to drop the user and all his objects, use key word cascade.
Like,

drop user DEV_MDS cascade;

drop user DEV_BIPLATFORM cascade;

Make sure that you have Admin privileges. Or connect as sys user and delete the user.  

BI Publisher Parameters in the URL

Through this post am discussing about the parameters specified in the report link / URL

Consider a URL like this

http://<ip/address>:<Port>/xmlpserver/Sales/Sales+Report/Sales+Report.xdo?_xpf=&_xpt=1&_xdo=%2FSales%2FSales+Report%2FSales+Report.xdo&Id=1-104x&_xt=Standard&_xf=html

The URL after the report name consists of:

?_xpf=&_xpt=1&_xdo=%2FSales%2FSales+Report%2FSales+Report.xdo&Id=1-104x&_xt=Standard&_xf=html

Note the following standard URL syntax:

? - The first parameter is added with a question mark in the beginning.

& - Additional parameters have to be specified like this.

The BI Publisher parameters are as follows:

_xpf - Its an application internal string. Not to be used by the developer.
_xpt - Chooses the report rendering way or style.

    0 - uses the BI Publisher window (iFrame)

    1- renders just the document

_xdo - Provides the path to the current report. To be note that it is not mandatory to give this parameter.

dept - Report specific parameter that we created in the report.

_xt - Specifies the template to be used. Usually for the reports which have more than one templates.

_xf - Controls format of the output file. Accepted values are: pdf, html, excel, rtf, or data.

ODBC not showing Oracle DB Driver, Windows 7

Most of the Win 7 users go for the 64 bit OS. Here in this OS let us install Oracle DB 32 bit. After the install go to run then type "odbcad32".

While trying to create an odbc connection to our db we wont be able to see the driver of our Oracle db.
The reason is that, its a naming convention in windows that ODBC should be called by "odbcad32". Even though you type odbcad32 its pointing to the 64 bit version of it. You can see this 64bit odbc from "c:\windows\system32". Since our driver is only 32 bit it wont get listed here.

The solution to this issue is go to your "C:\Windows\SysWOW64\odbcad32.exe" (Here I assume you have installed OS in C drive itself).This is the path to the 32 bit version, in this odbc you can see your 32 bit driver.

Steps to Enable BI Publisher logging

 When ever we face an issue in configuration of BI Publisher the easiest method to resolve is by looking into logs.Please find below the steps to enable BI Publisher logging:

1. In the Bi Publisher enterprise go to Admin > Server Configuration.
2. Under General Properties, set Debug Level to Debug.
3. Create a target folder for the logs like D:\BIPLogs.
4. Create xdodebug.cfg with the following test:
  LogLevel=STATEMENT.
  LogDir=D:\BIPLogs.
5. Place the file in jdk\jre\lib in the OraHome folder where the application server is installed.
6. Restart Bi Publisher server.
7. Do some activities in BIP like scheduling.
8. Wait for some time and attach the logs from D:\BIPLogs folder.

ORA-12154: TNS:could not resolve the connect identifier specified

The cause of this error: SQL Plus cannot identify the connect string specified.
What can be the possible reasons:
1. The tnsnames.ora is not present under $ORACLE_HOME. You can check for it by typing %ORACLE_HOME% in command prompt.
2. The file is not accessible.
3. The file is invalid – because of the presence of junk characters or unmatched parenthesis.
4. The connect string that you have used – may not be present in the tnsnames.ora or present with somewhat different name – basically a mismatch of what you have entered and what is present in tnsnames.ora
5. Check the default directories for old or bad copies of tnsnames.ora
6. Please check with the database admins that the Listener is up and running for the connect string that you are trying to connect to.
7. Please check if TNSNAMES is present as one of the values for the NAMES.DIRECTORY_PATH parameter present in the sqlnet.ora file
NAMES.DIRECTORY_PATH= (TNSNAMES)
Please find below sample tnsnames and sqlnet.ora
TNSNAMES.ORA
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.90)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME=ORCL)
)
)

Macro Cannot be found error in BI Publisher- MS Office

After installing BI Publisher for MS Office Suit I got an error stating "Macro Cannot be found..." Just like anybody else i was also frustrated when i saw the following error for the first time.

Solution
Close all MS Office Applications (Including Outlook, Groove etc)
For 64-bit operating systems, type the following in command prompt:
Regsvr32 "C:\Windows\SysWOW64\MSCOMCTL.OCX"
For 32-bit operating systems, type the following in command prompt:
Regsvr32 "C:\Windows\System32\MSCOMCTL.OCX"

It worked fine for me and my colleagues

.
Waiting for your valuable feedback.

Internal database error *** ERROR*** Assertion failed :201129(9.0.2.3228) File is shorter than expected -- transaction rolled back

If you find the following error while configuring odbc connection for siebel tools.

Then most probably your .dbf file is corrupt. Its better to take a new database abstract file.
Reference: http://sqlanywhere-forum.sybase.com

External Database Authentication (simplified)

This article is about creating a external database authentication in OBIEE 10G.

When the number of users is so large the best practice to go for external-database authentication.
this can be done in two small parts.

Part 1:
This part deals with the database part and the steps are:

• Create and populate a table with these columns GROUP,USERNAME,DISPLAYNAME,PASSWORD,LOGLEVEL
• Commit all changes

Part 2:
This is what to be done in OBIEE 10 G Admin tool.

• Go to Manage> Variables
• In the left pane, under session you can find "Initialization Blocks", just click it.
• In the right pane, right click and select 'New Initialization Block' from it.
• Give a name for that block (say 'Security')
• Click on Edit Data Source
• In the text field  type the query "select USERGROUP,NAME,USERNAME,LOGLEVEL,ROW_ID from LOGIN where  LOGIN.USERNAME = ':USER' and  LOGIN.PASSWORD = ':PASSWORD' "
• select the connection pool of the database with login details and click ok.
• Now  you are back at the window where you gave the name 'Security'. Here click 'Edit Data Target'.
• In the newly opened window(window to create a session variable) click 'New'. Give it the name 'GROUP' click ok.
  similarly create session variables as DISPLAYNAME, USER, LOGLEVEL. While creating these variables there will be a prompt for using system defined variables.Accept all of them.Save Initialization block.
• Save the RPD

Restart the server services(BI Server and presentation services)
Try to login with a valid username and password that you have entered in Database.


Please comment with issues while setting up External DB authentication.