Degenerate Dimension in OBIEE

A degenerate dimension (DD) acts as a dimension key in the fact table, however does not join to a corresponding dimension table because all its interesting attributes have already been placed in other analytic dimensions.

Sometimes people want to refer to degenerate dimensions as textual acts, however they're not facts since the fact table's primary key often consists of the DD combined with one or more additional dimension foreign keys.
Degenerate dimensions commonly occur when the fact table's grain is a single transaction.


How to Implement in OBIEE :

Refer: http://obieetalk.com/oracle-bi-ee-101341-%E2%80%93-modeling-degenerate-dimensions-%E2%80%93-fact-attributes

Reference:http://www.kimballgroup.com/html/designtipsPDF/DesignTips2003/KimballDT46AnotherLook.pdf

MULTISELECT PROMPT AND PRESENTATION VARIABLE CAUSES ‘NO CHOICES AVAILABLE’

You are on OBI 10.1.3.3.3 and have configured a Dashboard Prompt with a couple of columns where the value selected by the user in the first prompt column gets stored in a presentation variable, and the values in the second prompt column depends on
the value in this presentation variable.

When both column prompts are defined as ‘Drop down’ type. The prompts work
fine. The second column correctly displays in the dropdown the values.

However when the second column prompt control is defined as ‘multi select’.

ACTUAL BEHAVIOR
———————–
No data is displayed in the prompt. The prompt displays ‘No Choices available’.

EXPECTED BEHAVIOR
———————–
The multiselect prompt column should correctly display values in the prompt as it does when it is defined as a dropdown.

CAUSE

Statement describing the cause of the problem
This issue has been determined to be a bug, which is now reported in Change Request Bug No: 7571682 (MULTISELECT PROMPT AND PRESENTATION VARIABLE CAUSES ‘NO CHOICES AVAILABLE’).
The proof that this is the cause of the problem
This issue is caused by Bug No: 7571682 (MULTISELECT PROMPT AND PRESENTATION VARIABLE CAUSES ‘NO CHOICES AVAILABLE’) as per our inhouse testcase which replicated the same behavior your system exhibits.

SOLUTION

This issue has been determined to be a bug, which is now reported in Change Request Bug No: 7571682 (MULTISELECT PROMPT AND PRESENTATION VARIABLE CAUSES ‘NO CHOICES AVAILABLE’).

As possible workarounds we have found that, on the Dashboard, when you click the ellipsis and invoke the multiselect popup and you get the message “No Choices Available.”,

1. Click the ‘Go’ button will bring back data

2. Alternatively you can enter a value in the match box and it returns data

3. Also you can consider using the vanilla constrain option for the dashboard prompts, so you can have column 2 constrained by column 1.

Please consider the above as possible workarounds.

Some OBIEE Answers limitations

Even though, I believe that OBIEE Answers is a very fine piece of software, sometimes, I’m annoyed about certain OBIEE bugs and quirks.  Here’re some of my favorites (least favorite OBIEE Answers limitations).

1. You can not use prompts on a calculated column in answers.  Workaround: you can create this calculated column in the rpd in the business layer and than add that field to the presentation layer. This also adheres to best practices (put as many metrics as you can to Business Model Mapping layer).

2. When a prompt is created based on an SQL Condition (in “Show” “Sql Results” Option), if a filter is applied when this Prompt is used the condition created originally is lost. This is most likely an OBIEE bug and I’m not aware of any workarounds.

3. Inability to display the beginning date and the end-date in the report based on the operator “between”, because with this operator, you can’t define a presentation variable.In this case, the Between Date Prompt isn’t working properly and is a known OBIEE Answers bug. The workaround is to  create a second report. This will have the date column used by the report, only twice. For the first date column, change the column formula to min(date) and for the second date column change the column formula to max(date). Then use a narrative view something like ‘Report for period beginning @1 and ending @2′.

4. In Answers, when creating a filter, you sometimes see an incorrect behavior in the Edit Filter popup where the wrong values are loaded for a column. This behavior occurs when clicking the ‘All Choices’ link in the filter popup, for a column and then cancelling it. When you then navigate to another column to create a filter and select ‘All Choices’ you get the dropdown values for the previous column being loaded. No workaround, however, you can refresh the same filter again to get correct dropdown values.

5. Download Data and Download to Excel work differently. OBIEE is downloading all the columns present in Report Crieteria page of Answers part (even if they’re not included in the Dashboard). The workaround is to use Download to Excel.

How to reset/change the ‘Administrator’ password in OBIEE, BI Publisher and BI Scheduler

We would like to reset the ‘Administrator’ password for:

i) ‘OBIEE’
ii) ‘BI Publisher’ and
iii) ‘BI Scheduler’

Please provide a solution to perform a password reset.
Solution

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 I also proved you could 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

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
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. I would expect the encrypted password strings to exactly match my 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) .

What rule is followed when several fact are at the same content level ?

I think this is an interesting question especially for those who would like to know how BI “thinks”.

The goal is to understand what rule is followed when several fact are at the same content level

Normally the server uses the content level to pick the correct fact table.

The server is looking for the most aggregated source.

First choice is a query in which the grain of the query matches the grain of the content filter.
If there are no sources which match that criteria, it will look to other means to chose.

It looks at how many dimensions are associated with the fact table (size of the content filter), levels of the content filter, number of values from the level definitions multiplied.

error “access denied for user to path

I just had a terrible catalog security situation, and while looking for solution stumbled into this bug. I think it’s important, because the error message is confusing and it’s really hard to troubleshoot this sort of problem.

Catalog Manager copy/paste removes correct permissions on Users subfolders, causes error “access denied for user to path..” at OBI login

Applies to:
Business Intelligence Server Administrator – Version: 10.1.3.2 to 10.1.3.4.0 [1900] – Release: 10g to 10g

In OBIEE 10.1.3.4, users are copied from one web catalog A (TEST environment) to another web catalog B (PRODUCTION Environment), using the Catalog Manager. After loading the new web catalog B, users are unable to login into OBI and see the following error:

access denied for user to path /users/…/_portal/dashboard layout.
Error Details
Error Codes: O9XNZMXB

Cause

In the Catalog Manager, when copying users in the catalog manager, permissions are not copied. The users are part of the system folder (i.e Catalog Manager > Users > Properties > Owner Account = System Account) , which is why Catalog Manager does not transfer the permissions.

The behavior was reproduced with 2 copies of Paint web catalog A and B.
Note: Before copying from Web Catalog A, here are the privileges for

a) Users folder – Owner – System Account
Explicit Permission – Presentation server Administrator(full), Everyone(Traverse)

b) Users > Paint Folder – Owner – System Account
Explicit Permission – Paint (change/delete)

c). Users > Paint > _portal folder – Owner – paint
Explicit Permission – paint (change/delete)

After pasting user folder in web catalog B, here are the permissions:
Note how the properties and permissions changed after pasting the user to the following:

a) Users > Paint Folder – Owner – System Account
Explicit Permission – Presentation server Administrator(full), Everyone(Traverse)

b). Users > Paint Folder – Owner – System Account
Explicit Permission – Presentation server Administrator(full), Everyone(Traverse)

Solution
The following has been raised to address a product enhancement request:

BUG 8316638 COPY AND PASTE USERS IN CATALOG MANAGER DOES NOT COPY PERMISSIONS

The current workarounds are:

a). Manually change the permissions on the user_id, _portal and other subfolders in the target web catalog so that they are the same as the source web catalog.

b). Use SAWREPA utility to promote the changes from TEST to PRODUCTION instead. The process works online, so you do not lose any up-time, and it should promote the users permissions correctly too.

Information about SAWREPA is documented in the following:

Oracle Business Intelligence Presentation Services Administration Guide > Administering the Oracle BI Presentation Catalog > Replicating Presentation Catalogs

Please note that SAWREPA requires that both the PROD and TEST webcatalog were originally developed from the same web catalog. If the PROD webcatalog was created from scratch, it could cause problems with SAWREPA since it relies upon common attributes in both catalogs.

importance of defining dimensional hierarchies

Dimensional modeling is very important in OBIEE. Many mistakes happen because of incorrectly defined time dimension or other dimensions. This service request below is a demo of how an incorrectly defined key in the beginning can block the development efforts in the future.

Overview:
========

Modified rpd and added a new dimension for Key Accounts (Sales.”Key Account”.”Key Acct”) into the Sales subject area . After this change when building a report with Sales.Brand and drillingdown on Brand to show Brand_SKU ( a different dimension throws an error in Answers.

ERROR
===========-
[nQSError: 14020] None of the fact tables are compatible with the query request Brand.Brand.

STEPS
===========
By following these steps the issue can be reproduced:

1. Add dimension called Sales.”Key Account”.”Key Acct into sales subject area in rpd
2. save rpd and checkin changes
3. create answers report with a different dimension Brand.
4. Drilldown on Brand to show the next level “Brand”.”Brand_SKU”.
5. Get the error mentioned above

EXPECTED BEHAVIOR
===========
The report should work fine because the “Brand”.”Brand_SKU” was not modified at all.

Business Impact
===========
This is a very important issue that is holding up all BI projects. You cannot move forward until this issue is fixed.
Cause

The error thrown when drillingdown from Brand to Brand_SKU is caused because of incorrect definition of level keys in your rpd. The issue is, that by definition, a key is the lowest level of a table. Any higher levels are made up of aggregating the lowest or detail level. Since Brand and Product have the same detail key, there should be some consistency between the detail or lowest level of their respective hierarchies. There is not. The lowest level of the Brand hierarchy has a level key of CHILD_KEY_WID. The lowest level of the Product hierarchy has a level key of ‘SKU Nbr’ which is mapped to LVL6_KEY. It seems that the navigator is expecting the same detail key but is not seeing it. This seems to be the cause of the navigation error.

As confirmed by reviewing your rpd inhouse, the level keys are not properly defined for Brand and Product dimensions.
The levels were not defined according to best practices. The document from Engineering that lists the issues in your rpd that dont meet best practice is attached to the SR.
Solution

Please follow these steps in order to fix the error “[nQSError: 14020] None of the fact tables are compatible with the query request Brand.Brand”

1) Open rpd using Admin tool
2) Select BMM layer and delete the current hierarchy created for Brand
3) Make the following change to Product Dimension

–Dimension –Logical File –Physical File
Product Product HSAMI_SKU_DH and
HSAMI_BRAND_SKU_DH

The Product Dimension has two hierarchies, CMDYGRP and BRAND:
ALL
CMDYGRP
COMMODITY
SKU
BRAND
SKU

So now both hierarchies are constructed in one dimension over
one logical dimension table, with multiple phyiscal source tables.

4) Save changes to rpd
5) Test in Answers by creating report with Brand and Product columns
6) Navigation/Drilldown from brand and product should work fine.

The following Expression always return 0.00% in a custom OBI EE Report

Something else, I’ve found. I suggest always adding 1.00* when working with non-integers, just for sake of convenience.

(Customers.”Total Customers” / sum(Customers.”Total Customers”)) * 100
Cause
The problem was caused because that Integer Data Type values are involved in the calculation. If the calculation is performed on Integer values, then the correct answer is ‘0′.

When a Grand Total is specified, then the Data Type is promoted to Double Precision. If the operation is performed on a Double Precision field, then non-zero values will be returned.

Solution

The solution to this issue and to ensure that non-zero values can always be returned in an OBI EE Report is by modifying the formula expression as follows: -

e.g. (1.0*Customers.”Total Customers” / sum(Customers.”Total Customers”)) * 100 or 1000*Customers.”Total Customers” / sum(Customers.”Total Customers”)

Users missing cache

f your users are missing cache, you might find the service request below helpful.

Description:
User1 one and User 2 have the same profile but User 2 is not hitting User 1’s cache even though the physical queries are exactly the same.

Cause
Customer had implemented data-level security. This resulted in different SQL for each user.

Solution
Cache hits are not based upon the physical queries.
They were using session variables in the connection pool login/password fields. Each different login has a different query, the issuer of the query is different, this results in the cache being specific to a user / session variable.
They modified the connection pool to use a shared login/password and this resulted in a cache hit.

Connection Pools – Best Practices

Most of the times not much thought is 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.

OBIEE Presentation Layer Design Principles/Best Practices

It is a Last Chapter of OBIEE repository.

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 BMM Layer Design Principles/Best Practices

OBIEE BMM 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 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.