OBIEE Mobile Dashboard Demo

Following video shows you OBIEE Mobile dashboard demo

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);

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.

Unable to Login in to OBIEE 11g

• Check whether all the services are up or not.
• Most of the times cluster server will not be up due to which you may see this issue.
• If you are using Windows Environment then try configuring loop back adapter.
• If you are using Windows Environment in Laptop then make sure that before you start the servers wifi is switched of. This is mandatory step because host will be conflicted in case of wifi.
• Check in the logs what are the errors it is throwing.
• Most of the errors will be related to Networking such as Host, Wifi, Environment Variables, Improper shutdown.

If you see any errors in the log file kindly send to me. I will try to help you regarding this.

Unresolved Column in OBIEE 11g

Many of you might have notices this error during their Initial developments. This is one of the many common mistakes we might done during the import.


Odbc driver returned an error (SQLExecDirectW).

A general error has occurred. [nQSError: 27005] Unresolved column:

Primary check what you have to do is to check whether this column is present in your Database or not.

• Open the RPD.
• Right click on the column.
• Select view data.
• If you see that ODBC error here then it would not be present in the database.
• Check the column name again in the database and correct it.

OBIEE Checklist Document

Checklist document is used to validate all the development work that you have done in OBIEE.

This is one of the important document which will let the business know that we have completed and validated all the development.

You can download this file from the below link

http://downloads.ziddu.com/download/25275530/OBIEE_Checklist_Document.rtf.html


Forward me if you have any other versions of the same.

OBIEE LLD - Low Level Design

1. Scope of the project
2. Data source system structure which includes all columns and its details
3. Repository details (Physical layer details, tables and join between tables, BMM Layer details and Presentation Layer Details) and variables and user group details
4. Report Details
5. Users and Security details

OBIEE HLD - High Level Design

1. scope of the project
2. Source database design
3. High level warehouse design ( Star schema /snowflake schema) structure
4. Fact tables and Dimension tables and relation between the tables
4. Oracle BI Distributed architecture
5. Authorization and Authentication Details (User and group)
6. Standard Guidelines