Monday, July 30, 2012

Use File.> Import from Server with OCI 10g/11g selected (avoids need to change the Connection Pool later)
TNS Name = Data source name on Connection Pool dialog

TNS Name = polydata.world

Connection Pool

Require fully qualified table names on connection pool (needed for proxy account since it has only read access to tables)

Add caption

Create Primary Keys

Verify that primary keys were imported and identified in the tables. If not, add them.
Right-click table, select Properties
Select the Keys tab
If primary key is not marked or the wrong one is selected, modify the keys accordingly.

Create the Physical Diagram

Click Foreign Join button, then click Dimension table and drag to Fact table
For various DATES in the fact table, copy the TIME dimension 3 times with different alias names, then join to 3 different dates in the fact table (e.g., withdrawn_date, enrolled_date, dropped_date)

Business Layer

Use Complex Joins to join new items created in the Business Layer, if any
Aggregate items that can be summed
Use Rename Wizard to rename fields and tables

One table at a time (in on-line mode)
Rules must be ordered, or perform one rule at a time

Group columns with dummy columns, e.g.

--- Program Plan Begin ---

--- Program Plan End ---

Check "Use existing logical column as the source"
Enter the number 1 in the formula box

Presentation Layer

Group columns with subfolders

Create new Presentation Table as "– [Name]" (that's hyphen space name)
Creates a subfolder under the parent folder

It is also possible to do this with "->" in the Description, but this is less desirable because it is not visible in Administrator unless you are looking at the column's Properties

For Administrator convenience, use icons to relate items in Presentation and Business Model layers

Denormalized tables

Physical layer

Several subject-related denormalized tables may be pulled in together
Create an alias of each denormalized table
In the Physical Diagram, use a simple join from the original table to the alias
The alias becomes the "fact" table

Business Layer

Delete columns from the "fact" table that are not used for measures (all but the join column in most cases)

Presentation Layer

Delete alias table (assuming it does not contain measures)
Create different folders (subject areas) for different denormalized tables so that people don't try using them together. (While this would not be allowed in Answers, trying to do so returns an error message that is confusing for the user.)

Repository Documentation Utilities

BI Administration Tool > Tools menu > Utilities
For more information search Help

Rename Wizard

Rename columns
Remove underscore character
Change all column names so that first letter of each word is uppercase and rest is lowercase.

Generating and Deploying a Metadata Dictionary

Web-based XML file of information in the repository file.

Generating Documentation of Repository Mappings

Use this tool to document the tables, columns, etc in the repository file.
Produces csv or tab-delimited file.
Only columns that are in the Presentation Layer will be included in the report.
Run the utility after ALL columns are brought to the Presentation Layer and after running the Rename Wizard.
Run the utility before fields (ex. Keys, Extract Date, Maint Date) are deleted from the Presentation Layer.

Before moving tables to the Business Model or Presentation Layer verify that the Data Type is correct for the columns. OBIEE may define the column as a different data type than what was intended or used in the Warehouse.

Change the Date Data Type

Most date columns come into OBIEE with a Data Type of DATETIME which produces a date format that includes the date and the time.

To remove the time from the field change the Data Type to DATE. This change will push through from the Physical Layer through the Business Model and Presentation Layers.

Dates are brought in as DATETIME.

DATETIME = 1900/02/25 00:00:00
DATE = 1900/02/25

Columns defined as Number will come into OBIEE as DOUBLE which includes two decimal places. If this is incorrect for the column select INTEGER.

DOUBLE = 19000225.00
INTEGER = 19000225

Note: Column Properties which include Style, Column Format, Data Format and Conditional Format are applied in Answers.

Where are system-wide changes stored?

System-wide changes are stored in the Web Catalog and must be moved from development to production servers along with requests and dashboards. These files are stored in folders separate from requests and dashboards.

Create Hierarchies

1. Right-click dimension table and select Create Dimension at the bottom of the shortcut menu.

2. A Hierarchy is created using the name of the dimension table followed by the word Dim.

3. A Grand Total level and Detail level will be created. The Detail level will contain all of the columns of the dimension.

4. Create child levels using names that match your hierarchy. Right-click the Detail level, select New Object, Child level.

5. Create a child level below the lowest child level (this will contain the dimension key.) Move the dimension key to this level. Right-click this key column and remove the checkmark next to Use for drilldown.

6. Move other columns to their respective levels.

7. Delete any unneeded columns from any level.

8. Select columns within each level, right-click and select New Logical Level Key. Select all columns as keys if you want to see them in drilldown.

9. For any levels that should be skipped in the drill-down, select all columns in the level, right-click and remove the checkmark next to Use for drilldown.

10. At level two check for a Detail key that was automatically created. (Right-click level two, Properties, click the Keys tab). Highlight and delete Detail Key.

Hierarchy Aggregation

In the hierarchy, starting from the top and moving down. At each level below the Grand Total level, double-click the level. In the field Number of elements at this level: enter 10 and increase in increments of 10 through all levels in the hierarchy.

Hierarchy Errors

OBI Tool Metadata Errors and Solutions

[nQSError: 15001] Could not load navigation space for subject area Student Enrollment.

[nQSError: 15019] Table Student Class Section is functionally dependent upon level Institution Code, but a more detailed child level has associated columns from that same table or a more detailed table.

This message occurs when either a key is in the Total level or when the first child level below the Total level contains two keys. Right-click on the Total level and select Properties. The Keys tab should be grayed out. If it is selectable, check to see what is there and delete it. Go to the next level down, right-click, select the Keys tab. Only one Key should appear. Delete the key that does not belong.

http://www.oracle.com/technology/products/bi/index.html

Building Hierarchies With Columns In Different Tables

This first step is needed if columns for hierarchy reside in multiple dimension tables or a dimension table and fact table or because the data model contains only one dimension table and no fact table.

1. Create a new or second dimension table from an already existing table

2. Create an alias of the table in the physical layer.

3. Right-click on table in Physical layer, select New object > Alias.

4. Join the new dimension in the Physical diagram.

5. Select the data model, right-click Physical Diagram > Object(s) and All Joins.

6. Click the New Foreign Join button. Click the new dimension table, then click the Fact table.

7. If you are joining a new dimension table to itself then create joins between all of the keys in both tables.

8. Click the first key in the table on the left. Find the matching key in the table on the right. Hold down the CTRL key and click the next key on the left and the matching key on the right. Continue until all keys are identified in the Expression box at the bottom of the Physical Foreign Key window. Click the OK button.

9. A message “ a matching table key doesn’t exist…” will appear. Click Yes.

10. Check in changes.

11. Drag the new dimension (Alias) table from the Physical Layer to the Business Model.

12. Select the data model, right-click Business Model Diagram > Whole Diagram.

13. Use the Complex Join button between the two tables. No changes are made on the Logical Join window. Click OK. Close the Diagram Window.

14. The new dimension table now has a dimension table icon.

15. Check in changes. Save repository.

16. Cleanup on tables

Remove all “Fact” columns from the new dimension table.
Remove all dimension columns that don’t belong in the “Fact” table.

Assuming an item in the newly created dimension A that needs to be used for a hierarchy in a different dimension B:

Select items from physical layer table corresponding to A and drag to the B dimension in the Business Model.

Expand Sources for B table (click + ) then double-click on the B table source.

Click the Add button on the General tab.

In the box below name click the B table then click the Select button. This creates a join between those two dimensions.

Then move Items that you want in the B dimension from the Physical layer to the A dimension Source name.

This forces a logical join from the physical join between the dimension table and Fact table.

Changes Applied Through Answers

Formatting Data Values

Column Properties which include Style, Column Format, Data Format and Conditional Format are applied in Answers. When a certain format is desired for a column everytime the column is used then system-wide changes can be applied. System-wide changes can be saved at the column level or the data type level.

A perfect example for a system-wide change is the GPA (Grade Point Average). The field is defined with a Data Type of DOUBLE which gives you two decimal places but you need three decimal places in Answers and Dashboards.

Select the GPA column in Answers.
Select the Column Properties button.
Select the Data Format tab.
Click the Override Default Data Format checkbox
Change the number of Decimal Places.
Click the Save button and choose "as the system-wide default for "Student.GPA"

Request Filters

Protect Filters Passed through Dashboard

On the filter in the Answers Request, select Filter Options button > Protect Filter.

This ensures that the filter used in the request is not lost or overwritten by another filter or dashboard prompt that may supersede the request.

This option is only available if a value has been specified in the filter. If the filter item is set to "is prompted" then the Protect Filter option if not available.

Utilities

Relace Coulmn or table in logical table sources
Oracle BI event tables
Externalize strings
Rename Wizard
Update Physical layer
Repository Documentation
Generate matadata Dictionary
Remove unused physical objects
Aggrigate persistence wizard

Fragmentation

Fragmentation in OBIEE

We use fragmentation when we have fact or dimensional data in one or more different tables or data is splitted in different data sources. Then each logical table source represents one data segment.

For example, clients, in the first table are clients from A to M, in the second from M to Z.

Fragmentation of dimension

If our dimensional data is located on two or more physical tables in database (tables) or it is on separeted data sources then we can handle this by using UNION ALL views in physical layer.

However for testing this option we'll simulate this by using CHANNELS table. So we splitt it to a table CHANNELS_OTHER and CHANNELS. Fragmentation attrubur is CHANNEL_ID.

In our SALES fact table that we used here we use CHANNEL_ID from both tables CHANNELS and CHANNELS_OTHER.

Physical model, join:

SALES.CHANNEL_ID >- CHANNELS.CHANNEL_ID
SALES.CHANNEL_ID >- CHANNELS_OTHER.CHANNEL_ID

BMM:

Settings:

Test in Answers:

Result:

NQQuery.log:

We see that UNION ALL is generated.

If we choose:

NQQuery.log:

We see that the SQL is using only the second fragmented logical table source and the condition is applied only for that logical table source.

If we choose:

NQQuery.log:

If we now choose any other attribut (CHANNEL_CLASS) that is not CHANELL_ID which we used as a fragmentation key:

Then this condition is applied on both logical table sources CHANNEL and CHANNEL_OTHER:

Fragmentation of fact table

Let's split data for SALES 1998 in separate table SALES_HIST. Data from 1998 we leave in SALES.

Physical model, join:

SALES.TIME_ID >- TIMES.TIME_ID
SALES_HIST.TIME_ID >- TIMES.TIME_ID

BMM:

Settings (for a fragmentation key we choose CALENDAR_YEAR):

Test in Answers:

Result:

NQQuery.log:

If we choose:

NQQuerylog:

If we choose:

NQQuery.log:

If we now choose any other attribut (CALENDAR_MONTH_DESC) that is not CALENDAR_YEAR which we used as a fragmentation key:

Then this condition is applied on both logical table sources SALES and SALES_HIST:

In this post we showed how to combine different sources of information using fragmentation option. We see that at any moment we can see which source OBIEE takes while generating code.

OPAC View

OPAC views:-
An opaque view is in the physical layer a physical table that consists of a Select statement .
In the repository, opaque views appear as tables in the physical layer but the view does not actually exist.

Only repository variables can be used in the definition. An error will generate if a session variable is
used in the view definition.
Syntax:
CREATE VIEW <view name> AS <select statement>,

Friday, July 27, 2012

OBIEE Cache Issues

OBIEE Cache Issues

The OBIEE cache is enabled but the query is not being cached. There may be multiple reasons behind that; some of them is as follows;

Use of Non-cacheable SQL functions:
If any of the Non-cacheable functions are used in the request then the query will not be cached.
i.e CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, RAND, POPULATE

Non-cacheable Table:
If the query for the request makes any connection to a non cacheable table then the query/results will not be cached even if all other tables are set to cacheable.

Query execution Terminated:
While running the request on Presentation Server if the execution is forcibly cancelled or time out occurred, cache will not create.

Cache Configuration not enabled:
There is a cache enable/disable entry in NQSConfig.ini file. Cache will be enabled only if it is set to YES.

Bad cache configuration:
If the MAX_CACHE_ENTRY_SIZE parameter is set to a bigger value as compared to the actual storage space (DATA_STORAGE_PATHS) available on the drive then nothing can possibly be added to the cache.

Result set to Large to Handle by Cache:
The max row limit for cache is set in a parameter in nqsconfig.ini, named MAX_ROWS_PER_CACHE_ENTRY (default 100,000 rows) and MAX_CACHE_ENTRY_SIZE (default 1 MB). If the rows retrieved in the request exceeds the MAX ROW LIMIT or the data retrieved by the request exceeds the MAX CACHE ENTRY SIZE then the cache will not be created.

Query got a cache hit:
In general, if the query gets a cache hit on a previously cached query, then the results of the current query are not added to the cache. The only exception is the query hits that are aggregate “roll-up” hits, will be added to the cache if the nqsconfig.ini parameter POPULATE_AGGREGATE_ROLLUP_HITS has been set to Yes.

Thursday, July 26, 2012

Performance Increasing In OBIEE

Performance Increasing In OBIEE
While working on OBIEE every one need some tips to increase performance, I searched the net but not found helpful tips. Hence I thought putting some of the points together that will be helpful for you.
Following are the few points which improve the performance of OBIEE.

Alias Tables
Using Aggregate Navigation
Using Cache
Connection Pool
Turning Off Log Level
Using Database Hints
Using Where Clause

1.) Alias Tables
An Alias table (Alias) is a physical table with the type of Alias. It is a reference to a Physical table, and inherits all its column definitions and some properties from the Physical table. Alias Tables can be an important part of designing a physical layer
OBIEE doesn’t support the self-join, so we can use alias table to implement self-join in OBIEE.
The following is a list of the main reasons to create an alias table:

To reuse an existing table more than once in your physical layer (without having to import it several times)
To set up multiple alias tables, each with different keys, names, or joins
To help you design sophisticated star or snowflake structures in the business model layer.

To create Alias table in Physical Layer, right click on Table -> New Object -> Alias, select source table if required.
images 1

NOTE: In previous version we need to synchronize the alias table with the source tables but in OBIEE Alias will be automatically synchronized with source table, any change to Source Table will be immediately reflected in Alias Table.
2.) Using Aggregate Tables
All knows that when we create Summary Report then data from the fact table got roll up to the appropriate level & then showed to the customer. Oracle BI server lets you register aggregate (summary) tables that contain the pre-computed sums, averages and so on for a fact table, which it then uses in preference to rolling up the detail-level fact table if this would speed up a query.
We will be using aggregate tables in physical layer if you required to create the aggregate tables then you can use Aggregate Persistent Wizard.
We will import aggregate Fact & Dimension Table into physical layer..

We have the aggregate tables in physical layer; will use physical Diagram to create foreign key relationships between the aggregate fact tables, the aggregate dimension tables

We’re now at the point where we can map these aggregate tables to the existing logical tables in the business model layer
We need to identify the existing logical tables in the business model that have columns that correspond to the incoming data, and then drag the columns you want to match on over from the new, physical table.
We will create new Logical Table Source to map Logical Column to Physical Columns (from Aggregate table).

The final step is to tell the BI Server that this new data source for the units measure, is only valid Month, Sales Rep and Type Level.

Now, when a query comes in against the Dollars measure at the product Type or Customer Sales rep Level, the BI Server will use the D1_ORDER_AGG1 table instead of D1_ORDER2.
3.) Using Cache:

Cache in OBIEE:-
Cache is component hat improves performance by transparently storing data such that future requests for that data can be served faster. Like other application cache is not virtual memory in OBIEE. In OBIEE cache will be stored as file on Hard Disk in the form of files.
To use the cache we need to enable it, to enable cache we need to edit the NQSConfig.ini as well as need to make the tables cacheable.
Following are the parameters from NQSConfig.ini that will be used in enabling the cache:-
ENABLE
To enable the cache set the ENABLE parameter to YES.
DATA_STORAGE_PATHS
This parameter specifies one or more directory paths for where the cached query results data is stored and are accessed when a cache hit occurs. The maximum capacity in bytes, kilobytes, megabytes or gigabytes. The maximum capacity for each path is 4 GB. For optimal performance, the directories specified should be on high performance storage systems.
Each directory listed needs to be an existing, fully-qualified, writable directory pathname, with double quotes ( ” ) surrounding the pathname. Specify mapped directories only.
UNC path names (“\server.name.edusomefolder“) and network mapped drives are allowed only if the service runs under a qualified user account. To change the account under which the service is running, see the corresponding topic in the Oracle Business Intelligence Enterprise Edition Deployment Guide.
Specify multiple directories with a comma separated list.

MAX_ROWS_PER_CACHE_ENTRY
Specifies the maximum number of rows in a query result set to qualify for storage in the query cache. 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. When set to 0, there is no limit to the number of rows per cache entry.
MAX_CACHE_ENTRY_SIZE
Specifies the maximum size for a cache entry. Potential entries that exceed this size are not cached. The default size is 1 MB.
Specify GB for gigabytes, KB for kilobytes, MB for megabytes, and no units for bytes.
MAX_CACHE_ENTRIES
Specifies the maximum number of cache entries allowed in the query cache. Limiting the total number of cache entries provides another parameter with which to manage your cache storage. The actual limit of cache entries might vary slightly depending on the number of concurrent queries.

OPULATE_AGGREGATE_ROLLUP_HITS
Specifies whether to aggregate data from an earlier cached query result set and create a new entry in the query cache for rollup cache hits.

SE_ADVANCED_HIT_DETECTION
When caching is enabled, each query is evaluated to determine whether it qualifies for a cache hit. A cache hit means that the server was able to use cache to answer the query and did not go to the database at all. The Oracle BI Server can use query cache to answer queries at the same or later level of aggregation.

Following are the parameters used to make Table Cacheable:-
To make table cacheable we need to edit the table properties.

Cacheable
To include the table in the Oracle BI Server query cache, select this check box. When you select this check box, the Cache persistence time settings become active.

Cache never expires
When you select this option, cache entries do not expire. This could be useful when a table will be important to a large number of queries users might run. For example, if most of your queries have a reference to an account object, keeping it cached indefinitely could actually improve performance rather than compromise it
Cache persistence time
How long table entries should persist in the query cache. The default value is Infinite, meaning that cache entries do not automatically expire. However, this does not mean that an entry will always remain in the cache. Other invalidation techniques, such as manual purging, LRU (Least Recently Used) replacement, metadata changes, and use of the cache polling table, result in entries being removed from the cache.
If a query references multiple physical tables with different persistence times, the cache entry for the query will exist for the shortest persistence time set for any of the tables referenced in the query. This makes sure that no subsequent query gets a cache hit from an expired cache entry.
4.) Connection Pool:-

Most of the times not much thought are given to defining connection pools while developing RPD. Improperly defined connection pool would affect the OBIEE performance and user experience. Here are some of the things to consider while defining connection pool.

Change the default maximum connections. The default is 10. Based on your system usage change the value that is more realistic to your system usage
Create a separate connection pool for execution of session variables
Create a separate connection pool for the execution of aggregate persistence wizard. Remember that you need to give the schema user owner credentials for this connection pool as the wizard creates and drops tables
If need be create a separate connection pool for VVIPs. You can control who gets to use the connection pool based on the connection pool permissions.

5.) Turning Off Log Level:-
Though query logging has immeasurable development value, do not use this for regular production users as the runtime logging cost is extremely high. Every log item is flushed to the disk, which in turn hurts query response. Also, note that the query log files are not created on per user or query basis, there is only one query log per OBIEE server and it would have exclusive lock on the log file, which kills concurrent performance. The log file is NQQuery.log which resides in OracleBI Directory.

Logging Level	Information That Is Logged
Level 0	No logging.
Level 1	Logs the SQL statement issued from the client application.Logs elapsed times for query compilation, query execution, query cache processing, and back-end database processing. Logs the query status (success, failure, termination, or timeout). Logs the user ID, session ID, and request ID for each query.
Level 2	Logs everything logged in Level 1.Additionally, for each query, logs the repository name, business model name, presentation catalog (called Subject Area in Answers) name, SQL for the queries issued against physical databases, queries issued against the cache, number of rows returned from each query against a physical database and from queries issued against the cache, and the number of rows returned to the client application.
Level 3	Logs everything logged in Level 2.Additionally, adds a log entry for the logical query plan, when a query that was supposed to seed the cache was not inserted into the cache, when existing cache entries are purged to make room for the current query, and when the attempt to update the exact match hit detector fails. Do not select this level without the assistance of Technical Support.
Level 4	Logs everything logged in Level 3.Additionally, logs the query execution plan. Do not select this level without the assistance of Technical Support.
Level 5	Logs everything logged in Level 4.Additionally, logs intermediate row counts at various points in the execution plan. Do not select this level without the assistance of Technical Support.
Level 6 and 7	Reserved for future use.

6.) Using Database Hints
images 5

Database hints are instructions placed within a SQL statement that tell the database query optimizer the most efficient way to execute the statement. Hints override the optimizer’s execution plan, so you can use hints to improve performance by forcing the optimizer to use a more efficient plan.
Hints are database specific. The Oracle BI Server supports hints only for Oracle 8i, 9i, and 10g servers.

These are the following objects where you can apply the hint

Physical Complex Join
Physical foreign key
Physical Table – Object type Alias
Physical Table – Object type None

Hints that are well researched and planned can result in significantly better query performance. However, hints can also negatively affect performance if they result in a suboptimal execution plan.
You should only add hints to a repository after you have tried to improve performance in the following ways:

Added physical indexes (or other physical changes) to the Oracle database.
Made modeling changes within the server.
Avoid creating hints for physical table and join objects that are queried often.

To create a hint
1. Navigate to one of the following dialog boxes:

Physical Table—General tab
Physical Foreign Key
Physical Join—Complex Join

2. Type the text of the hint in the Hint field and click OK.
7.) Using Where Clause Filter
The WHERE clause filter is used to constrain the physical tables referenced in the logical table source, If there are no constraints on the aggregate source, leave the WHERE clause filter blank.

Filter applied in WHERE Clause will applied on physical table that will be result in restricted or required data, other data which is not necessary, will not be fetched each time when there is query on that table.