Friday, 14 October 2011

ETL ( Extraction Transformation and Loading)

ETL Stands for Extraction, Transformation and Loading, its a process that has Database usage. Especially in Data Warehousing. It was also used during the Data Migration but not in all the cases.

Extract
The first part of an ETL process involves extracting the data from the source systems.

Most data warehousing projects consolidate data from different source systems. Each separate system may also use a different data organization/format. Common data source formats are relational databases and flat files, but may include non-relational database structures such as Information Management System (IMS) or other data structures such as Virtual Storage Access Method (VSAM)or Indexed Sequential Access Method (ISAM), or even fetching from outside sources such as through web spidering or screen-scraping. The streaming of the extracted data source and load on-the-fly to the destination database is another way of performing ETL when no intermediate data storage is required. In general, the goal of the extraction phase is to convert the data into a single format which is appropriate for transformation processing.
An intrinsic part of the extraction involves the parsing of extracted data, resulting in a check if the data meets an expected pattern or structure. If not, the data may be rejected entirely or in part.
Transform
The transform stage applies a series of rules or functions to the extracted data from the source to derive the data for loading into the end target. Some data sources will require very little or even no manipulation of data. In other cases, one or more of the following transformation types may be required to meet the business and technical needs of the target database:
  • Selecting only certain columns to load (or selecting null columns not to load). For example, if the source data has three columns (also called attributes), for example roll_no, age, and salary, then the extraction may take only roll_no and salary. Similarly, the extraction mechanism may ignore all those records where salary is not present (salary = null).
  • Translating coded values (e.g., if the source system stores 1 for male and 2 for female, but the warehouse stores M for male and F for female), this calls for automated data cleansing no manual cleansing occurs during ETL
  • Encoding free-form values (e.g., mapping "Male" to "1" and "Mr" to M)
  • Deriving a new calculated value (e.g., sale_amount = qty * unit_price)
  • Sorting
  • Joining data from multiple sources (e.g., look up, merge)
  • Aggregation (for example, roll up — summarizing multiple rows of data — total sales for each store, and for each region, etc.)
  • Generating surrogate-key values
  • Transposing or pivoting (turning multiple columns into multiple rows or vice versa)
  • Splitting a column into multiple columns (e.g., putting a comma-separated list specified as a string in one column as individual values in different columns)
  • Disaggregation of repeating columns into a separate detail table (e.g., moving a series of addresses in one record into single addresses in a set of records in a linked address table)
  • Look up and validate the relevant data from tables or referential files for slowly changing dimensions.
  • Applying any form of simple or complex data validation. If validation fails, it may result in a full, partial or no rejection of the data, and thus none, some or all the data is handed over to the next step, depending on the rule design and exception handling. Many of the above transformations may result in exceptions, for example, when a code translation parses an unknown code in the extracted data.
Load
The load phase loads the data into the end target, usually the data warehouse (DW). Depending on the requirements of the organization, this process varies widely. Some data warehouses may overwrite existing information with cumulative information, frequently updating extract data is done on daily, weekly or monthly basis. Other DW (or even other parts of the same DW) may add new data in a historic form, for example, hourly. To understand this, consider a DW that is required to maintain sales records of the last year. Then, the DW will overwrite any data that is older than a year with newer data. However, the entry of data for any one year window will be made in a historic manner. The timing and scope to replace or append are strategic design choices dependent on the time available and the business needs. More complex systems can maintain a history and audit trail of all changes to the data loaded in the DW.
As the load phase interacts with a database, the constraints defined in the database schema — as well as in triggers activated upon data load — apply (for example, uniqueness, referential integrity, mandatory fields), which also contribute to the overall data quality performance of the ETL process.
  • For example, a financial institution might have information on a customer in several departments and each department might have that customer's information listed in a different way. The membership department might list the customer by name, whereas the accounting department might list the customer by number. ETL can bundle all this data and consolidate it into a uniform presentation, such as for storing in a database or data warehouse.
  • Another way that companies use ETL is to move information to another application permanently. For instance, the new application might use another database vendor and most likely a very different database schema. ETL can be used to transform the data into a format suitable for the new application to use.
  • An example of this would be an Expense and Cost Recovery System (ECRS) such as used by accountancy, consultancies and lawyers. The data usually ends up in the time and billing system, although some businesses may also utilize the raw data for employee productivity reports to Human Resources (personnel dept.) or equipment usage reports to Facilities Management.




Thursday, 13 October 2011

SAP Procurement Process Flow.


Determination of Requirements
The user department responsible is manually passing a requirement for materials to the purchasing department via Purchase Requisition. Purchase requests are generated when business runs the MRP and Request are also generated based in Consumption based planning (CBP) for Production and Engg.Stores. 
Determination of Source of Supply
The purchasing component helps the buyer determine the sources of supply. User use the determination of the source of supply to create Requests for quotation (RFQs) and then to enter quotations. User is accessing the existing Purchase Orders and Conditions in the System. 
Vendor Selection and Comparison of Quotations
The System is capable of simulating pricing scenarios and simplifies the selection of vendors by making price comparisons between the various quotations. 
Purchase Order Processing
The purchasing system adopts information from purchase requisition and the quotation to help to create Purchase Orders. They are also creating manual purchase order directly for stock, non-stock items, services etc. 
Purchase Order Monitoring
The buyer monitor the processing status of the purchase order online at any time and determine whether goods or an invoice have been received for the relevant purchase order item. 
Goods Receipt and Inventory Management
The Goods receiving requester confirms the receipt of the materials by simply entering the Purchase Order Number. The system compares the goods receipt quantity with the purchase order quantity. By specifying permissible tolerances, buyers can limit over deliveries and under deliveries of ordered materials.

Data Warehousing Introduction

A data warehouse is a type of computer database that is responsible for collecting and storing the information of a particular organization. The goal of using a data warehouse is to have an efficient way of managing information and analyzing data.

Despite the fact that data warehouses can be designed in a number of different ways, they all share a number of important characteristics. Most data warehouses are subject oriented. This means that the information that is in the data warehouse is stored in a way that allows it to be connected to objects or events which occur in reality. 

Another characteristic that is frequently seen in data warehouses is called a time variant. A time variant will allow changes in the information to be monitored and recorded over time. The information that exists in data warehouses is non-volatile. This means that it cannot be deleted, and must be held to be analyzed in the future. All of the programs that are used by a particular institution will be stored in the data warehouse, and it will be integrated together. The first data warehouses were developed in the 1980s. As societies entered the information age, there was a large demand for efficient methods of storing information. 

Many of the systems that existed in the 1980s were not powerful enough to store and manage large amounts of data. There were a number of reasons for this. The systems that existed at the time took too long to report and process information. Many of these systems were not designed to analyze or report information. In addition to this, the computer programs that were necessary for reporting information were both costly and slow. To solve these problems, companies begin designing computer databases that placed an emphasis on managing and analyzing information. These were the first data warehouses, and they could obtain data from a variety of different sources, and some of these include personal computers and mainframes. 

Spreadsheet programs have also played an important role in the development of data warehouses. By the end of the 1990s, the technology had greatly advanced, and was much lower in cost. The technology has continued to evolve to meet the demands of those who are looking for more functions and speed. There are four advances in data warehouse technology that has allowed it to evolve. These advances are offline operational databases, real time data warehouses, offline data warehouses, and the integrated data warehouses. 

The offline operational database is a system in which the information within the database of an operational system is copied to a server that is offline. When this is done, the operational system will perform at a much higher level. As the name implies, a real time data warehouse system will be updated every time an event occurs. For example, if a customer orders a product, a real time data warehouse will automatically update the information in real time. The offline data warehouse is a database that is updated on a regular from an operational system. 

With the integrated data warehouse, transactions will be transferred back to the operational systems each day, and this will allow the data to easily be analyzed by companies and organizations. There are a number of devices that will be present in the typical data warehouse. Some of these devices are the source data layer, reporting layer, data warehouse layer, and transformation layer. There are a number different data sources for data warehouses. Some popular forms of data sources are Teradata, Oracle database, or Microsoft SQL Server. 

Another important concept that is related to data warehouses is called data transformation. As the name suggests, data transformation is a process in which information transferred from specific sources is cleaned and loaded into a repository.

Data transformation can either be a manual or automated process. Code can be manually generated, or an ETL tool can be utilized. The device that is responsible for transforming the data will compare it to other systems. It will also placed the data in a specific standard. In addition to this, it will often be linked to other systems which can assist it. The goal of using a data warehouse is to store and monitor information in a way that allows it to easily tbe analyzed. The data held in the warehouse will typically remain on file for a year.

Key Data Warehouse systems and the most widely used database engines for storing and serving data for the enterprise business intelligence and performance management.

  • SAP BW - Business Information Warehouse (SAP Netweaver BI)
  • Microsoft SQL Server
  • Teradata
  • Oracle
  • IBM DB2 (Infosphere Warehouse)
  • SAS



SAP BW - Introduction

In this Blog we will some thing about SAP BW. The area in which SAP HANA is expected to revolutionize the way the Business Intelligence will work. 

In the fiercely increasing competition amongst corporations it has become mandatory to make quick and sound crucial business decisions based on analysis of business critical data. This is the point where data warehouses come into play. Although many companies are relying on data warehouses, they are still struggling to achieve positive results due to fragmented and inconsistent data. For them SAP’s Business Information Warehouse (BW) provides a complete information factory solution. BW is the central component in the SAP suite of applications with an added advantage of being a software package that can be used in both SAP and non-SAP environments.

SAP Business Information Warehouse (SAP BW) is the name of the Business Intelligence, analytical, reporting and Data Warehousing (DW) solution which is one of the major enterprise software applications produced by SAP AG. It was originally named SAP BIW, (Business Information Warehouse), but is now known as "SAP NetWeaver BI".

For the modern enterprise, time wasted due to data loss and inconsistencies is unacceptable, particularly when this can often be remedied easily through the use of a data warehouse. SAP introduced Business Warehouse Information Systems (BW) in the first half of 1997. This system approach consolidates the external and the internal sources of data into a single repository and provides preconfigured data that aids the organizations and enterprises in dealing with the data management and archiving. The SAP environment therefore expanded its horizon with the introduction and implementation of SAP BW to encompass new applications for the SAP R/3 administrators responsible for administering and maintenance of these data reserves.


Wednesday, 12 October 2011

T-Codes of SAP MM


IH09 - Display Material 
MM01 - Create Material  
MM02 - Change Material  
MM03 - Display Material 
MM50 - List Extendable Materials 
MMBE - Stock Overview 
MMI1 - Create Operating Supplies 
MMN1 - Create Non-Stock Material  
MMS1 - Create Service 
MMU1 - Create Non-Valuated Material 
 
ME51N - Create Purchase Requisition 
ME52N - Change Purchase Requisition 
ME53N - Display Purchase Requisition 
ME5A - Purchase Requisitions: List Display 
ME5J - Purchase Requisitions for Project 
ME5K - Requisitions by Account Assignment 
MELB - Purch. Transactions by Tracking No. 
 
ME56 - Assign Source to Purch. Requisition 
ME57 - Assign and Process Requisitions 
ME58 - Ordering: Assigned Requisitions 
ME59 - Automatic Generation of POs 

ME54 - Release Purchase Requisition 
ME55 - Collective Release of Purchase Reqs. 
ME5F - Release Reminder: Purch. Requisition 
 
MB21 - Create Reservation 
MB22 - Change Reservation 
MB23 - Display Reservation 
MB24 - Reservations by Material 
MB25 - Reservations by Account Assignment 
 
MB1C - Other Goods Receipts 
MB90 - Output Processing for Mat. Documents 
 
MB21 - Create Reservation 
MB22 - Change Reservation 
MB23 - Display Reservation 
MB24 - Reservations by Material 
MB25 - Reservations by Account Assignment 
 
MBRL - Return Delivery per Mat. Document 

MB1C - Other Goods Receipts 
MB90 - Output Processing for Mat. Documents 
 
MB1B - Transfer Posting 
 
MIBC - ABC Analysis for Cycle Counting 
 
MI01 - Create Physical Inventory Document 
MI02 - Change Physical Inventory Document 
MI03 - Display Physical Inventory Document 
MI31 - Batch Input: Create Phys. Inv. Doc. 
MI32 - Batch Input: Block Material 
MI33 - Batch Input: Freeze Book Inv.Balance 
MICN - Btch Inpt:Ph.Inv.Docs.for Cycle Ctng 
MIK1 - Batch Input: Ph.Inv.Doc.Vendor Cons. 
MIQ1 - Batch Input: PhInvDoc. Project Stock 
 
MI01 - Create Physical Inventory Document 
MI02 - Change Physical Inventory Document 
MI03 - Display Physical Inventory Document 
MI31 - Batch Input: Create Phys. Inv. Doc. 
MI32 - Batch Input: Block Material 
MI33 - Batch Input: Freeze Book Inv.Balance 
MICN - Btch Inpt:Ph.Inv.Docs.for Cycle Ctng 
MIK1 - Batch Input: Ph.Inv.Doc.Vendor Cons. 
MIQ1 - Batch Input: PhInvDoc. Project Stock 
 
MI01 - Create Physical Inventory Document 
MI02 - Change Physical Inventory Document 
MI03 - Display Physical Inventory Document 
MI31 - Batch Input: Create Phys. Inv. Doc. 
MI32 - Batch Input: Block Material 
MI33 - Batch Input: Freeze Book Inv.Balance 
MICN - Btch Inpt:Ph.Inv.Docs.for Cycle Ctng 
MIK1 - Batch Input: Ph.Inv.Doc.Vendor Cons. 
MIQ1 - Batch Input: PhInvDoc. Project Stock 
 
MI21 - Print physical inventory document 
 
MI04 - Enter Inventory Count with Document 
MI05 - Change Inventory Count 
MI06 - Display Inventory Count 
MI09 - Enter Inventory Count w/o Document 
MI34 - Batch Input: Enter Count 
MI35 - Batch Input: Post Zero Stock Balance 
MI38 - Batch Input: Count and Differences 
MI39 - Batch Input: Document and Count 
MI40 - Batch Input: Doc., Count and Diff. 
 
MI08 - Create List of Differences with Doc. 
MI10 - Create List of Differences w/o Doc. 
MI20 - Print List of Differences 
 
MI11 - Physical Inventory Document Recount 
 
MI07 - Process List of Differences 
MI37 - Batch Input: Post Differences 
 
CT01 - Create Characteristic 
CT02 - Change Characteristic 
CT03 - Display Characteristic 
 
CL01 - Create Class 
CL02 - Classes 
CL03 - Display Class 
CL04 - Delete Class 
CL2B - Class Types