Thursday, April 25, 2013

OLAP(Online Analytical Processing)


 OLAP deals with Historical Data or Archival Data. Historical data are those data that are archived over a long period of time.
Example: If we collect last 10 years data about flight reservation, The data can give us many meaningful information such as the trends in reservation. This may give useful information like peak time of travel, what kinds of people are traveling in various classes (Economy/Business)etc.

    Historical Data or Archival Data
    Infrequent updates
    Analytical queries require huge number of aggregations
    Integrated data set with a global relevance

Updates are very rare here. Analytical queries requires huge number of aggregations. In analytical queries the performance issue is mainly in query response time. Query need to access large amount of data and require huge number of aggregation.
OLAP Queries have significant importance in strategic decision making. This helps the top level management in decision making.
Examples for OLAP Queries
    How is the profit changing over the years across different regions?
    Is it financially viable continue the production unit at location X?

OLTP (Online Transaction Processing)


We can divide IT systems into transactional (OLTP) and analytical (OLAP) systems.
OLTP stands for OnLine Transaction Processing. OLTP is a class of program that facilitates and manages transaction-oriented applications.
The classic examples for transaction-oriented applications are airline reservations, credit-card authorizations, ATMwithdrawals, and so on.
Queries of OLTP systems are typically simple and return relatively few records.
The main emphasis for OLTP systems is put on very fast query processing, maintaining data integrity in multi-access environments and an effectiveness measured by number of transactions per second
Transaction and data recovery is paramount for OLTP systems since they deal with current business data used to conduct real-time business operations
Similarly, OLTP systems are often decentralized to avoid single points of failure. This can also help spread volume over multiple servers to maximum the volume transaction processing possible and minimize response times.
Operational data is the data you use to run your business. This data is what is typically stored, retrieved, and updated by your Online Transactional Processing (OLTP) system.
The structure of OLTP databases are highly normalized which means tables and fields are organized to minimize data redundancy and dependency
But this doesn’t lend itself to efficient processing of complex queries. More complex querying is typically run against historical data stores which are classified as OLAP systems (On-Line Analytical Processing) or Data Warehouses.
In general we can assume that OLTP systems provide source data to data warehouses, whereas OLAP systems help to analyze it

Following are the characteristic of operational system:
•Continuous availability
•Transaction integrity
•High volume of transaction
•Low data volume per query
•Is used by operational staff
•Supports day to day control operations
•Supports large number of users

Examples for OLTP Queries:
    What is the Salary of Mr.John?
    What is the address and email id of the person who is the head of maths department?

Friday, December 28, 2012

Oracle Application Express


What is Oracle Application Express?


Oracle Application Express (Oracle APEX) is a declarative, rapid web application development tool for the Oracle database. It is a fully supported, no cost option available with all editions of the Oracle database. Using only a web browser, you can develop and deploy professional applications that are both fast and secure.
Whether you are an experienced SQL and PL/SQL developer or a power user used to writing reports, wizards allow you to quickly build Web applications on top of your Oracle database objects. Enhancing and maintaining these applications is done using a declarative framework, all of which increases your productivity.
Oracle Application Express is database-centric and suited to building a vast array of applications. You can start with webifying a spreadsheet to facilitate collaboration or dive right into extremely complex applications with numerous external interfaces such as the Oracle Store. Because Oracle APEX resides within the Oracle Database and can easily integrate with authentication schemes (such as Oracle Access Manager, SSO, LDAP, etc.) you can build secure applications that can scale to meet your largest user communities.

SCD Type2 Through Informatica with date range


https://docs.google.com/open?id=0ByVBmePwMuhZd01iT3VNd3ZzUWM

DAC Tasks & Explanation

Step Task Name Descriptiopn Notes
1 Seed Data Configuration a) Task Logical folders One Time Only. This is to be done when a new Informatica Folder is created.
b) Task Physical Folders One Time Only. This is to be done when a new Informatica Folder is created.
c) Task Phase – Optional One Time Only. This is to be done when a new Informatica Folder is created.
2 Source Folder Relationship In the Design under Source System Folders tab, link the Task Logical folders and Task Physical Folders One Time Only. This is to be done when a new Informatica Folder is created.
3 Import Table/s In the Design view under Tables Tab, import the new fact or dimension table by right clicking in the work space. This will show a list of tables and select the table that you want to import. Everytime you Add a new Dimension (Extending an existing Star or Custom Star) or Fact table (a new Star Schema)
4 Import Columns for the custom table/s Right click on the custom table imported in step 3 and select Import > Database columns. This imports all of the columns for the particular table. If it is a fact table under the columns sub-tab make sure to enter the Foreign Key table and Foreign Key. (This is a very important step) Everytime you Add a new Dimension (Extending an existing Star or Custom Star) or Fact table (a new Star Schema)
5 Create Task In Design view under Task Tab, click New. Create a new Task. Enter the all of the required details in the Edit Subtab. Make sure to enter the correct Workflow names for Full and Incremental loads. Select the correct Logical folder name from the drop down. Also, select the correct source and target connections for the task. Default Source = DBConnection_OLTP and Target = DBConnection_OLAP Everytime you Add a new Dimension (Extending an existing Star or Custom Star) or Fact table (a new Star Schema)
6 Synchronize the Task Right click on the Task that you created in Step 5 > Select Synchronize task. In the pop-up window select the option for the Selected Task Only. This should synchronize the DAC Task with your Informatica task and the Source and Target tables are filled out for the Task Everytime you Add a new Dimension (Extending an existing Star or Custom Star) or Fact table (a new Star Schema)
7 Update the Source and Target table options After you perform Step 6, click on the Sources subtab and enter the required details. Make sure to enter the Source connection value which is always DBConnection_OLTP.                                   Repeat the above step for the target table. Select the truncate for Full load or Truncate always option. Make sure to enter the Target connection value which is always DBConnection_OLAP  Everytime you Add a new Dimension (Extending an existing Star or Custom Star) or Fact table (a new Star Schema)
8 Update the newly imported tables In the Design view under Tables Tab, identify all of the tables including source and targets that got imported from Step 6 and update the correct Table type values. Everytime you Add a new Dimension (Extending an existing Star or Custom Star) or Fact table (a new Star Schema)
9 Import Indexes In the Design view under Tables Tab, identify all of the tables that you created in Step 6 and right click and import all the indexes. The prerequisite for this is the index has to be created on the tables at the database level.  Everytime you Add a new Dimension (Extending an existing Star or Custom Star) or Fact table (a new Star Schema)
10 Set Index Properties Once the Indexes have been imported in Step 9, click Indexes Tab in the Design View and identify all the indexes you just imported and set the correct value. Make sure to set it as an ETL or a query index.  Also, set the Drop and recreate bit map indexes always option so that the indexes are always dropped and recreated for Full load. Everytime you Add a new Dimension (Extending an existing Star or Custom Star) or Fact table (a new Star Schema)
11 Create Subject Area In Design View > Subject Area Tab, click new. Enter a name for your subject area. Click Save. Click on the Tables subtab and add the fact table/s for the star or subject area that your are building. Click Save.  Everytime you Add a new Dimension (Extending an existing Star or Custom Star) or Fact table (a new Star Schema)
12 Assemble the Subject area Once the Subjecta area is built as in Step 11, click on the Assemble button to create a list of all Tasks for that subject area. This will prompt you for the selected or all records option and make sure to select select record only. If you are assmebling more than one SA, then select the other option. For this step to happen correctly, Steps 4, 5 & 6 needs to be completed accurately.  Everytime you Add a new Dimension (Extending an existing Star or Custom Star) or Fact table (a new Star Schema). When ever you add delete or Modify any Tasks or related objects Assemble the Subject Area. Advisable to perform this step on changes to any of the DAC objects.
13 Create New Execution Plan In Execute View, click on New. Enter a Name for the Execution Plan. Click Save. When you create a new Execution Plan
14 Add the Subject area Cick on the Subject Area subtab under Execute View > Add the new Subject Area configured in Step 11 and 12. Click Save When you create a new Execution Plan or Modify an existing one to add or remove Subject Areas
15 Generate Parameters Cick on the Parameters subtab under Execute View > Click Generate. Select 1 from the pop-up window. Once the parmeters are generated, select appropriate values for the DataSources. Validate the Folder assignment which happens by default. Click Save When you create a new Execution Plan or Modify an existing one to add or remove Subject Areas
16 Build the Execution Plan In Execute View > Execution Plan, Select the execution Plan created and configured in Step 13,14,15. Click Build. This will prompt you for the selected or all records and make sure to select select record only. Complete a series of prompts and wait for the Execution Plan to be Built. Your execution plan is ready to be Run! When ever you add delete or Modify any Tasks or related objects. Advisable to perform this step on changes to any of the DAC objects.

Basic UNIX/Linux commands




Basic UNIX/Linux commands

Custom commands available on departmental Linux computers but not on 
Linux systems elsewhere have descriptions marked by an *asterisk. 
Notice that for some standard Unix/Linux commands, friendlier versions 
have been implemented; on departmental Linux computers the names of such 
custom commands end with a period.
------------------------------------------------------------------------------
INTERACTIVE FEATURES
TAB                         Command completion !!! USEFUL !!!
UPARROW                     Command history !!! USEFUL !!!

CTRL-C                      Interrupt/kill current process.

CTRL-D                      (at the beginning of the input line) end input.
CTRL-D CTRL-D               (in the middle of the input line) end input.
------------------------------------------------------------------------------
WILDCARDS AND DIRECTORIES USED IN COMMANDS
*                           Replaces any string of characters in a file name
                            except the initial dot.
?                           Replaces any single character in a file name
                            except the initial dot.
~                           The home directory of the current user.
~abcde001                   The home directory of the user abcde001.
..                          The parent directory.
.                           The present directory.
/                           The root directory

Example:
ls ~/files/csc221/*.txt
------------------------------------------------------------------------------
REDIRECTIONS AND PIPES

COMMAND < FILE              Take input from FILE instead of from the keybaord.
COMMAND > FILE              Put output of COMMAND to FILE
COMMAND >> FILE             Append the output of COMMAND to FILE
COMMAND 2> FILE             Put error messages of COMMAND to FILE.
COMMAND 2>> FILE            Append error messages of COMMAND to FILE.
COMMAND > FILE1 2> FILE2    Put output and error messages in separate files.
COMMAND >& FILE             Put output and error messages in the same FILE.
COMMAND >>& FILE            Append output and error messages of COMMAND to FILE.

COMMAND1 | COMMAND2         Output of COMMAND1 becomes input for COMMAND2.
COMMAND | more              See the output of COMMAND page by page.
COMMAND | sort | more       See the output lines of COMMAND sorted and page by page.
------------------------------------------------------------------------------
ONLINE HELP

h                           *Custom help.
COMMAND --help | more       Basic help on a Unix COMMAND (for most commands).
COMMAND -h | more           Basic help on a Unix COMMAND (for some commands).
whatis COMMAND              One-line information on COMMAND.
man COMMAND                 Display the UNIX manual page on COMMAND.
info COMMAND                Info help on COMMAND.
xman                        Browser for Unix manual pages (under X-windows).
apropos KEYWORD | more      Find man pages relevant to COMMAND.
help COMMAND      Help on a bash built-in COMMAND.
perldoc                     Perl documentation.
------------------------------------------------------------------------------
FILES AND DIRECTORIES

ls                          List contents of current directory.
ls -l                       List contents of current directory in a long form.
ls -a                       Same as ls but .* files are displayed as well.
ls -al                      Combination of ls -a and ls -l 
ls DIRECTORY                List contents of DIRECTORY (specified by a path).
ls SUBDIRECTORY             List contents of SUBDIRECTORY.
ls FILE(S)                  Check whether FILE exists (or what FILES exist).

pwd                         Display absolute path to present working directory.

mkdir DIRECTORY             Create DIRECTORY (i.e. a folder)

cd                          Change to your home directory.
cd ..                       Change to the parent directory.
cd SUBDIRECTORY             Change to SUBDIRECTORY.
cd DIRECTORY                Change to DIRECTORY (specified by a path).
cd -                        Change to the directory you were in previously.
cd. ARGUMENTS               *Same as cd followed by ls

cp FILE NEWFILE             Copy FILE to NEWFILE.
cp -r DIR NEWDIR            Copy DIR and all its contents to NEWDIR.
cp. ARGUMENTS               *Same as cp -r but preserving file attributes.

mv FILE NAME                Rename FILE to new NAME.
mv DIR NAME                 Rename directory DIR to new NAME.
mv FILE DIR                 Move FILE into existing directory DIR.
swap FILE1 FILE2            *Swap contents of FILE1 and FILE2.
ln -s FILE LINK             Create symbolic LINK (i.e. shortcut) to existing FILE.

quota                       Displays your disk quota.
quota.                      *Displays your disk quota and current disk usage.

rm FILE(S)                  Remove FILE(S).
rmdir DIRECTORY             Remove empty DIRECTORY.
rm -r DIRECTORY             Remove DIRECTORY and its entire contents.
rm -rf DIRECTORY            Same as rm -r but without asking for confirmations.
clean                       *Remove non-essential files, interactively 
clean -f                    *Remove non-essential files, without interaction.
junk FILE                   *Move FILE to ~/junk instead of removing it. 
find. FILE(S)               *Search current dir and its subdirs for FILE(S).
touch FILE                  Update modification date/time of FILE.
file FILE                   Find out the type of FILE.
gzip                        Compress or expand files.
zip                         Compress or expand files.
compress                    Compress or expand files.
tar                         Archive a directory into a file, or expand such a file.
targz DIRECTORY             *Pack DIRECTORY into archive file *.tgz 
untargz ARCHIVE.tgz         *Unpack *.tgz archive into a directory.

------------------------------------------------------------------------------
TEXT FILES

more FILE                   Display contents of FILE, page by page.
less FILE                   Display contents of FILE, page by page.
cat FILE                    Display a file. (For very short files.)
head FILE                   Display first lines of FILE.
tail FILE                   Display last lines of FILE.

pico FILE                   Edit FILE using a user-friendly editor. 
nano FILE                   Edit FILE using a user-friendly editor. 
kwrite FILE                 Edit FILE using a user-friendly editor under X windows.
gedit FILE                  Edit FILE using a user-friendly editor under X windows.
kate FILE                   Edit FILE using a user-friendly editor under X windows.
emacs FILE                  Edit FILE using a powerful editor.
vim FILE                    Edit FILE using a powerful editor with cryptic syntax.
aspell -c FILE              Check spelling in text-file FILE.
ispell FILE                 *Check spelling in text-file FILE.

cat FILE1 FILE2 > NEW       Append FILE1 and FILE2 creating new file NEW.
cat FILE1 >> FILE2          Append FILE1 at the end of FILE2.

sort FILE > NEWFILE         Sort lines of FILE alphabetically and put them in NEWFILE.

grep STRING FILE(S)         Display lines of FILE(S) which contain STRING.
grep. STRING FILE(S)        *Similar to that above, but better. 
wc FILE(S)                  Count characters, words and lines in FILE(S).
diff FILE1 FILE2 | more     Show differences between two versions of a file.

filter FILE NEWFILE         *Filter out strange characters from FILE.

COMMAND | cut -b 1-9,15     Remove sections from each line.
COMMAND | uniq              Omit repeated lines.
------------------------------------------------------------------------------
PRINTING

lpr FILE                    In Hawk153B or Redcay 141A, print FILE from a workstation. 
lprint1 FILE                *Print text-file on local printer; see help printing
lprint2 FILE                *Print text-file on local printer; see help printing

------------------------------------------------------------------------------
PROGRAMMING LANGUAGES
python                      Listener of Python 2.x programming language.
python3                     *Listener of Python 3.x programming language.
idle                        IDE for Ptyhon 2.x.
idle3                       *IDE for Ptyhon 3.x.
cc -g -Wall -o FILE FILE.c  Compile C source FILE.c into executable FILE.
gcc -g -Wall -o FILE FILE.c Compile C source FILE.c into executable FILE.
c++ -g -Wall -o FIL FIL.cxx Compile C++ source FIL.cxx into executable FIL.
g++ -g -Wall -o FIL FIL.cxx Compile C++ source FIL.cxx into executable FIL.
gdb EXECUTABLE              Start debugging a C/C++ program.
make FILE                   Compile and link C/C++ files specified in makefile
m                           *Same as make but directs messages to a log file.
c-work                      *Repeatedly edit-compile-run a C program.
javac CLASSNAME.java        Compile a Java program.
java CLASSNAME              Run a Java program.
javadoc CLASSNAME.java      Create an html documentation file for CLASSNAME.
appletviewer CLASSNAME      Run an applet.

scheme                      *Listener of Scheme programming language.
lisp                        *Listener of LISP programming language.
prolog                      *Listener of Prolog programming language.
------------------------------------------------------------------------------
INTERNET
lynx                        Web browser (for text-based terminals).
firefox                     Web browser.
konqueror                   Web browser.
BROWSER                     Browse the Internet (with one of the browsers above.
BROWSER FILE.html           Display a local html file.
BROWSER FILE.pdf            Display a local pdf file.

mutt                        Text-based e-mail manager.
pine                        Text-based e-mail manager (on some systems).

ssh HOST                    Open interactive session on HOST using secure shell.
sftp HOST                   Open sftp (secure file transfer) connection to HOST.
rsync ARGUMENTS             Synchronize directories on local and remote host.
------------------------------------------------------------------------------
UNDER X-WINDOWS
libreoffice                 LibreOffice productiveity suite
soffice                     OpenOffice productivity suite
 
acroread FILE.pdf           Display pdf FILE.pdf
epdfviewer FILE.pdf         Display pdf FILE.pdf
okular FILE                 Display FILE (pdf, postscript, ...)

xterm                       A shell window
konsole                     A better shell window
xcalc                       A calculator
xclock                      A clock
xeyes                       They watch you work and report to the Boss :-)
------------------------------------------------------------------------------
RESET

xfwm4                       Reset the windows manager on Lab and MiniLab computers.
Ctrl-Alt-Backspace          Restart the X-server. (You may need to do that twice.)
clear                       Clear shell window.
xrefresh                    Refresh X-windows.
reset                       *Reset session.
setup-account               *Set up or reset your account (Dr. Plaza's customizations)
------------------------------------------------------------------------------
MISCELLANEOUS

exit                        Exit from any shell.
logout                      Exit from the login shell and terminate session.

svn                         Version control system.    

date                        Display date and time.
------------------------------------------------------------------------------
COURSEWORK IN DR. PLAZA'S COURSES
Commands and directory names related to csc219 have 219 as a suffix. 
By changing the suffix you will obtain commands for other courses.
ls $csc319                  *List files related to csc319. 
cd $csc319                  *change into instructor's public directory for csc319. 
cp $csc319/FILE .           *Copy FILE related to csc319 to the current directory
cp -r $csc319/SUBDIR .      *Copy SUBDIR of csc319 to the current directory.
submit                      *Submit a directory with files for an assignment. 
grades                      *See your grades. Used in some courses only.
------------------------------------------------------------------------------
PROCESS CONTROL
Notes: A process is a run of a program; 
       One program can be used to create many concurrent processes.
       A job may consist of several processes with pipes and redirections.
       Processes are managed by the kernel. 
       Jobs are managed by the shell.
 
CTRL-Z                      Suspend current foreground process.
fg                          Bring job suspended by CTRL-Z to the foreground.
bg JOB                      Restart suspended JOb in the background.
ps                          List processes.
ps.                         *List processes.
jobs                        List current jobs (A job may involve many processes).
kill PROCESS                Kill PROCESS (however some processes may resist).
ctrl-C                      Kill the foreground process (but it may resist).
kill -9 PROCESS             Kill PROCESS (no process can resist.)
kill. PROCESS               *Kill PROCESS; same as kill -9.
COMMAND &                   Run COMMAND in the background. 
------------------------------------------------------------------------------
ENVIRONMENT VARIABLES IN BASH
env | sort | more           List all the environment variables with values.
echo $VARIABLE              List the value of VARIABLE.
unset VARIABLE              Remove VARIABLE.
export VARIABLE=VALUE       Create environment variable VARIABLE and set to VALUE.
------------------------------------------------------------------------------


Dimensional Modeling


Dimensional modeling (DM) is the name of a set of techniques and concepts used in data warehouse design. Dimensional modeling always uses the concepts of facts (measures), and dimensions (context).
Facts : Facts are typically (but not always) numeric values that can be aggregated, and dimensions are groups of hierarchies and descriptors that define the facts.
Types of Facts :
There are three types of facts:
  • Additive: Additive facts are facts that can be summed up through all of the dimensions in the fact table.
  • Semi-Additive: Semi-additive facts are facts that can be summed up for some of the dimensions in the fact table, but not the others.
  • Non-Additive: Non-additive facts are facts that cannot be summed up for any of the dimensions present in the fact table.
Types of Fact Tables
There are two types of fact tables:
  • Cumulative: This type of fact table describes what has happened over a period of time. For example, this fact table may describe the total sales by product by store by day. The facts for this type of fact tables are mostly additive facts. The first example presented here is a cumulative fact table.
  • Snapshot: This type of fact table describes the state of things in a particular instance of time, and usually includes more semi-additive and non-additive facts. The second example presented here is a snapshot fact table.
Dimension : A dimension is a data element that categorizes each item in a data set into non-overlapping regions. A data warehouse dimension provides the means to "slice and dice" data in a data warehouse. Dimensions provide structured labeling information to otherwise unordered numeric measures.

Types of Dimension :

Conformed dimension :

In data warehousing, a conformed dimension is a dimension that has the same meaning to every fact with which it relates. Conformed dimensions allow facts and measures to be categorized and described in the same way across multiple facts and/or data marts, ensuring consistent reporting across the enterprise.
A conformed dimension can exist as a single dimension table that relates to multiple fact tables within the same data warehouse, or as identical dimension tables in separate data marts.
Junk dimension :
A Junk Dimension is a dimension table consisting of attributes that do not belong in the fact table or in any of the existing dimension tables.The junk dimension should contain a single row representing the blanks as a surrogate key that will be used in the fact table for every row returned with a blank comment field.
The designer is faced with the challenge of where to put  attributes that do not belong in the other dimensions,Solution is to create a new dimension for each of the remaining attributes, but due to their nature, it could be necessary to create a vast number of new dimensions resulting in a fact table with a very large number of foreign keys.
Degenerate dimension :
A dimension key, such as a transaction number, invoice number, ticket number, or bill-of-lading number, that has no attributes and hence does not join to an actual dimension table. Degenerate dimensions are very common when the grain of a fact table represents a single transaction item or line item because the degenerate dimension represents the unique identifier of the parent. Degenerate dimensions often play an integral role in the fact table's primary key.

Dimensional modeling structure:

The dimensional model is built on a star-like schema, with dimensions surrounding the fact table. To build the schema, the following design model is used:
  1. Choose the business process
  2. Declare the Grain
  3. Identify the dimensions
  4. Identify the Fact

Benefits of dimensional modeling :

Benefits of the dimensional modeling are following:
  1. Understandability
  2. Query performance
  3. Extensibility