OBIEE Mobile Dashboard Demo

Following video shows you OBIEE Mobile dashboard demo

Starting and Stopping OBIEE Services from Putty

Sometimes even though we stop all the weblogic processes still node manager/obiee services will be running in the background.

To make sure no other process is running kill nodemanager and obiee processes

ps -ef |grep Node|grep nodemanager |cut -c10-15

ps -ef |grep obiee

Kill the above processes ( kill <PID>)

Stopping Weblogic

Go to

<OBIEE Home>/user_projects/domains/bifoundation_domain/bin/

Then run -> ./stopWebLogic.sh

Starting Weblogic

---------------

nohup sh

<OBIEE Home>/user_projects/domains/bifoundation_domain/bin/startWebLogic.sh -Dweblogic.management.username=weblogic -Dweblogic.management.password=weblogic234 > wls_start.log &

Note: Change password of your application. Nohup is the command to run the process in the linux and give the control back to putty.

Starting NodeManager

------------------

nohup sh  <OBIEE Home>/wlserver_10.3/server/bin/startNodeManager.sh > startNodeManager.log &

Starting EM

------------

nohup sh <OBIEE Home>/user_projects/domains/bifoundation_domain/bin/startManagedWebLogic.sh bi_server1

Note: bi_server1 is the server name. make sure your server name given properly

Starting OPMN

----------

cd <OBIEE HOME>/instances/instance1/bin/

./opmnctl startall

./opmnctl status   -- To Check the Status

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.

How to set Cache Settings in OBIEE 11g

Enabling Cache:

1. Go to Fusion Middleware Control and log in as weblogic.
2. In the left pane, expand Business Intelligence
3. Click coreapplication.
4. Click Capacity Management.
5. Click the Performance tab.
6. Click Lock and Edit Configuration.
7. Select Cache Enabled.
8. Click Apply to activate the changes.
9. Click Activate Changes.
10. Restart Oracle BI components to apply recent changes.

Modifying Cache Parameters:

Use Fusion Middleware Control to set query cache parameters. You can use Fusion

Middleware Control to set the maximum number of cache entries in the query cache, as

well as the maximum size for a single cache entry.

1. Return to Fusion Middleware Control, which should still be open. If it is not open, enter the http://localhost:7001/em and log in to Fusion Middleware Control as weblogic with password welcome1.
2. In the left pane, expand Business Intelligence.
3. Click coreapplication.
4. Click Capacity Management.
5. Click the Performance tab.
6. Click Lock and Edit Configuration.
7. Change Maximum cache entries from 1000 to 10000.
8. Apply and activate your changes.
9. Leave Fusion Middleware Control open, but do not restart to apply recent changes at this time.

Modifying Cache Parameters Manually

Use NQSConfig.ini to view and manually edit additional query cache parameters.

1. Navigate to D:\bi\instances\instance1\config\OracleBIServerComponent\coreapplication_obis1.
2. Before making changes to NQSConfig.ini, make a copy of the file and paste it in the same directory.
3. Open NQSConfig.ini.
4. Navigate to the CACHE section.
5. Make the following modifications:
6. Modify the MAX_ROWS_PER_CACHE_ENTRY parameter as follows: MAX_ROWS_PER_CACHE_ENTRY = 50000

This parameter controls the maximum number of rows for any cache entry. Limiting the

number of rows is a useful way to avoid using up the cache space with runaway

queries that return large numbers of rows. If the number of rows a query returns is

greater than the value specified in the MAX_ROWS_PER_CACHE_ENTRY parameter,

the query is not cached.

Notice the DATA_STORAGE_PATH parameter. This parameter specifies one or more

directories for query cache storage, and the maximum size for each storage directory.

These directories are used to store the cached query results and are accessed when a

cache hit occurs.

Notice that the MAX_CACHE_ENTRIES parameter is changed to 10000. Changes

made to cache configuration in Fusion Middleware Control are written to this file.

       7. Save and close NQSConfig.INI.
       8. Return to Fusion Middleware Control and restart Oracle BI components.

How to set Logging Level to user in OBIEE

1. Set logging levels for users to allow you to track queries in query logs.
• Select Manage > Identity to open Identity Manager.
• In the left pane, select Identity Management > BI Repository.
• Select Action > Set Online User Filter.
• Enter an asterisk in the field and click OK to retrieve users.
• In the right pane, double-click on the User to open the User properties dialogue box.
• Set the logging level to 2 and click OK.
• Save the repository.

Best Practices in OBIEE BMM Layer

1. Use the Business Names in the Logical Layer itself. This will reduce the rework in the presentation layer.
2. Use the Set Icon feature to easily distinguish the Development Areas or functional areas.
3. Remove ETL unused fields in the BMM Layer Only.
4. Use rename wizard to rename all the columns and tables

Best Practices in OBIEE Physical Layer

1. Use alias for every Dimensional Warehouse tables.
2. Set Connection Pool Maximum Connections to 10. Increasing the number of connections will increase the multiple connections to database so Increase it as per the recommendations by DBA. More connections will result multiple idle sessions which will in turn decrease the performance.
3. Set the appropriate Database Features in the Connection Pool Properties. Sometimes you will encounter the errors related to database features. Align with DBA to find actual features.
4. Turn of cache for the tables which dont require cacheing.
5. Use alias tables to remove Circular Joins.
6. Check for the Data Types for each columns.

OBIEE Supplier Dump for Practice

You can use the below links to download OBIEE Supplier Schema which you can use for practicing any of the Datawarehouse reporting

Link to download Supplier Schema

http://downloads.ziddu.com/download/25274656/Batch_Supplier_2.rar.html

There are two files .dmp file which contains tables and columns with related data. another one for creating user schema.

High Availability configuration when two database present

Follow the below steps to configure OBIEE to refer two databases dynamically.

• Prepare an Update statement which will update the DSN name of the Primary and Secondary Node.
• Create a Dynamic Repository Variable to refresh at the regular Interval and refer the DSN column from the above table. This will check the Active DSN name.
• Use the above variable reference in the Connection Pool so that It will refer the query to get the Active DSN every time.
• Now During the start and End of load execute the Update Statement created at the Step 1.

Automatically during the load the queries will be fired on the secondary server so that users can experience faster results even during the load time.