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

TimestampDiff and TimestampADD( Difference of two dates) in OBIEE

How to get a difference between two dates (in terms ) of days,weeks,months what every it may be

The below formula gives you no.of days between day date and current_DateDays difference: 
TIMESTAMPDIFF(SQL_TSI_DAY, Time."Day Date",CURRENT_DATE)

The below formula gives you no.of months day date and current_Date
Months difference:
TIMESTAMPDIFF(SQL_TSI_MONTH, Time."Day Date",CURRENT_DATE)

The below formula adds months to day date column
Toadd 12 months to a date column:
TIMESTAMPADD(SQL_TSI_MONTH, 12,Time."Day Date")

similarly we can write the formulas using different intervals based on the date format in the column
Here are the intervals :
SQL_TSI_SECOND, 
SQL_TSI_MINUTE, 
SQL_TSI_HOUR, 
SQL_TSI_DAY, 
SQL_TSI_WEEK, 
SQL_TSI_MONTH, 
SQL_TSI_QUARTER, 
SQL_TSI_YEAR.

External Table Authentication - Encrypted Password

I am trying to blog on this from long time now i have some time to write a blog :) how to store the encrypted passwords in database for External Database Authentication

Step1 : Create a function in Database by executing below code.

create or replace FUNCTION obiee_pass(p_login IN VARCHAR2,p_password IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN LTrim(To_Char(Dbms_Utility.get_hash_value(Upper(p_login)|| '/' || p_password,10000,Power(2,30)),RPad('X',29,'X') || 'X')); END;

Step2 : create a table with columns Login,Password,Loglevel,Group

we have to insert the values to the above table obiee_users

with the following statement
insert into OBIEE_USERS values('User3',obiee_pass('User3','User3'),'Group2',2);

Step3: In rpd we need to configure Click at step7 i..e. Initialization Block we need to change with following SQL CODE

SELECT USER_NAME,LOGLEVEL,GROUP_NAME FROM OBIEE_USERS where 
USER_NAME=':USER' NQS_PASSWORD_CLAUSE(and password= obiee_pass(':USER',':PASSWORD'))NQS_PASSWORD_CLAUSE

Step4: Login with users you will able to login to dashboards normally as before and apply the security as usual 

Default Dashboard using "PortalPath" system session Variable

I am going to explain how to make a dashboard default using portal path,you can find this on several blogs but for my reference doing this

Step1 : In below figure i have "8" dashboards out of those when a user logs in he has to see "USER" dashboard instead of My Dashboard (Default)

Step2: Create a system-session variable

Step3 : Give the following code in your default initialize box

Where "User" is my Dashboard name

Step4: Creation of Initialization block and assign PoratlPath system session variable to this newly created block

Step5:Next,Login to dashboard with Administrator user and you need to change the

"My Account" settings -->Preference-->Default Dashboard-->Default

Logout and login with Administrator you will get USER as default dashboard,if you already have "Default" no need to go for step5

Reverse function in obiee

Today,i came across with a scenario to reverse values in a column 

'abc' is one for a column where as in another column have to show 'cba'

for this i used DB REVERSE function using evaluate 

Syntax : EVALUATE('REVERSE(%1)', "Dim Customer".Customer_Name)

Filtering Table data using "Request Variable" and Session varaible/OBIEE- Request Variable

When you want to filter the table data based upon the selected value in prompt

For example ,I have a table called SRC_ORDER_LINES it will show Sales amount and Order quantity based on order_id.My requirement is in dashboard i want to filter whole table data based on the VALUE(Order quantity) selected in Prompt

1) Create a session variable initialization block with following query

2) Next,variable creation and assign the above initialization block to this newly created variable

3) Double click on Variable qty,check in "Enable any user to set the value"

4) We need to add this session variable to the "BMM layer Content Level Filter" in order to filter the table


5) Double click on LTS(Logical Table Source) of SRC_ORDER_LINES click on content tab we need to give the condition under "Using where condition to filter out row counts"

6) Coming to answers create a prompt and declare a request variable with same name as session variable "Qty"

7) Add the prompt to dashboard and its respective report .when you select value of quantity as 1500 then the SRC_ORDER_LINES will limit the rows like where qty >1500,it will retrieves only those records

Below figure shows 

Quantity >1341 records

Quantity >1881 records

How to Get page breaks in obiee

Hi,
Today i came across the post in otn forums
http://forums.oracle.com/forums/thread.jspa?threadID=1123570&tstart=0

I thought we can do that in bi publisher bursting But,David given an perfect solution for this

If,you to get a pdf by a customer type (each page one single type)

Here is my report


Goto pivot table and move the Customer type in Sections section

Next,click on hand symbol in Sections Section and you can see the Insert Page break from drop down select Column name(product_type).So that report will print by customer type

Then take a look at PDF page it will break the pages based on customer type

Getting 30days back data from the selected date in dashboard prompt

1) Create a prompt day week and declare presentation variable

2) Here is the report ,i pulled product and booked qty

To show 30days back booked quantity the date selected in prompts

In fx use

FILTER("Facts Other"."Booked Qty" USING (Time."Day Date"=TIMESTAMPADD(SQL_TSI_DAY, -30, DATE '@{day}')))

You can apply the same formula for 1....n days

For month level use
FILTER("Facts Other"."Booked Qty" USING (Time."Day Date"=TIMESTAMPADD(SQL_TSI_MONTH, -3, DATE '@{month}')))

where -3= three months back

Data/Object/Column Level Security in OBIEE

Authorization can be done in two ways 

Object level Security :In this we can restrict dashboards, pages, sections, tables
Data level Security: Here we can restrict access to values in columns.
Column level Security: Giving access to certain columns in a table.

Here we are having three users’ duser1, vuser1.

Groups for which the users belong to-

duser1 - DISNEY

vuser1 - VERIZON

These groups are created in repository under Manage->Security->Groups

Object & Data level security for Disney group:

When duser1 logs in and he belongs to DISNEY group who can view only Financial Dashboard (Object level security) and data in the column, based on the access given to him (Data level security). He can view only few clients (Brand 1). Restricting data in Client column.

Fig (1) : Restricting data from GROUP level

Object level security

1) Dashboard/Page/Section level security

Create these groups in Answers.

Admin->Manage Presentation Catalog Groups and Users->Create new catalog group

Give permissions to dashboards for each group as per requirement. (Object level)

Restricting the groups to a dashboard

Settings-->Administration-->Manage Interactive Dashboards-->Click on Dashboard permissions

After clicking on permissions tab then assign the respective group to your dashboard 

When a duser1 logs in he will see only Financial dashboard

Column level security:

In the presentation layer of repository we need to restrict tables and columns for those groups

Double click on the table->Permissions tab->General

Check show all user/groups and change check box read to tick or cross mark. Now the users under that group cannot view that table in answers.

We have a subject area called Sample Sales Reduced. In this we restricted Other

Dimensions table. When duser1 logs in, he cannot see table from presentation

view/answers. In the below picture we can see Other Dimensions table in

Presentation Layer of repository but its not present in Answers

In the same way i restricted particular column in a table to this

user. Here we restricted No of customers,employees, orders these three columns from

Facts other table for this users. When the user logins he cannot view those columns, but i can see these columns in repository.

In the below picture we can see Facts Others with different columns in Answers and Repository

NOTE: 

If a report is created using a column which is having access to one user and no access to other user, then the user who doesn’t have the access cannot view report they will get ERROR to avoid the error message you change the NQSCONFIG.INI file 

PROJECT_INACCESSIBLE_COLUMN_AS_NULL = NO; under security in NQSConfig.INI Change it to YES so that he can view the report properly without that column.

Configuring the Presentation Catalog for More Than 4000 Users

If you have more than 4000 Presentation Catalog users or you intend to have more than 4000 Presentation Catalog users in the future, you need to turn on the hashing of users’ home directories to take care of a file system limitation.

To do so, you set the HashUserHomeDirectories element in the Oracle BI Presentation Services configuration file (instanceconfig.xml) to 2. 

The following entry is an example:

http://docs.google.com/View?id=dgh6q53h_126fpwdd4gt


NOTE: This element must be set immediately after installing Oracle BI Presentation Services to be effective

Source:Presentation services admin guide

How to get one year back date in obiee

Formula to get one year back date 

TIMESTAMPADD(SQL_TSI_YEAR, -1, CURRENT_DATE)

Ex: current_date=05/01/2010 then we will get 05/01/2009

Evaluate Function

EVALUATE function can be used in OBIEE to directly to call a Database Function from OBIEE Answers 

Syntax:-EVALUATE('your db function(%1,%2)', parameter list)
---%1 and %2 are no.of parameters (in this example 2 parameters will be passed to the database function)

%1, ..., %X means - x number of parameters will be passed, and the values for those parameters will be passed in the parameter list.

Example:
EVALUATE('TO_CHAR(%1,%2)' ,"Dim- Date".Start Date,'DD-MON-YY')
or
EVALUATE('TO_CHAR(%1,%2)' AS CHARACTER ( 30 ), "Dim- Date".Start Date, 'MON-YY')