Using Set Operations in OBIEE 11g ( Union / Intersect / Minus)

There are 4 Set Operations available in OBIEE in OBIEE Answers Report Level to leverage the usual set operations functionality that a normal SQL writer would do to join two disjoint queries.

4 Set Operations are:

Set Operator	Effect on Results
Union	Returns non duplicate rows from all requests.
Union All	Returns all rows from all requests, including duplicate rows.
Intersect	Returns rows that are common to all requests.
Minus	Returns rows from the first request that are not in the other requests.

In OBIEE you can join distinct Subject Areas using set operations as well. The below article talks about the requirement and then solution using Set Operations.

Requirement 1: Show Open and Closed Service Requests(SR) Count by Month.

You cannot get Open and Closed SRs count in same report unless you create a RPD column and its not advisable to create RPD column for everything which you can achieve by creating simple report.

Solution using Union Operation:

Create a Simple Report having three columns as Month , Open SR Count and Closed SR Count.
Add Column Formula for Month as Opened Month and Count(SR_NUMBER) as Open SR Count and Closed SR Count as sum(0) to give this column as Aggregation data type.
In the Filters , Add SR Open Year = 2015 to get Open SR Count for 2015 year. This filter will give you all the SRs Count that was opened in 2015 , irrespective of the current SR status.

See below :

Open SR Count = Count(SR_NUMBER)

Closed SR Count = SUM(0)

Month = SR Open Month

Now Click on The Button to combine /Union another analysis to get Closed SR Count.

 Select the Subject Area as Service Request and you will see that Set Operations button is now added and you can create another analysis now with different columns and filters. Select the operator you want to choose.

 Here you will have to drag the columns as :

Drag SR Closed Month column next to SR Opened Month

Drag SR_NUMBER Column to Open SR Count

Drag SR_NUMBER Column to Closed SR Count

Then go to edit the formula and edit formula for last two columns as :

 Second Column will be Open SR Count, Edit the Formula to sum(0) as results will come from previous analysis. Change the heading as Open SR Count.

Third Column will be Closed SR Count, Edit the formula as Count(SR_NUMBER) and change the heading to Closed SR Count.

Add a filter SR Closed Year = 2015, to fetch you all data for 2015.

Click on Results tab to see the results.

This report is fetching all SRs which were opened and Closed in Year 2015 by Month.

Note: You can see January Closed SRs more than Opened because the data exists in warehouse for years prior to 2015.

 This was how you will use Union Operation.

In Progress for other Operations.

OBIEE 11G - Conformed Dimensions and Alias Tables

I figured out that most of the newbies in OBIEE struggle with basic concepts of Data warehousing and how to deal with them. Among them is Conformed dimensions and Alias tables what are they and when to use these concepts.

Real World Problem 1: Conformed Dimension

You need to develop a report with a relationship like :

Products : P1 , P2 

Service Request : Customer calls for the Product P1 and complains issue.

Activities : Go and Fix the Product at Customer Site

Create a report for Open Service Request and details for Activities associated .

Solution using Conformed Dimension:

You will create Physical Model like : 

Here Fact Tables Service Request Fact and Activity Fact will have Product Dimension and Customer Dimension as Conformed Dimensions and same Dimension tables will be joined with joins like :

Activity Fact Model:

ProductDim.Row_wid = ActivityFact.Product_wid ;

CustomerDim.Row_wid = ActivityFact.Customer_wid

 ServiceRequest Fact Model:

ProductDim.Row_wid = ServiceRequestFact.Product_wid ;

CustomerDim.Row_wid = ServiceRequestFact.Customer_wid

 Report will have Columns like:

Customer Name	Service Request #	Activity #	Product Name	Service Request Details
ABC	S1	A1	P1	P1 is not working as expected
ABC	S2	A2	P2	P2 needs a battery replacement
XYZ	S3	A3	P2	P2 needs a battery replacement

Where you will get Activity Number and Service Request Number by Same Product and Customer Name.

So this is how and why you will use Conformed Dimension.

 Real World Problem 2 : Using Alias Tables

You need to develop a report with a relationship like :

Products : P1 , P2 

Service Request : Customer calls for the Product P1 and complains issue.

Activities : Go and Fix the Product at Customer Site

Service Request Open Date

Activity Created Open Date

Create a report for Open Service Request and details for Activities associated and to choose Prompts(Filters) by Service Request Open/Activity Created Date.

Solution using Conformed Dimension/Alias Tables:

You will create Physical Model like : 

Here Fact Tables Service Request Fact and Activity Fact will have Product Dimension and Customer Dimension as Conformed Dimensions and along with that , you will create two Alias Tables from Calendar Dimension Table in RPD Physical Layer and name them as Service Request DateDim and Activity DateDim  Dimension tables will be joined with joins like :

Activity Fact Model:

ProductDim.Row_wid = ActivityFact.Product_wid ;

CustomerDim.Row_wid = ActivityFact.Customer_wid;

ActivityDateDim.Row_wid = ActivityFact.Created_wid; //Where Created_wid is Activity Created Date wid

ServiceRequest Fact Model:

ProductDim.Row_wid = ServiceRequestFact.Product_wid ;

CustomerDim.Row_wid = ServiceRequestFact.Customer_wid;

ServiceRequestDateDim.Row_wid = ServiceRequestFact.Opened_wid; //Where Opened_wid is Service Request Opened Date wid

 Report will have Columns like:

Customer Name Service Request # Service Request Opened Date Activity # Activity Created Date Product Name Service Request Details ABC S1 9/1/2015 A1 9/1/2015 P1 P1 is not working as expected ABC S2 9/2/2015 A2 9/4/2015 P2 P2 needs a battery replacement XYZ S3 9/5/2015 A3 9/6/2015 P2 P2 needs a battery replacement

Where you will get Activity Number and Service Request Number by Same Product and Customer Name but with Service Request Date and Activity Date as separate dimensions.

So this is how and why you will use Alias Dimension.

Creating Alias Tables in RPD: Right Click on the Table you want to create Alias for-> New Object and Click on Alias and then Give this Alias table a name.

Alias Table to remove Circular Join

There is another term use to define Alias tables is they are used in order to prevent Circular Joins. lot of times in OBIEE we come across factless fact tables / or we need to join face table with another fact table, not recommended approach but during data modelling , we come across these scenarios. We use Alias Tables then to remove Circular Joins.

e.g Service Request Fact table consist of Service request Details, and there is another table that consists of Audit History for the Service Request which is again a transnational data and then you join the two transaction tables( Fact Tables ) based on Service Request, but then Audit Table also is used to fetch information to get Employee who worked on the Service Request.

See the picture below to understand how we resolved Circular Join using Alias Tables by Creating two Employee Dimensions.

Alias Table to have Self Join in OBIEE

Alias tables are also used to  implement Self Join in OBIEE as there is no concept of Self Join in OBIEE otherwise.

Alias Tables are used where you want to implement more than one relationship between fact and dimension based on multiple reference keys.

Suppose you have a Service Request with multiple date attributes like Created Date , Opened Date , Closed Date , Last Update date etc all for the same Service Request in Service Request Fact Table then you can create multiple Alias tables for the same Calendar Table to join with different keys.

Post your questions or comments below.

How to implement VLOOKUP function in OBIEE 11G - Filter Based on Results of Another Analysis

Most of users who are using OBIEE are basically trained to use Excel sheets and have come from using excel background and want to achieve all of the functionality in OBIEE as compared to Excel.

One of the function is VLOOKUP which is nothing but just is filter which in OBIEE can be used by using usual filters , but this topic talks about using VLOOKUP as in two separate excel workbooks.

How to lookup when we have two different reports.

E.g. We have a report having Purchase Order, Product Name and Price from Accounts Payable Subject Area , another report where we have details of Open Purchase Orders from Purchase Orders Subject Area. Requirement is to get only those Products whose Purchase Order is Open.

Assumption : These two reports are unrelated in OBIEE and this information cannot be obtained from single report from same subject area and there is no conformed dimension to combine these two Subject Areas.

We can create two separate reports and then use Filter Based on Results of Another Analysis to lookup Products which have Open Purchase Orders.

Step 1: Create Report with Open Purchase Orders and save

Step 2 : Report with Product Price and Purchase Orders , Contains all Purchase Orders irrespective of PO Status.

Step 3: To lookup Open Purchase Orders from Open Purchase Orders Report created at Step 1 , Add a filter and choose Operator as is based on Results of another Analysis

Step 4: choose the Report Name and relationship and then Column Name to filter the records.

Step 5: Click on OK and save this analysis. This report now will give you the results based on a filter from other report.



Note: In case of issues or further questions, you can reach me out and I will be happy to answer.

OBIEE 11G Narrative View - showing Number of Records at runtime and many more ...

Narrative View in OBIEE 11G.

I am a fan of Narrative views and use it in almost all of my dashboards that I create and End users love it too.

Narrative Views can be used :
1) To show the number of records that report is yielding at run time.
2) To Show User name etc at Run time.

e.g.

If you have a requirement to show How many calls Agent has made in a day with a detailed report , you can achieve this by using Narrative Views :

Create a report with Agent Name , Call Details and Calendar Date and then Add a Pivot Table View and Narrative View to the Compound View and Edit Narrative view as following:

Select Contains HTML Markup and Rows to Display = 1.
Enter below text in Narrative section:

<center><b><font color=#0054a0 size= 3>Call Detail Report of @2 for @1 Calls <br /><p /></font></b></center>


Here "Call Detail Report of @2 for @1 Calls" is what you will see as the heading of the report. @2 and @1 are the locations of the Columns in Criteria level.

@2 is Location of  Agent Name , and @1 is the counter in my report's criteria level.

max(RCOUNT(1)) gives the number of records in the report. so if this report contains 33 records, then Max(Rcount(1)) will give 33 for each row. Add any column in selection pane and then clear the column formula and add max(RCOUNT(1)) under formula and then Hide this column in Criteria Level to hide from actual results.

Report Results will be something like the below and all End Users really love to see the results this way: To add more to it, Provide users flexibility to choose Agents from the Prompt or You can use this Narrative views for the heading for the Drill reports.

Agent Brian has made total 7 calls for today : Drill Link from Summary report to Detail report and using Narrative View

I use Narrative Views in almost all of my reports , even use them to use the different HTMLs for Heading Type and Color . Love them.

OBIEE 11G - Interview Questions for Experienced Professional

OBIEE 11G - Interview Questions for Experienced Professional.

OBIEE Interview depends on the years of experience one posses in OBIEE and the questions really should come from the CV based on areas of expertise. Below questions are for experience level more than 5 years.

1) Tell me about overall OBIEE Experience: 

Answer should include experience with RPD development, Multi user development experience if any , Reports and Dashboard development , Security , Usage accelerator , performance tuning , End User Requirement gathering , OBI Mobile App , BI Publisher , Informatica , DAC etc as per the job requirements. But if you have worked in any of the components listed above then mention it separately.

2) What is the purpose of Alias Tables. What is Conformed Dimension.

3) What are Select Views in Physical Layer and do they have any performance impact in OBIEE. If yes then how can they be avoided.

4) Why snowflake schema becomes necessary  sometimes and can they be avoided all together. What is the impact.

5) How can data level security is handled.

6) What is overall Security Architecture of OBIEE 11G. How can we handle Security Groups within weblogic.

7) Why do we create Multiple LTS, what is the purpose.

8) Where in RPD we create Outer Joins if we have to.

9) What is Content Level Filter and why it is used.

10) What does Logical Level means to you and what happens if that is not defined.

11)What are the steps to troubleshoot if you get an error while running a report : None of the fact tables exists at this level of detail.

12) When you login to OBIEE , you see a message that There are no reports to show ? What does it mean , How can you set your default dashboard.

13) User is trying to export to pdf format and not able to and getting error : Please contact your administrator fro more details, what will be the steps you take to troubleshoot.

14) How to convince users not to use OBIEE as data extraction tool?

15) Is there any limit on how much data we can export from OBIEE? If yes, then how much and can we increase and decrease the limit ?

16) Can we join two unrelated Subject areas to create a single output report? if yes, then how?

  

In Progress : More to come with possible answers. Answers all depends on experience of individual as these are not theoretical questions.