Neeraj Nathani PERFORMANCE BOOSTERS FOR ETL PROGRAMS:

PERFORMANCE BOOSTERS FOR ETL PROGRAMS:

Challenges

ETL processes can involve considerable complexity, and significant operational problems can occur with improperly designed ETL systems.

The range of data values or data quality in an operational system may exceed the expectations of designers at the time validation and transformation rules are specified. Data profiling of a source during data analysis can identify the data conditions that will need to be managed by transform rules specifications. This will lead to an amendment of validation rules explicitly and implicitly implemented in the ETL process.

Data warehouses are typically assembled from a variety of data sources with different formats and purposes. As such, ETL is a key process to bring all the data together in a standard, homogeneous environment.

Design analysts should establish the scalability of an ETL system across the lifetime of its usage. This includes understanding the volumes of data that will have to be processed within service level agreements. The time available to extract from source systems may change, which may mean the same amount of data may have to be processed in less time. Some ETL systems have to scale to process terabytes of data to update data warehouses with tens of terabytes of data. Increasing volumes of data may require designs that can scale from daily batch to multiple-day microbatch to integration with message queues or real-time change-data capture for continuous transformation and update

Performance

ETL vendors benchmark their record-systems at multiple TB (terabytes) per hour (or ~1 GB per second) using powerful servers with multiple CPUs, multiple hard drives, multiple gigabit-network connections, and lots of memory. The fastest ETL record is currently held by Syncsort,^[1] Vertica and HP at 5.4TB in under an hour which is more than twice as fast as the earlier record held by Microsoft and Unisys.

In real life, the slowest part of an ETL process usually occurs in the database load phase. Databases may perform slowly because they have to take care of concurrency, integrity maintenance, and indices. Thus, for better performance, it may make sense to employ:

• Direct Path Extract method or bulk unload whenever is possible (instead of querying the database) to reduce the load on source system while getting high speed extract
• most of the transformation processing outside of the database
• bulk load operations whenever possible.

Still, even using bulk operations, database access is usually the bottleneck in the ETL process. Some common methods used to increase performance are:

• Partition tables (and indices). Try to keep partitions similar in size (watch for null values which can skew the partitioning).
• Do all validation in the ETL layer before the load. Disable integrity checking (disable constraint ...) in the target database tables during the load.
• Disable triggers (disable trigger ...) in the target database tables during the load. Simulate their effect as a separate step.
• Generate IDs in the ETL layer (not in the database).
• Drop the indices (on a table or partition) before the load - and recreate them after the load (SQL: drop index ...; create index ...).
• Use parallel bulk load when possible — works well when the table is partitioned or there are no indices. Note: attempt to do parallel loads into the same table (partition) usually causes locks — if not on the data rows, then on indices.
• If a requirement exists to do insertions, updates, or deletions, find out which rows should be processed in which way in the ETL layer, and then process these three operations in the database separately. You often can do bulk load for inserts, but updates and deletes commonly go through an API (using SQL).

Whether to do certain operations in the database or outside may involve a trade-off. For example, removing duplicates using distinct may be slow in the database; thus, it makes sense to do it outside. On the other side, if using distinct will significantly (x100) decrease the number of rows to be extracted, then it makes sense to remove duplications as early as possible in the database before unloading data.

A common source of problems in ETL is a big number of dependencies among ETL jobs. For example, job "B" cannot start while job "A" is not finished. You can usually achieve better performance by visualizing all processes on a graph, and trying to reduce the graph making maximum use of parallelism, and making "chains" of consecutive processing as short as possible. Again, partitioning of big tables and of their indices can really help.

Another common issue occurs when the data is spread between several databases, and processing is done in those databases sequentially. Sometimes database replication may be involved as a method of copying data between databases - and this can significantly slow down the whole process. The common solution is to reduce the processing graph to only three layers:

• Sources
• Central ETL layer
• Targets

This allows processing to take maximum advantage of parallel processing. For example, if you need to load data into two databases, you can run the loads in parallel (instead of loading into 1st - and then replicating into the 2nd).

Of course, sometimes processing must take place sequentially. For example, you usually need to get dimensional (reference) data before you can get and validate the rows for main "fact" tables.

Parallel processing

A recent^[update] development in ETL software is the implementation of parallel processing. This has enabled a number of methods to improve overall performance of ETL processes when dealing with large volumes of data.

ETL applications implement three main types of parallelism:

• Data: By splitting a single sequential file into smaller data files to provide parallel access.
• Pipeline: Allowing the simultaneous running of several components on the same data stream. For example: looking up a value on record 1 at the same time as adding two fields on record 2.
• Component: The simultaneous running of multiple processes on different data streams in the same job, for example, sorting one input file while removing duplicates on another file.

All three types of parallelism usually operate combined in a single job.

An additional difficulty comes with making sure that the data being uploaded is relatively consistent. Because multiple source databases may have different update cycles (some may be updated every few minutes, while others may take days or weeks), an ETL system may be required to hold back certain data until all sources are synchronized. Likewise, where a warehouse may have to be reconciled to the contents in a source system or with the general ledger, establishing synchronization and reconciliation points becomes necessary.

Source: Wikipedia.

Category management Neeraj nathani

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 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.

FMCG Market Smart Bridge Neeraj Nathani

                                                                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.