OBIEE Express

OBIEE 10g Web Catalog "Best Practices"

2011-06-01T04:16:00.000-07:00

Originally Posted by Ritman Mead Consulting.
http://www.rittmanmead.com/2010/01/obiee-10g-web-catalog-best-practices/

I was working with an Oracle Partner last week who asked me to QA their OBIEE project. One of the areas I took a look at was their web catalog setup, and I was asked to come up with some “best practices” around web catalog design and maintenance. As my speciality is more the server and data-side of OBIEE, I asked around some of my colleagues and pulled together this list of web catalog best practices. Thanks to Pete, Ragnar, Mike and Borkur for their tips, and of course if you’ve got any other best practices, or disagree with what we’ve come up with, just add a comment and we’ll incorporate the feedback. Anyway, here we go…

1. When starting on a new project for a new client, make sure you create a new web catalog.

This goes for the RPD as well. As John Minkjan pointed out a few months ago, it’s not good practice to build off of the Paint or Sample Sales demo RPD, and the same goes for the web catalog. To create a fresh, new web catalog, first create an empty directory within the $ORACLEBIDATA/web/catalog directory where the BI Presentation Server is installed, like this:

Then edit the $ORACLEBIDATA/web/config/instanceconfig.xml file to point to this new directory.

Then stop and start the BI Presentation Server service (from the Services applet in Windows, or using the run-saw.sh script under Linux/Unix). When the Presentation Server sees the empty directory, it will create the necessary folder structure within it, giving you a nice, clean web catalog to work with.

You can now create reports, dashboards and so on in here, without any of the demo items from Oracle cluttering things up.

2. Create catalog groups and folders for each area of analysis within the web catalog.

In terms of the folder structure within the web catalog, we tend to leave the users folder to itself (the Presentation Server creates a folder for each user that registers in the web catalog), the system folder to itself (the Presentation Server looks after this), and concern ourself instead with the shared folder.

How you structure sub-folders under the shared folder really a question of how the system will be used. If you’ve got just a single user community for your system, you might create one folder per dashboard, and place all the requests used by that dashboard within that folder, keeping things simple for when you want to apply security. Typically though, you will have a number of departments or functions that are using OBIEE, and so you will usually want to create web catalog “groups” and “group folders” per department or function. The simplest way to create these groups is to use the Add/Edit Group function with the web-based Presentation Services Administration screen.

According to the instructions on this screen, it should also create the corresponding group folder as well, but when I tried it the folder wasn’t there. Therefore you may need to separately create the group folder using the Catalog Manager, just under /Shared, to hold that group’s dashboards and requests.

3. Create a “Corporate MI” folder for “gold standard” reports

If you have a “Corporate MI” portal where individual departments publish and maintain data, you might want to create another group folder to hold these dashboards. Then, departments can develop their own “private” dashboards and requests in their own group folders, and promote them to the Corporate MI dashboard using an approvals/change control process. These dashboards and requests can then become “gold standard” or “kite marked” authoritative sources within the organization, whilst giving departments the ability to create reports specifically for their own purpose (this blog post by Phil Wright contains some good suggestions around report governance, user acceptance and BI portals in organizations).

4. Enable drop-down menus for dashboards within each catalog group.

Each department can then set up it’s own dashboards, requests, alerts and filters within its own shared, group folder (or indeed, create subfolders for specific areas of analysis). If departments end up creating lots of dashboards, the Presentation Server will automatically show them in a drop-down list with the group folder as the menu name once the number of visible dashboards for a user is fifteen or more. You can control this setting by adding a <DashboardMaxBeforeMenu> tag to the instanceconfig.xml file; I typically set it to 1 on real projects so that all departmental dashboards are shown in drop-down menus.

Then when users view the dashboard, all of the dashboards are clearly grouped by function.

Note that dashboards can only normally be placed in these top-level, group folders (within the _portal subfolder), and you get the choice of which group folder in which to place your dashboard when it is initially created.

These group folders can then be used to hold the requests associated with each group’s dashboards. If you like, you can store saved filters either directly in these group folders, or in a special “Filters” sub-directory under the corresponding group folder.

5. Enable permissions and security within the web catalog.

If you followed the process above, you will have web catalog groups and corresponding group folders in which you will store their dashboards. reports, alerts and filters. To secure access to these groups, ensure that their names correspond to your groups in the RPD or your LDAP server, and then the presentation server will assign them to the corresponding web catalog groups when the user is authenticated using the BI Server. If you’ve organized your web catalog content into these group folders, applying security should be reasonably straightforward as you’ll assign the same permissions to all objects within each group folder. Use the Catalog Manager to set permissions, where you can select “Apply Recursively” to set these permissions on all objects within the group folder.

Make sure you only ever assign permissions against groups, rather than users, to keep things managable. Even so, setting up web catalog security can get quite complicated, especially once you get down to individual dashboard pages and the like. This posting by Kurt Wolff goes through some of the options and suggests some approaches to make it work.

6. Set subject area permissions using the BI Administration tool, not the Presentation Services Administration screen.

The Presentation Server Administration screen can be used to set access to subject areas, stopping users from creating reports using these subject areas or running reports that reference them.

My preference though is to set these permissions at the RPD level instead. This keeps all data access permissions in one place (row-level and subject/table/column-level security) and also makes it easy to control access through the groups in your RPD or LDAP server. When you change permissions on these RPD objects you may need to restart the Presentation Server before the restrictions pass through correctly to the Presentation Server – until you do this users who don’t now have permission may still see the subject area listed and may be able to see the table listing, but each of the tables will be empty of columns.

7. Control access to Presentation Services functions using the Presentation Services administration screen.

Subject area security excepted, the Presentation Services administration screen lets you control whether a user or group can access Answers, Delivers or much lower-level items of functionality, such as which particular views they can include in their requests.

Again, define groups in the catalog for profiles of users, and use these groups to assign privileges in the web catalog. You can also assign permissions on BI Publisher functionality using the Presentation Server administration screens. Also, as Jeff McQuigg points out, if you’re currently only providing dashboards to users and someone suggests turning Answers on for them, be aware that this might mean that your RPD might need some additional thought before you let users loose on it.

8. Back up your web catalog regularly, and use the Catalog Manager for archiving/restoring catalog entries

You can back-up your whole web catalog by zipping it up and storing it on a backup device. To restore it, unzip the backup and copy it back in its entirety. If you want to archive or backup individual requests, dashboards or group folders, use the Catalog Manager application and select the Archive / Unarchive options.

Whatever you do, don’t filesystem copy elements of the web catalog and then try and copy them back, as you may hit problems around permissions that only manifest themselves some time later. Also consider using the Content Accelerator Framework (CAF) plug-in to the Catalog Manager to copy reports, dashboards and RPD calculations from one dashboard to another, although as Kevin McGinley points out, this doesn’t make CAF an environment migration tool.

9. Implement the Usage Tracking subject area and reports to monitor web catalog usage

OBIEE 10g now ships with a set of scripts, a predefined RPD and web catalog that you can import into your project to provide statistics on dashboard and request usage. This Oracle by Example article sets out how to install Usage Tracking and once you’ve done so, you can get a good handle on what requests are used most and which users might be having problems. Combine the usage tracking data with ibots and time-series functions, and you can start to generate alerts when key reports are taking longer to run that normal, letting you fix problems with performance before users can report it to you.

10. Implement an “impact analysis” system for your web catalog entries

One thing that’s lacking “out of the box” with OBIEE 10g is any way of measuring the impact, on web catalog entries, from changes to the RPD. You can export a list of reports, subject areas, columns and tables from the Catalog Manager, which gives you a central list of what RPD items are used by what reports.

You can then view this list on screen, or output it to a file.

From this list you can then at least see what reports are going to be affected by an RPD change. Within Rittman Mead we have developed an ApEx application that takes this file as an input, parses the RPD and allows us to do an automatic impact analysis; when we get a moment we’ll post details on here together with details on how you can use it on your projects.

So, there’s my “starter for 10″ in terms of web catalog best practices. Do you have any others that you can share? Do you disagree with any that we have come up with, or have a better way to achieve their aim? Have we missed anything off? Just add a comment and we’ll incorporate it into the posting.

Simplifying Migration Process – Changing Environment Specific Variables in RPD

2011-05-30T00:40:00.001-07:00

When it comes to migrating repository file between environments (Dev – TEST-Prod), one of the common questions from OBIEE environment administrators is if there is a way to change the connection information without having to change them manually in all the places.

Creating repository variables for DSN and DSN Username solves the issue to an extent. However, imagine having to deal with changing multiple DSNs and their respective usernames. Moreover, there is the setting password to the DSN username. One way to automate the setting of the variables is to store the values in a file and set them via admin tool command line mode.

First, define repository variables that can be used in the connection pool.

Reference the DSN information in the connection pool.

Now create a control file SetVariables.txt with the environment specific values.

_______________

‘To Open rpd – Open <rpdname> <Administrator User> <administrator password>

Open YourRpd.rpd Administrator SADMIN

‘Setting OLAP DSN variable

SetProperty ”Variable” ”OLAP_DSN” Initializer ” ‘QA_DSN’ ”

‘ Setting OLAP DSN Username variable

SetProperty ”Variable” ”OLAP_DSN_USER” Initializer ” ‘QA_User’ ”

‘Setting OLAP DSN User QA database password

SetProperty “Connection Pool” “AppDW”.”Connection Pool” “Password” “QAPassword”

Save

Exit

______________

Create one per environment.

In the command line run the following command:

AdminTool.exe /command SetVariables_QA.txt

And the repository is now prepped for the other environment (QA in this case).

OBIEE Presentation Layer Design Principles/Best Practices

2011-05-30T00:14:00.001-07:00

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 Presentation Layer.

Principle 1.Subject Areas

Do not include all presentation layer objects in a single subject area (Presentation Catalog). While it is possible but still not recommended approach.

Principle 2. Development with End Users in Mind

Always develop the presentation layer so that end users are able to understand and use it.

Principle 3. Role Based Subject Area.

It is always good idea to create presentation catalog to suit the needs of individual users or user types. For ex. create a separate presentation catalog for sales managers because they need to see only an overview of an organization.

Principle 4. Presentation Tables

Presentation Tables should consistent across presentation catalog.

List Dimension tables first.
Do not mix dimension and fact columns in the same table.
Apply consistent ordering and naming conventions for tables and columns across catalogs.
Include the words “measure” or “fact” in the names of the fact presentation tables.

Principle 5. Rule of Seven

Keep presentation catalogs small and easy to understand by limiting the number of tables to seven.

Keep Presentation catalogs small and easy to understand by limiting the number of columns to seven.

Principle 6. Fact Tables

Try to limit the presentation catalogs so that there are no more than a couple of fact presentation tables in each catalog. It is important to avoid situation in which there are multiple join paths existing within one presentation catalog.

Principle 7. Implicit Fact Columns

If you have multiple fact presentation tables, it is always best practice to assign an implicit fact column. Implicit facts come into play when an Answers report contains columns from more than one logical dimension table and no explicit facts.

Principle 8. Canonical Time

Canonical time is a useful way for allowing users to report for a specific period of time across multiple star schema. If you use canonical time ,make sure that the corresponding time presentation table is given a very generic name and that name is consistent across all the presentation catalogs.

It should be the first table in a catalog.

Principle 9. Secondary Time Dimension

This function enables users to build time reports for specific star schema.

Secondary time dimensions can be given their own presentation tables further down the list.We can place all secondary time dimension objects into a single presentation table.

Principle 10. Nesting Presentation Table

Prefix presentation table names with a hyphen to group common objects together into sub folders.

Principle 11. Presentation Object Names & Description

Use the Alisa tab to keep track of prior names.

Use the default option that synchronize the presentation column name with the underlying logical column name.

Use only logical, business -oriented names ( rather than physical object names) in the presentation Layer.

OBIEE Physical Layer Design Principles/Best Practices

2011-05-30T00:13:00.000-07:00

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.

Import Tables

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

Physical Layer Alias Table

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

Cache Property of the tables

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

Physical Layer Multiple connection Pool

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.

COnnection Pool Property

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

OBIEE BMM Layer Design Principles/Best Practices

2011-05-30T00:11:00.000-07:00

OBIEE BMM Layer Design Principles / Best Practices

Today we will discuss the design principles for BMM Layer.

Principle 1. Use Multi User Development Environment

Use the Multi- User Development facility if there are multiple developers. Multiple developers to connect “online” to the same repository file and making changes is not recommended.

Multi User Development allows user to define a series of projects within the repository file ,where each project is a subset of the entire repository .If developers want to make changes , they can check out a project to a local machine make and test the changes,and then check the modifications back into the master repository file.

Principle 2. Always run Global Consistency Check before releasing a repository.

Whenever we make changes to a repository ,always be sure to run Global consistency check. It is bad practice to release a repository that still contains consistency check errors. In some cases, consistency errors prevent Oracle BI Server from loading the repository. Use the Consistency check manager to identify and debug check messages.

Principle 3. Separate Business Model

Even if you have only a single data source or schema in the physical layer, or you have only one physical data source for the repository, it is still good practice to break out the physical objects into multiple business models in the BMM layer to represent the independent areas of functionality.

Principle 4. Logical Tables

When building logical tables, do not merge multiple dimension tables into a single logical dimension table,and do not merge multiple fact tables into a single logical fact table.

Having multiple logical fact tables also makes it easier to create well defined projects for Multi User development.

It is also a good practice to prefix logical table names with either Dim-, Fact- ,or Fact Compound -.

This allows you to easily see how the tables are being used. It also groups the tables in the business model, so that facts are groups with facts, dimensions with dimensions and so on.

Principle 5. Time Dimension

There are few things to keep in mind in time dimension factor.

Always must ensure that time Dimension hierarchy is built correctly and the logical level of each time- logical table source is set correctly
If there are multiple time dimensions within the business model, for consistency, make sure that all time dimension logical table contains the same columns and general structure. This is good for reporting purpose.

Principle 6. Logical Fact & Dimension table columns

Always assign a primary key for logical dimension tables. All logical dimension columns should be renamed in a way that is meaningful to users.
Bring only required columns in to the BMM layer for reporting.
Do not assign logical primary key for logical fact tables.
Create meaningful name for measures
Set aggregation rule for every logical fact columns.
Create “dummy” measures to group facts.

Principle 7. Logical Joins

Use only logical(complex ) joins in BMM layer. And always accept default properties when creating joins.

Principle 8. Calculated Measure

Be very careful when building calculated measures.

Use logical columns for calculations that require an aggregation rule that is applied before the calculation.
Use physical columns for calculations that require an aggregation rule to be applied after the calculation.

Principle 9. Aggregates

Few important things to keep in mind about Aggregates.

Try to ensure that each aggregate table has an effective summary ration with underlying detail.
Ensure that the logical level of every aggregate logical table source is set correctly.
Always test to ensure that aggregates tables are being used as expected.
If an aggregates is not used ,try changing the number of elements on one of the related logical dimension levels.

Principle 10. Dimension Hierarchies

In Dimension Hierarchies few things are very important to keep in mind

It is best practice to create dimensional hierarchy for every logical dimension table in BMM layer.
All Dimension must have at least two levels : the total level and detail level.
If you are creating Dimensional Hierarchy manually, be sure to check Grand total level for the Total Level.
Use Update Row Counts or Estimate Levels to set the number of elements for every level of every Dimension Hierarchy.
Think about the experience of user when enabling drill down.

Principle 11. Avoid Snowflake schema

When there is Snowflaking in physical model,We should try to avoid Snowflaking in BMM layer and build models that use only star schema .

Use WHERE clause filters to help avoid using opaque views or complex joins in the physical layer.

Authentication In OBIEE

2011-05-30T00:03:00.000-07:00

OBIEE Support four types of authentication.

1)LDAP Authentication : Users are authenticated based on credentials stored in LDAP.This is the BEST method to do authentication in OBIEE and it supports company’s Single Sign On (SSO) philosophy as well.

2)External Table Authentication : If users belongs to multiple groups ,that information is being pulled from the data base scheme table.This usually implemented with LDAP Authentication

3)Database Authentication: The Oracle BI Server can authenticates user based on database logins. If a user has read permission on a specific database.Oracle BI Presentation Services authenticates those users

4)Oracle BI Server User Authentication: User are directly crated in OBIEE and are authenticated against those credentials. This is the worst authentication method if company has larger then few people using the system. Believe me Maintenance is a nightmare here.

Two authentication methods are usually combined together.like LDAP and external table authentication.

And as i said earlier LDAP and/or LDAP with external table are BEST authentication(Again each case is different)

Oracle BI Server User Authentication is not quite popular as it has its support/maintenance issue associated ,once system grows beyond certain users

Hide dashboard prompts/Section in OBIEE

2011-05-10T23:49:00.000-07:00

There is a way to hide the dashboard prompts in OBIEE.

But, need to have a separate section containing this prompt.. that’s reason i made my article title as: prompt/Section

Here is the way..

1. Edit Dashboard, go to Properties of Section > Format Section..

2. Expand Custom CSS Style Options (HTML Only) then select Use Custom CSS Style check box… and write the following code as shown:

display:none;

It's more effective then collapsible option..

One Dimension – Multiple Hierarchies

2011-05-09T01:44:00.000-07:00

There was a question yesterday on the forums regarding the usage/availability of Multiple Hierarchies for a single dimension. I thought i would blog about it since there is very little documentation on this. Now let us take an example and try to understand what multiple hierarchies are and where they are used. Classic example for this would be the Time Dimension. In time dimension we can have a hierarchy based on Calendar Year, Quarter, Month and Day and the Fiscal Year, Quarter, Month and Day. BI EE can accommodate such possible multiple hierarchies within the same dimension. Let’s try to build one for a Product Dimension. There are certain ground rules for building multiple hierarchies for the same dimension.

1. There must be a unique key for the dimension at the lowest level.

2. This unique key must be shared by all the multiple hierarchies i.e. all the hierarchies should have their last levels as a level with the unique key.

3. All the hierarchies should roll up to the top Total Level. And Top Level attributes should not have a Logical Key defined.

In our case, we have a unique key called DIMENSION_KEY which would form the last level. Our aim is to build 2 hierarchies like this

               Total Level
   Pack Size                  Category
   Products                    Sub-Category
               DIMENSION_KEY
i.e
Hierarchy1 = Total Level -> Pack Size -> Products -> Unique Dimension Key
Hierarchy2 = Total Level -> Category -> Sub-Category -> Unique Dimension Key
As you see above DIMENSION_KEY is a shared level by both Products and sub-category. Now let’s start building the hierarchy.

Once we have reached till the 3 level for both the hierarchies, create a child level for the level Sub-Category. The child level would be DIMENSION_KEY.

After this create a Shared Child Level for the Products level and use the DIMENSION_KEY for this shared level.

Now we have basically created a dimension containing 2 hierarchies. Just test it out to see whether you get the proper drill downs.

Changing Default views of Compound Layout

2011-05-05T03:07:00.000-07:00

changing the default views of Compound Layout in Answers. So, basically if you go to answers and create a new report, you would find that by default Compound layout would have 2 views included i.e. Table and Title views as shown below

But what the customer basically wanted was to include a filter and table view by default (i.e. filter view instead of title view). The method is given in the bookshelf. But unfortunately it does not work as specified due to a documentation bug. In order to make this work, go to {OracleBI}\web\msgdb\messages folder and you would find a file called answerstemplates.xml. Copy this file to {OracleBIData}\web\msgdb\customMessages. If you don’t have these folders then create it. Now edit this file and search for kuiCriteriaDefaultViewElements. This is the message that handles all the default values possible in Answers. Now add the following tags to this message after the HTML tags,

Once this is done restart your presentation services. Now you would see that by default you would have got Table and Filter views within your compound layout.

If you notice, the methodology above is same as the one that we followed for enabling the sorting of reports in Dashboards. So, use this methodology when you want to modify any custom XML messages.

Import Through Server

2011-05-03T22:26:00.000-07:00

If you open the Administration Console and go to import, you would basically find 4 options. They are

1.   Import from Database
2.   Import through Server
3.   Import from Repository
4.   Import from XMLA

The options 1,3 and 4 are pretty self explanatory. But so far i have never used the Import through Server option. This option again has some interesting features. And this works only if you have open a repository in the online mode. Let us try to understand this here. Consider that we have 2 machines A and B as shown below.

So in effect an user in Machine B is opening the repository in the server machine A in online mode. So, let’s see what happens when we open Import from Database.

As you see when we click on import from Database it shows all the set of System DSNs that have already been created in Machine B(local to Machine B). So, now lets see what happens when we click on Import through Server

The list of DSNs have changed, havent they? So what Import through Server does is it exposes the Machine A’s DSNs, TNSNames entries etc to Machine B. So, any user who is involved in the repository creation process does not have to create local DSNs or TNSNames entries. All they have to do is to connect to the repository on the Server(in Machine A) in online mode and then click on Import through Server. This is very much used in the Multi User Development of the Repositories.

How to reduce the space between multiple pivot tables placed side by side

2011-05-02T22:35:00.001-07:00

1. Provided that s_oracle10 is in use; backup the following files before editing them
- C:\OracleBI\web\app\res\s_oracle10\b_mozilla_4\views.css
- C:\OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics\res\s_oracle10\b_mozilla_4\views.css

2. Set the following section in both files above:
.PTSectsTable

{
border:none;
border-spacing:0px;
padding:0px;
margin:0px;
}

3. Restart the OC4j and the Presentation server

Setting LOGLEVEL from Answers

2011-05-02T02:52:00.000-07:00

Normally, we check query in NQQuery.log file or from Answers also we can check the query directly.

Difference is: NQQuery.log contains all the queries and need to identify them by time the report run.

If it’s directly from Answers (go to Settings > Administration > Manage Sessions), log available for every report runs until the query is changed.

For every query of report there will be created one cursor, by accessing the cursor you can see the physical query generated by BI server.

If we are unable to see the log file or if we find any error message saying that, No Log found while trying to watch the query in log the first step in the mind is to check the LOGLEVEL. When creating rpd by default, loglevel takes 0 values for Administrator user.

If it’s set to 0, we go for 2 or 3 accordingly, such that, the query will be appeared.

For suppose, if you are accessing presentation service of another system and unable to see the query. You need not to set the LOGLEVEL=2 in the rpd presented in another system.

I am assuming that all cursors are closed and LOGLEVEL is zero for particular user.

Log into answers with user who has LOGLEVEL =0, run any report then go to Settings > Administration > Manage Sessions. Click on View Log option.

You will have error message or you may open the view log but you can’t find the query run at this moment because of loglevel – “0″. (To test this error message, you better delete the content of NQQuery.log completely but before doing this you need to stop your BI Server service then clear the content and save file again start BI Server service)

Close the window as well as cursors

Just go to advanced tab of particular report.

Scroll down to see the option: Prefix

Here write SET VARIABLE LOGLEVEL=2; (semi-colon at the end is must)

Now click on Results tab to re – run the report.

Now you go Settings > Administration > Manage Sessions

Click on View Log to view the query. Now you are able to see the query and according to the LOGLEVEL – 2

Go back to Advanced tab, observe the Logical query.

Before SQL, you find the text you written in Prefix field.

By mentioning the semi colon, BI Server executes these statements one after another.

This post explain you, how to set loglevel from answers itself, without going to rpd,

This process overrides the loglevel set in the rpd.

Renaming Dashboards in OBIEE

2011-04-29T06:04:00.000-07:00

There are 2 ways to rename the shared dashboards.

1. Using Catalog Manager

2. From Answers admin tab.

Using Catalog Manager:

1. Log in to catalog manager and navigate on shared folders and select the Group Folder under which you have created that dashboard.

2. Navigate till you find _Portal folder under the shared folders

3. Select a dashboard that you want rename from the right side pane, right click and select Smart Rename (fix refs) option

4. Here you give your desired name for the selected dashboard.

I am renaming it to Rename Test

5. Press Enter to submit the name to server

6. Now go to login to presentation service and go to dashboards. You will see that your dashboard name has been changed.

Rename dashboard from Answers admin tab:

Log into presentation service with Administrator, go to Settings > Administration > Manage Presentation Catalog.
It opens a window; in this window you find option Show Hidden Items. Select this check box. Now you are able to
Navigate to the Shared Folders and the group folder under which you have created the dashboard.
Select _Portal folder (this will only appear when you select Show Hidden Items options)
It displays the list of dashboards under this folder. Now select Rename option corresponding to the dashboard that you want to rename

6. Here you mention the desired name and click on Update.

7. Now go to Dashboards and check whether name has renamed or not.

General Properties of Connection Pool

2011-04-28T01:32:00.000-07:00

Connection Pool is a very important repository object and we should have a good knowledge of that. Moreover, many interview questions are based out of this. Now, lets understand something more about it…

We have 3 tabs:

1)General 2)XML 3)Write Back

In the General tab,

We need to give the Name of the connection pool.

Call Interface specifies that the Siebel Analytics will be making use of this application program interface (API) to access the Datasource. We have a list of call interfaces available based on the type of DB we are using as the data source. Like for Oracle we have OCI, ODBC etc.

Maximum Connections tells the maximum number of users that can connect through this connection pool. The default value is 10.

Require fully qualified table names is used to have the tables names with complete details like DB.schema.table_name. When this option is selected, all requests sent from the connection pool use fully qualified names to query the underlying database.

In Data Source Name we need to give the Host String of the DB to which the queries will be routed. We need to give valid logon info for the data source else will fail to connect to the particular Data source.

If Shared Logon option is checked, then all connections to the database that use the connection pool will use the user name and password specified in the connection pool. If this option is not selected, connections through the connection pool use the database user ID and password specified in the DSN or in the Siebel user profile.

In Username & Password and password u need to specify the Username and password of the underlying DB. We can also create a variable for the Username and make changes in that when we intend to change the underlying DB.

The Enable Connection Pooling allows a single DB connection to remain open for specified time so that the future query requests could route through the same connection. It saves the overhead of opening and closing new connections and if this option is unchecked then we for each query the DB need to open a new connection.

Timeout specifies the time for which the connection to data source remains open after the request completes. During this time the new requests make use of this connection instead of opening a new connection. If timeout value set to 0 then connection pooling is disabled.

Execute queries Asynchronously specifies that whether the data source supports asynchronous queries or not.

Execute on connect allows the Siebel Analytics Admin server to specify a command to the DB each time the connection to the DB is established. It can be any command accepted by the DB.

Parameters Supported when checked tells that that all the DB parameters mentioned in the DB features file are supported by Siebel Analytics server.

Isolation Level controls the transaction locking behavior for all statements issued by a connection.

Committed Read- Locks are held while the data is read to avoid dirty reads. Data can be changed before the transaction ends with that connection.
Dirty Read- 0 Locking. Can read uncommitted or dirty data, change values in data during read process in a transaction. Least restrictive of all types.
Repeatable Read- Places locks on all data used in a query so that nobody can update the data. However new rows can be inserted by other users but will be available in later reads in the current transaction.
Serialization- Places a range lock on data set preventing other users to insert or update the rows in data set until the transaction is complete. Most restrictive of all.

How to reset/change the 'Administrator' password in OBIEE, BI Publisher and BIScheduler

2011-04-27T00:23:00.000-07:00

The steps to perform this task are below.

The example solution begins by creating a BI Publisher superuser.
On both OBIEE and BI Publisher.

Start username/password: Administrator/Administrator
After username/password: Administrator/admin3

BI Publisher superuser username/password admin2/admin2

After the test it was proved that it was possible to log into OBIEE and click on the preconfigured Dashboard.

Please follow the exact steps below and create the same usernames and passwords to start off with.

1. Take a backup copy of the c:\OracleBI\xmlp\XMLP\Admin\Configuration\xmlp-server-config.xml file.

2. Log into BI Publisher
3. Select Admin > Security Center > Security Configuration
4. Enable a Local Superuser called: admin2 with the password admin2 . Click <Apply>
5. Restart oc4j
6. Sign into BI Publisher as admin2/admin2. This should log in successfully.
7. Log into OBIEE Admintool (Online) and change the Administrator password to 'admintool'.
8. Check in and save the changes.
9. Log into OBIEE Answers as Administrator/admin3

10. Change the 'Administrator' password under the 'Security Model' to 'admin3'. Click <Apply>
11. Restart oc4j
12. Login to BI Publisher as Administrator/admin3. This should log in successfully.
13. Log into OBIEE Answers as Administrator/admin3
14. Click on More Products > BI Publisher
15. This should go straight through to BI Publisher without prompting for a username and password.

If the above method still fails attach your c:\OracleBI\xmlp\XMLP\Admin\Configuration\xmlp-server-config.xml file for comparison. It is expected that the encrypted password strings exactly match your environment.

** BI Scheduler password **

This password can be changed in 'schconfig' from the DOS/UNIX command line/shell.

C:\>schconfig
Copyright (c) 1997-2006 Oracle Corporation, All rights reserved

***** Delivers Configuration Menu *****
1 - Configure Scheduler

>>> Enter Choice: 1

***** Scheduler Configuration *****
1 - Database
2 - General
3 - Advanced
0 - Quit

>>> Enter Choice:

>>> Enter Choice: 2

***** Scheduler General Configuration *****
10 - Administrator Name : Administrator
11 - Administrator Password : *****
0 - Quit

>>> Enter Choice: 10, then 11

0 - Quit
0 - Quit
0 - Quit
>>>>>>>>

Quit 3 times and restart all Analytics Services (To bring them back in sync. Restarting the BI Scheduler on its own also works, but Job Manager connections may exhibit stale behaviour depending on what has been changed) .

Physical Layer Tips

2011-04-27T00:14:00.000-07:00

After you import, set up a PK and some joins, you usually are done with the physical layer – aside from the occasional alias. But you should pay more attention to it, as it can have some unforeseen problems if you overlook the details.

Circular Joins

The first physical layer problem is Circular Joins. A circular join is where there are 2 routes to get from one table to another, going from a dimension to a fact. Aggregate tables don’t count, as they are alternate tables used and won’t cause circular joins in the SQL. A real circular join is where usually 2 dimension tables join to each other, and each dimension tables also joins to a fact. This can happen frequently with small look-up tables in a non-denormalized schema, such as a snowflake or Transactional model.

There are three ways to fight this, and if you follow them, you will never ever encounter them. In fact, I haven’t seen a circular join in a few years.

Import physical tables without FK joins turned on. Make the joins yourself, so there is nothing hidden that could cause problem. You should “own” your joins, not the other way around.

Aliases are the technique one uses to eliminate circular joins. An easy example of how to use aliases is when you have a lookup table, like W_LOV_D – each version of it requires an alias, which will be joined to differently.

With all tables & aliases, using a naming standard will guarantee that only one physical layer object will exist in one Business Model object. If you prefix every object in the physical layer with its Logical usage first (essentially making everything an alias), you will not be able to use the Customer (W_COUNTRY_D) table in the Employee logical table, as the standard would require you to use a table alias called Employee (W_COUNTRY_D). The 7.9 OOB repository has finally adopted a similar approach.

Nullable Flag

The most devious Gotcha however is the nullable flag on each column. Since many DBAs get lazy and do not set NOT NULL constraints in the database; this mis-information is consumed by the BI Server and gives it a tummy ache. Incorrect not-null settings are important to the BI server as they guide how it not only generates SQL but does internal joins as well.

By leaving the default of Nullable set to true, just think what that is saying: it is saying that one can expect null values to appear in the column. Whenever a join on this column is needed, such as for a subtotal or a logical query that derives into multiple physical SQLs, the join syntax can be affected. When subtotalling on such a field, certain kinds of metrics that are calculated in the SQL (such as a count distinct) can cause problems.

When subtotalling on a field in a report, this will translate into a multi-part query of the basic format:

Select fields

from Select (Detail Rows) DR,

Select (SubTotal Rows) SR

Where DR.SubTotal Field = SR.SubTotal Field

Note that for straight additive measures, the server will do the calculation and not the SQL.

If the field you are SubTotaling on is set to nullable, then you have the following join condition:

nvl(DR.c1 , 88.0) = nvl(SR.c1 , 88.0)

and nvl(DR.c1 , 99.0) = nvl(SR.c1 , 99.0)

No that is not a typo. Although the join condition it generates is a little bizarre by being redundant, it is clear that the server is taking into consideration that the field may be null. For varchar fileds, it uses ‘q’ and ‘z’ instead of 88 and 99. (This is dangerous if you actually have those values in the field!)

When set to Not Null, your join condition will be simply:

D1.C1 = SR.C1

There are two errors you can make, each the opposite of the other:

Setting the column to null when it is really not null. This will give you the correct answer, however it will be slower than the straight join due to the non-use of indexes.

Setting the column to not null when it is really nullable. This will result in the wrong answer, as the equi-join will remove null rows from the recordset completely.

Thus, be very accurate about your nullable settings in the physical layer. Ideally this should be handled in the database data model before you import a table to ensure that your physical layer does not become out of synch with the actual database.

Primary Keys

When beginning with Analytics / OBI, a lot of people make the mistake of creating too many primary keys when their PK simply has multiple columns.

Normally in Data Warehousing, a dimension table has a single field to be its primary key. However sometimes we do not have that luxury, and must use multiple fields to represent our PK. The best example of this is a Fact table: its PK is a combination of all the FKS to the dimension. For example, a small fact table with FKs to Product, Month and Region has a composite Primary Key of PRODUCT_KEY, MONTH_KEY, REGION_KEY. It is one key and not three: in the Analytics Physical layer, configure one Primary key but select multiple physical columns to define the key.

As a basic rule, think of the Highlander: “There can be only one!” There are advanced scenarios where their may be more than one, but 99% of the time, make sure every physical layer table has a single PK.

Complex Joins

Occasionally I will see complex joins where a complex join is not needed. The vast majority of the time your joins should be straight FK joins, even if the PK of the parent table is a composite PK (see above).

Complex joins are used in the following scenarios:

Range Joins. Useful for YTD metrics or rolling metrics. In 10.1.3.2, the need to do this has been eliminated, but your legacy config may still need them for this purpose. An example might be: W_DAY_D.DAY_KEY <= FACTS.DAY_KEY AND W_DAY_D.DAY_KEY < FACTS.DAY_KEY + 7. This query will bring back the last seven days of facts for the current day selected. (Yes this looks strange, but try it out on a piece of paper to see how it works)

Data Type Conversion Joins. This is common in non-star schema models, or poorly designed ones. The most common example is the conversion of a date field to a YYYYMMDD format to join to a date table that has a field of the same YYYYMMDD format. Incidentally, this is one of the reasons why you would have more than one PK of the date table – DAY_KEY and DAY_NUMBER would both be PKs if you had to do this.

Cartesian Joins. In strange circumstances, a 1=1 join can be used. Be extremely careful, as this is a Cartesian product, which means the same thing as no join, which in turn means all records from table 1 will be pared with all records from table 2. I have had to use this in my current project extensively, where we have to force a dimension to work with a fact table for the sake of the UI. When doing so, you must guarantee that only one of the dimensional values is selected in the report or prompt; otherwise your result will be substantially incorrect.

Scenarios when you should not use a complex join

1:1 Joins. This is common when using _DX and _FX tables in the Oracle BI applications (the RMW). A complex join frequently is over included in SQL, meaning that sometimes it is added and slows down performance even when it is not needed. Model this with the _DX or _FX as the parent of the base _D or _F table using a regular FK join

Filtering

This is where there is a filter in the join to exclude some rows from one of the tables. Think about a LOV table. One could make a join to a LOV and filter out the TYPE field to just ‘Case Status Types’. Although this will work, it is poor design and can be missed in other joins. It is best to put the filters in the LTS Content tab for the Dimension table it is mapped to.

Best of luck with your Physical Layer.

Steps to change the password of the repository

2011-04-26T23:45:00.000-07:00

Open NQSConfig.INI file
Go to Security part and find AUTHENTICATION_TYPE = BYPASS_NQS;
Uncomment (remove #) this part.
Stop the BI Server service
Close Administration Tool, if opened. (Then only the changes are affecting to the Administration Tool)
Open Administration Tool, then rpd which needs password reset, in offline mode
Give any password, it accepts.
Now go to Manage > Security.
From here it’s the normal process how you change password.
Save the changes to rpd
Revert back the changes in NQSConfig.INI
Start BI Server and open rpd with new password.

2011-04-21T07:35:00.000-07:00

Oracle Business Intelligence components

Reference: http://download.oracle.com/docs/cd/E10415_01/doc/bi.1013/b40058.pdf

The Oracle Business Intelligence components consist of:

Oracle Business Intelligence Presentation Services

The Oracle Business Intelligence Presentation Services provides the framework and interface for presentation of Business Intelligence data to web clients. It maintains a Presentation Catalog service on the file system for the customization of this presentation framework. It is a standalone process and integrates with the Oracle Business Intelligence Presentation Services Plug-in from which it receives web client requests. It communicates with the Oracle Business Intelligence Server using ODBC over TCP/IP.

Oracle Business Intelligence Server

The Oracle Business Intelligence Server is a stand-alone process that maintains the logical data model which it provides to BI Presentation Services via ODBC. Metadata is maintained for the data model in a local proprietary file called the repository file (rpd). On the back-end, the BI Server connects to customer data stores via data source adaptors.

Oracle Business Intelligence Scheduler

The Oracle Business Intelligence Scheduler is an extensible scheduling application for scheduling reports to be delivered to users at specified times. It is the engine behind the Oracle Business Intelligence Delivers feature.

Oracle Business Intelligence Publisher

The Oracle Business Intelligence Publisher generates highly-formatted, pixel-perfect enterprise reports.

Oracle Business Intelligence Java Host

The Oracle Business Intelligence Javahost provides services to BI Presentation Services for Charts, Gauges and PDFs. The services are provided based on request-response model.

Oracle Business Intelligence Presentation Services Plug-in

The Oracle Business Intelligence Presentation Services Plug-in is the entry point for web client requests to BI Presentation Services. There are two types of BI Presentation Services Plug-ins. For Oracle Business Intelligence that is serviced by J2EE application servers, the BI Presentation Services Plug-in is a Java Servlet. For Oracle Business Intelligence where the web server used is Microsoft Internet Information Services (IIS), the Oracle BI Presentation Services Plug-in is an ISAPI Plug-in.

Oracle Business Intelligence for Microsoft Office

Note: The Oracle BI Presentation Services, Oracle BI Client, and Oracle BI Server components can be installed to run on separate machines