Business Intelligence (BI) - ERP - Dashboards

 
The Business Intelligence and Data Warehousing Glossary
I have collected and compiled this from a very nice website.Some of the terms are relating to Microsoft’s tool (Microsoft’s Analysis Services ) but that can be generalised to other non-microsoft tools also.
Link >>
Agent
An application that searches the data and sends an alert when a particular pattern is found.
 
Aggregations
Information […]

No Comments »
Filed under: Business Intelligence, DataWarehousing, Discoverer, Interview Questions(& answers), OBIEE, PL/SQL, Reports, SQL

USER EXITS: USER EXITS are the 3rd party programs (i.e. java,Pro*c, etc.) that we can link with our reports to perform a particular task.
We can link 3rd party programs with our reports with SRW.USER_EXIT ( Name_of_the_User_Exit)’. We can reference the input parameters in user exist with SRW.REFERENCE( input_parameters).
FND SRWINIT: This User Exit is used to […]

No Comments »
Filed under: AOL (Application Object Library ), ERP, Interfaces, Interview Questions(& answers), Oracle Apps, PL/SQL, Reports, SQL

Repository is a place where data are stored and maintained. A repository can be

a place where data is stored
a place where specifically digital data are stored
a site where eprints are located
a place where multiple databases or files are located for distribution over a network,
a computer location that is directly accessible to the user without […]

1 Comment »
Filed under: Ad Hoc Reporting, Business Intelligence, DataWarehousing, Discoverer, PL/SQL, SQL

Database Normalization and Table structures
Normalisation is the term used to describe how you break a file down into tables to create a database.
There are 3 or 4 major steps involved known as
1NF (First Normal Form),
2NF (Second Normal Form),
3NF (Third Normal Form) and
BCNF (Boyce-Codd Normal Form).
There are others but they are […]

No Comments »
Filed under: DBA (DataBase Administrator), DataWarehousing, Interview Questions(& answers), PL/SQL, SQL

OPEN INTERFACE LOGIC

The data from source application is loaded into a database table (called Interface Table).
The provided program logic validates the records.
Any errors are transferred into another table (called Error Table).
The correct records are then transferred through a process into destination application table.

INTERFACE COMPONENTS
Picture not available
Source Application· You obtain data from a source application to […]

2 Comments »
Filed under: ERP, Interfaces, Interview Questions(& answers), Oracle Apps, PL/SQL, SQL

Here the sample code of Interface program is presented for training purpose only.
DAILY RATES INTERFACE (ORACLE GENERAL LEDGER)
USING UTL_FILE PACKAGE

DECLARE
FP UTL_FILE.FILE_TYPE;
FROM_CURR VARCHAR2(15);
TO_CURR VARCHAR2(15);
FROM_CON_DATE DATE;
TO_CONV_DATE DATE;
USER_CON_TYPE VARCHAR2(30);
CONV_RATE NUMBER;
MODE_FLG VARCHAR2(1);
MY_LINE VARCHAR2(150);
BEGIN
FP:=UTL_FILE.FOPEN(’D:\ORACLE\PRODDB\9.2.0\PLSQL\TEMP’,'dailyrates.dat’,'R’);
UTL_FILE.GET_LINE(FP,MY_LINE);
LOOP
UTL_FILE.GET_LINE(FP,MY_LINE);
FROM_CURR:=SUBSTR(MY_LINE,1,3);
TO_CURR:=SUBSTR(MY_LINE,6,3);
FROM_CON_DATE:=SUBSTR(MY_LINE,10,11);
TO_CONV_DATE:=RTRIM(SUBSTR(MY_LINE,24,11));
USER_CON_TYPE:=RTRIM(SUBSTR(MY_LINE,36,9));
CONV_RATE:=TO_NUMBER(RTRIM(SUBSTR(MY_LINE,50,2)));
MODE_FLG:=SUBSTR(MY_LINE,59,1);
DBMS_OUTPUT.PUT_LINE(FROM_CURR||’ ‘||TO_CURR||’ ‘ ||FROM_CON_DATE||’ ‘||TO_CONV_DATE||’ ‘||
USER_CON_TYPE||’ ‘||CONV_RATE||’ ‘||MODE_FLG);
INSERT INTO GL.GL_DAILY_RATES_INTERFACE(FROM_CURRENCY,TO_CURRENCY,FROM_CONVERSION_DATE,TO_CONVERSION_DATE,USER_CONVERSION_TYPE,CONVERSION_RATE,MODE_FLAG)
VALUES(FROM_CURR,TO_CURR,FROM_CON_DATE,TO_CONV_DATE,USER_CON_TYPE,CONV_RATE,MODE_FLG);
DBMS_OUTPUT.PUT_LINE(’One row successfully inserted’);
COMMIT;
END LOOP;
UTL_FILE.FCLOSE(FP);
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;

dailyrates.dat /* This file should be placed in D:\ORACLE\PRODDB\9.2.0\PLSQL\TEMP */
FROM TO FROM_CON_DATE […]

2 Comments »
Filed under: DBA (DataBase Administrator), ERP, Interfaces, Interview Questions(& answers), Oracle Apps, PL/SQL, SQL

Q: What is the maximum tablespace size?
A: The limitation is dependant on the limit the operating system places on
the filesize and the limitation of the number of datafiles the database
allows. For example some Unix boxes have a limit of 2 GIGA per Datafile,
then the largest file you can create for a tablespace on this operating […]

No Comments »
Filed under: DBA (DataBase Administrator), ERP, Interview Questions(& answers), Oracle Apps, PL/SQL, SQL

A single datatype called the universal rowid, or UROWID, supports
both logical and physical rowids, as well as rowids of foreign
tables such as non-Oracle tables accessed through a gateway. A column
of the UROWID datatype can store all kinds of rowids. The value of the
COMPATIBLE initialization parameter must be set to 8.1 or higher to
use UROWID columns.
 
You […]

No Comments »
Filed under: ERP, Interview Questions(& answers), Oracle Apps, PL/SQL, SQL

Oracle uses a ROWID datatype to store the address (rowid) of every
row in the database.
Physical rowids store the addresses of rows in ordinary tables
(excluding index-organized tables), clustered tables, table
partitions and subpartitions, indexes, and index partitions and
subpartitions.

Logical rowids store the addresses of rows in index-organized tables.

[…]

No Comments »
Filed under: DBA (DataBase Administrator), ERP, PL/SQL, SQL