Category Archives: Administration Tool

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.

display prompt values in Excel

How can we get the Prompt values to be displayed when the report is exported in MS Office formats or printed in PDF using the ‘Download’ or ‘Print’ report links?

When you use the ‘Printer Friendly’ option on the Dashboard, all objects on the Dashboard get downloaded for printing including the Dashboard Prompt section.

However using the report ‘Download’ or ‘Print’ link, this does not print the Dashboard Prompt section.
Please note this is expected behavior. Using the Printer Friendly icon at the lower lefthand corner, it prints the entire dashboard. Alternatively the ‘Download’ or ‘Print’ link option is available, for the report. So this will print the Compound layout view of the report, or any specific report sections that have been made available on the Dashboard.

A workaround to have the Dashboard Prompt values displayed when you use the Report ‘Download’ or ‘Print’ link is by having the filter view as part of the report.

Oracle has now raised the Enhancement Request# 8242921 to address this matter. The enhancement request is to enhance the product so that so there is also an option to download to the various formats at the dashboard page level. Similar to the Dashboard Page ‘printer friendly’ functionality.

How to set session variables using url variables

The goal is to set session variables using url variables, but can you also do this for the user and password ?
url variable (&Upwd) is not passed to session variable USER_PWD.
The variable USER is correctly passed, the variable USER_PWD is not!

Solution

The steps to set an OBIS session variable via a URL call utilizing the
instanceconfig.xml tag should be as follows

1. Create a session init block that will act as a ‘placeholder’ for the
session variable to be set via the url call – the variable can be set to
anything.

2. Set the ‘Enable any user to set the value’ option for the variable.

3. Add the following tag block to the instanceconfig.xml file anywhere
between the <ServerInstance></ServerInstance> tags:

<Auth>
<UserIdPassword enabled=”true”>
<ParamList>
<Param name=”NQ_SESSION.TEST_VAR”
source=”url”
nameInSource=”SETVAR”/>
</ParamList>
</UserIdPassword>
</Auth>
“TEST_VAR” should match the session variable name (case sensitive).

4. The following option will need to be appended to the OBI url passed –
&SETVAR=’variable value to pass. So a full example would be:
http://localhost:9704/analytics/saw.dll?Dashboard&nqUser=USER001&nqPassword=US
ER001&SETVAR=SomeValue

However, note that you cannot set the value of any System Security Session variable (specifically USER, PROXY, GROUP and WEBGROUPS) using any source method (e.g.: url, cookie, httpHeader) by design. Having this ability would open possible security breaches.

If you attempt to set the USER variable with the following instanceconfig.xml setting:

<Param name=”NQ_SESSION.USER” source=”url” nameInSource=”nquser” />

You will get the following error when using the url: http://localhost:9704/analytics/saw.dll?Dashboard&nquser=user1&nqpassword=public :

nQSError: 10018: Access for the requested connection is refused
nQSError: 1315 You do not have the permission to set the value of the variable :USER