OBIEE Online Training

Sunday, December 18, 2011

access denied for user to path /users/weblogic

Hi,

Installed Drive :\Oracle\Middleware\instances\instance2\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog\YourCatalog\root\users

1. delete the <user> folder and <user>.atr
2. Restart the BI services
3. Login using the user.

fyi:
https://forums.oracle.com/forums/thread.jspa?threadID=2193737
https://forums.oracle.com/forums/thread.jspa?messageID=10019813
or try to refereshing GUID users account

http://bimetrics.wordpress.com/tag/obiee11g-11g-refresh-guid-migrating-rpd-catalog-between-environments-weblogic-error-codes-o9xnzmxb-access-denied-for-user-to-path-usersweblogic_portaldashboard-layout/


Thanks

Deva

Monday, November 21, 2011

Application role migration from DEV to UAT

Application role migration from DEV to UAT


1) To migrate security there is a WLST method called as MigrateSecurityStore which can move the roles from one system-jazn-data.xml to the other. To do that, we need to first copy the system-jazn-data.xml from Dev instance to the Prod instance (to a temporary directory). In the same way copy the system-jazn-data.xml from the Prod instance to the temporary directory.


2) Take a backup of all these files before doing the migration. Now, copy the jps-config.xml from any one of the instances (Dev or Prod) to the temporary Directory. And rename the file to jps-config-policy.xml.


3) Open up the jps-config-policy.xml. You will notice that this file contains all the details about all the stores in the environment. Now, for policy migration, we need to basically use this file to point to the locations of the Dev and Prod (source & target) system-jazn-data.xml files. Also, since we are doing only policy migration, we do not need the other store related tags within this file. Remember that we are modifying only a copy of the jps-config.xml. This copy file will be used only for migration.


You will need the following lines for migration:


<!-- JPS XML Policy Store Service Instance -->


<serviceInstance name="policystore.xml" provider="policystore.xml.provider" location="c:/temp/dev/system-jazn-data.xml">
<description>File Based Policy Store Service Instance</description>
</serviceInstance>


<serviceInstance name="policystore.xml" provider="policystore.xml.provider" location="c:/temp/prod/system-jazn-data.xml">
<description>File Based Policy Store Service Instance</description>
</serviceInstance>




4) Now that we have our jps-config file ready, navigate to the temporary directory from command prompt and initialize the WLST using the following command.


In WLST, application role migration can be done even in offline mode. So, effectively there is no need for us to connect to the weblogic admin server. In offline interactive mode, fire the following command


migrateSecurityStore(type=”appPolicies”, srcApp=”obi”, configFile=”C:/SecurityMigration/jps-config-policy.xml”, src=”sourceFileStore”, dst=”targetFileStore”, overWrite=”false”)

Thanks

Deva

Date format into Timestamp with AM/PM

Hi,

In Answres,

MM/DD/YYYY format but I want to dispaly it in MM/DD/YYYY HH:MM AM

You can try below in custom date format:

MM/dd/yyyy hh:mm tt

for more :
https://forums.oracle.com/forums/thread.jspa?threadID=2312541&tstart=0

Thanks

Deva

How to Apply Patches Using Opatch

Hi,

Step:1  Ensure you have the latest Opatch:

Make sure you have the latest supported version of OPatch downloaded.

You can download the latest Opatch and install it in the directory on your machine under:
D:\Oracle\Middleware\Oracle_BI1\OPatch

Step 2 : Download the patch to PATCH_TOP

Download the patch to PATCH_TOP directory, as instructed in the README

Step: 3
 cd to patch (for example 10042883) directory:
D:\PATCH_TOP>cd 10042883

Step 4 : Make sure Opatch is in the PATH
Add D:\OPatch to the PATH:
D:\PATCH_TOP\10042883>set PATH=%PATH%;D:\Oracle\Middleware\Oracle_BI1\OPatch

Verify that the PATH is set:
D:\PATCH_TOP\10042883> PATH <Enter>

Step 5 : Set ORACLE_HOME

Set ORACLE_HOME to your BI Home.
For example, in this case,  BI Home is set to D:\Oracle\Middleware\Oracle_BI1
D:\PATCH_TOP\10042883>set ORACLE_HOME=D:\BI11\Oracle_BI1<Enter>

Verify that the Oracle Home is set correctly:
D:\PATCH_TOP\10042883>echo %ORACLE_HOME% <Enter>


Note: You are setting ORACLE_HOME for this command-prompt session only. This is set to only facilitate patch application/rollback/inventory, etc. Do not make this a permanent environment variable as it will affect other Oracle Products running on your system which may need a permanent ORACLE_HOME environment variable.

Step 6 : Verify Opatch version

Verify you have the correct OPatch version:
D:\PATCH_TOP\10042883>opatch version <Enter>

Invoking OPatch 11.1.0.8.3
OPatch Version: 11.1.0.8.3
OPatch succeeded.

Step 7 : Apply the OBIEE patch (for example 10042883)
7.1: Shutdown all BI services:
Apply patch 10042883 -- Before applying any OBIEE patch, make sure all
BI Services are shut down.
Start->Programs->Oracle BI->Stop BI Services
7.2: Apply the patch:
D:\PATCH_TOP\10042883>opatch apply <Enter>

If you are running a 64-bit OS: Windows64bit / Linux 64bit / AIX 64bit, make sure
you use the following command:
opatch apply -jre %ORACLE_HOME%/jdk/jre <Enter>
OPatch succeeded.
Step 8 : lsinventory command
Verify that the patch was applied correctly by checking the inventory:
D:\PATCH_TOP\10042883>opatch lsinventory <Enter>
If you are running a 64-bit OS: Windows64bit / Linux 64bit / AIX 64bit, make sure
you use the following command:
opatch lsinventory -jre %ORACLE_HOME%/jdk/jre <Enter>

For more info refer below Oracle Doc:

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=BULLETIN&id=1220799.1

Thanks

Deva

Friday, November 11, 2011

obiee-11g-auto-start-all-with-windows

OBIEE 11.1.1 - How to Install Windows Services For OBIEE 11g AdminServer And BI Managed Server?

Following Fusion Middleware 11g components are configured by default as MS Windows Services:
  • Weblogic Node Manager
    (configured as a MS Windows Service if Custom Install route is chosen when installing Weblogic)
  • FMW 11g Process Manager (OPMN)
    (installed as a MS Windows Service if a System Component Instance is configured)
This article shows how to install the WebLogic AdminServer and BI Managed Server as MS Windows Service for the OBIEE 11g installation.

Friday, November 4, 2011

OBIEE11g Changing unchangeable cells -Pivot table

Hi,

   [br/]<script type="text/javascript">
var tds = document.getElementsByTagName('td');
var lCSS = new Array();
for(var td=0;td<tds.length;td++){
if( tds[td].className != 'PTCC'){
continue;
}
tds[td].style.backgroundColor="#F1E87D";
// Info on property naming: http://codepunk.hardwar.org.uk/css2js.htm
lCSS.push(tds[td].innerHTML);
}
for(var len =0; len < lCSS.length; len++){
//document.write("Bingo " + "<BR>");
//debug only
}
</script>

fyi..see the screen short:





https://forums.oracle.com/forums/thread.jspa?threadID=2306222&tstart=0

For more: refer Joe post..in obiee10g version
http://obiee101.blogspot.com/2010/02/obiee-changing-unchangeable-cells.html
 

Thursday, November 3, 2011

List of Bug Fixes Included In OBIEE 11.1.1.5.0

Hi,

Admin Tool
•7538753 SAP BW IMPORT DOES NOT RETRIEVE UNIQUE NAME FOR MEASURE COLUMNS
•10012297 ADMIN TOOL CRASH ON VERY LARGE EXPRESSIONS – OVERFLOW AT APPROX 3200 CHARACTERS
•10032385 TABLE MISSING IN SQL “FROM” CLAUSE – FWD PORT 8308443 TO 11G
•10045212 EQUALITY EXPRESSION IN LTS “WHERE” CAUSES INTERNAL SQORRQLIST ERROR ON QUERY

Saturday, October 29, 2011

Refreshing GUID in obiee11g

Hi,

To regenerate user GUIDs:

This task requires that you manually edit the configuration files to instruct Oracle BI Server and Oracle BI Presentation Server to regenerate the GUIDs on restart. Once completed, you edit these files to remove the modification. For information about where to locate Oracle Business Intelligence configuration files, see "Where Configuration Files are Located" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.
1.Update the FMW_UPDATE_ROLE_AND_USER_REF_GUIDS parameter in NQSConfig.INI:
a.Open NQSConfig.INI for editing at:
ORACLE_INSTANCE/config/OracleBIServerComponent/coreapplication_obisn
b.Locate the FMW_UPDATE_ROLE_AND_USER_REF_GUIDS parameter and set it to YES, as follows:
FMW_UPDATE_ROLE_AND_USER_REF_GUIDS = YES;
c.Save and close the file.
2.Update the Catalog element in instanceconfig.xml:
a.Open instanceconfig.xml for editing at:
ORACLE_INSTANCE/config/OracleBIPresentationServicesComponent/
coreapplication_obipsn
b.Locate the Catalog element and update it as follows:
<Catalog>
<UpgradeAndExit>false</UpgradeAndExit>
<UpdateAccountGUIDs>UpdateAndExit</UpdateAccountGUIDs>
</Catalog>
c.Save and close the file.
3.Restart the Oracle Business Intelligence system components using opmnctl:
cd ORACLE_HOME/admin/instancen/bin
./opmnctl stopall
./opmnctl startall
4.Set the FMW_UPDATE_ROLE_AND_USER_REF_GUIDS parameter in NQSConfig.INI back to NO.
Important: You must perform this step to ensure that your system is secure.
5.Update the Catalog element in instanceconfig.xml to remove the UpdateAccount GUIDs entry.
6.Restart the Oracle Business Intelligence system components again using opmnctl:
cd ORACLE_HOME/admin/instancen/bin
./opmnctl stopall
./opmnctl startall
   

For More:
 Refreshing in obiee11g
good one
http://obiee11gqna.blogspot.com/2011/10/obiee11g-updating-guids.html

http://varanasisaichand.blogspot.com/2011/08/refresh-user-guids-obiee-11g.html
http://gerardnico.com/wiki/dat/obiee/migration


Reading the Oracle Documentation I see that the refresh of GUIDs need to be done for Presentation services as well. http://download.oracle.com/docs/cd/E14571_01/bi.1111/e10543/privileges.htm#CHDHGAAH

Thanks

Deva

Monday, October 24, 2011

ODBC driver returned an error(SQLExecDirectW).NQSError:46118 out of disk space



Hi,
   
   Error:
   ODBC driver returned an error(SQLExecDirectW).NQSError:46118 out of disk space






Solution : Need to keep minimum 10MB for obiee-disk memory space...after cleaning the drive that issue was resolved.

Thanks
Deva

Friday, October 21, 2011

OBIEE11g Features


Hi,

OBIEE11g Features

1) Multiple Subject Areas

Did you know that Answers reports (Analyses) can now be sourced from multiple Subject Areas?    This is significant as it means you no longer have to cram lots of content into a single Subject Area!

2) Conditional Data Formatting
When you apply Conditional Formatting via "Column Properties" in Answers, there is an additional tab to allow you to conditonally change the "Data Format" as well as the "Style".

Thursday, October 13, 2011

Windows 7 Home Premium edition is supported for installation of OBIEE Server 11.1.1.5

HI,


It is supporting and working fine for more please refer 

http://obieeelegant.blogspot.sg/2012/07/obiee111160-software-only-installation.html





Windows 7 Home Premium edition is not supported for installation of OBIEE Server 11.1.1.5.


Issue: 1
while installing obiee11.1.1.5.0 (software only install ) on win7 64 bit SP1 version i am getting Executing:opmnctl start coreapplication_obisch1 error at the last step


Issue 2:
OBIEE 11g Install On Windows 7 Hangs At Creating Domain Step (Doc ID 1209259.1)
Refer:http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/bi-11gr1certmatrix-166168.xls


Solution:
Please kindly refer to the Certification matrix available from :
http://www.oracle.com/technetwork/middleware/bi/oracle-obia-111151-cert-matrix-525376.xls


http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/bi-11gr1certmatrix-166168.xls


Windows 7 Home Premium edition is not supported for installation of OBIEE Server 11.1.1.5.


Install OBIEE 11.1.1.5 only on certified platform
ie. Windows 2003 with SP2/R2+
or Windows Server 2008 with SP1+
Window 7 is only certified as an OBIEE client.


For more Refe:SR 3-4684846891: Executing:opmnctl start coreapplication_obisch1 while installing obiee11g (11.1.1.5.0) win64bit

Thanks

Deva

Friday, September 23, 2011

OBIEE 11g Installation and Configuration Steps


Oracle Business Intelligence11g System Requirement

This section contains important information for the system on which you are installing Oracle Business Intelligence11g and the database (MS SQL Server) containing the schemas.requirements: requirements:


Hardware

Disk Space: 20GB or more
Physical Memory: 4GB or more
Available Memory: 1GB or more
Temp Space: 950MB or more
Swap Space: 3GB or more
CPU & Processor: dual-core Intel 32/64 Bit, 2.10GHz or greater
Monitor Display : requires 1024 x 768 minimum resolution
Software Operating System: Windows Xp/Vista/Server 2003/ above with SP2 or higher version
Microsoft Internet Explorer 8.0 above
Microsoft .Net Framework 4.5 SP2 or above version
Static IP Address Required (i.e.: DHCP to access network install Look Back Adopter)
JDK 1.6 or above version
MS SQL Server 2005/2008 Enterprise Edition 32/64 bit
OBIEE 11g (11.1.1.3.0) Version
RCU 11.1.1.3.0 (Repository Creation Utility ) database schema creation


Note By default, TCP/IP properties are configured to use DHCP.
 
 
OBIEE 11g Download Link:
http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/bus-intelligence-11g-165436.html

http://www.oracle.com/technetwork/middleware/bi/oracle-obia-111151-cert-matrix-525376.xls


Note: Make sure database repository must be created before installation of the components with the Repository Creation Utility (RCU) tool

Database Requirement
Below are the recommended disk space requirements for the database containing the Oracle Business Intelligence Scheduler database tables
500MB Microsoft SQL Server databases for standalone and Business Intelligence applications and deployments
OBIEE 11g and Weblogic Disk Space Requirement
The following list identifies memory requirements for various components.
Use the list as a guideline for determining the memory requirements specific to your
deployment:
WebLogic Administration Server: 750 MB
WebLogic Managed Server running Oracle Directory Integration Platform and
Oracle Directory Services Manager: 720 MB
WebLogic Managed Server running Oracle Identity Federation: 650 MB
Oracle Internet Directory: 75 MB
Oracle Virtual Directory: 105 MB
Oracle Enterprise Manager (EM) Agent: 25 MB
Oracle Process Manager and Notification Server (OPMN): 5 MB
Oracle Identity Management requires 2,200 MB of disk space if you are only installing the software (you select the Install Software - Do Not Configure installation type). If you configure the software, an additional 150 MB of space are required if all components are selected for configuration.
RCU Supported Platforms:
RCU is available only on 32-bit x86 Linux and 32-bit Microsoft Windows operating system platforms. You can run RCU from these machines to connect to any certified versions of Oracle, IBM DB2, or Microsoft SQL Server database in order to create the schemas required by Fusion Middleware components. The database server can be running on any platform that is supported by its respective database.
RCU Schema Creation through MSSQL Server Script :
Note: don’t create short cut database/table creation because you can’t able to succeeded on Metadata configuration in your RCU DB Schema

Wednesday, September 21, 2011

Unable To Create Session Variable Using Obiee 11.1.1.5 Admin Tool issue

Hi,
   
An interesting thing happen, when I was trying to choose a connection pool for creating a Session variable Init Block , connection pool Select button was disabled. This functionality worked in 11.1.1.3. Just to avoid any doubt I restarted admin tool, followed by machine restart, but end result didn’t change. After looking around, I remembered that in one of the metalink they talked about “Allow First Connection for init block”. I tried that option and it work . Below are the details ::
Issue :
Select button is disabled when trying to choose a connection pool for creating a Session variable Init Block

Solution:
Check “Allow First Connection for init block” to true
For setting “Allow First Connection for init block”  follow below steps :
  1. In the Administration Tool, select Tools, then select Options to display the Options dialog.
  2. On the General tab, select ”Allow First Connection for init block. (this option is not a best practice and might cause performance issues.)
THanks

DEva

Unable To Export All Rows from a Request to csv, pdf Or Excel In Obiee 11g

Hi,


To implement the solution, please do:

1. Change instanceconfig.xml file in <biee11g_install>\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1 as following
<Views>
<Pivot>
<!--This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager-->
<DefaultRowsDisplayedInDelivery>75</DefaultRowsDisplayedInDelivery>
<!--This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager-->
<DefaultRowsDisplayedInDownload>2500</DefaultRowsDisplayedInDownload>
<!--This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager-->
<DisableAutoPreview>false</DisableAutoPreview>
<MaxVisibleColumns>5000</MaxVisibleColumns>
<MaxVisiblePages>2500</MaxVisiblePages>
<MaxVisibleRows>500000</MaxVisibleRows>
<MaxVisibleSections>5000</MaxVisibleSections>

</Pivot>
<Table>
<!--This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager-->
<DefaultRowsDisplayedInDelivery>75</DefaultRowsDisplayedInDelivery>
<!--This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager-->
<DefaultRowsDisplayedInDownload>2500</DefaultRowsDisplayedInDownload>
<MaxVisiblePages>2500</MaxVisiblePages>
<MaxVisibleRows>500000</MaxVisibleRows>
<MaxVisibleSections>5000</MaxVisibleSections>

</Table>

</Table>
</Views>

3. Note the new elements added are the following to both <Pivot> and <Table>, the Pivot part controls Pivot view, and Table part controls Table View, my testing was done with Table View.
<MaxVisiblePages>2500</MaxVisiblePages>
<MaxVisibleRows>500000</MaxVisibleRows>
<MaxVisibleSections>5000</MaxVisibleSections>

4. Restart Presentation Server from EM, then run your request, do pdf export to verify.

Note:

When a table or pivot table includes a large number of rows, a set of buttons can be displayed under the view. You can use the following buttons to page through the rows in the view:

•First 25 Rows — Displays the first 25 rows of data for the view. The number that is used for the First, Previous, and Next buttons is specified by the Rows per Page field in the "Table Properties dialog: Style tab".

•Previous 25 Rows — Displays the previous 25 rows of data for the view.

•Next 25 Rows — Displays the next 25 rows of data for the view.

•Display maximum (500) rows per page — Allows you to display as many as the maximum number of rows per page at one time temporarily. The default is 500. The administrator can specify a different maximum value that is displayed on the tooltip for the button using the MaxVisibleRows element in the configuration file.


THanks

DEva

Monday, September 19, 2011

Displaying External Documents on an OBIEE Dashboard

Hi,



OBIEE11g - setting external Document file(word,excel,pdf,txt..etc) 100% suiteblae
https://forums.oracle.com/forums/thread.jspa?threadID=2303372&tstart=0
for intro and overview...
http://total-bi.com/2011/02/external-files-obiee-dashboard/

link or imges dashboard object and Try like this

1) 1st upload it u r pdf files in catalog like below one
refer my screen short

http://imageshack.us/photo/my-images/28/step1updloadexteranlfil.jpg/

2) use dashboard objects --> link or image -->

refer my screen short
http://imageshack.us/photo/my-images/593/step2updloadexteranlfil.jpg/

URL Plath like this

http://1.1.1.1:9704/analytics/saw.dll?downloadFile&path=%2FShared%2FDOC%2Fobiee.pdf

100% it should work.

OBIEE10g verion below one ok, but lot of impact this version
   
     Placing the image file in /l_en folder dint work for me too however you can place them inside the following paths and restart the services.





For more information:
https://forums.oracle.com/forums/thread.jspa?messageID=9878467&#9878467


FYI,
     if you are using obiee10g version please look at the below path

Drive:\OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics\res
Drive:\OracleBI\web\res

Restart all services including oc4j

Thanks

Deva

Tuesday, September 6, 2011

Clear/Remove OBIEE/SQL Server Services from Services.msc

Hi,
    
  deleting obiee11g services in windows services.msc by using below comment

c:/>sc delete "service name" that's all.


Second method,

You have to also delete the services registry entry using regedit
HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services

FYI..Ref:

http://www.howtogeek.com/howto/windows-vista/how-to-delete-a-windows-service-in-vista-or-xp/

Thanks

Deva

Monday, September 5, 2011

logo and title in one line

Hi,

          In an Answres report, you can set logo and tittle in the same line please check the below line

fmap: image path

e.x:

fmap:{imagename.jpg}


Thanks

Deva

Monday, August 29, 2011

OBIEE11g - While installing RCU Error: failed to connect DB - invalid server name/port no

Hi,

SQL Configuration Manager make sure the TCP/IP enabled.

http://blog.ropardo.ro/2011/03/04/the-repository-creation-utility-rcu/



Thanks and Regards,
Deva

Hide the Go button in OBIEE


Hi,

If you need to hide the go button for a dashboard prompt in OBIEE insert a static text object on to the dashboard, select the “Contains HTML Markup” option and enter the following text into it:
<style type=”text/css”>
.XUIPromptEntry{display:none}
</style>


Regards,

Deva

Tuesday, August 23, 2011

Enable “Change Password” for OBIEE 10g &11g Dashboard - my Account Page for end users purpose

Enable “Change Password” for OBIEE end users

Here I will details the steps on How to enable OBIEE 10g users changing their password without having Administrator privilege.

I have tested this on OBIEE 10.1.3.4.1 running on a Windows server and it works fine.

All it requires is a simple change to controlmessages.xml file which can be located under $:\web\msgdb\messages directory.
Changes show below -
Replace the line
<WebMessage name=”kmsgChangePasswordLink”><!–<HTML><a insert=”1″><sawm:messageRef name=”kmsgUIChangePassword”/></a></HTML>–></WebMessage>
with
<WebMessage name=”kmsgChangePasswordLink”><HTML><a insert=”1″><sawm:messageRef name=”kmsgUIChangePassword”/></a></HTML></WebMessage>
This requires restarting the BI server, After restarting the BI server successfully, users should be able to see the “Change Password” link on their OBIEE My Account page.



But it wont work in obiee11g kindly refer the below reason:

The ability to change password is not possible in OBIEE11g. With 11g, OBIEE essentially uses the 10g notion of external authentication. By default, this is the WLS (Weblogic)LDAP identity store, but may be any defined Authenticator either within WLS, or in the OBIS metadata (i.e. Custom Authenticator or LDAP). As such, OBIEE proper no longer has any control over user passwords - this is why the steps referenced in Doc ID 1102353.1do not apply to 11g OBIEE

See Change Passsword Link In Answers/Dashboard Obiee 11g (Doc ID 1302091.1)

OBIEE 11g : (Workaround Method from Venkat Blog)

http://www.rittmanmead.com/2011/10/changing-your-password-in-obiee-11g/

Thanks

Deva

Monday, August 22, 2011

Acessing Shored folder/Share Point(Web Link) from OBIEE11g

Hi,
You can able to acess Share point/Shared network folder from your OBIEE11g Dashboard page.

You just add dashboard objects--> images and folder --> then put your shared network folder path --> then save it and run it.

also you can give it your share your Share point web documentory web link.

Thanks and Regards,

Deva

Thursday, August 18, 2011

OBIEE11g Setting up Usage Tracking

Setting Up Usage Tracking in Oracle BI EE

This Document will used to Set up usage tracking and create usage reports to monitor queries.

1) It is used to monitor system and ad hoc query performance and to provide input on usage details

2) Usage tracking is particularly useful in determining user queries that are creating performance, based on query frequency and response time. When usage tracking is enabled, the Oracle BI Server collects usage tracking data for each query and writes statistics to a usage tracking log file or inserts them directly to a database table.

Wednesday, August 17, 2011

How to find current quarter without using time series function OBIEE

How to find current quarter without using time series function OBIEE ?

It can be resolved in multiple ways like - either using variable or without variable
Last qtr of every year is fixed that is 4th for that calendar year so either 4 or QTR4 or something like this which can be hardcoded in your expr here:
Method 1:
Using with variable...I have a curr_year dynamic repository variable here...and a date column in my report:
CASE WHEN YEAR(datecolumn) = VALUEOF("curr_year") THEN QUARTER_OF_YEAR(datecolumn) ELSE 4 END
method 2:
Using without variable....and a date column in my report:
CASE WHEN YEAR(datecolumn) = YEAR(CURRENT_DATE) THEN QUARTER_OF_YEAR(datecolumn) ELSE 4 END

Hope this solves your problem..


Regards,

Deva

 


 

Tuesday, August 2, 2011

OPatch failed with error code = 255 and 14

Hi,

       If any one try to apply opath in your obiee server mchine please make which option choosed while installing obiee11g in your mechine.please make sure if have installed OBIEE11g ,windows server 2008 server 64 bit mechine , enterprice or software only installation option. don't install simple installation ...Opatch won't work.

Ex:
The Opatch error like below one,
************************
java.lang.UnsatisfiedLinkError: Can't load library: E:/OBIEE11G/Oracle_BI1/oui/lib/win32/oraInstaller.dll
at java.lang.ClassLoader.loadLibrary(ClassLoader.java:1657)
at java.lang.Runtime.load0(Runtime.java:770)
at java.lang.System.load(System.java:1004)
at oracle.sysman.oii.oiip.osd.win32.OiipwWin32NativeCalls.loadNativeLib(OiipwWin32NativeCalls.java:1509)
at oracle.sysman.oii.oiip.osd.win32.OiipwWin32NativeCalls.<clinit>(OiipwWin32NativeCalls.java:50)
at oracle.sysman.oii.oiip.oiipg.OiipgEnvironment.getEnv(OiipgEnvironment.java:211)
at oracle.sysman.oii.oiix.OiixEnvironmentOps.getEnv(OiixEnvironmentOps.java:53)
at oracle.opatch.OUIReplacer.getEnv(OUIReplacer.java:374)
at oracle.opatch.CmdLineParser.parse(CmdLineParser.java:995)
at oracle.opatch.OPatchSession.main(OPatchSession.java:1240)
at oracle.opatch.OPatch.main(OPatch.java:630)
Solution Satement:
===========
The error appears while running the OPATCH utility because, you have performed SIMPLE install on your 64 bit Windows server 2008 OS.
When a simple install is used a 32 bit JDK is used during install even though the win64 installer is used. This is because the WLS is installed by default with 32 bit JDK.
The only workaround to get rid of this issue is to download a JDK for 64 bit and use it to run opatch. Once done,
run opatch with -jdk option:
>opatch lsinventory -jdk <64 bit jdk location>
and OPATCH should work fine.
Please apply the patch using the OPATCH utility as mentioned in the patch readme.

For your Reference :
===============


- When searched the knowledge base found that the above error shows up when,
Customer uses SIMPLE install on their 64 bit Windows server 2008 OS.
When a simple install is used a 32 bit JDK is used during install even though the win64 installer is used.
This is because the WLS is installed by default with 32 bit JDK.
Please refer to,
Oracle note: Doc ID 1265807.1 (Error: "java.lang.UnsatisfiedLinkError: Can't Load Library: E:/OBIEE11G/Oracle_BI1/oui/lib/win32/oraInstaller.dll" Installing OBIEE 11g on 64-Bit Windows)
- When checked with the customer, got to know that they have also performed a SIMPLE install on their 64 bit Windows server 2008 platform.
- The only workaround here is to download a JDK for 64 bit and use it to run opatch. Once done,
run opatch with -jdk option:
>opatch lsinventory -jdk <64 bit jdk location>
and OPATCH should work fine.
Please apply the patch using the OPATCH utility as mentioned in the patch readme.
Thanks and Regards,

Deva

Monday, June 27, 2011

Financial reporting rpd using obiee

Hi,
      Building RPD and Financial Reporting  by using multidimensional  essbase datasource in obiee

http://www.slideshare.net/alooa2/financial-reporting-rpd-using-obiee-7968605


Thanks and Regards,

Deva


Thursday, June 23, 2011

Migrating Application Roles from Dev to UAT server and Production server.

Hi,

Reference:

To move Application Roles, please kindly review the following information:
Oracle Fusion Middleware Application Security Guide 11g Release 1 (11.1.1)
7.3.2 Migrating Policies with the Command migrateSecurityStore
http://download.oracle.com/docs/cd/E14571_01/core.1111/e10043/cfgauthr.htm#JISEC2929


Thanks and Regards,

Deva

ODBC SQL Server Driver]Optional feature not implemented in RPD - Phesical layer feature

I came across an interesting error recently. I had a very simple data model with 1 fact table and 1 dimension, both coming from a SQL Server 2008 database. When querying the model through Answers I would get ODBC driver errors like the ones below.

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000]
[nQSError: 10058] A general error has occurred.
[nQSError: 43113] Message returned from OBIS.
[nQSError: 43119] Query Failed:
[nQSError: 16001] ODBC error state: 37000 code: 8180 message: [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared..
[nQSError: 16001] ODBC error state: 37000 code: 145 message: [Microsoft][ODBC SQL Server Driver][SQL Server]ORDER BY items must appear in the select list if SELECT DISTINCT is specified..
[nQSError: 16002] Cannot obtain number of columns for the query result. (HY000)

Below thing in addred in Physical layer feature then also it's not working:

Untick the checkbox for EXPRESSIONS_IN_ORDERBY_SUPPORTED in the features tab of the database properties in the physical layer.


However I did notice that from your error that you are using [Microsoft][ODBC SQL Server Driver] and not "[Microsoft][SQL Server Native Client 10.0]" as given by the Doc 1308545.1

Thanks and Regards,
Deva

nQSError: 60008


Hi,
    I came across an interesting error recently, as a admin (weblogic user)  able to see the o/p without any error dashboard page, if any one user/group loging that time they are getting attached screen short error message.

FYI,

SR 3-3888346391: Odbc driver returned an error (SQLExecDirectW) on obiee11g Dashboard page


Solution Statement:

In your OBIEE11g  --> RPD file---> Manage--> Identity -->Users/Application role --> increase the each user/group wise that maximum number of rows the user or group is allowed to retrieve from a database. then it will work.

Reference:

At the rpd level, you can Manage Query Execution Privileges to limit queries by number of rows received by a user or group with
the max rows parameter. It determines the number of rows that can be returned from the database. If the number of rows exceeds this limit, an ODBC error occurs.
From the Administration Tool menu bar, choose Manage > Security.
•In the Security Manager dialog box, in the tree pane, select Users or Groups.
•In the right pane, right-click the name that you want to change and select Properties.
•In the User or Group dialog box, click the Permissions tab.
•In the User/Group Permissions dialog box, click the Query Limits tab and expand the dialog box to view all columns.
•To specify or change the maximum number of rows each query can retrieve from a database, in the Query Limits tab, perform the following steps:
•In the Max Rows column, type the maximum number of rows.
•In the Status Max Rows field, select a status.
•Click OK twice to return to the Security Manager dialog box.
If a query exceeds the limit set by the Administrator, the end user will get the message:
[nQSError: 60008] The query for user 'user1'
exceeded the maximum query governing rows n from the database x
Status  Description 
Disable  When selected, disables any limits set in the Max Rows or Max Time fields 
Enable  This limits the number of rows or time to the value specified. If the number of rows exceeds the Max Rows value, the query is terminated. 
Ignore  Limits will be inherited from the parent group. If there is no row limit to inherit, no limit is enforced 

Thanks and Regards,

Deva

Wednesday, June 22, 2011

By Pass Server Cache in OBIEE Answres

Hi,
   It's used to Overcome below kind of issue:
 .> Data Not Getting Refreshed in Answers when Changed in Base Database. Cache problem.


Let say User wants dashboard data updated on real-time after back-end ETL populates, then rather suggesting user to click on Refresh button each time is not the best option as fickle minded user1 could see stale data and can raise a hue and cry unnecessarily on old data .That could be easily avoided using Bypass Web Server Cache feature in Advanced tab of the corresponding reports .

Monday, June 20, 2011

OBIEE11g Start and Stop services automattically

Hi,

It seems you would like to Start/Stop BI services without entering the login/password details.
Two ways to achieve this.
1. Modifying “StartStopServices.cmd” located in below path:

Monday, June 13, 2011

Reset Prompt and Hiding the Go Button in OBIEE

Hiding the Go Button in OBIEE

   If you need to hide the go button for dashboard prompt in OBIEE insert a static text object on
to the dashboard, select the "Contain HTML markup" option enter the below lines,

<style type="text/css">
.XUIPromptEntry{display:none}
<style>

==========

Prompt Reset Button :

The given below will use to create reset button,
<div><a href ="#" onclick ="return  PersonalizationEditor.removeDefaultSelection(false)" Reset Prompt>
</div>



Regards,

Deva

Saturday, June 11, 2011

Basics of Data Model

Relational Vs Dimensional Model :



1) Dimensional Modeling approach provides a way to improve query performance for summary reports thereby enabling faster business decision making process.
2) Dimensional Modeling is easier to understand and navigate as it represents business subject areas in an intuitive style.
3) Dimensional Data Modeling is used for storing and reporting summarized data. For example, sales data could be collected on a daily basis and then be aggregated to the week level, the week data could be aggregated to the month level, and so on.

Friday, June 10, 2011

IDENTITY row's in SQL Coumns

Hi,
   Auto Increment row's in a column
while creating SQL use  sql for that column.

[Col1] [int] IDENTITY(1,1) Not Null

Regards,
Deva





Customizing Oracle Business Intelligence Enterprise Edition 11g

Deploying Custom Skin
=====================
After exposing
 Copy the default skin (
analyticsRes to WLS successfully, this directory can be used for custom skins. Follow the steps below for deploying a custom skin: ORACLE_HOME/bifoundation/web/app/res/sk_blafp) to analyticsRes In Linux, for example: cp ORACLE_HOME/bifoundation/web/app/res/sk_blafp/analyticsRes/sk_Eden Change the skin name in instaceconfig.xml NOTE: For user specific skin, use NQ_SESSION.SKIN session variable
 Modify the images and css files in
NOTE: If the size of the skin is a problem, delete the unmodified files. While deleting, make sure to not delete any file which is referenced in a modified css file as it picks up the files relative to the local directory. Default files (blafp skin) will be picked for the deleted files.
 Restart the Oracle BI Presentation Services and clear the browser cache to see the changes. Here are a few examples.
 Change the logo: Replace the
 Change the banner color: Modify
sk_Eden directory for achieving the customization. analyticsRes/sk_Eden/b_mozilla_4/oracle_logo.png and analyticsRes/sk_Eden/login/oracle_logo.png with the new png file. Try to keep the size of the new png file close to the original file (119X25 px). HeaderContainer class in analyticsRes/sk_Eden/b_mozilla_4/common.css Change the Tabs color (for dashboard page) in a dashboard: Modify the tab images (*.gif) in analyticsRes/sk_Eden/b_mozilla_4/uicomponents/obips.TabBar Customizing Oracle Business Intelligence Enterprise Edition 11g Page 9 NOTE: If the
 Change the body color of the home page: Modify the background color of the body in
uicomponents directory does not exist in analyticsRes/sk_Eden/b_mozilla_4 then create the uicomponents directory and copy the obips.TabBar from ORACLE_HOME/bifoundation/web/app/res/b_mozilla/uicomponent/obips.TabBar. Delete *.js and *.xml files. analyticsRes/sk_Eden/b_mozilla_4/home.css Change the alert color: Modify the HeaderAlerts class in analyticsRes/sk_Eden/b_mozilla_4/common.css Change the header bar separator line: Modify the HeaderBarSeparator class in analyticsRes/sk_Eden/b_mozilla_4/common.css Change the background color of the dashboard name: Replace the analyticsRes/sk_Eden/b_mozilla_4/l1_seltab_bg.gif with the new image. Make sure the size of the new image is the same as the old one. analyticsRes/sk_Eden/common/header_spacer_bg.gif with the new image. Make sure the size of the new image is the same as the old one. analyticsRes/sk_Eden/b_mozilla_4/l2_spacer_bg.gif with the new image. Make sure the size of the new image is the same as the old one. DashUpperContainer class in portalcontent.css file of the chosen style. This is a bug and is being fixed. For now, change this class to reflect the Eden skin (sk_eden). analyticsRes/sk_Eden/common/page_lev_connected.gif with the new image. Make sure the size of the new image is the same as the old one. menubar_gSeparator.gif in analyticsRes/sk_Eden/b_mozilla_4/uicomponents/obips.UberBar NOTE: If the
 Change the color, font and left margin of the brand name text (next to the logo): Modify the
uicomponents directory does not exist in analyticsRes/sk_Eden/b_mozilla_4, create the uicomponents directory and copy the obips.UberBar from ORACLE_HOME/bifoundation/web/app/res/b_mozilla/uicomponent/obips.UberBar. HeaderBrandName class in analyticsRes/sk_Eden/b_mozilla_4/common.css and appname class in analyticsRes/sk_Eden/login/login.css .HeaderBrandName{..; color:#007700;font-family:Arial; left:120px; ..} Customizing Oracle Business Intelligence Enterprise Edition 11g Page 10 .appname{..; color:#007700; font-family:Arial; ..} Deploying Custom Style The same analyticsRes directory can be used for a custom style using the steps below.
 Copy the default style (
ORACLE_HOME/bifoundation/web/app/res/s_blafp) to analyticsRes In Linux, for example: cp ORACLE_HOME/bifoundation/web/app/res/s_blafp analyticsRes/s_Eden
NOTE: For user specific style, use NQ_SESSION.STYLE session variable
 Modify the images and css files in
NOTE: If the size of the style is a problem, delete the unmodified files. While deleting, make sure to not delete any file which is referenced in a modified css file as it picks up the files relative to the local directory. Default files (blafp style) will be picked for the deleted files.
 Restart the Oracle BI Presentation Services and clear the browser cache to see the changes. As an example:
 Change the background color of the pivot table header: Replace the
Choose the Eden Style in Dashboard Properties analyticsRes/s_Eden directory to achieve your customization. analyticsRes/s_Eden/viewui/pivot/ptgrd_overly.png with the new image. Make sure the size of the new image is the same as the old one. Deploying Custom Message Custom messages should be stored in the same analyticsRes directory.
 Create
In Linux, for example:
customMessages folder in analyticsRes directory for the custom messages (.xml file). A good example would be writeback template that should be saved here. mkdir customMessages analyticsRes/ Customizing Oracle Business Intelligence Enterprise Edition 11g Page 11
 Change the brand name text –
For creating the language dependent custom message, create the appropriate directory structure within analyticsRes directory. As an example: o
In Linux, for example:
Create the message directory for the required language (English language here). mkdir analyticsRes/customMessages/l_en/messages o Create CompanyName.xml and add the text within kmsgHeaderBIBrandName tag: <WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1"><WebMessageTable protect="true" export="true" translate="false" system="ProductMessages" table="ProductNames">
<WebMessage name="kmsgHeaderBIBrandName"><TEXT> Report Center </TEXT></WebMessage>
</WebMessageTable></WebMessageTables>

Regards,
Deva
.HeaderBarSeparator{..; border-top: 1px solid #005500; ..}
 Change the header spacer line: Replace the
 Change the background color of the dashboard page edit and help icon: Replace the
NOTE: The path of this image is hard coded in
 Change the status icon next to Sign Out (top right corner): Replace the
 Change the menu bar separator line: Replace the .HeaderAlerts a:hover{..; color:#FF7777; ..} body{..; background-color: #006600; ..} .HeaderContainter {..; background-color: #006600; ..} <UI><DefaultSkin>Eden</DefaultSkin></UI>

How to Replace Null Values in an OBIEE Pivot Table?

How to Replace Null Values in an OBIEE Pivot Table

It's simple tip that allows you to supress null values in a pivot for cells which have no data.
Create a simple report. I’m using the Samplesales RPD here.
 
A pivot table view of this data would look something like this by default:
 
Now switch back to the criteria tab and edit the ‘column properties’ associated with your fact measure. Choose the ‘Data Format’ tab, tick to override the default format and choose ‘Custom’.
It seems that the syntax for this custom format is positive-value-mask (semi colon) negative-value-mask (semi colon) null-mask. So this means we have a few options.
E.g. if you want zeros (0) instead of null then enter:
#,##0;-#,##0;0
 
If you want a dash/strike (-) then you could enter:
#,##0;-#,##0;-
Or if you want to add a custom message then something like this would work:
#,##0;-#,##0;no data
Our pivot table now shows something other than null values.


Its a bug in OBIEE11g

I have raised SR and confirmed it defect obiee11.1.1.3.version but after obiee11.1.5 released also that major bug is not yet resolved that why i am just wondering
fyi:
Fix by 11.1.1.6.1 as mentioned in the Bug.
Bug 13054445 [http://bug.oraclecorp.com/pls/bug/webbug_edit.edit_info_top?rptno=13054445] - REPLACING NULL VALUES WITH "0" IN AN OBIEE 11G PIVOT TABLE IS NOT WORKING
for more refer :
https://forums.oracle.com/forums/thread.jspa?threadID=2319791


Regards,
Deva

Removing BrandName text (next to oracle logo) on obiee11g home page

Removing BrandName text (next to oracle logo)  on obiee11g home page
D:\OBIEE_HOME\Oracle_BI1\bifoundation\web\msgdb\l_en\messages\productmessages.xml
Rds,
Deva

OBIEE - LDAP Configuration and implementation Reference

Hi,

    Check the Below link for OBIEE - LDAP Configuration and implementation in Prod Server.
   
http://download.oracle.com/docs/cd/E14571_01/bi.1111/e10543/privileges.htm#BABCDCFE
You need to get the following information from the network team.
 
1. LDAP Server name
2. Base DN
3. Bind DN
4. Bind Password
5. Whether it is ADSI or not.
6. User Attribute
Regards,
Deva

Exporting in Excel to download more than 65000 rows in obiee11g/10

Hi,
   update the below code in you instance config.xml file, after that restart all bi services.
<Pivot>
<MaxCells>1920000</MaxCells>
<MaxVisibleColumns>30</MaxVisibleColumns>
<MaxVisiblePages>1000</MaxVisiblePages>
<MaxVisibleRows>64000</MaxVisibleRows>
<MaxVisibleSections>25</MaxVisibleSections>
<DefaultRowsDisplayed>64000</DefaultRowsDisplayed>
<DefaultRowsDisplayedInDelivery>75</DefaultRowsDisplayedInDelivery>
<DefaultRowsDisplayedInDownload>64000</DefaultRowsDisplayedInDownload>
<DisableAutoPreview>false</DisableAutoPreview>
</Pivot>
<Table>
<MaxCells>1920000</MaxCells>
<MaxVisiblePages>1000</MaxVisiblePages>
<MaxVisibleRows>64000</MaxVisibleRows>
<MaxVisibleSections>25</MaxVisibleSections>
<DefaultRowsDisplayed>64000</DefaultRowsDisplayed>
<DefaultRowsDisplayedInDelivery>75</DefaultRowsDisplayedInDelivery>
<DefaultRowsDisplayedInDownload>64000</DefaultRowsDisplayedInDownload>
</Table>
Thanks and Regards,
Deva

obiee-date-expressions-reference

obiee-date-expressions-reference
===========================
1)  First Day of the Previous Year
-------------------------------------------
TIMESTAMPADD( SQL_TSI_YEAR , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD removes a year from the returned date for the First Day of the Previous Year.
2) First Day of the Current Year

Thursday, June 9, 2011

Cutomizing "No Data View" in 11g

Alerting Users to No Data in the Results of Analyses:
=======================================================
When the results of an analysis return no data, the following default message is displayed to users:
No Results
The specified criteria didn't result in any data.
This is often caused by applying filters and/or selections that are too
restrictive or that contain incorrect values.
Please check your Analysis Filters and try again.
The filters currently being applied are shown below.

Rather than display the default message, you can create a customized message to alert users. This allows you to provide your own explanation as to why the analysis returned no data.

To create a custom message to alert users to no data in the results of an analysis:

1. Edit the analysis to which you want to add a custom message.
2. Click the "Analysis editor: Results tab".
3. Click the Analysis Properties toolbar button. The "Analysis Properties dialog" is displayed.
4. In the No Results Settings box, select Display Custom Message.
5. In the Header field, enter the text of the header for the custom message.
6. In the Message field, enter the explanatory text.
7. Click OK.

Reference:
Oracle® Fusion Middleware User's Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1 (11.1.1) user guide down loadable from http://download.oracle.com/docs/cd/E14571_01/bi.1111/e10544.pdf.
chapter 2 Creating Analyses. Page # 2-12.

Regards,
Deva