Discoverer Business Views Overview

What are Business Views that Oracle refers to within all the intelligence products like BIS, Discoverer for APPs and Daily Business Intelligence?

• A Business View is a database view on top of Oracle Apps Tables
• Logical view encapsulating key columns and extra information from Flex Fields (Key and Descriptive)
• Easy and Efficient mechanism to reflect any Flex Field changes
• Pre-built Security encoded within each Business views.
• Security is defined based on Operating Unit, Inventory Organization and Business Groups
• Pre-seeded EUL Folders use Business Views as their data source
• Business Views is comparable to Noetix views

Why do we need to Regenerate Business Views?

• Oracle just provides the base skeleton of the view.
• This skeleton view usually has a suffix of _FV (FV = Full View)
• This skeleton view has all the required columns plus instructions on how to extract the Flex Field Columns
• Sample instruction '_DF:PER:PER_GRADES:grd‘
• Business View Generator Program reads the skeleton view definition and creates a complete _FG view (Full Generated)
• EUL Folders point to the _FG views
  

If you found the above article useful, you may find my Free Discoverer Tools and Dashboard Designer tools beneficial for your Organization.
BI Dashboards, Drills, Monitors, Document Discoverer Workbooks

Free Discoverer Workbook Dumper

If you have not already checked this free utility then now is the time to save all the frustrations!

Check this http://www.infocaptor.com/workbook-dump.php

Also, I would really appreciate if you can spend 1 minute of your time and respond to this POLL question

http://www.infocaptor.com/phpBB2/viewtopic.php?t=15

Thanks and Cheers!

If you found the above article useful, you may find my Free Discoverer Tools and Dashboard Designer tools beneficial for your Organization.
BI Dashboards, Drills, Monitors, Document Discoverer Workbooks

View Discoverer Reports through Oracle Apps Self Service

In this article we will discuss how to enable Discoverer reports to be viewed through Oracle Applications Menu structure.

We will take an example report say "AR - Invoice Aging Buckets" with developer key "AR_INV_AGING_REPORT"

Let us assume there is a seperate responsibility to view AR reports such as "Accounts Receivables Reports". We also assume that there is a menu (top level) called "ALL_AR_REPORTS" which is attached to this responsibility using the Define Responsibility form.

Using the Discoverer User tool we share the "AR - Invoice Aging Buckets" to "Accounts Recievables Reports" responsibility.

Now we will begin creating the Function and menus for this workbook.

• Navigate > System Administrator > Application > Function

• Go to the Description TAB
• Enter Function Name = AR_INV_AGING_REPORT_DWB
• Enter User Function Name = AR - Invoice Aging Buckets
• Enter Description = AR - Invoice Aging Buckets

We appended a suffix of "_DWB" at the end of the function name to indicate this is a Discoverer Workbook function. You may choose any other name for your function but it is a good idea to keep the function name same as the developer key for easy identification and tracking.

• Now Go to the Properties TAB
• Select Type = SSWA plsql function that opens a new window (Kiosk Mode)
• Maintenance Mode Support = NoneContext Dependence = Responsibility

Selecting the Kiosk Mode opens the Discoverer workbook in a new window.

• Now Go to Form TAB
• Enter Parameters => “workbook=AR_INV_AGING_REPORT&viewer=Y”

In the above we entered the workbook developer key and forcing the report to be opened using Discoverer Viewer. (If your ICX profile is set to use Discoverer Viewer then you may omit the part from "&" )

• Now Go to Web HTML TAB
• Enter HTML Call = OracleOASIS.RunDiscoverer and then Click on the Save Button

Now that we have created our Form Function we will create a new menu to hold this function and then attach this new menu to the "ALL_AR_REPORTS" menu.

Navigate > System Administrator > Application > Menus

Create a new menu "Aging reports"

Then attach the function to this menu



Now query the main menu "ALL_AR_REPORTS"



and add submenu to this main menu

Now we have finally linked all the pieces together. Wait a minute, just ensure that the user who needs to access this report is given the "Accounts Receivables Reports" responsibility. So when user "ARUSER" logs onto Self service signon, the user will see the "Accounts Receivables Reports" responsibility as part of the menu list. Clicking on this responsibility, the user will see the "Aging Reports" as one of the sub-menu and underneath there will be the link to the discoverer workbook. If "all is fine", clicking on the report link will launch a seperate window where user can enter parameter values and execute the report.

NOTE: If the menu changes does not appear as expected then ask your DBA's to bounce the Apache middle tier. Also, make sure all the Discoverer profile values are set properly.

Have a nice day!

If you found the above article useful, you may find my Free Discoverer Tools and Dashboard Designer tools beneficial for your Organization.

BI Dashboards, Drills, Monitors, Document Discoverer Workbooks

Raptor: Toad Competition here I come!

http://www.oracle.com/technology/products/database/project_raptor/index.html

Oracle just made the public release of Raptor, its new graphical interface to its database.
"
Project Raptor is a new, free graphical tool that enhances productivity and simplifies database development tasks. With Project Raptor, you can browse database objects, run SQL statements and SQL scripts, and edit and debug PL/SQL statements. You can also run any number of provided reports, as well as create and save your own
"

Here are some of the questions that come up in my mind...
Why it took so many years for Oracle to come up with this tool? And when it did why now?
Why is it free? What happens to the competiting products?

There was one good thing that happened to all the ISVs(Toad , etc) that develops and sells graphical interface to Oracle database. I am sure most of them are doing good selling these GUI tools. With the entry of a free tool there is a slight punch to these ISVs, its not that this is the first free GUI tool (since there are freeware and Open source already available) but since this is from Oracle it makes a lot of difference.

With the threat from Open source database like MySql, Oracle recently released an Xpress Edition of Oracle 10g and now introducing Raptor as a freeware, Oracle intends to stop the migration to other Open Source databases. Now there is more incentive for any ISV or micro-ISV to consider Oracle as the development platform.

With regards to competition (ISVs who make living selling GUI for Oracle database), I think the market has become more healthy and still there is lot more room to innovate and keep an edge.

Looks like many of Raptor's interface and components are borrowed from JDeveloper (which is a mature product in itself).

(PS: InfoCaptor is developed completely using JDeveloper)
http://www.InfoCaptor.com <--- Create Drills, Dashboards, Monitors

Father-Son Team Builds Hospital Intelligence From Scratch

A father-son team of physicians has taken aim at the medical field -- notoriously behind the curve when it comes to using business intelligence to gather and analyze data, especially hospital records -- with a software product that combines elements of both business process management (BPM) and BI analytics. Called Micro-Cares, the product tracks the treatment of patients in a hospital setting, and its creators say it could represent a revolution in how doctors think not only about patient care, but the training of physicians.

read the complete article

http://www.InfoCaptor.com <--- Create Drills, Dashboards, Monitors

Restrict Discoverer Data access?

Restrict Discoverer Data access by apps user_id, org_id, responsibility etc..

A simple solution is to create a simple sql folder in your EUL with the below query

select fnd_global.resp_id,
fnd_global.resp_name,
fnd_global.user_id,
fnd_global.user_name,
fnd_global.employee_id,
fnd_global.per_security_profile_id,
fnd_global.org_id
from dual;

You can use this folder joined to any other folder to restrict data based on user_id, employee_id or org_id

Alternatively you can incorporate the above query in your view to restrict data directly inside the view and use that view in the discoverer EUL folder.

Have a nice day!
http://www.InfoCaptor.com
<--- Create Drills, Dashboards, Monitors -->

How to pick the best Idea?

If you are contemplating on starting your own software business, here are some points worth noting to evaluate your software idea. All the below details are from the spreadsheet mentioned in this article (What is your idea score?)


Do you know atleast one person who needs this solution including yourself?
(A solution has to be for a person or a company, having atleast one person is a good start)

Can you describe the solution in one sentence
(How will you tell your audience what your product/solution does?)

Will the solution save money for the user?
(this is defining the ROI, is it Money saver or Time Saver?)

Will it save time for the user?

Will it Entertain the user?

Will it Educate the user?

Between 1 and 10, how do you rate the necessity of this solution?
(10 - Can't live without it or cannot run business)

How many competitors exists who are either providing the exact same solution or part of the solution that you intend to provide

How strong is the presence of your competitor?
(think in terms of their product downloads, check user forums, google rank, customer profiles etc, Rate from 1-10 where 10 indicates a very popular product, website and company)

Is there any USP(Unique Selling Point) for your solution

(define barrier to entry)
How many Man Months will it take to develop your solution?

(define barrier to entry)
Are you inventing new stuff in order to provide this solution (algorithm, protocol etc)


Is the solution for Consumers

Is the solution for Business

Do you have all the resources to complete the solution?

Do you know how to complete the solution?

Will you use your own solution if you had the exact same pain/necessity as your target audience?

For how many days have you been thinking about this idea?
(passion factor)

http://www.InfoCaptor.com

Documenting EUL, Discoverer Workbook, Worksheet

Do you need to document EUL Schema, Oracle Discoverer Work-books, worksheets?

Discoverer is a great ad-hoc and BI tool. People working and using this tool often need to document the EUL (End User Layer) but there is no straight forward ways to do it. I have seen clients run some scripts against the EUL meta data and then create a word doc file for later referencing.

Lets focus on some of the key EUL tables which could be used to document the different Discoverer components.

In the below examples, replace "disco" with the appropriate schema owner of EUL tables. In my case it is the disco owner and hence the examples

List of Business Areas
select ba_name "Business Area", ba_created_by "Creator", ba_created_date "Creation Date", ba_updated_by "Updated By ", ba_updated_date "Last Update Date" , ba_id
from disco.eul4_bas
where ba_created_by like 'DISCO'

List of Folders
select b.ba_name, f.obj_name folder_name, f.obj_id, f.obj_ext_owner Owner
from disco.eul4_objs f,
disco.eul4_ba_obj_links l,
disco.eul4_bas b
where 1=1
and f.obj_id= l.bol_obj_id
and b.ba_id= l.bol_ba_id
and upper(b.ba_name) like upper('Video Store Tutorial')
and upper(f.obj_name) like upper('%')
order by b.ba_name,f.obj_name

List of Folder Items
select i.exp_name item_name, i.exp_id, i.it_ext_column, f.obj_name folder_name, b.ba_name
from disco.eul4_expressions i,
disco.eul4_objs f,
disco.eul4_ba_obj_links l,
disco.eul4_bas b
where f.obj_id= i.it_obj_id
and f.obj_id= l.bol_obj_id
and b.ba_id= l.bol_ba_id
and upper(i.exp_name) like upper('%')
and upper(b.ba_name) like upper('Video Store Tutorial')
and upper(f.obj_name) like upper('Products')
order by b.ba_name,
f.obj_name,
i.exp_name

List of Folder Joins
select key_description
from disco.eul4_key_cons
where fk_obj_id_remote=100027 or key_obj_id=100027

(substitute the numeric id from the Folders obj_id (see the List of Folder query)

List of Workbooks
select doc_name "Document",doc_developer_key, doc_description "Description" from disco.eul4_documents

Documenting Workbooks and worksheets ..........continued ....

http://www.InfoCaptor.com
<--- Create Drills, Dashboards, Monitors

Discoverer Presentation Options

Discoverer Viewer allows passing all Presentation Options as a binary string into the URL in the following syntax

Discoverer Viewer version 4.1.42 and higher introduces a new URL parameter called "_po" which is used for passing all the Presentation Optionsfrom URL without having to modify the XML/XSL files.

Each option is set on or off by the binary value of 1 or 0.

there are 23 presentation options controlled by _po parameter. Each prestentation option correlates to one of the 23 positions in the parameter.

Below is a list of the presentation option and its relative position: 1. Logo
2. New Connection
3. Open Workbook
4. Options
5. Exit
6. Help
7. Top Blue Bar
8. Worsheets Link
9. ReRun Query
10. Presentation Option
12. Export
13. Parameters
14. Workbook Name
15. Data(Including Worksheet Title)
16. Chart
17. Page Item
18. Drill
19. Pivot
20. Row Numbers
21. Bottom Blue Bar
22. Oracle Copyright
23. Show XML

For e.g if you want to turn off the parameters(13th position) then the PO would be like this.
this:_po=11111111111101111111111

Change the ICX profile ICX: DISCOVERER PARAMETERS (ICX_DISCOVERER_PARAMETERS)at the site, user or responsibility level and it will be added automatically to the URL when an E-Business Intelligence (BIS/EDW) analysis workbook is called.


http://www.InfoCaptor.com <--- Create Your Own Drills, Dashboards, Monitors <-----Browse Oracle Meta data, Discoverer EUL Data----->
Use InfoCaptor to store all your frequently used queries