OBIEE 11g Multiple Values in single column using the advance tab and similar way in the Criteria Pane

Multiple values in single column using union operator in the advanced tab and using the operators in the selected column

For my example,

I am creating two analyses both with two columns

•   Product – Brand and Base Facts - Revenue
•   Product – Type and Base Facts- Revenue

Step 1

•         Create an analysis with Product – Brand and Base Facts - Revenue

   

•          Go to the advanced tab and copy the SQL

         

•          Without closing the window, create a new analysis

Step 2

•  Create an analysis with Product – Type and Base Facts- Revenue
• Then go to the advanced tab, under SQL Issued click on New analysis

•              In the Analysis Simple SQL Statement join the two queries using the union operator.

Such as,

SELECT "Product"."Type" saw_0, "Base Facts"."Revenue" saw_1 FROM "Sample Sales" union SELECT "Sample Sales"."Product"."Brand" s_1, "Sample Sales"."Base Facts"."Revenue" s_2 FROM "Sample Sales" ORDER BY saw_0

•        Then click ok. You will see that Brand and type is joined into one column.

       

 Combine results based on union, intersection and difference operators

•       The other way to join multiple values is using the option in the selected column pane.
•        Click on the + sigh in the far right hand side.
•        Now, In the Result Column, You will see two criteria’s
•        In the first criteria Drag Product – Brand and Base Facts – Revenue
•        In the second Criteria Drag Product – Type and Base Facts- Revenue

   

•              Similarly as above you will get the same results.

       

Configure Oracle BI Scheduler Email Settings in OBI 11g

SETTING UP SMTP CONFIGURATION TO SEND REPORTS THROUGH EMAILS.

·         Log on to Enterprise Manager

·         On the left hand side click on Business Intelligence-> Coreapplication

·         On the right hand side click on the Deployment Tab-> Mail subtab

This is the only place where we need to setup email smpt setting. The recipients emails can be written while creating an agent.

If we want to configure the mail settings for another user instead of the old one or if we want to change the password for existing username then we need to delete the mail.server (Password) entry in EM.

1. Log in to Enterprise Manager, navigate to the WebLogic Domain -> bifoundation_domain using the left-hand navigation panel. 

2. Right click bifoundation_domain and Choose ‘Security’ -> ‘Credentials’ from the menu. 

3. Open the ‘oracle.bi.enterprise’ map, and select the ‘mail.server’ key. 

4. Choose ‘Delete…’ 

5. Re-start the system. 

The screen shot is as follows-

TO DELETE SMTP SETTINGS IN OBIEE 11G ENTERPRISE MANAGER 

Enter the new settings

Ø  Login to Enterprise Manager >Deployment > Email and Remove user name and password. (in case of new username).

Ø  Login to Enterprise Manager >Deployment > Type the new password (in case of new password) 

Ø  Click apply and re-start the server.





 



Create Variables using Administration tool

Steps to Create variables
1)      Open the Variable Manager.
2)      Create an initialization block.
3)      Edit the data source.
4)      Edit the data target.
5)      Test the initialization block query.
6)      Use the variable to determine content.
7)      Verify your work.

1)      Open the Variable Manager.

Manage > Variables

2)      Create an initialization block.

3)      New > Repository > Initialization Block

4)      Name it as current Period and click on Edit Data Source.

5)      Click the Edit Data Source button to open the Repository Variable Initialization Block Data Source dialog box.

6)      Click the Browse button to open the Select Connection Pool dialog box and double click on Connection pool object to select it.

7)      The connection pool is added.

8)     Enter the following SQL to determine the value of the current day, month, and year by finding the maximum value of the period key (BILL_DAY_DT) in the fact table:

SELECT CALENDAR_DATE, PER_NAME_MONTH, PER_NAME_YEAR FROM BISAMPLE.SAMP_TIME_DAY_D WHERE CALENDAR_DATE = (SELECT MAX(BILL_DAY_DT) FROM BISAMPLE.SAMP_REVENUE_F)

9)      Click Test and confirm the expected results are returned. In this example, the results are determined by the data in the sample database used for this tutorial, which holds data through December 2010. Close the window.

                       

10)   Create Variable

Click Edit Data Target to open the Repository Variable Initialization Block Variable Target dialog box.

11)   Use the New button to create three new variables: CurrentDay, CurrentMonth, CurrentYear. The order is important. Click OK to close the Repository Variable Initialization Block Variable Target dialog box.

12)   Leave the default refresh interval set to every hour. This means that the variables will be reinitialized every hour.

13)   Click the Test button and check the results. In this example, the results are determined by the data in the sample database used for this tutorial, which holds data through December 2010.

 Close the window.

14)   Check your work in the Variable Manager. Close the variable Manager and save the repository and check consistency.

15)   Test your work.