Dashboards, Reporting & Business Intelligence

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.

Often data in Oracle Applications is stored in extended columns known as Descriptive Flex field columns. These are character columns which you can map it to store any kind of information. On the front end, it is possible to apply validations like numeric, date or character but the ultimate value that gets stored inside these flex columns is just character strings.

The trouble comes during the time of reporting. Its not a problem if you just want to display the value stored in the column. The pain starts when you want to do some logic on top of those numeric or date values. In this article we just consider numeric values and how can they be utilized.

The problem can be simplified by using the to_number function that Oracle provides but this assumes that the column infact has *ONLY NUMERIC* values and no “white space,” or “non-numeric” characters in it. If it does have non-numeric characters then the to_number function is going to bomb right in your face. Descriptive Flex fields are context dependent ie. each attribute column can store different data types for a given row, so for row 1 the attribute1 column might store a numeric value, row2 might store a date value and so on…

To deal with the above situation, we can create a new generic function as below

function get_number(p_val varchar2) return number is
l_value number;
begin
l_value := to_number(p_val);
return l_value;
exception
when others then
return -1;
end;

In the above wrapper function, it tries to convert the character value into number, if it succeeds then returns the value else returns a -1.

So you can modify this function to return any value depending on the requirement e.g return a zero value instead of -1. The reason we chose -1 is to highlight any data issues. If we return a zero value then there is a possibility that the user might overlook the data error. So depending on the situation return a value which would tell the user that the data is not correct e.g like a huge negative value.

You can enhance the function to strip out non-numeric values like spaces etc using ltrim, rtrim or replace functions but caution should be exercised to not overload this function with more logic and calculations as it will effect the performance of your reports.

 

Microsoft Corp. on Tuesday unveiled PerformancePoint Server 2007, which would eventually become the focal point for all of the company’s business intelligence tools. Scheduled for release in beta in November, PerformancePoint Server is expected to leverage analysis and reporting capabilities in SQL Server 2005 database and present information to a company’s knowledge workers through Office 2007.

In particular, PerformancePoint would present data and allow it to be manipulated in Excel, which should reduce the learning curve since most workers are familiar with the spreadsheet application. For IT, PerformancePoint would include business-modeling capabilities to develop performance metrics that would be consistent across an organization.

http://www.networkworld.com/news/2006/060606-microsoft-office-2007.html 

 

Dun and Bradstreet has been supplying business intelligence to the federal government for the past two and a half decades, but in the past five years, the government has dramatically increased its use of the company’s data. The Homeland Security Department and the intelligence community in particular rely on D&B information for risk mitigation.

http://www.sci-tech-today.com/story.xhtml?story_id=01200000B93C

I have been doing the same thing for past several years, i.e consulting for other companies, ofcourse the technology changes but overall its the same thing over and over. Sometimes I feel its enough doing the same thing but what else am I suppose to do? Whatever I would do for living my life, at a Macro level (i.e Higher level) it would always appear to be repetitive even though at the micro level (i.e looking at the details) things would be totally different.

For e.g At a Higher level, we all are born in this world, grow up, study, college, get a job, and then repeat { family – earn money – routine } until death. And the same thing would be different for each of us at the micro level, For e.g I go to a different school than you, different job than you, make different level of income than you and so on. So looking scantily everyone’s living the same life, almost mechanical!

Ofcourse there is more purpose to life than the predefined routine that I have been living all these years. I need to explore more and need to increase the pace of work in this area. In the mean time I work on the macro level coarse of my life, I have been thinking on making some changes at some of the micro level details in how I intend to make my living. I have strong intentions to follow through the micro changes.

Some highlighted areas of improvement for Enterprise Planning and Budgeting Minipack A over Financial Analyzer
• Enterprise Planning and Budgeting sits on top of Enterprise Performance Foundation, which is the platform for other Corporate Performance Management applications such as Oracle Financial Consolidation Hub. This allows Enterprise Planning and Budgeting to share common dimensions and data, enabling the creation of truly integrated performance management solutions.
• With Oracle Workflow-enabled processes Enterprise Planning and Budgeting allows users to control and understand their performance management tasks, something that had to be done largely outside of the system with Financial Analyzer.
• Enterprise Planning and Budgeting provides users with personal workspaces to allow for personal dimensions members to be added and to save personal copies of budget and forecast data before they are submitted. Compared to Financial Analyzer the synchronization of these workspaces is automated and the amount of duplicated data drastically reduced.
• Enterprise Planning and Budgeting uses the standard E-Business Suite concepts of users and does not require operating system user ids for authentication.
• The Enterprise Planning and Budgeting administrative responsibilities have been separated for data loading, security and business processes, making it easier to devolve implementation and maintenance tasks. This alleviates the bottleneck caused by the Administrator workstation concept in Financial Analyzer.
• The definition of Enterprise Planning and Budgeting calculations are template-driven, making it easier for business and functional users to maintain and use the system.
• Enterprise Planning and Budgeting provides a level of abstraction between the logical and physical data model used in analytic workspaces. This makes business lifecycle changes to dimensionality of the data much easier to manage than in Financial Analyzer, and simplifies reporting for functional users.
• The Enterprise Planning and Budgeting calculation rules are tied to a business process, allowing for greater flexibility in the definition of the level at which budgeting or planning is performed within an organization. These rules take
advantage of the advances made in dynamic aggregation and calculation within Oracle OLAP, leading to reduced load aggregation, and solve times.
• Improvements to the reporting and analysis features in Enterprise Planning and Budgeting include better control over document formatting and easier definition of dimension selections.
• New folder and document privileges make it easier to define shared documents within Enterprise Planning and Budgeting compared to Financial Analyzer.
• There are many new features in Enterprise Planning and Budgeting in the area of data collection. They include:

1.  multiple views of reference data within one worksheet;
2.  automated cell locking driven by calculation and security rules;
3.  setting of targets that drive the validation of data entered by users;
4.  automatic generation and distribution of worksheets;
5.  Workflow-enabled approval processes based on data ownership rules.

Rollup patch E1 contains a small amount of
important bug fixes which fell outside the Rollup Patch E window.  It
also contains certification for the new version of FEM released on
Friday -
FEM D1.  Please refer to the FEM About Document on Metalink for all the
details on the new features it contains.  For details on bugs fixed by
this rollup please refer to the EPB About document on Metalink.
Enterprise Planning and Budgeting Rollup Patch
E1 (5244100)
To receive the passwords to download this patch, please log a TAR with
Oracle Support, product code 1272. 

Larry Ellison selling his house, so can we predict another acquisition soon? But which software company is worth $16 million?

Maybe I should quickly create one, just in case my company gets picked up, he he he )

- OGL EPB BALANCE RULE ERROR CHART OF ACCOUNTS HAVE NOT BEEN MAPPED PROPERLY : ZPB -  

Ever encountered the unmapped accounts error during running of the GL balance rule? If you hit this error then the concurrent request output will display all the GL code combinations for which the run has failed. The common solution mentioned in metalink document is to re-submit all the dimension rules in order to catch any newly created dimension members. But knowing that the instance is a cloned instance from production and there is only one user currently working on this instance since the clone I was pretty sure that there were no new dimension members created.

But when the Doc says, you have to religiously follow it before complaining further. So I did resubmit all the dimension rules and then re-ran the GL balance rule but the error persisted. I did further digging into the topic and found the following note 357959.1 which mentions that the issue could be related to the incompatible summary flag settings between the flex values of any of the code combination segments and the summary flag in the gl_code_combinations table.

Let me clarify further, each GL chart of account is made of more than one segment (field). This segment has a list of values that is maintained in the fnd_flex_values table. This table contains a column known as summary_flag. So lets say the typical structure contains 5 segments Company, Account, Cost Center, product, Country then the values of these segments is maintained in the fnd_flex_values table.

E.g case Company { 01, 02, ..99 }, Account { 1000, ,…. 9999} , Cost Center { 000 … 999} , Country { US, UK …IN etc},

And the combination of these values is stored in the gl_code_combinations table

for eg. 01.1000.001.US , 01.2000.001.US , 03.1000.003.UK etc, and these combinations also have a summary_flag.

So any of the above values either company -01 or Cost Center 000 or country UK could have the summary flag set differently than the summary flag in the code combinations table.

To detect which segment values had the problem there are a bunch of scripts to be run as follows.

You will need the following information:
– Chart of Account Id from #1
– Code Combinations ID from #2
– Segment and the flex_value_set_id of the specific segment from above

select a.flex_value, b.code_combination_id,
a.summary_flag as “Flex Summary Flag”, b.summary_flag as “GL Summary Flag”
from fnd_flex_values a, gl_code_combinations b
where b. = a.flex_value
and a.summary_flag <> b.summary_flag
and b.chart_of_accounts_id =
and b.code_combination_id in (value1, value2, value3, etc.)
and a.flex_value_set_id = ;

Example for the Chart of Account “Vision Operations” – SEGMENT3:

select a.flex_value, b.code_combination_id,
a.summary_flag as “Flex Summary Flag”, b.summary_flag as “GL Summary Flag”
from fnd_flex_values a, gl_code_combinations b
where b.SEGMENT3 = a.flex_value
and a.summary_flag <> b.summary_flag
and b.chart_of_accounts_id = 101
and b.code_combination_id in (12831, 18068, 90094)
and a.flex_value_set_id = 1002472;

Repeat this query for all Segments. 

You can find the list of segments and their value set id using the below script

select segment_name, application_column_name, flex_value_set_id, enabled_flag
from fnd_id_flex_segments
where application_id = 101
and id_flex_code = ‘GL#’
and id_flex_num = ;

But this seemed to be a lengthy and tedious process, so I used the trial version of InfoCaptor and quickly added one more qlet using the below query.

select a.flex_value     “G_PARAM

“      , b.code_combination_id,
a.summary_flag as “Flex Summary Flag”, b.summary_flag as “GL Summary Flag”
from fnd_flex_values a, gl_code_combinations b
where b.G_PARAM 

    /*application_column_name*/     = a.flex_value
and a.summary_flag <> b.summary_flag
and b.chart_of_accounts_id =     ‘G_PARAM 

‘    /*gsob.chart_of_accounts_id*/   
and b.code_combination_id in
    (
    select code_combination_id
    from gl_code_combinations
   where segment4||’.'||segment1||’.'||segment3||’.'||segment2||’.'||segment5||’.'||segment6||’.'||segment7
   in
      (
‘001.13105.000.290.000.000.00000′,
‘001.28100.000.100.000.000.00000′, 

………

………
‘001.19035.000.100.000.000.00000′
      )
    )
and a.flex_value_set_id =     ‘G_PARAM

‘    /*flex_value_set_id*/    

The seeded dashboard has global parameters where you can pick the Set of books, Chart of accounts and the segment. Internally it can pass the segments flex_value_set_id and this is used inside the above dynamic query. So each time you run the above query the G_PARAM values are obtained at run time and results displayed on the grid. Using this I was able to determine that the country segment had 4 values that had the summary flag set to “Y” and the corresponding code combinations had summary flag set to “N”.

To fix, login to GL responsibility in order to modify the flex values. Query the country segment and their values. For the four defective settings ”un-check” the parent checkbox and save it. (yes summary flag on the front end is displayed as parent checkbox)

Then I went ahead and ran “undo” of GL Balance rule, then ran “undo” of country dimension, submitted all the dimension rules again and re-ran the GL Balance rule. This time the actuals load completed successfully