nEERAJ nATHANI Smart Bridge ETL TOOLS USED IN BUSINESS INTELLIGENCE & DATAWAREHOUSING PROJECTS.

ETL TOOLS USED IN BUSINESS INTELLIGENCE & DATAWAREHOUSING PROJECTS.

EXTRACT

The first part of an ETL process involves extracting the data from the source systems. In many cases this is the most challenging aspect of ETL, since extracting data correctly sets the stage for how subsequent processes go further.

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)
• Encoding free-form values (e.g., mapping "Male" to "1")
• Deriving a new calculated value (e.g., sale_amount = qty * unit_price)
• Sorting
• Joining data from multiple sources (e.g., lookup, merge) and deduplicating the data
• Aggregation (for example, rollup — 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., converting a comma-separated list, specified as a string in one column, into 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)
• Lookup 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 historical 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 historical 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 accountancies, 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.

BELOW ARE THE TOOLS USED:

A Ab Initio (company) Apatar B BusinessObjects Data Integrator D Data Moving Tool Data Transformation Services DMExpress E Expense and Cost Recovery System (ECRS)

I

• IBM InfoSphere DataStage
• Informatica

L

• LogiXML

M

• MuleSoft

O

• Oracle Warehouse Builder

P

• Pentaho
• Pervasive Software
• Pipeline Pilot

S

• Scriptella
• SnapLogic
• Sprog (software)
• SQL Server Integration Services
• Sunopsis

T

Talend Open Studio for Data Integration

Source: Wikipedia.

Neeraj Nathani_Smart Bridge FMCG Market

                                                                FMCG Market

Fast-moving consumer goods (FMCG) or consumer packaged goods (CPG) are products that are sold quickly and at relatively low cost. Examples include non-durable goods such as soft drinks, toiletries, and grocery items.^[1][2] Though the absolute profit made on FMCG products is relatively small, they are generally sold in large quantities, and so the cumulative profit on such products can be substantial.
Fast-moving consumer electronics are a type of FMCG and are typically low priced generic or easily substitutable consumer electronics, including lower end mobile phones, MP3 players, game players, and digital cameras, which have a short usage life, typically a year or less, and as such are disposable. Cheap FMCG electronics are often retained even after immediate failure, as the purchaser rationalizes the decision to not return the goods on the basis that the goods were cheap to begin with, and that the cost of return relative to the low cost of purchase is high. Thus low-quality electronic FMCG goods can be highly profitable for the vendors.
The term FMCGs refers to those retail goods that are generally replaced or fully used up over a short period of days, weeks, or months, and within one year. This contrasts with durable goods or major appliances such as kitchen appliances, which are generally replaced over a period of several years
FMCG have a short shelf life, either as a result of high consumer demand or because the product deteriorates rapidly. Some FMCGs—such as meat, fruits and vegetables, dairy products, and baked goods—are highly perishable. Other goods such as alcohol, toiletries, pre-packaged foods, soft drinks, and cleaning products have high turnover rates. An excellent example is a newspaper—every day's newspaper carries different content, making one useless just one day later, necessitating a new purchase every day.

The following are the main characteristics of FMCGs:^[1]

• From the consumers' perspective:
• Frequent purchase
• Low involvement (little or no effort to choose the item – products with strong brand loyalty are exceptions to this rule)
• Low price
• From the marketers' angle:
• High volumes
• Low contribution margins
• Extensive distribution networks
• High stock turnover

Source: Wikipedia.

Category management Neeraj nathani smart bridge

Category management

Category management is a retailing and purchasing concept in which the range of products purchased by a business organization or sold by a retailer is broken down into discrete groups of similar or related products; these groups are known as product categories (examples of grocery categories might be: tinned fish, washing detergent, toothpastes). It is a systematic, disciplined approach to managing a product category as a strategic business unit.^[1] The phrase "category management" was coined by Brian F. Harris.^{[n/a 1]}

Category management in a retail context

Each category is run as a "mini business" (business unit) in its own right, with its own set of turnover and/or profitability targets and strategies. Introduction of Category Management in a business tends to alter the relationship between retailer and supplier: instead of the traditional adversarial relationship, the relationship moves to one of collaboration, with exchange of information, sharing of data and joint business building.

The focus of all supplier negotiations is the effect on turnover of the category as whole, not just the sales of individual products. Suppliers are expected, indeed in many cases mandated, to only suggest new product introductions, a new planogram or promotional activity if it is expected to have a beneficial effect on the turnover or profit of the total category and be beneficial to the shoppers of that category.

The concept originated in grocery (mass merchandising) retailing, and has since expanded to other retail sectors such as DIY, cash and carry, pharmacy, and book retailing.^[2]

Definition of category management (retail)

Category management lacks a single definition thus leading to some ambiguity even among industry professionals as to its exact function. Three comparative mainstream definitions are as follows:
Category management is a process that involves managing product categories as business units and customizing them [on a store by store basis] to satisfy customer needs. (Nielsen)^[3]
The strategic management of product groups through trade partnerships which aims to maximize sales and profit by satisfying consumer and shopper needs (Institute of Grocery Distribution)^[4]
.. marketing strategy in which a full line of products (instead of the individual products or brands) is managed as a strategic business unit (SBU). (Business Dictionary)^[5]
The Nielsen definition, published in 1992, was a little ahead of its time in that customising product offerings on a store by store basis is logistically difficult and is now not considered a necessary part of category management; it is a concept now referred to as micromarketing. Nevertheless, most grocery retailers will segment stores at least by size, and select product assortments accordingly. Wal*Mart's Store of the Community, implemented in North America is one of the few examples of where product offerings are tailored right down to the specific store.^[6]

Definition of a category

The Nielsen definition of a category, used as the basic definition across the industry is that the products should meet a similar consumer need, or that the products should be inter-related or substitutable.^[8] The Nielsen definition also includes a provision that products placed together in the same category should be logistically manageable in store (for example there may be issues in having room-temperature and chilled products together in the same category even though the initial two conditions are met).

However, this definition does not explain how the process often works in practical retailing situations, where demographic or marketing considerations take precedence.

The category management 8-step process (retail)

The category management 8-step process

The industry standard model for category management in retail is the 8-step process, or 8-step cycle developed by the Partnering Group.^[9] The eight steps are shown in the diagram on the right; they are :

1. Define the category (i.e. what products are included/excluded).
2. Define the role of the category within the retailer.
3. Assess the current performance.
4. Set objectives and targets for the category.
5. Devise an overall Strategy.
6. Devise specific tactics.
7. Implementation.
8. The eighth step is one of review which takes us back to step 1.

The 8-step process, whilst being very comprehensive and thorough has been criticized for being rather too unwieldy and time-consuming in today's fast-moving sales environment; in one survey only 9% of supplier companies stated they used the full 8-step process.^[10] The current industry trend is for supplier companies to use the standard process as a basis to develop their own more streamlined processes, tailored to their own particular products^[11]

Market research company Nielsen has a similar process based on only 5 steps : reviewing the category, targeting consumers, planning merchandising, implementing strategy, evaluating results

Category captains

It is commonplace for one particular supplier into a category to be nominated by the retailer as a category captain. The category captain will be expected to have the closest and most regular contact with the retailer and will also be expected to invest time, effort, and often financial investment into the strategic development of the category within the retailer.
In return, the supplier will gain a more influential voice with the retailer. The category captain is often the supplier with the largest turnover in the category. Traditionally the job of category captain is given to a brand supplier, but in recent times the role has also gone to particularly switched-on private label suppliers.^[12]
In order to do the job effectively, the supplier may be granted access to a greater wealth of data-sharing, e.g. more access to an internal sales database such as Walmart's Retail Link

Category management in purchasing

Category management can also be applied to purchasing within an organisation. Although the term is the same and there are many similarities with elements of retail category management including the use of similar tools and techniques applied in reverse, the methodology is fundamentally different. Applying Category Management in purchasing benefits organisations by providing an approach to reduce the cost of buying goods and services, reduce risk in the supply chain, increase overall value from the supply base and gain access to more innovation from suppliers. It is a strategic approach that focuses on the vast majority of organisational spend. If applied effectively throughout an entire organisation the results can be significantly greater than traditional transactional based purchasing negotiations.

The concept of Category Management in purchasing originated in the late 80's. There is no single founder or originator but the methodology first appeared in the automotive sector and has since been developed and adopted by organisations worldwide. Today Category Management is considered by many global companies as an essential strategic purchasing approach. Category Management has been defined as “an evolving methodology that drives sourcing strategy in progressive organisations today”.^[15]

The Chartered Institute of Purchasing & Supply defines Category Management as:

"organising the resources of the procurement team in such a way as to focus externally onto the supply markets of an organisation (as against having a focus on the internal customers or on internal Procurement departmental functions) in order to fully leverage purchasing decisions”.^[16]

Jonathan O'Brien, author of Category Management in Purchasing, defines Category Management as:

"the practice of segmenting the main areas of organisational spend on bought-in goods and services into discrete groups of products and services according to the function of those goods or services and, most importantly, to mirror how individual marketplaces are organised. Using this segmentation organisations work cross functionally on individual categories, examining the entire category spend, how the organisation uses the products or services within the category, the marketplace and individual suppliers.",^[17]

Peter Hunt, partner at ADR International, writes

“the term category management can mean different things to different people, so a working definition is needed. A ‘category’ is the logical grouping of similar expenditure items, such as spend on advertising agency services or IT hardware. Category management is the sourcing process used to manage these categories to satisfy business needs while maximising the value delivered from the supply base”.^[18]

Many public sector organisations have recently adopted category management as a strategic transformation tool. Sir Philip Green, in his “Efficiency Review” of UK government spending, recommended that “centralised procurement [should be] mandated for common categories to leverage this buying power and achieve best practice”.^[19]

Source: Wikipedia.

Neeraj Nathani Category management

Category management

Category management is a retailing and purchasing concept in which the range of products purchased by a business organization or sold by a retailer is broken down into discrete groups of similar or related products; these groups are known as product categories (examples of grocery categories might be: tinned fish, washing detergent, toothpastes). It is a systematic, disciplined approach to managing a product category as a strategic business unit.^[1] The phrase "category management" was coined by Brian F. Harris.^{[n/a 1]}

Category management in a retail context

Each category is run as a "mini business" (business unit) in its own right, with its own set of turnover and/or profitability targets and strategies. Introduction of Category Management in a business tends to alter the relationship between retailer and supplier: instead of the traditional adversarial relationship, the relationship moves to one of collaboration, with exchange of information, sharing of data and joint business building.

The focus of all supplier negotiations is the effect on turnover of the category as whole, not just the sales of individual products. Suppliers are expected, indeed in many cases mandated, to only suggest new product introductions, a new planogram or promotional activity if it is expected to have a beneficial effect on the turnover or profit of the total category and be beneficial to the shoppers of that category.

The concept originated in grocery (mass merchandising) retailing, and has since expanded to other retail sectors such as DIY, cash and carry, pharmacy, and book retailing.^[2]

Definition of category management (retail)

Category management lacks a single definition thus leading to some ambiguity even among industry professionals as to its exact function. Three comparative mainstream definitions are as follows:
Category management is a process that involves managing product categories as business units and customizing them [on a store by store basis] to satisfy customer needs. (Nielsen)^[3]
The strategic management of product groups through trade partnerships which aims to maximize sales and profit by satisfying consumer and shopper needs (Institute of Grocery Distribution)^[4]
.. marketing strategy in which a full line of products (instead of the individual products or brands) is managed as a strategic business unit (SBU). (Business Dictionary)^[5]
The Nielsen definition, published in 1992, was a little ahead of its time in that customising product offerings on a store by store basis is logistically difficult and is now not considered a necessary part of category management; it is a concept now referred to as micromarketing. Nevertheless, most grocery retailers will segment stores at least by size, and select product assortments accordingly. Wal*Mart's Store of the Community, implemented in North America is one of the few examples of where product offerings are tailored right down to the specific store.^[6]

Definition of a category

The Nielsen definition of a category, used as the basic definition across the industry is that the products should meet a similar consumer need, or that the products should be inter-related or substitutable.^[8] The Nielsen definition also includes a provision that products placed together in the same category should be logistically manageable in store (for example there may be issues in having room-temperature and chilled products together in the same category even though the initial two conditions are met).

However, this definition does not explain how the process often works in practical retailing situations, where demographic or marketing considerations take precedence.

The category management 8-step process (retail)

The category management 8-step process

The industry standard model for category management in retail is the 8-step process, or 8-step cycle developed by the Partnering Group.^[9] The eight steps are shown in the diagram on the right; they are :

1. Define the category (i.e. what products are included/excluded).
2. Define the role of the category within the retailer.
3. Assess the current performance.
4. Set objectives and targets for the category.
5. Devise an overall Strategy.
6. Devise specific tactics.
7. Implementation.
8. The eighth step is one of review which takes us back to step 1.

The 8-step process, whilst being very comprehensive and thorough has been criticized for being rather too unwieldy and time-consuming in today's fast-moving sales environment; in one survey only 9% of supplier companies stated they used the full 8-step process.^[10] The current industry trend is for supplier companies to use the standard process as a basis to develop their own more streamlined processes, tailored to their own particular products^[11]

Market research company Nielsen has a similar process based on only 5 steps : reviewing the category, targeting consumers, planning merchandising, implementing strategy, evaluating results

Category captains

It is commonplace for one particular supplier into a category to be nominated by the retailer as a category captain. The category captain will be expected to have the closest and most regular contact with the retailer and will also be expected to invest time, effort, and often financial investment into the strategic development of the category within the retailer.
In return, the supplier will gain a more influential voice with the retailer. The category captain is often the supplier with the largest turnover in the category. Traditionally the job of category captain is given to a brand supplier, but in recent times the role has also gone to particularly switched-on private label suppliers.^[12]
In order to do the job effectively, the supplier may be granted access to a greater wealth of data-sharing, e.g. more access to an internal sales database such as Walmart's Retail Link

Category management in purchasing

Category management can also be applied to purchasing within an organisation. Although the term is the same and there are many similarities with elements of retail category management including the use of similar tools and techniques applied in reverse, the methodology is fundamentally different. Applying Category Management in purchasing benefits organisations by providing an approach to reduce the cost of buying goods and services, reduce risk in the supply chain, increase overall value from the supply base and gain access to more innovation from suppliers. It is a strategic approach that focuses on the vast majority of organisational spend. If applied effectively throughout an entire organisation the results can be significantly greater than traditional transactional based purchasing negotiations.

The concept of Category Management in purchasing originated in the late 80's. There is no single founder or originator but the methodology first appeared in the automotive sector and has since been developed and adopted by organisations worldwide. Today Category Management is considered by many global companies as an essential strategic purchasing approach. Category Management has been defined as “an evolving methodology that drives sourcing strategy in progressive organisations today”.^[15]

The Chartered Institute of Purchasing & Supply defines Category Management as:

"organising the resources of the procurement team in such a way as to focus externally onto the supply markets of an organisation (as against having a focus on the internal customers or on internal Procurement departmental functions) in order to fully leverage purchasing decisions”.^[16]

Jonathan O'Brien, author of Category Management in Purchasing, defines Category Management as:

"the practice of segmenting the main areas of organisational spend on bought-in goods and services into discrete groups of products and services according to the function of those goods or services and, most importantly, to mirror how individual marketplaces are organised. Using this segmentation organisations work cross functionally on individual categories, examining the entire category spend, how the organisation uses the products or services within the category, the marketplace and individual suppliers.",^[17]

Peter Hunt, partner at ADR International, writes

“the term category management can mean different things to different people, so a working definition is needed. A ‘category’ is the logical grouping of similar expenditure items, such as spend on advertising agency services or IT hardware. Category management is the sourcing process used to manage these categories to satisfy business needs while maximising the value delivered from the supply base”.^[18]

Many public sector organisations have recently adopted category management as a strategic transformation tool. Sir Philip Green, in his “Efficiency Review” of UK government spending, recommended that “centralised procurement [should be] mandated for common categories to leverage this buying power and achieve best practice”.^[19]

Source: Wikipedia.