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

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 migrateSecurityStorehttp://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

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 .



Steps to implement By Pass Server Cache


1 Build Report with required fields,

2 Go to Advance SQL tab check the Check Box ' Bypass Oracle BI Presentation Services Cache'

3 Goto bottom of page look for 'Prefix' in text box type 'SET VARIABLE DISABLE_CACHE_HIT=1;'

4 Save the report

or

If you always want to bypass the Presentation server cache add this to the instanceconfig file of the development presentation server:

add below lines in instanceconfig.xml  file then restart all bi services.


<WebConfig>
<ServerInstance>
<ForceRefresh>TRUE</ForceRefresh>
</ServerInstance>
</WebConfig>




Thanks and Regards,


Deva

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:


.\\[MIDDLEWARE_HOME]\instances\instance1\bifoundation\OracleBIApplication\coreapplication

and add the below strings (weblogic user credentials) to pass the value of the variables: -DWLS_USER=weblogic -DWLS_PW=weblogic1

Note: It will not encrypt the user details.

2. Another approach is Enabling Auto Login by Using the Boot Identity File
Please refer http://st-curriculum.oracle.com/obe/fmw/wls/10g/r3/installconfig/enable_auto_login/boot_identity_file.htm for details.

Regards,

Deva

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

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.



Star Schema:

1) Star Schema is a relational database schema for representing multidimensional data.
2) It is the simplest form of data warehouse schema that contains one or more dimensions and fact tables.
3) Entity-relationship diagram between dimensions and fact tables resembles a star where one fact table is connected to multiple dimensions.
4) The center of the star schema consists of a large fact table and it points towards the dimension tables. 
5) The advantage of star schema are slicing down, performance increase and easy understanding of data.

Snowflake Schema:

1) A snowflake schema is a term that describes a star schema structure normalized through the use of outrigger tables. i.e. dimension table hierarchies are broken into simpler tables.
2) Hierarchies (category, branch, state, and month) are being broken out of the dimension tables (PRODUCT, ORGANIZATION, LOCATION, and TIME) respectively and shown separately. 
3) Snowflake schema approach increases the number of joins and poor performance in retrieval of data.
4) Normalization of dimension makes it difficult for user to understand
5) Dimension tables are normally smaller than fact tables - space may not be a major issue to warrant snowflaking.

 Important aspects of Star and Snowflake schema:

1) In a star schema every dimension will have a primary key.
2) In a star schema, a dimension table will not have any parent table.
3) In a snow flake schema, a dimension table will have one or more parent tables.
4) Hierarchies for the dimensions are stored in the dimensional table itself in star schema.
5) Whereas hierarchies are broken into separate tables in snow flake schema. These hierarchies helps to drill down the data from topmost hierarchies to the lowermost hierarchies.



Model Implementation:

1) In the Data Modeling Life cycle the last phase is the implementation of the Physical model. During the physical design process, you convert the data gathered during the logical design phase into a description of the physical database structure. Physical design decisions are mainly driven by query performance and database maintenance aspects.
2) During the physical design process, you translate the expected schemas into actual database structures. At this time, you have to map:
a) Entities to tables
b) Relationships to foreign key constraints
c) Attributes to columns
d) Primary unique identifiers to primary key constraints
e) Unique identifiers to unique key constraints

3) Once you have converted your logical design to a physical design you will need to create or use some or all of the following features while implementing you Data warehouse solution.
Partitions, Bitmap Index, Materialized Views, Analytic SQL function, Parallel Execution, etc

Regards,

Deva

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

http://www.rittmanmead.com/2010/10/obiee-11gr1-security-explained-an-11g-security-overview/

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



----------------------------------
TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)
This calculation returns the first day of the year by deducting one less than the total number of days in the year.
3) First Day of the Next 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 adds a year to the date returned which will give the first day of the next year.
4) First Day of the Previous Month
-----------------------------------
TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
 From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD then subtracts one month from the first day of the Current Month arriving to the First Day of the previous month.
5) First Day of the Current Month
--------------------------------------------
TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE) This expression gets the current day of the month and subtracts one less than the current day to arrive at the first day of the month.
6) First Day of the Next Month
-------------------------------------
TIMESTAMPADD(SQL_TSI_MONTH, 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD then adds one month from the first day of the Current Month arriving to the First Day of the next month.
7) First Day of Current Quarter
---------------------------------------
TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)
This was included to show the calculations discussed above can be used with other functions. This is the same expression as the one that returns the first day of the current month except this one uses the DAY_OF_QUARTER property to return the first day of the current quarter.
8) Last Day of the Previous Month
--------------------------------------------------
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD subtracts a month to arrive at the first day of the previous month.
9) Last Day of Current Month
---------------------------------------
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))
From right to left the first TIMESTAMPADD finds the first day of the current Month. The second TIMESTAMPADD adds one month to the date to arrive at the first day of the next month. The final TIMESTAMPADD subtracts one day from the returned date to arrive at the last day of the Current Month.
10) Last Day of the Next Month
-----------------------------------------
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 2, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))
From right to left the first TIMESTAMPADD finds the first day of the current Month. The second TIMESTAMPADD adds two months to the date to arrive at the first day of month after next. The final TIMESTAMPADD subtracts one day from the returned date to arrive at the last day of the Next Month.
11) Last Day of Previous Year
------------------------------------------
TIMESTAMPADD( SQL_TSI_DAY , -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 subtracts one day to arrive at December 31st of the previous year.
12) Last Day of Current Year
------------------------------------------
TIMESTAMPADD(SQL_TSI_YEAR, 1, TIMESTAMPADD( SQL_TSI_DAY , -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 deducts one day to arrive at December 31 of the previous year. The third TIMESTAMPADD adds a single year to the date to arrive at December 31 of the Current Year.
13) Last Day of the Next Year
----------------------------------------
TIMESTAMPADD(SQL_TSI_YEAR, 2, TIMESTAMPADD( SQL_TSI_DAY , -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 deducts one day to arrive at December 31 of the previous year. The third TIMESTAMPADD adds 2 years to the date to arrive at December 31 of the Next Year.
14) Last Day of Current Quarter
--------------------------------------------
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_QUARTER , 1, TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))
Demonstrated using Quarters. From right to left the first TIMESTAMPADD returns the first day of the Current Quarter. The second TIMESTAMPADD returns the first day of the next quarter. The final TIMESTAMPADD subtracts a single day from the date to arrive at the last day of the Current Quarter.
15) Number of days between First Day of Year and Last Day of Current Month
------------------------------------------------------------------------------------------------------------------
 TIMESTAMPDIFF(SQL_TSI_DAY, CAST('2010/01/01 00:00:00' AS DATE), TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))))
For simplicity I hard coded the January 1, 2010 date and CAST it to a date. I could have used the First Day of the Current Year calculation but didn’t want to over clutter the example. The second part of the TIMESTAMPDIFF uses Last Day of the Current Month calculation to force the TIMESTAMPDIFF to calculate the number of days between the first day of the year and the last day of the current month.

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
 

This note provides a quick reference to guide the users how to create a default footer/header for all new Analytics reports?

Hi,

This note provides a quick reference to guide the users how to create a default footer/header for all  new Analytics reports.
Solution
To implement the solution, follow the steps below:
1.Make a backup of Answerstemplates.xml.
2.Refer to Add Default Header or Footer to New Reports (Page 61) to accomplish the setting.

3. Retest the change.

References

NOTE:535619.1 - Footer Text and Column Headings Printing on Every Page
NOTE:577373.1 - Add image on the header of Configurator view
NOTE:759856.1 - Add custom text in the "Footer" section of all pages in eChannel application(SI)
B31766-01

Regards,

Deva

how to remove brand name text (next to the logo) on obiee 11g home dashboards?

You can Customise OBI welcome page by overriding XML messages. Please follow the below steps to change the Brand Name:-

a. Create a directory named customMessages under <ORACLE_INSTANCE>\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\msgdb\l_en\
Note:- Please create l_en directory if this does not exist.
b. Create an xml file under customMessages directory.
c. Add the below contents inthis xml file and add any customised text you require between TEXT tag:-
--------------------------------------------------------------- <?xml version="1.0" encoding="utf-8"?>
<WebMessageTables xmlns:sawr="com.siebel.analytics.web/response/v1" xmlns:sawm="com.siebel.analytics.web/message/v1" sawm:systemMessages="false">
<WebMessageTable system="Custom Messages">
<WebMessage name="kmsgHeaderBIBrandName">
<TEXT>Customised Text</TEXT>
</WebMessage>
</WebMessageTable>
</WebMessageTables>
---------------------------------------------------------------

Restart Presentation services for the changes to take effect.

Method 2:

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

D:\OBIEE_HOME\Oracle_BI1\bifoundation\web\msgdb\l_en\messages\productmessages.xml

For more:

for more refer
Customizing Oracle Business Intelligence Enterprise Edition 11g An Oracle White Paper
http://www.oracle.com/technetwork/middleware/bi/customizing-oracle-biee-11g-176387.pdf


Regards,

Deva

Change BGBanner name in OBIEE11g Portal header

If you want to change the OBIEE11g Common portal header name open <MW_HOME>\Oracle_BI1\bifoundation\web\msgdb\l_en\messages\
productmessages.xml (You can make changes in this file to change browser title for application)
search for webmessage name=kmsgHeaderBIBrandName and change the text in <TEXT> </TEXT> tag
E.g:
<WebMessage name=”kmsgHeaderBIBrandName”>
<TEXT>Business Intelligence</TEXT> ——-> change this text to your application name e.g: BI Application
</WebMessage>
Regards,

Deva

Enabling Sorting order for more than 1000 rows - OBIEE

I have a report which a normal table view where customer wants to show 1000 rows per page with a sorting order enabled.Normally,
if we have (>=)1000 rows per page the sorting order option will not work because by default MaxHydercubeRecords =1000 we need to reset in InstanceConfig.xml (OracleBIData/Web/Config/InstanceConfig.xml)
Add following tag
>Hypercube< >MaxTableViewRecords>12000<MaxTableViewRecords< >Hypercube <
Regards,

Deva

how to Changing theOBIEE11g homepage for all users/group wise?

Hi,

Changing the homepage for all users

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


1. Created a session init block
2. Used data source as select '/shared/SH Test/_portal/Test1' from dual
3. Assigned this value to PORTALPATH session variable
4. In Presentation services > Administration > My account > Default dashboard should be set to 'default'. Then only the dashboard specified in init block will be displayed otherwise My account will override the init block.



5. Save the changes made to rpd.
5. Logout and relogin to see if it is working fine. it is working perfectly fine.
if you like to have user/group based home page.
1. They may need to have 2 separate tables.
i. Group_path_tab with 2 columns. Group_id, portal_path
Have group wise portal path for all the groups
ii. User-group map table
Group_id, Group_name, user_id
User should be part of some group.
2. Then in the init block write the sql should be something like this
select A.portal_path from Group_path_tab A, User_group_map B
where B.user_id = :USER
and B.Group_id = A.Group_id
E.x:
Creat new init blok like below one
GPC_Home
select '/shared/AFS/_portal/GPC AFS Reporting' from dual
see my screen short:
http://imageshare.web.id/images/z5gz8tithwypn4vfgm14.jpg
here set u r home page url path like below one
'/shared/AFS/_portal/GPC AFS Reporting'
create anothe init blok
USER_LOGIN
select User_ID from User_Group_Map where User_ID=':USER'

So based on USER session variable, it will try to identify the group and then the portal_path.
Finally assign this value to PORTALPATH session variable.
for more  refer below SR:
http://varanasisaichand.blogspot.com/2011/03/system-session-variabels-obiee-11g.html
http://varanasisaichand.blogspot.com/2011/03/system-session-variabels-obiee-11g.html
SR 3-3432126511: ^how to Changing theOBIEE11g homepage for all users/group wise?


FYI,I have followed given below link.

http://total-bi.com/2011/01/obiee-11g-change-default-dashboard/



Regards,
Deva

LDAP Configuration in obiee11g

for more clear steps in this context.
http://download.oracle.com/docs/cd/E14571_01/bi.1111/e10543/sso.htm#CEGJJFED

http://download.oracle.com/docs/cd/E21764_01/bi.1111/e10543/toc.htm.

SSO can be implemented in OBIEE 11g by steps like

1. Configure the SSO authentication provider.
2. Configure HTTP proxy.
etc

How to create session variables for UserID in obiee?

http://forums.oracle.com/forums/thread.jspa?messageID=3447246&#3447246
http://forums.oracle.com/forums/thread.jspa?messageID=3376727&#3376727
http://gerardnico.com/wiki/dat/obiee/saw_url_session_variable

fetching dashboards login userid
session variable:
--------
DISPLAYNAME
init block:
---------
datasource type is OBIEE server
SELECT username,groupname from usera where username=':USER'

Unable to Sign In into presentation services and RPD obiee 11

Issue :I had a  trouble to login into RPD (online mode) the Presentation services lately.
It was working fine before and after changed console user sequrity things changed and was having issues to login lately after that.
It always gives me invalid username/Password was entered message when I tried to login no matter what ever username I tried.
I was able to login into Enterprise manger and Weblogic Admin console successfully with out any issues. All the processes are up and running with out any issues.
Tried rebooting successfully and still it wont let me login into Answers. So I tried if i could login into Admin tool and it wont let me login there as well.
So I created new Admin user in the Weblogin console and tried to login using the new user and still no luck. The new admin will let me login into the EM and WLC but not into Answers and rpd.
I tried deleting old references of the user directories in the catalog and still no luck.
It has something to do with the credential store. What ever user name I created its not mapping it to the Answers/rpd inspite of both the services are running
 Solution:
======

1. Please follow instructions given in
How to fix “Unable to Sign in. An error occured during Authentication" error when a user logs in OBIEE 11g (Doc ID 1302924.1) step by step, for those steps you have done, please double check and make sure what you've done are correct.

2. You are still not able to logon because you have missed a few steps as the following:
8. Edit the NQSConfig.INI file to reset the FMW_UPDATE_ROLE_AND_USER_REF_GUIDS = YES to NO and restart the Oracle BI Servers.
9. Remove, set to none, or comment out the line (see UpgradeAndExit in the following example) added to the instanceconfig.xml file (that instructs Oracle BI Presentation Server to refresh GUIDs on restart).
10. <ps:Catalog xmlns:ps="oracle.bi.presentation.services/config/v1.1">
11. <ps:UpgradeAndExit>false</ps:UpgradeAndExit>
12. <ps:UpdateAccountGUIDs>none</ps:UpdateAccountGUIDs>
13. Restart the Presentation Server for the instanceconfig.xml file that was updated.
14. Make sure Oracle WebLogic Server and the system components are also running, if they are not running, restart them.

b) Relogin as the problematic user to verify if you still see the “Unable to Sign In” error

Step 2 : Delete user from rpd if present

a)Launch admin tool and open rpd in OFFLINE Mode
b)Click Manage > Identity > Users tab to verify if you see this user present
Note : Normally you will only see list of users when you open rpd in ONLINE mode. Unless the users were created manually in rpd, no users should be visible in OFFLINE mode
c)If the user is present, delete this user entry from rpd
d)save rpd and deploy this changed rpd using EM
e)restart OBI Server component
f)relogin as the problematic user to see if you still see “Unable to Sign In” error

Step 3 : Delete the cacheduserinfo file from webcatalog
If Step1 and Step2 does not work, then do the following
Note : In the example below, catalog name is SampleApp and the user who gets “invalid Login” error is Administrator user.


a)Take a backup of your webcatalog
b)Navigate to C:\OBIEE11G\instances\instance2\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog\SampleApp\root\users\Administrator\_prefs
c)Delete the files name cacheduserinfo and cacheduserinfo.atr
d)Launch OBIEE 11.1.1.3 and test by logging in as the specific user.

the best one is 3rd one.

Replacing Null as 0 in obiee

 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
2) in that formula columns we have put it below case condition also ,
CASE WHEN Nom_amt IS NULL THEN 0 ELSE Nom_amt END
3) we have uncheked IS NULL check box in the admin tool also
IFNULL(columname,0)


IN OBIEE 11g above mothods won't work because it's a bug in obiee11.1.1.5.0 version please refer the BUG reference :

Bug 13054445 - REPLACING NULL VALUES WITH "0" IN AN OBIEE 11G PIVOT TABLE IS NOT WORKING

Thanks

Deva

Write Back in OBIEE11g

HI,
  

19.8.2 Configuring for Write Back

Complete the following steps to configure for users to write back values to the data source.
To configure for write back:

1.     Create a physical table in the database that has a column for each write-back field needed. In the table create statement, make the write-back fields non-null-able.

Note:

For optimum security, store write-back database tables in a unique database instance.

2.     Use the Oracle BI Administration Tool to configure the new table, as described in Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition.

3.     Create a write-back template that specifies the SQL statements that are necessary to both insert and update values into the table that you created. For more information, see Section 19.8.3, "About the Write-Back Template."

4.     Add the LightWriteback element in the instanceconfig.xml file, as described in Section 19.8.2.1, "Setting the LightWriteback Element."

5.     In Oracle BI Presentation Services, grant the following write-back privileges to the appropriate users: Manage Write Back and Write Back to Database.

For information, see Section C.2.3.2, "Setting Privileges in Oracle BI Presentation Services Administration."

6.     Configure a write-back analysis, as described in Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Enterprise Edition.

19.8.2.1 Setting the LightWriteback Element

In order for users to write back values, you must manually add the LightWriteback element in the instanceconfig.xml file. Before you begin this procedure, ensure that you are familiar with the information in Section 3.4, "Using a Text Editor to Update Oracle Business Intelligence Configuration Settings".
To manually set the element for write back:

1.     Open the instanceconfig.xml file for editing, as described in Section 3.6, "Where are Configuration Files Located?"

2.     Locate the ServerInstance section in which you must add the LightWriteback element.

3.     Include the element and its ancestor elements as appropriate, as shown in the following example.

4.         <WebConfig>

5.             <ServerInstance>

6.                 <LightWriteback>true</LightWriteback>

7.             </ServerInstance>

8.         <WebConfig>

Note that this example does not include parameters that might exist in the file, but that are centrally managed by Fusion Middleware Control and cannot be changed manually.

9.     Save your changes and close the file.

10. Restart Oracle Business Intelligence.

19.8.3 About the Write-Back Template

The write-back template is an XML-formatted file that contains SQL statements that are needed to insert and update records in the write-back table and columns that you have created. You can create multiple write-back templates, customizing each one for the fields that are used in each specific analysis. In the table view properties, you specify the name of the write-back template to use.

19.8.3.1 How Write Back Works

If a user has the Write Back to Database privilege, then the write-back fields in their analyses can display as editable fields if properly configured. If the user does not have this privilege, then the write-back fields display as normal fields. If the user types a value in an editable field and clicks the appropriate write-back button, then the application reads the write-back template to get the appropriate insert or update SQL command. It then issues the insert or update command. If the command succeeds, then it reads the record and updates the analysis. If there is an error in either reading the template or in executing the SQL command, then an error message is displayed.
The insert command runs when a record does not yet exist and the user enters new data into the table. In this case, a user has typed in a table record whose value was originally null.
The update command runs when a user modifies existing data. To display a record that does not yet exist in the physical table to which a user is writing back, you can create another similar table. Use this similar table to display placeholder records that a user can modify in dashboards.

19.8.3.2 Requirements for the Write-Back Template

The write-back template must meet the following requirements:

·         To meet security requirements, you must specify the connection pool along with the SQL commands to insert and update records. These SQL commands reference the values that are passed in the write-back schema to generate the SQL statements to modify the database table. Values can be referenced either by column position (such as @1, @3) or by column ID (such as @{c1234abc}, @{c687dfg}). Column positions start numbering with 1. The use of column ID is preferred. Each column ID is alphanumeric, randomly generated, and found in the XML definition of the analysis in the Advanced tab of the Analysis editor.

·         You must include both an <insert> and an <update> element in the template. If you do not want to include SQL commands within the elements, then you must insert a blank space between the opening and closing tags. For example, you must enter the element as

·                <insert> </insert>

rather than

<insert></insert>

If you omit the blank space, then you see a write-back error message such as "The system cannot read the Write Back Template 'my_template'".

·         If a parameter's data type is not an integer or real number, then add single quotes around it. If the database does not do Commits automatically, then add the optional postUpdate node after the insert and update nodes to force the commit. The postUpdate node typically follows this example:

·                <postUpdate>COMMIT</postUpdate>

·         Store the write-back template files in the analyticsRes directory that the administrator has configured for static files and customer messages:

ORACLE_INSTANCE/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obipsn/analyticsRes/customMessages

While XML message files that affect a language-specific user interface must be localized, the XML file that is used for configuring a write-back template is usually not translated, because it is language-independent.

In the rare cases where write-back template files must be language-dependent (for example, if a user logging in using the l_es (Spanish) locale would use a different SQL command then a user logging in using l_fr (French) locale), then the write-back template messages should exist in appropriate language directories. For information, see Section 22.5, "Customizing the User Interface Using XML Message Files."

·         The write-back template files can have any name of your choosing, because the system reads all XML files in the CustomMessages folder. To ensure that write back works correctly, include in the WebMessage element of the file the name of the SQL template that you specified when you created the write-back table. You can have multiple WebMessage elements in one file, with each element specifying one SQL template.

The following example shows the specification of the SQL template that is called "SetQuotaUseID."

<WebMessage name="SetQuotaUseID">

19.8.3.3 Example: Write Back Template

A write-back template might resemble this example:

<?xml version="1.0" encoding="utf-8" ?>

<WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1">

<WebMessageTable lang="en-us" system="WriteBack" table="Messages">

   <WebMessage name="SetQuotaUseID">

      <XML>

         <writeBack connectionPool="Supplier">

            <insert>INSERT INTO regiontypequota VALUES(@{c0},@{c1},'@{c2}','@{c3}',@{c4})</insert>

            <update>UPDATE regiontypequota SET Dollars=@{c4} WHERE YR=@{c0} AND Quarter=@{c1} AND Region='@{c2}' AND ItemType='@{c3}'</update>

         </writeBack>

      </XML>

   </WebMessage>

   <WebMessage name="SetQuota">

      <XML>

         <writeBack connectionPool="Supplier">

            <insert>INSERT INTO regiontypequota VALUES(@1,@2,'@3','@4',@5)</insert>

            <update>UPDATE regiontypequota SET Dollars=@5 WHERE YR=@1 AND Quarter=@2 AND Region='@3' AND ItemType='@4'</update>

         </writeBack>

      </XML>

   </WebMessage>

</WebMessageTable>

</WebMessageTables>

file path:

writeback.xml under
C:\BI_HOME\instances\instance3\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\WEB-INF\customMessages
and it should be kept under
C:\BI_HOME\instances\instance3\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes\customMessages