If you ever have a requirement in which you need to create an Analysis but do not find the right Subject Area for it, then you can use the "Create Direct Database Query" that OBIEE offers.
Or if you want to place the results of a BIP sql query on a Dashboard (by creating an Analysis).
Depending on your requirement, you may choose to use this option.
Lets look at the steps to do this:
1. Login to OBIEE (BI Tool)
2. Select New > Analysis > Create Direct Database Query
3. On the Criteria tab
a. Connection Pool = "HCM_OLTP"."Connection Pool" (add the quotes as well)
b. SQL Statement = SELECT Last_Name from PER_PERSON_NAMES_F
Note: For FSCM use "FSCM_OLTP"."Connection Pool"
4. Click the "Validate SQL and Retrieve Columns"
5. Select the Results tab
You should see the results that your query returned
6. Save the Analysis
Regards,
Brendon Charles
and can you add this analyse to a dashboard and add some dashboard prompts?
ReplyDeleteYes, you can, please find details below:
Delete1. Edit the Analysis to add a WHERE condition to the SELECT query:
a. As shown below:
SELECT Last_Name FROM PER_PERSON_NAMES_F WHERE Last_Name LIKE '@{P_Last_Name}{Direct}'
b. Now when you move to the Results tab, you will get the below response:
No Results
The specified criteria didn't result in any data.
This is perfectly fine.
c. Save your changes
2. Create a Dashboard Prompt
a. New > Under Analysis and Interactive Reporting > Dashboard Prompt
b. Select any random Subject Area, it does not matter because we will be creating a Variable Prompt
c. Click on + symbol and select the “Variable Prompt” option
d. In the dialogue, enter the following values:
Prompt for = Presentation Variable
The name of the Presentation Variable is P_Last_Name (add this to the Empty Text box)
Label = Last Name
Description = This is the prompt for Last Name
User Input = Text Field
Click on the “Options” dropdown
Variable Data Type = Default (Text)
Require user input = checked
Default Selection = None
Text Field Width = Dynamic
e. Save the Dashboard Prompt
3. Create a Dashboard
a. New > Under Analysis and Interactive Reporting > Dashboard
Provide the following details:
Name = Person Name Dashboard
Description = This dashboard displays the name of the person based on the name you provide in the prompt
Location = /Shared Folders/Custom/Dashboards
Content = Add content now
b. On the Edit Dashboard Page
Add 2 columns
Drag and Drop the Prompt created to the Column 1 section
Drag and Drop the Analysis to the Column 2 section
Click on Preview, you should see the Last Name prompt above and No Results in the Section below
Now Save the changes you made to the Dashboard
Click Run
Provide the value in the text box and click on Apply
You should see the results returned in the Analysis below
Best Regards,
Brendon
Hello,
ReplyDeletewithout the role : "BI Administrator Role"
I have the error message :
"User or application role has not been granted the Direct Database Access privilege to access the database 'HCM_OLTP'. Please verify the User/Group Permissions in the Oracle BI Administration Tool."
How do you give access to user who doesn't have the "BI Administrator Role"?
To add the BI Administrator role to your user, you need to follow the below steps:
ReplyDelete1. In the Fusion Application, go to Setup and Maintenance
2. Search for the task called Manage Duties
3. This will open up a new tab, Oracle Entitlements Server
4. Under Search, For = Users, In = Global Scope (should change automatically)
5. Enter the user name in the search box and search
6. The user should be returned in the list below
7. Select the user, click the Open icon
8. A new tab open to the right region of the page
9. Go to the tab called Application Role Assignments
10. Click on the Map icon (+ sign)
11. In the dialogue, Application Name = obi, Display Name (Starts with) = BI Admin, click the Search button
12. The BI Administrator Role should be returned in the results below, select it if not already selected
13. Click the Map Roles button
14. Ensure a new structure is created (obi > BI Administrator Role) for the user under the Application Role Assignments tab
15. Go back to the main application, Navigator > Scheduled Processes
16. Submit the process called Retrieve Latest LDAP Changes
17. Now try again and confirm
Best Regards,
Brendon
is there any way by using direct database query create analysis and create action link by passing parameters
ReplyDelete