All About OBIEE and DAC

OBIEE Physical Layer Design Principles/Best Practices

There are certain design principles or best practices oracle suggest in designing the OBIEE repository. If you open any standard/sample repository provided by oracle you will understand what exactly I am talking about

Today we will discuss the design principles for physical layers

Principle 1 :Import only needed tables in physical layer and do not create tables manually in physical layer.

Reasons to do that :Its easier to import the table then creating it manually.In that way we are avoiding the table name , column name , data type mis match possibility. Import only those tables which you need. If you need more tables at later stage in project, you can always import them.

You will be using connection pool to import the table and that helps to make sure that connectivity to source database system is working fine.

Principle 2 Create alias for the physical tables

Reasons to do that :

It helps in avoiding the circular joins.

It helps when we have confirm dimension(e.f time dimension) and we want to join dimension different way

Its easier to give the access of objects to users in case of Multi User Development Environment

Alias naming convention helps in easily finding the fact and dimension table in physical layer

We can import the key relationship in physical layer from database and maintain the relationship which is required among the alias tables

Principle 3 Set the cache  property of the physical table

Reasons to do that

Its better to set the caching at physical table level and not at the alias table level.This applies to most of the scenarios

It your data source is OLTP system make sure you disable to caching. As  that changing frequency would be high and you do not want to cache results.

For all the data warehouse sources have better cache persistence time set

Principle 4 connection pool(s) and its properties

Reasons to do this.

To have multiple connection pool for difference purpose. e.g for usage tracking, security implementation etc it better to have different connection pools.

Call interface: Use native call interface for the connection(e.g OCI10g) etc based on source database. Its faster then generic ODBC connection.

set connection pool , time out ,maximum connections etc based on server configurations and usage

connection pooling- it avoids the database connection overhead. If users run the same query it users the same connection from the pool. What does this mean is user sessions share the connection in this.

Maximum Connection :

Max what it should be :Approx 1024 kb of server memory is consumed in each connection.So tThis has to be set per server configuration and requirement.

Min what it should be: 25% of max users times the reports they execute.

e.g if you have 1000 users accessing around 10 reports  in dashboard that this number should 250 ( 25% of 1000) * 10 = 2500

Please leave me a comment if you have lean any other design principal as a hard lesson in your projects

Utilities provided in OBIEE ( OBIEE Administration tool)

Today, we are going to discuss something about the “Utilities” feature in Admin tool. This Utility feature is very helpful  in the development, maintenance and administration of repository.

Accessing Wizards and Utilities

Start > Programs > Oracle BI > Administration

In Administration tool, go to  tool > Utilities

Click on Utilities , Now the next screen will look like this

Step by step will discuss all…

1) Replace Column or Table in Logical Table Source

Select Replace column or table in logical table source and click ” Execute”

With the help of this wizard we can replace single column or entire table. We can see that from the screen shot.

2) Oracle BI Event Tables

Oracle BI Event tables Utility allows us to Identify a table as an Oracle BI Event Polling table. An Event pulling table is a way to notify the Oracle BI Server  that one or more physical tables have been updated.

To mark the table object as an Event Polling Table
1 Click on the Tools > Utilities menu item.
2 Select the option Oracle BI Event Tables from the list of options.
3 Click Execute.
4 Select the table to register as an Event Table and click the >> button.
5 Specify the polling frequency in minutes, and click OK.
The default value is 60 minutes.

3) Externalize Strings

We will discuss this later…

4) Rename Wizard

Rename Wizard allows us to rename BMM Layer and Presentation layer table and columns. I t provides a convenient way to transform physical names to User -Friendly names.

Here you can select  Layer and add the tables or columns which you want to rename.

5) Update Physical Layer Wizard

This wizard allows you to update database objects in the Physical layer of a repository based on their
current definitions in the back-end database.

6) Repository Documentation

To run the Repository Documentation utility
1 From the Tools menu, choose Utilities.
2 In the Utilities dialog box, select Repository Documentation, and then click Execute.
3 In the Save As dialog box, choose the directory where you want to save the file.
4 Type a name for the file.
5 Choose a type of file and an Encoding value and click Save.

7) Remove Unused Physical Objects

Large repositories use more memory on the server and are harder to maintain. Additionally,
development activities take longer on a large repository. This utility allows you to remove objects
that you no longer need in your repository. You can remove databases, initialization blocks, physical
catalogs, and variables.
To remove unused physical objects
1 From the Tools menu, choose Utilities > Remove Unused Physical Objects, and then click Execute.
2 In the Remove Unused Physical Objects dialog box, from the Type drop-down list, select the type
of object.
3 In the list of objects, verify that only the objects that you want to remove are checked.
Below the list of objects, the number of checked and the total number of objects appears.
4 To remove the checked objects, click Yes.
5 To cancel, click No.

8 ) Aggregate Persistence Wizard

Performance Tuning in OBIEE

Performance Tuning

Today we are going to discuss about various options for improving performance of reports in OBIEE.

There are lots of options available in OBIEE to improve performance of reports.

Today will discuss about these…

1)   Aggregate tables

2)  Cache  ( Cache Management)

3) Turning off logging

4) Constructing result using where clause

5) Limiting Number of Initialization block

6) Setting Query limits

7) Modeling Dimension Hierarchies  Correctly

8)  Setting NQSConfig Parameters

1) Aggregate Table (Aggregate Persistence Wizard)

Aggregate Table: Aggregate tables store precalculated measures that have been aggregate over a set of dimensional attributes.

This is very useful technique for speeding up query response time in decision support systems. This eliminates the need of run time calculations and delivers faster results to users.

The calculations are done ahead of time and the results are stored in the tables.

The key point is that the aggregate table should have fewer rows than the non aggregate table and therefore processing should be quicker.

Aggregate Persistence Wizard

Go to: OBIEE Admin > tool> Utilities > Aggregate Persistence Wizard

2) Caching

3) Turning off Logging

Logging can affect in performance  of  Oracle BI Server and can create large log files. Logging can be used in problem solving and trouble shooting problematic queries.  So when it is necessary then only set logging level to 0 to 2. Set log level greater than 2 is not recommended by Oracle.

4) Constructing result using where clause

With the help of where clause in content tab in logical table source you can filter the data ( limits the rows ) returned from database.

Here , you can see the where clause and restrict the data using Expression Builder( marked in Blue).

5) Limiting Number of Initialization block

Initialize block are the only means to initialize dynamic repository , system session  and non system session variables. We should be very careful about not to create too many init blocks.

As we know, In the case of system and non system session variables, the initialization blocks get executed every time a user logs in to the server.  And In the case of Dynamic repository variables, the SQL in the Initialization blocks get executed every time the server is started or periodically if a schedule is set up to refresh the value of the variable.

6) Setting Query limits

We can enable oracle BI Server to track and cancel run way queries by placing various limits on the repository for a given user or group. For each user or group it is possible to limit queries by various condition :

1) maximum number of rows a query can retrieve from a database

2) maximum time a query can run on a database

3) restricting access to a database during particular time periods from Analytic server

7) Modeling Dimension Hierarchies  Correctly

Dimension Hierarchy must be modeled accurately to ensure that Oracle BI optimizer chooses the most economical source. The Number of element for each level must be specified. The Number does not have to be exact,but ratio of number from one parent to child logical level should be accurate. See below screen shot,

8 ) Setting NQSConfig Parameters

NQSConfig.INI includes parameters that affect Oracle BI performance :

• Sort Memory size
• Sort buffer Increment size
• Virtual table page size

NQSConfig.INI – Important things to Remember

File Path: < OBIEE Install Directory> / Oracle BI/ Server/ Config / NQSConfig.INI

Here I would like to share the importance of this file

First of all  NQSConfig.INI is the main Configuration file for BI Server. Because it contains lots of parameters.

[Note: After changing anything to this file we  have to restart Oracle BI server Services to reflect that changes  ]

1)  General

If Values in the literals, digits or _, they can be given as such. If values contain characters other than literals, digits or _, values must be given in quotes. (Like “”)

2) Repository

All repositories must reside in OracleBI \ server\ Repository directory, where OracleBI is the directory in which the oracle BI server software is installed.

3)  Cache ( Learn in detail about CACHE MANAGEMENT)

Usually Cache ENABLE options = YES , If you change  as “ NO ” then u can not check in Cache Manager about the entries ( means cache will be disable)

( to check in cache manager follow this

Start \ All Programs \ Oracle Business Intelligence \ Administration  then  File \ open \ online

Give Administrator as user and password. Then go to Manage\cache)

We can change parameters like MAX ROWS PER CACHE ENTRY, MAX CACHE ENTRY SIZE, MAX CACHE ENTRIES .

Above parameters are by default. And we can change it to need our need. To check the current caching situation on the server path: Oracle BI Admin tool > Manage > Cache > Go right pane and right click> show info.

You will see the screen as below.

Time Series Wizard in OBIEE

Time Series Wizard (Time Series Measure)

Oracle BI Server provides Ago and ToDate functions for time series comparisons.

There is no direst way in SQL to make time comparison so Oracle BI Server has provided these two functions for time series comparison. Time series measures ( Ago & ToDate ) are the best way to compare sales between current year and previous year and sales till date.

Both these functions operate on measures.

Ago Function: Calculates aggregated value as of some time period shifted from the current time.

Example : Ago can produce sales for every month of the current quarter and the corresponding quarter.

ToDate Function:

The ToDate function is used to aggregate a measure attribute from the beginning of a specified time period to the currently displaying time. For example, the ToDate function can calculate Year to Date sales for a given year.

Steps to Model Time Series Data

1. Identify time dimension and chronological keys

2 Create the Ago Measure

Create a new logical column and then use the expression builder to build Ago function.

1. 

Now In this Example, Amount positive is the measure , Month is time level and 1 is the time period.After placing all the values and it will look like this,

Here we can see that in measure we have selected amount positive , next we are going to select month as a time dimension. And in the number of periods we will put 1.

Screen will look like this,

3.Use existing columns to Create Additional Ago Measure

Use existing columns to Create Additional Ago Measure ,such as Change Ago, Percent Change Ago.

In this example Ago Month measure is used to calculate the change in Amount between the current month and the previous month .

4. Create ToDate Measure

Create a new logical column and then use the expression builder to build the ToDate function.

In this Example Amount positive is the measure and year is the time level.

5. Add New Measure to the Presentation Layer

Add new time series  meausues to the Presentation layer so that users can include them in query criteria.

6. Test the results in Answers.

Run a request and test all the measures we have created using time series measures.

Some Limitations

• You may only enter Ago and ToDate functions in the Expression Builder Dialog Box in the Administration Tool.
• You can not use them in  coded SQL.
• These two functions are relational Data Source only.

Multi User Development Environment(MUDE) In OBIEE

Multi User Development Environment (MUDE)

Oracle BI repository development environment is not set up for multiple users. A more efficient development environment would permit developers to modify a repository simultaneously and then check in changes. This can be done by setting up the multi user environment using the Oracle BI Administration Tool to support concurrent development.

While more than one user wants to work on single repository MUD will help to avoid conflicts and User can work on subset of repository.

Even if we have single user we can use MUD to manage small pieces of repository in a better way.

To work on Multi User Development …here the steps .…

• Create a shared directory on the network for Multi User Development ( MUD)
• Open the rpd to use in MUD . From Admin > tool> Options , setup the MUD directory to point the above directory.
• Define Projects within the rpd to allow multiple users to develop within their subject area or facts.
• Save and move the rpd to the shared directory setup in point 1.
• When users work in the MUD mode, they open the admin tool and start with MUD -> check out to check in the project they need to work on.
• After completing the development ,User check in changes back to the network and merge the changes.

For more detail study….refer the below websites for best further ( Detail) Information on MUDE

• Multi User Development of Repository by Venkatakrishnan
• Multi User Development  in OBIEE by Nico
• Oracle by Example – MUDE

Execute Direct SQL

Uses of ” Execute Direct SQL “( Direct Database Request)

We have seen this in Answers page .Under the subject area.  Lets know something more about this today…

In Answers with the help of this feature Administrator can debug some issues like….

1) Check physical connectivity to the database .

2) Check report or dashboard performance (Performance Tuning) ..etc…

It is not a good practice to allow this feature to Production users. There are several reasons ….

1) User can by-pass a data level security defined in the repository .

2) They can overload  production database

3) Users can delete some important database object.

4) They can see some other tables which are not available for them to see…etc…

We can disable ” Execute Direct SQL ” by the path below:

Answers > Settings > Administration > Manage Privilege .