OBIEE - Session Variables are not updated problem

OBIEE - Session Variables are not updated problem

When you are using Session Variables, with Row-wise initialization.

In case you have a problem with those session variables not being updated, unless you restart the BI server...

Please make sure that the "use caching" option is turned off. Otherwise the Session Variables are not refreshed each time you log in.

(The use caching is very useful in many other cases).

How to Increase no of rows in excel output in OBIEE

To use Fusion Middleware Control to set configuration options for views:

Go to the Business Intelligence Overview page, as described in Section 2.2.3, "Using Fusion Middleware Control to Manage Oracle Business Intelligence System Components."

Display the Performance tab of the Capacity Management page.

Click Lock and Edit Configuration to enable changes to be made.

Complete the elements using the descriptions in the help topic for the page. Click the Help button on the page to access the page-level help for the following options:

Maximum Number of Rows to Download option
Maximum Number of Rows Per Page to Include option
Click Apply, then click Activate Changes.

Return to the Business Intelligence Overview page and click Restart.


Method 2:

To manually edit the settings that change the display of data in views:

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

Locate the Table, Pivot, Trellis, Charts, Narrative, Ticker, and Treemap parent sections, in which you must add the elements that are described in Table 19-2.

Include the elements and their ancestor elements as appropriate, as shown in the following example:

<ServerInstance>
  <Views>
      <Table>
        <MaxCells>10000</MaxCells>
        <MaxPagesToRollOutInDelivery>1000</MaxPagesToRollOutInDelivery>
        <MaxVisiblePages>1000</MaxVisiblePages>
        <MaxVisibleRows>500</MaxVisibleRows>
        <MaxVisibleSections>25</MaxVisibleSections>
        <DefaultRowsDisplayed>30</DefaultRowsDisplayed>
        <DefaultRowsDisplayedInDelivery>250</DefaultRowsDisplayedInDelivery>
        <DefaultRowsDisplayedInDownload>65000</DefaultRowsDisplayedInDownload>
        <DefaultRowsDisplayedInDownloadCSV>65000</DefaultRowsDisplayedInDownloadCSV>
      </Table>
      <Pivot>
        <MaxPagesToRollOutInDelivery>1000</MaxPagesToRollOutInDelivery>
        <MaxVisibleColumns>300</MaxVisibleColumns>
        <MaxVisiblePages>1000</MaxVisiblePages>
        <MaxVisibleRows>500</MaxVisibleRows>
        <MaxVisibleSections>25</MaxVisibleSections>
        <DefaultRowsDisplayed>30</DefaultRowsDisplayed>
        <DefaultRowsDisplayedInDelivery>250</DefaultRowsDisplayedInDelivery>
        <DefaultRowsDisplayedInDownload>65000</DefaultRowsDisplayedInDownload>
        <DefaultRowsDisplayedInDownloadCSV>65000</DefaultRowsDisplayedInDownloadCSV>
      </Pivot>
      <Trellis>
        <Simple>
            <MaxCells>1000</MaxCells>
            <MaxVisibleSections>10</MaxVisibleSections>
            <MaxVisiblePages>1000</MaxVisiblePages>
            <MaxVisibleRows>100</MaxVisibleRows>
            <MaxVisibleColumns>75</MaxVisibleColumns>
            <MaxPagesToRollOutInDelivery>1000</MaxPagesToRollOutInDelivery>
            <DefaultRowsDisplayed>10</DefaultRowsDisplayed>
            <DefaultRowsDisplayedInDelivery>100</DefaultRowsDisplayedInDelivery>
            <DefaultRowsDisplayedInDownload>6500</DefaultRowsDisplayedInDownload>
        </Simple>
        <Advanced>
            <MaxCells>5000</MaxCells>
            <MaxVisibleSections>50</MaxVisibleSections>
            <MaxVisiblePages>1000</MaxVisiblePages>
            <MaxVisibleRows>250</MaxVisibleRows>
            <MaxVisibleColumns>150</MaxVisibleColumns>
            <MaxPagesToRollOutInDelivery>1000</MaxPagesToRollOutInDelivery>
            <DefaultRowsDisplayed>25</DefaultRowsDisplayed>
            <DefaultRowsDisplayedInDelivery>250</DefaultRowsDisplayedInDelivery>
            <DefaultRowsDisplayedInDownload>10000</DefaultRowsDisplayedInDownload>
        </Advanced>
      </Trellis>
      <Charts>
        <MaxVisibleColumns>2000</MaxVisibleColumns>
        <MaxVisiblePages>1000</MaxVisiblePages>
        <MaxVisibleRows>2000</MaxVisibleRows>
        <MaxVisibleSections>25</MaxVisibleSections>
        <JavaHostReadLimitInKB>4096</JavaHostReadLimitInKB>
      </Charts>
      <Narrative>
        <MaxRecords>40000</MaxRecords>
        <DefaultRowsDisplayed>30</DefaultRowsDisplayed>
      </Narrative>
      <Ticker>
        <MaxRecords>40000</MaxRecords>
      </Ticker>
      <Treemap>
        <MaxCells>5000</MaxCells>
        <MaxVisiblePages>10000</MaxVisiblePages>
        <MaxVisibleRows>10000</MaxVisibleRows>
        <MaxVisibleSections>50</MaxVisibleSections>
      </Treemap>
  </Views>
</ServerInstance>
Note that this example does not include elements that might exist in the file, but that are centrally managed by Fusion Middleware Control and cannot be changed manually.

Save your changes and close the file.

Restart Oracle Business Intelligence.

Merged Columns in excel output in OBIEE

1. Take a back up copy of <Middleware_Home>/instances/instance1/config/OracleBIJavaHostComponent/coreapplication_obijh1/xdo.cfg file.

2. Open xdo.cfg for editing.

3. Add the following:
(Setting "xlsx-keep-values-in-same-column" to true)

<config version="1.0.0" xmlns="http://xmlns.oracle.com/oxp/config/">
<properties>
<property name="xlsx-keep-values-in-same-column">true</property>
</properties>
</config>

4. Save your changes.

5. Restart all OBIEE components. Make sure you restart bi_server from weblogic as well.

For Cluster environment change the above file in both the instance and then restart

connection failed while importing metadata in obiee 11g

There are many reasons you may get this error. One way to resolve the issue is to correct the shortcut which is placed on the desktop

Go to the Installed path of the Admin tool. 

In my case it is 

C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client Tools\oraclebi\orahome\bifoundation\server\bin\

Find Admintool.exe and create a shortcut by using right click and paste the below value

"C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client Tools\oraclebi\orahome\bifoundation\server\bin\bi_init.bat" coreapplication admintool.exe

Make sure that (“) didn’t miss.

Note: tnsnames.ora file should be kept in the repository folder from where you are opening the rpd. This file you can get it from Oracle database from the server under network folder.

How to Calculate AGE between two dates in OBIEE

Age in OBIEE

First thing is you need to have two date fields to calculate age. In OBIEE we have TIMEDIFF time function to calculate difference between two dates.

Example is shown below

Timediff(SQL_TSI_YEAR, Date of birth, CURRENT_DATE)

DATE OF BIRTH column is derived from the database

CURRENT_DATE is the predefined variable to fetch sysdate.

Output of this query will give you the age.

There is a big flaw in this query because it always finds the age by differentiating year.

Below query will even consider day and month of the birth date and calculates exact age between dates.

 SELECT 

--Year

TRUNC(MONTHS_BETWEEN(SYSDATE,DOB)/12) YEAR,

--Month

trunc(mod(months_between(sysdate,dob),12)) month,

--Day

TRUNC(SYSDATE-ADD_MONTHS(DOB,TRUNC(MONTHS_BETWEEN(SYSDATE,DOB)/12)*12 +TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,DOB),12)))) DAY

,

--Difference between Birth Month and Current Month

TO_NUMBER(TO_CHAR(SYSDATE,'MM'))-TO_NUMBER(TO_CHAR(DOB,'MM')) MON,

--Difference between Birth Day and Current Day

TO_NUMBER(TO_CHAR(SYSDATE,'DD'))-TO_NUMBER(TO_CHAR(DOB,'DD')) days,

--Compare the above two factors and decide which year. This query will calculate even day difference between the dates.

CASE WHEN TO_NUMBER(TO_CHAR(SYSDATE,'MM'))-TO_NUMBER(TO_CHAR(DOB,'MM'))<=0 OR 

TO_NUMBER(TO_CHAR(SYSDATE,'DD'))-TO_NUMBER(TO_CHAR(DOB,'DD'))<=0 THEN

CASE WHEN TO_NUMBER(TO_CHAR(SYSDATE,'MM'))-TO_NUMBER(TO_CHAR(DOB,'MM'))=0 then 

TRUNC(MONTHS_BETWEEN(SYSDATE,DOB)/12) -1

ELSE 

TRUNC(MONTHS_BETWEEN(SYSDATE,DOB)/12) 

END

ELSE 

CASE WHEN TO_NUMBER(TO_CHAR(SYSDATE,'MM'))-TO_NUMBER(TO_CHAR(DOB,'MM'))>0

THEN TRUNC(MONTHS_BETWEEN(SYSDATE,DOB)/12) END

end

correct_age

from (Select to_date('16042015','DDMMYYYY') dob from dual);

Migration of BI Publisher Reports

Migrating 10g to 11g BI PUBLISHER

The Following are the below steps in-order to migrate the BI Publisher reports from 10g to 11g.Copy  the 10g reports repository and restore  it to some location in the current system where BI Publisher 11g is Installed.

• Take a backup of the BI 11g Repository by going into the following location.
• mv  $ORACLE_HOME/user_projects/domains/bitest_domain/config/bipublisher  bipublisher_ORG
• Running the Upgrade wizard by going into the following location
•   Cd  $ORACLE_HOME/Oracle_BI/bin
• $ ./ua
• Click on Next
• Select  à  Upgrade Oracle BI Publisher Repository and click on next.
• Select  à  Upgrade 10g BI Publisher Repository Directory and click on next.
• Provide the 10g reports repository location which is copied and stored in the current system.
• /rtmrap/bi10greports_repos/XMLP
• Enter the destination 11g BI Publisher Repository Directory and click on next. Provide the 11g reports repository location  /rtmrap/bi_weblogic/middleware_home/user_projects/domains/bitest_domain/config/bipublisher/repository
• Enter the weblogic Admin Server Connections details 
• UserName       :   biweblogic
• Password        :   biweblogic1
• Click on Next
• Click on Finish
• 
  
• After completing the upgrading steps restart the weblogic domain and managed server.
• To Start the BI Core application go the following location and execute the below command.
• Cd  $ORACLE_HOME/instance/bi_inst1/bin
• ./opmnctl  startall
• ./opmnctl status
• After re-bouncing the BI domain & Managed Server. Login into the BI Publisher application and go the following location
• Click on Administration tab
• Select Server Configuration
• Select Catalog Type :  Oracle BI EE Catalog
• Specify the BI Publisher repository location
• For ex:/rtmrap/bi_weblogic/middleware_home/user_projects/domains/bitest_domain/config/bipublisher/repository
• Click on Upload to BI Presentation Catalog
• Restart the BI domain, Managed Server & core application.
• After re-bouncing the weblogic domain, login into the bi publisher application.
• In-order to view the reports
• Go the catalog and select the respective folder to view the reports.

Different Types of tables used in OBIA

Different type of tables used in Datawarehouse. This is important to know whoever is working on the data model. Each type of table has its own set of data.

Following are the different types of tables used in OBIA.

Aggregate tables (_A)	Contain summed (aggregated) data.
Dimension tables (_D)	Star analysis dimensions.
Delete tables (_DEL)	Tables that store IDs of the entities that were physically deleted from the source system and should be flagged as deleted from the data warehouse. Note that there are two types of delete tables: _DEL and _PE. For more information about the _PE table type, see the following Primary extract tables (_PE) row.
Dimension Hierarchy tables (_DH)	Tables that store the dimension's hierarchical structure.
Dimension Helper tables (_DHL)	Tables that store M:M relationships between two joining dimension tables.
Staging tables for Dimension Helper (_DHLS)	Staging tables for storing M:M relationships between two joining dimension tables.
Staging for Dimension Hierarchy (_ DHS)	Staging tables for storing the hierarchy structures of dimensions that have not been through the final extract-transform-load (ETL) transformations.
Staging tables for Dimension (_DS)	Tables used to hold dimension information that have not been through the final ETL transformations.
Fact tables (_F)	Contain the metrics being analyzed by dimensions.
Fact Staging tables (_FS)	Staging tables used to hold the metrics being analyzed by dimensions that have not been through the final ETL transformations.
Internal tables (_G, _GS, _S)	Internal tables are used primarily by ETL mappings for data transformation and controlling ETL runs.
Helper tables (_H)	Helper tables are inserted between the fact and dimension tables to support a many-to-many (M:M) relationship between fact and dimension records.
Map dimension tables (_M)	Tables that store cross-referencing mappings between the external data ID and the internal ID.
Mini dimension tables (_MD)	Include combinations of the most queried attributes of their parent dimensions. The database joins these small tables to the fact tables.
Primary extract tables (_PE)	Tables that are used to support the soft delete feature. The table includes all the primary key columns (integration ID column) from the source system. When a delete event happens, the full extract from the source compares the data previously extracted in the primary extract table to determine if a physical deletion was done in the Siebel application. The soft delete feature is disabled by default. Therefore, the primary extract tables are not populated until you enable the soft delete feature.
Persisted staging tables (_PS)	Tables that source multiple data extracts from the same source table. These tables perform some common transformations required by multiple target objects. They also simplify the source object to a form that is consumable by the warehouse needed for multiple target objects. These tables are never truncated during the life of the data warehouse. These are truncated only during full load, and therefore, persist the data throughout.
Pre-staging temporary table (_TMP)	Source-specific tables used as part of the ETL processes to conform the data to fit the universal staging tables (table types _DS and _FS). These tables contain intermediate results that are created as part of the conforming process.
Staging tables for Usage Accelerator (_WS)	Tables containing the necessary columns for the ETL transformations.