OBI Server is not mere SQL Generator:
SQL is generated by Oracle BI Server against the data sources using the data source connection, information from the repository, and database-specific parameters stored in a DBFeatures.ini file. Thus, Oracle BI Server is not just a SQL generator. It determines the best source and the optimal way to access data. In some cases, Oracle BI Server takes on operations that are more efficient for it to perform rather than the host data source.
Purging BI Server Cache:
• BI Server cache is stored in the directory: d:\Oracle\OracleBIData\cache • Create the script called PurgeBICache.sql with following command:
Call SAPurgeAllCache ();
• Run the following command to invoke the script and clear the cache: nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s PurgeBICache.sql -o purge_clear.log
• This will clear all the BI server cache entries for all the users.
Purging BI Presentation Server Cache:
Presentation cache stores the finished report output in the presentation server. This is different from BI server cache. BI server could be leveraged by multiple users and reports depending on whether they are accessing similar or subset of the cached data, though from a different reports.
Presentation cache is report specific and user specific cache entries residing in the D:\OracleBIData\tmp\ folder.
The expiry of this entry can be governed by following instance-config.xml file in <
CacheMinExpireMinutes Default 10
CacheMaxExpireMinutes Default 15
Authentication Options:
Oracle BI Server supports the following authentication types:
Operating system
When operating system authentication is enabled, users connecting to Oracle BI Server should not type a user ID or password at the logon prompt.If a user enters a user ID and (optionally) a password at the logon prompt, that user ID and password overrides the operating system authentication and Oracle BI Server performs the authentication.
To configure operating system authentication:
1. Enable operating system authentication in the NQSConfig.ini file.
2. Create a user in your repository named identically to the user ID in the trusted Windows domain.
3. Assign the group membership and rights that you want the user to have.
External table
• Instead of storing IDs and passwords in a repository, maintain lists of users and passwords in an external database table.
• Use Oracle BI session variables to get values.
LDAP
Database
Authenticates users through database logons
• To set up database authentication:
– Store user IDs (without passwords) in a repository.
– Import database to the repository.
– Specify authentication database in NQSConfig.ini.
Internal
Maintain lists of users and passwords in the repository using the Administration Tool.
• Oracle BI Server authenticates against this list unless:
– Another authentication method has already succeeded
– Database authentication is specified in NQSConfig.ini
Order of Authentication
1. Operating system (OS):
– No logon name
– Turned on in NQSConfig.ini
2. LDAP or external database table
– Populates session variables
3. Internal or database
Bypassing Oracle BI Security
It is possible to bypass Oracle BI Server security and rely on the security that is provided by issuing user-specific database logons and passwords.
Below is the obiee naming best practices:
The rules for naming of items in Analytics:
a. Be consistent – follow a format and stick with it
b. Be descriptive – make the meaning clear in the name
c. Be concise – don’t go overboard with descriptive names
d. Include Units of Measure – for metrics and numerical attributes
Try to include the aggregation rule in the name of the metric to allow easy identification of what the metric is doing.
· Counts can use the # sign as in # Cases
· Averages or ratios can be used as in Avg. # Open Days
· Sums may be shown as Totals as in Total Shipped Units
· Mins/Maxes as in Max # Shipped Units
Be very precise in the language to convey the meaning of the metric and what it is measuring:
· # Cases Opened means something different that # Open Cases. The former refers to a number that occurred in the past that will not change as cases close, where the latter is a current snapshot of open cases that will eventually change.
For Logical tables, the name must represent the logical role
· Account Monthly Average Sales is a different logical entity than Account Weekly Average Sales, and as such there should be two distinct logical tables with these names. Similar examples may include: Adapted Account vs. Registered Product and Manager vs. Employee
A general rule of data modeling is to not name attributes with the name of the entity. For example, in an Account table, don’t have columns such as
· Account Name
· Account Address
· Account Phone #
This is redundant because the fields are already organized in the Account table. Instead, just have the following fields:
· Name
· Address
· Phone #
An exception can be made for attributes whose proper name happens to include the table name. For example, Account Number is an attribute name that should not be split, as Number by itself typically does not have any meaning.
Consider adding certain units of measure to a metric to clarify what is being measure to a user. A metric called
· Coverage Met does not convey enough information. Is Coverage Met measured in weeks or days or %? Try 13 week Coverage Met or # Coverage Met
For currency metrics, consider appending the unit of currency to the metric name.
· For example, instead of Sale Amt, consider using Sale $ or $ Sale
· Possibly consider using Sale Amt USD for multi currency environments.
When Complex Join in Physical Layer ?
- If the repository file name contains a space, enclose the file name within single quotation marks.
- If multiple repositories are specified as the default repository, the last one listed becomes the default repository.Alerts for the Oracle BI Delivers :
This is about creating alerts for OBI delivers.
Go to Destination Tab – > Check Interactive Dashboard. It is very simple.
Interactive Dashboard
Active iBots will appear in a Dashboard Alerts section as well as on the Alerts! page. A link to this summary appears together with the application navigation links when new iBots are delivered. After these are cleared on the Alerts page, the alert link is removed.
View the original article here
Oracle Support released a patch for the [nQSError: 37005] Transaction update failed error.
This was “randomly” generated in OBIEE 11g during the check-in, in the Administrator Tool, of an online opened repository and it was very
annoying.
The patch number is 9884975 and can be downloaded from the Oracle Support site.
You have to ask the patch password by opening a new Service Request in order to download it.
Cache:
Oracle BI Server can be configured to maintain a disk-based cache of query results sets:
• Saves the results of queries in cache files
• Enables Oracle BI Server to satisfy subsequent query requests without having to access back-end databases
Overhead because of Caching:
• Disk space
– Query cache requires dedicated disk space.
• Administrative tasks:
– Set the cache persistence time appropriately.
– Purge the cache when necessary.
• Keeping the cache up-to-date:
– Evaluate what level of noncurrent information is acceptable.
– Remove stale data.
Cache Management Techniques
• Configuring the cache parameters
• Setting caching and cache persistence for tables
• Using the Cache Manager
• Inspecting SQL for cache entries
• Modifying the Cache Manager column display
• Inspecting the cache reports
• Purging the cache entries manually using the Cache
Manager
• Purging the cache entries automatically
• Using event polling tables
• Seeding the cache
Cache Hit Conditions
A cache hit occurs only when certain conditions are met, such
as the following:
• Query WHERE clause constraints need to be equivalent to the cached results or to a subset of the results.
• All the columns in the SELECT list of a new query must exist in the cached query or they must be able to be calculated from the columns in the query.
• Join conditions must be equivalent.
• Queries that request an aggregated level of information can use cached results at a lower level of aggregation.
