Dashboards, Applications & Business Intelligence

Please read the Multi-Org Background information

Virtual Private Database (VPD)

The Virtual Private Database (VPD) feature allows developers to enforce security by attaching a security policy to database objects such as tables, views and synonyms. It attaches a predicate function to every SQL statement to the objects by applying security policies. When a user directly or indirectly accesses the secure objects, the database rewrites the user’s SQL statement to include conditions set by security policy that are visible to the user.

Database Schema - Access to one operating unit

Database Schema - Access to multiple operating units

You can rewrite reference views that join data from multiple single organization views with the security policy attached to one secured synonym and the remaining reference to _ALL tables instead of single organization views. This improves performance because the policy is used once for the reference views that join data from multiple single organization views.
Multiple Organizations Security Policy Predicate

Synonyms replace single organization views that contain the CLIENT_INFO predicate attached to them. When installing, you must attach a security policy function to the multiple organizations synonyms. This indicates that the security is in place irrespective of the tools used to access the data.

The security policy function returns different predicate based on the number of accessible operating units. An application context attribute “ACCESS_MODE” is set based on the accessible operating units. Context sensitive security policy is used for multiple organizations access control to minimize the coding impact. The multiple organizations code in previous releases works in the context of only one operating unit. It was not anticipated that multiple organizations access would be supported. A solution to code impact is to change the policy predicate whenever needed. For example, when you open a form using a responsibility that can access multiple operating units and when you select an operating unit, the operating unit context is established and you do not need to modify the code that is used for validation from that point onwards, if the synonyms return data for the selected operating unit.

If the access mode is M (Multiple), then the policy predicate issues an EXISTS sub-query to a global temporary table. The global temporary table is a new feature in Oracle 8i. The table stores and manipulates data specific to a SESSION or TRANSACTION. If the access_mode is S (Single), then a simple equality predicate is used for performance reasons, since it is cost effective in comparison to the temporary table. An access mode A (All) is incorporated to bypass the security for functionality that needs full table access. If the access mode is not set or is NULL, then a simple predicate that uses the CLIENT_INFO value for ORG_ID is used for the policy predicate to support backward compatibility.

MO_GLOBAL.Org_Security function:

FUNCTION org_security(obj_schema VARCHAR2
                                           obj_name VARCHAR2)RETURN VARCHAR2
IS
BEGIN
  --
  -- Returns different predicates based on the access_mode
  -- The codes for access_mode are
  -- M - Multiple OU Access
  -- A - All OU Access
  -- S - Single OU Access
  -- Null - Backward Compatibility - CLIENT_INFO case
  --
 IF g_access_mode IS NOT NULL THEN
   IF g_access_mode = 'M' THEN
     RETURN 'EXISTS (SELECT 1
                       FROM mo_glob_org_access_tmp oa
                       WHERE oa.organization_id = org_id)';
   ELSIF g_access_mode = 'A' THEN -- for future use
     RETURN NULL;
   ELSIF g_access_mode = 'S' THEN
     RETURN 'org_id = sys_context(''multi_org2'',''current_org_id'')';
   END IF;
 ELSE
   RETURN 'org_id = substrb(userenv(''CLIENT_INFO''),1,10)';
 END IF;
END org_security;

The simple predicate using CLIENT_INFO is used for the following case:

* Access control is not enabled for older releases of the applications, it is not backward compatible: You cannot enable the multiple organizations access control feature for all products simultaneously because multiple organizations views are shared between products at different levels. For example, if you choose to upgrade Payables but choose to keep an earlier version of Purchasing then Payables is access control enabled, but Purchasing is not. Therefore, Purchasing must replace the views it shares with Payables, such as PO_VENDOR_SITES, and PO_HEADERS, with secured synonyms. The secured synonyms must work as before for Purchasing, since you have not upgraded Purchasing and Purchasing still relies on CLIENT_INFO.

The simple predicate using current_org_id is used for the following cases:

* Access control is limited to only one operating unit: In this case, the access mode is 'S'. An example is when a user can access to only one operating unit through the MO: Security Profile or the MO: Security Profile is not set and the user access depends on MO: Operating Unit.* Access control is enabled with access to multiple operating units: The security profile provides access to multiple operating units, but in the scope of a transaction since the operating unit is controlled, a simple predicate eliminates additional changes to the server and client side code.

The complex predicate is used for these cases:

* Access is enabled and the security profile gives access to multiple Operating Units. The access mode is set to 'M' for this case.

For example, any statement on RA_CUSTOMER_TRX (synonym to which the security policy is attached) is dynamically modified to use the policy predicate.

A simple query by the user:

SELECT trx_number from ra_customer_trx

is modified at runtime if the responsibility can access multiple operating units to:

SELECT trx_number from ra_customer_trxWHERE (EXISTS (SELECT 1FROM mo_glob_org_access_tmp oaWHERE oa.organization_id = org_id))

or is modified at runtime if the user's access responsibility can access one Operating Unit with access control enabled for the module to:

SELECT trx_number from ra_customer_trxORG_ID = sys_context(’multi_org2′,’current_org_id’)

Overview of Multiple Organizations Architecture for release 11 and prior

The primary objective of multiple organizations architecture, introduced in Oracle Applications Release 10.6, is to secure data from unauthorized access by individuals belonging to different operating units in an enterprise.

A new or fresh installation of an Oracle Applications instance does not automatically enable multiple organizations. The system administrator creates operating units using the Define Organizations window in Oracle Human Resources Management System (HRMS), and runs the Convert to Multiple Organization program from AD Administrator to enable the multiple organizations feature. Typically, the system administrator defines “MO: Operating Unit” profile at Responsibility and/or User level. The “organization_id” of the “MO: Operating Unit” profile option value filters the transactional data. The CLIENT_INFO application context space stores the multiple organizations context value.

Multi-Org views use the following WHERE clause to filter application records:
‘org_id = substrb(userenv(”CLIENT_INFO”),1,10)’

Overview of Multiple Organizations Access Control Architecture (Release 12)

The Access Control feature in Release 12 allows the user to enter or query records in one or more operating units without changing application responsibility. It is the system administrator’s discretion to either implement the feature or use the same multiple organizations profile option setting available before Release 12 by using the single operating unit mode (i.e. one operating unit for a responsibility).

In Release 12, the multiple organizations context value is no longer initialized by the FND_GLOBAL.APPS_INITIALIZE routine thereby reducing unnecessary context setting and resource consumption for applications that do not use operating unit context for data security.

To use the single operating unit mode, you must set the value for the “Initialization SQL Statement – Custom profile” to “mo_global.init(’S',null);”. This initializes the operating unit context based on the “MO: Operating Unit” profile option and the “MO: Security Profile” profile option must not be set.

Fresh install of Release 12 Application is enabled with multiple organizations, however, the system administrator must create operating units to use multi organizations sensitive application products. The user can create new operating units in the Accounting Setup Manager page in addition to HRMS’s Define Organizations page.

Here is some background information

Multiple organizations architecture (Multi-Org) was introduced in Release 10.6 to secure the data by operating unit. In Release 10.7, Oracle added a column ORG_ID to each base table to partition the data by operating units. The partitioned tables are renamed with the suffix, ‘_ALL’, and their corresponding secured views are created in Applications (APPS) schema. The following diagram shows a single organization view in the APPS schema.

Multiple organizations views restrict access by filtering records for an operating unit assigned to the application responsibility set for the “MO: Operating Unit” profile option. This profile option value is cached in application context, and is initialized when calling the FND initialization routine. The FND CLIENT_INFO predicate includes all multiple organizations views and SQL statements that require multiple organizations security. The FND_CLIENT_INFO function retrieves the ORG_ID value stored in the application context. This value is valid for a session, unless explicitly changed by the calling procedure.

Use the _ALL table in the SQL statement to retrieve information irrespective of the operating unit. To increase the flexibility and performance in a multiple organizations environment and provide the same level of data security, the DBMS Virtual Private Database (VPD) feature replaces the CLIENT_INFO function.

Continue reading Multi-Org structure - Vitual Private Database

Search results are very dynamic. They jump around everyday as new content is added and as new websites are added.

I don’t normally use Yahoo for my search needs but today I happened to casually google search for Dashboards on Yahoo and was pleasantly surprised by what I found.

Yep, InfoCaptor is one of the top three dashboards that show up on Yahoo, just behind Cognos!

It would be a real party time when it happens on Google

Dear Consultants, Developers and all readers,

Here is an interesting thought and let me know if it chimes well with you.

I believe the following idea if implemented could give a significant advantage and boost your marketability.

Now, everybody has a resume or a CV, well ofcourse,this is the first step you execute when you step out of college.

All of us store our resume in a more or less standard Doc format, so how about having an online version of your resume?

Sounds interesting?

This online version would be just a single web page having your resume content. You can either put a summary of your resume or complete details if you like.

So what will a single page resume serve for?

When I suggest someone to start their own blog, I see lot of enthusiasm but no follow through. Few of the main reasons cited is “Don’t know what to write about” or simply “Have no time” or “Is it worth investing the time?”

Creating a single page blog is just the beginning and helps in breaking the inertia. Once you create your resume blog, the web-url becomes your signature URL and you can put this URL back on your resume or when you update your CV on job sites like Monster or Dice.

Appsbi.com started as a professional journal and it has become modestly huge than I orginally thought of. But it did not happen overnight. No big success happens overnight unless you have a Genie in a bottle. But interestingly all successes appeart to be an overnight success, isn’t it?

Start small, take the first step and do it now

Appsbi.com has tremendously helped me for my consulting career and is now helping as a marketing engine for InfoCaptor

Similarly, I believe that creating your own blog will help you in one way or the other, if not today then sometime in future.

The blog that you will create will act as one of the building block in your career.

Now let us attack the negatives or the holding back thoughts

“Don’t know what to write about”

Just make it your online professional journal. Start by putting up your resume online and slowly add more pages. Don’t feel pressurised to add more contents. Just add what comes naturally to you. Don’t worry about things like “is it worth posting”. Even if you have a small piece of code or found a new technique, just post it.

You should keep personal stuff out and stick with work related things. Better have a seperate personal blog.

“Have no time”

It would take 5 minutes to create a blog and add your resume to it. Then add a weekly recap of your work related things to it. This would take another 15 minutes. If weekly is too much then stick with bi-weekly or monthly. Is the initial 5 minutes and 15 minutes / week or /month asking too much?

“Is it worth investing the time?”

Do you call 15 minutes/month investment of time? You will eventually know if it is worth the time if you stick to it.

So where do you begin?

Create a blogger account http://www.blogger.com , pick appropriate blog name and add your first post right now within 5 minutes.

Blogger is a free service by Google.

Another free service similar to blogger is to create an account at http://wordpress.com/

Let me know if this is a good idea and if you really followed through send me your blog url by commenting to this post.

This is the screenshot of a customer satisfaction survey dashboard for a client. The dashboard was developed using InfoCaptor and the data is sourced from MySQL database.

Thermometer in Dashboard

To create this chart, your SQL should return only one column and one row

select sum(quantity_sold) as qty_sold
from [detail_data$]
where fiscal_year = 2001
and country_region like ‘Americas’

(Complete Steps) Create Thermometers in Dashboard

Tags: thermometer chart, dashboard thermometer, dashboard gauges, jfreechart thermometer

Dashboards continue to get a lot of attention in most organizations, Not just because of their visual appeal but they help organizations communicate strategy, monitor and adjust the execution of strategy, and deliver insights to all.

Let us break this into simple terms.

Dashboards, in the most simplest terms is a collection of different reports, all in one page or view. Now let us tune this statement. These reports in the dashboard contain high level summary information rather than detail transactions. For e.g in traditional reports, you see a detail listing of orders or invoices with neat formatting, totals and sub-totals whereas the reports in dashboard mostly contain aggregate information such as total orders for current month, total invoices for current month, profit for this month, total cost for this month. If you notice, the common term here is total (sum, aggregate, or high level) view. Now you may show these numbers as they are or canvas them in beautiful charts.

Once you have your base information that you want your users to see, you display them in an appropriate format on the dashboard. Some information is better suited to be in tabular and some can only make sense in a Line chart or Stacked bar chart.

“If you ain’t got no goals, you ain’t got nothin”

Now smart companies want to grow their business and one thing they often ask is How did we do and How can we improve? So they try to attach goals to these numbers.
For e.g If last year in July, if the total Orders were 10,000 and if they have been growing at 30% rate historically then they would like to see a 30% jump in the Total Orders for July of this year. This is known as “key performance” monitoring. They would like to visually see if total orders reached the goal or not. One way to visually see this is through a Gauge where the indicator can clearly indicate if it met the goal or not. If the indicator is in Green then it met and if it is in Red or yellow then there are some issues that needs to be investigated.

So when something is not right, users would like to know more about the details. This act of finding more details is known as “DRILLING”. In the Drills users would like to see a detailed listing of all the orders or some other information so they can investigate.

In a nutshell, your dashboard becomes your first interface to the information in your system and hence it is very productive to the end users for their analysis.

Here are few types of dashboards their purpose and benefits

Benefits of Finance Dashboard: Monitor Revenue, Profit, Loss and Expenses

Benefits of HR Dashboard: Monitor Employee activity, Headcount growth, Expenses etc

Benefits of IT dashboard : Monitor system activity, this may include database monitoring, server monitoring, memory metrics etc

Benefits of Inventory Dashboard : Monitor Inventory levels, warehouse and item information, orders etc

To get a feel of dashboards, try this Dashboard Designer, you download it and it will launch a pre-defined dashboard based on sample Excel data.
You feel right at home with once you see the dashboard in action.

One of the new features available in the upcoming release of InfoCaptor is the ability to auto-refresh multiple dashboards and automatically cycle through each dashboard.

You can set the cycle rate by clicking on the options button, click on the “Auto Refresh & Cycle Each Dashboard” checkbox and select the refresh rate at the desired number of seconds.

Issues: Currently if the refresh rate is set very low, Java is not able to trigger the Garbage collection and hence it creeps memory and eventually the application becomes un-responsive.

Ideally if the refresh rate is set to 2-3 minutes then it should run fine on a good machine. Ocassionally you may need to restart InfoCaptor if it starts slowing down.

Along with this we are also working on launching multiple dashboards at once using a common config file. This should go handy with the Auto-refresh and cycling option.

You can test drive the auto-cycle option using this test version

What are the benefits of this feature?

We already have had the auto-refresh feature, and auto-cycling of dashboards during refresh is a natural extension of the feature. We have numerous users and clients asked for the cycling feature so that a DBA or Sysadmin person can setup a dedicated desktop just to monitor the metrics. If the metrics are corporate goal specific then they could also be broadcasted over a TV or a huge display for employee awareness.

Tags: dashboard on tv, tv dashboards, auto refresh dashboards, auto cycle dashboards

When I started my career with Oracle Apps, I wished so hard if there were any books that would get me jump started. But times have changed now, for new comers to Oracle Apps there is ample of information available all around the web. This is an advantage and might also lead to some confusion as to where to begin.

Recently, Roel of http://www.altasen.com/ send me these awesome beginner books. I quickly glanced through it and knew at the first sight that these are truly useful resources for any beginner in Oracle Apps. So I asked one of my colleagues Kamlesh, who just started his career in Oracle Apps.

Flexfields for Oracle EBS
——————————

—————–

This book, I wished I had it in my early days and it would not had taken
that much of time to understand the topics of Flexfields and good screen shots to
understand the topics.

The topics of KFF and DFF are very well explained enough to be an expert.

BI Publisher for Oracle EBS
——————————

At the very first this books will tempt you to open through all the pages because of its cool green shiny
cover with nice animated nerdy charater.

This book is a real good start for myself. Havent touched those big
fat books for I was scared to invest considerable amount of time to commit for knowing BI publisher.

This book gives a very ground up start . With the simple and easy languange with lots of
screen shots to catch up the text description.

The book starts with the history of versions and the advantage of
learning BI publisher is that BI publisher developer can develop reports not just for Oracle but
also for Seibel,JD edwards and People soft.

The book gives step by step guided practice lessons. In short I would recommend this will be a great selection to start with

———————

Apart from the above two books, there are others in the series like System Administration and Workflow. I am looking forward to more topics being covered in similar style.