Informatica Workflow Successful : No Data in target !

This is a frequently asked in the Informatica forums and the solution is usually pretty simple. However, that will have to wait till the end because there is one important thing that you should know before you go ahead and fix the problem.
Your workflow should have failed in the first place. If this was in Production, Support Teams should know that something Failed. Report users should know the data in the Marts is not ready for reporting. Dependent workflows should wait until this is resolved. This coding practice basically violates the Age-old Principle of fail-fast when something goes wrong, instead of continuing flawed execution pretending “All is well”, causing the toughest-to-debug defects.
Of Course, this is not specific to Informatica. It is not uncommon to see code in other languages which follows this pattern. The only issue that is specific to Informatica is that this is the default behavior when you create a session. So you might have this “bug” in your code without even knowing it.

Stop On Errors:
Indicates how many non-fatal errors the Integration Service can encounter before it stops the session. Non-fatal errors include reader, writer, and DTM errors. Enter the number of non-fatal errors you want to allow before stopping the session. The Integration Service maintains an independent error count for each source, target, and transformation. If you specify 0, non-fatal errors do not cause the session to stop.
Optionally use the $PMSessionErrorThreshold service variable to stop on the configured number of errors for the Integration Service.

In Oracle, it is the infamous “when others then null” .

BEGIN
  <process SOME Data>
exception
   WHEN others 
       THEN NULL;  
END;
/

In Java..Something like..

try {
   fooObject.doSomething();
}
catch ( Exception e ) {
   // do nothing
}

The solution to this problem in Informatica is to set a limit on the number of allowed errors for a given session using one of the following methods.
a) Having “1″ in your default session config : Fail the session on the first non-fatal error.
b) Over-write the session Configuration details and enter the “Stop On Errors” to “1″ or a fixed number.
c) Use the $PMSessionErrorThreshold variable and set it at the integration service level. You can always override the variable in the parameter file. Take a look at this Article on how you can do that.
Remember, if your sessions do not belong to one of these categories, you are doing it wrong!.
a) Your session Fails and Causes the workflow to fail whenever any errors occur.
b) You allow the session to continue despite some (expected) errors, but you always send the .bad file and the log file to the support/business team in charge.
Why is there no data in Target
The solution to “why the records didn’t make it to the target” is usually pretty evident in the session log file. The usual case (based on most of the times this question is asked) is becuase all of your records are failing with some non-fatal error.
The only point of this article is to remind you that your code has to notify the right people when the workflow did not run as planned.

ORA-01403: no data found

Pretty common oracle error. Raised when you are trying to fetch data from sql into a pl/sl variable and the sql does not return any data.
>> Using the data from this schema

 
SQL> SELECT COUNT(*)
  FROM scott_emp
  WHERE empno = 9999;
 
  COUNT(*)
----------
         0
 
SQL> DECLARE
   l_ename scott_emp.ename%TYPE;
   l_empno scott_emp.empno%TYPE := 9999;
BEGIN
   SELECT ename
     INTO l_ename
     FROM scott_emp
     WHERE empno = l_empno;
END;
/
 
DECLARE
*
ERROR at line 1:
ORA-01403: no DATA found
ORA-06512: at line 5

What to do next
1. Re-Raise it with a error message that provides more context.

DECLARE
   l_ename scott_emp.ename%TYPE;
   l_empno scott_emp.empno%TYPE := 9999;
BEGIN
   SELECT ename
     INTO l_ename
     FROM scott_emp
     WHERE empno = l_empno;
EXCEPTION
  WHEN no_data_found
   THEN raise_application_error(-20001,'No employee exists with employee id ' || l_empno);
END;
/
ERROR at line 1:
ORA-20001: No employee EXISTS WITH employee id 9999
ORA-06512: at line 11

2. Suppress the error if this is a valid business scenario and do the necessary processing.
Example CASE : If a user has a preference to display the numbers in local currency, convert the amount, else, display in USD.

 
CREATE OR REPLACE PROCEDURE p_calc_sales_metrics(
   p_user_id IN users.user_id%TYPE,
   p_profit  IN net_sales.profit%TYPE
) AS
  l_pref_currency user_prefs.pref_currency%TYPE;
  l_profit_local_amt net_sales.profit%TYPE;
BEGIN
 
---other code
 BEGIN
 
 SELECT pref_currency
   INTO l_pref_currency
  WHERE user_id = p_user_id
 
        l_cur_conv_factor := get_conv_rate('USD',l_pref_currency);
 
 
 exception
   WHEN no_data_found 
    THEN l_cur_conv_factor := 1;
 END;
 
--- other code..
 
 l_profit_local_amt :=  p_profit * l_cur_conv_factor;
 
 
END;
/

3. Functions, by design, do not raise the NO_DATA_FOUND exception, instead they return null to the calling program.

CREATE OR REPLACE FUNCTION STGDATA.f_get_ename(
   i_empno IN scott_emp.empno%TYPE
) RETURN scott_emp.ename%TYPE
AS
  l_ename scott_emp.ename%TYPE;
BEGIN
 
  SELECT ename
    INTO l_ename
    FROM scott_emp
   WHERE empno = i_empno;
 
  RETURN l_ename;  
 
END;
/
 
SQL> SELECT f_get_ename(7839) FROM dual;
 
F_GET_ENAME(7839)
---------------------
KING
 
SQL>  SELECT f_get_ename(9999) FROM dual;
 
F_GET_ENAME(9999)
-------------------------------------------
 
 
SQL> SELECT nvl(f_get_ename(9999),'NULL RETURNED') FROM dual;
 
NVL(F_GET_ENAME(9999),'NULLRETURNED')
------------------------------------------------
NULL RETURNED

Introducing Informatica Cloud 9 – The Defining Capability For Cloud Computing

Today we made an announcement called Informatica Cloud 9.  This is the culmination of many years of hard work and effort and builds on the Informatica 9 announcement we made last week.  So what is so special about Informatica Cloud 9?  Is it the new Platform-as-a-Service offering?  Or it is the new Cloud Services we delivered?  Or is it the new capabilities on Amazon EC2?  What are all these things and why are they important?
Let me explain:
Informatica Cloud 9 started over four years ago when we noticed the beginnings of a revolution happening around us – namely Cloud Computing.  Many of you may not be familiar with our work in the Cloud, but we have been very focused on delivering data integration as a “Software-as-a-Service” (SaaS) solution.  This has meant taking our enterprise class capabilities and simplifying the interface to an extent where a simple non-technical business user can point-and-click to connect a cloud application with an on-premise application.
We have always believed that this is critical to realize since business users typically put off the integration tasks because they don’t want to rely on IT for time or resources. So we wanted to make it incredibly easy for the business user to do this on their own.  We focused on salesforce.com and built data movement and then data synchronization capabilities.  Indeed, our Data Loader Service for salesforce.com was voted the best data integration solution on their AppExchange this year.
However, our belief is that while business users must be able to define an integration task, their IT colleagues should be able to see the same integration processes from within their environment.  Only then will the business user be able to truly bring new applications into critical usage.  The same needs to be true the other way around – we want to be able to define complex integrations and enable business users to be able to run them through an easy-to-use browser interface.  Only then will the CFO, and others, be confident that they can trust the data being deployed across public and private clouds and begin to embrace cloud computing for core business requirements.  We call this Business-IT collaboration and it was a big part of last week’s Informatica 9 announcement as well.
Cloud computing is re-defining IT and data integration needs to follow-suit.  It is data integration that will be THE defining capability for cloud computing – not outsourced datacenters, or sexy new application solutions.  So, to really embrace cloud computing, one needs a whole new way of delivering enterprise data integration that brings together the ease of use that business users require with the sophistication that must be delivered for IT architects. Otherwise cloud computing will simply remain the domain of non-critical fancy-looking applications on the periphery of true enterprise business requirements.
Informatica Cloud 9 is a significant step forward in solving this problem of providing data integration in the clouds.  With today’s announcement, anyone who is involved with Informatica can build, share and deploy any data integration components and deploy them anywhere. These components may relate to data quality or data integration or indeed, with time, any of the other components that make up the Informatica 9 Platform. After all, Informatica Cloud 9 is built on the comprehensive and unified Informatica 9 and therefore will eventually inherit the core capabilities of the platform.
Informatica Cloud 9 delivers on this belief and provides three critical components towards that goal:

• With Data Quality Cloud Edition and PowerCenter Cloud Edition on Amazon EC2 we are providing a low-cost hourly build capability for IT users. With this, developers can build complex integrations between applications that can be published to non-technical line-of-business managers to consume and manage.  Indeed any of the 50,000+ developers on the Informatica TechNet, or any of our Systems Integration partners will be able to do this.  These integrations can be thought of as templates – picked up by anyone using the Informatica Cloud 9 Platform and re-deployed.
• With the Informatica Cloud 9 Platform-as-a-Service we are providing the multi-tenant, scalable enterprise engine for deploying data integration in the clouds. One note that you may not be familiar with – we are already running over 17,000 jobs a day through our multi-tenant Informatica Cloud Services and moving over three Billion rows a month of client data.
• With our new Informatica Cloud 9 services we are enhancing our own simple-to-use suite of data integration cloud applications that continue to evolve the role of the business user to be self-sufficient in their approach to accessing and integrating trustworthy cloud-based and on-premise data.

Now an enterprise can enable business users to use any cloud application and remain in control of their most critical asset – their data.  Developers can share re-usable templates across the business; System Integrators can build data integration templates for specific cloud and on-premise applications and deploy them across to their clients; consultants can move from client to client with toolboxes of pre-configured templates.
Informatica Cloud 9 is the evolution of enterprise data integration to the clouds.  Take a few moments please to re-read the press release and, in particular, the quotes therein:

• “Informatica Cloud 9 will dramatically simplify cloud-to-cloud and cloud to on-premise data integrations…”
• “… the ability to develop more complex mappings and workflows and run them as custom services for line of business managers will allow us to continue to provide self-service, while IT remains in control…”
• “… we’ve developed an SAP data integration as a service solution…”
• “… we plan to develop re-usable templates to accelerate time to market and reduce total cost of ownership for our customers… “
• “Informatica Cloud Platform gives us the power and flexibility to meet enterprise requirements and deliver solutions to non-technical business users …”

Hopefully now you can see why we are all on Informatica Cloud 9 here!

How Big Data Changes Data Integration

With Big Data systems now in the mix within most enterprises, those charged with data integration are interested in how their world will soon change. Rest assured, most of the patterns of integration that we deal with today will still be around for years to come.
However, there are some clear trends that data integration managers need to understand, such as:

• The ability to imply structure to the data at the time of use.
• The ability to store both structured and unstructured data.
• The need for faster data integration technology.

The ability to imply structure to the data at the time of use refers to the fact that Big Data systems using the Hadoop set of technologies have the ability to add a structure at the time of use. Thus, you don’t need to pre-define a structure as we do in the world of relational data, you can map a structure to existing data.
While this has certain advantages, such as the ability to create dynamic structure around in-line analytical services, this also causes some complexity when dealing with data integration technology. Most data integration technology leverages some type of structure on either end of the integration flow. The idea is that you need to layer a structure as the data is consumed, translated, and produced from one system or data store to another.
The ability to store both structured and unstructured data, as related to the layering in a dynamic structure, brings both complexity and flexibility. Big Data systems are basically file systems with anything and everything stored in them. This means that documents, text, and data are all intermingled. This information may be bound to a structure, or freestanding.  In any event, you need to provide the ability to move both structured and unstructured data from store to store.
The need for faster data integration technology is a result of the fact that we deal with much larger volumes of data than more traditional enterprise systems. Therefore, there is more data that has to be moved from data store to data store. Thus, there is a renewed focus on data integration technology’s ability to keep up with the data integration performance requirements.
In many respects, the ability to create a data integration solution that is able to move larger volumes of structured and unstructured data between data stores is dependent upon the way you’ve designed the data integration flows, as much as the data integration technology itself. As Big Data systems move into your enterprise, and you join them together using data integration technology, you’ll find that the patterns of the integration flows need to change as well. Before these systems are put into production, it’s a good idea to review what needs to change and best practices around the design of the integration flows.
Big Data is more of an evolution around the way we store and deal with data. It provides more primitive commodity mechanisms that provide more flexibility and the ability to deal with larger amounts of data using highly distributed data management technology. Data integration technology needs to adapt to this change, which is further reaching than anything we’ve seen of late.

How Integration Platform-as-a-Service Impacts Cloud Adoption

Did you know that Forrester estimates in their 10 Cloud Predictions For 2012 blog post that on average organizations will be running more than 10 different cloud applications and that the public Software-as-a-Service (SaaS) market will hit $33 billion by the end of 2012?
However, in the same post, Forrester also acknowledged that SaaS adoption is led mainly by Customer Relationship Management (CRM), procurement, collaboration, and Human Capital Management (HCM) software and that all other software segments will “still have significantly lower SaaS adoption rates”. It’s not hard to see this in the market today, with cloud juggernaut salesforce.com leading the way in CRM, and Workday and SuccessFactors doing battle in HCM, for example. Forrester claims that amongst the lesser known software segments, Product Lifecycle Management (PLM), Business Intelligence (BI), and Supply Chain Management (SCM) will be the categories to break through as far as SaaS adoption is concerned, with approximately 25% of companies using these solutions by 2012.
I am not at all surprised that CRM, and HCM are leading the way as far as SaaS application adoption is concerned. One only needs to examine the reason behind why these categories took off. During the so-called “Great Recession,” companies wanted an efficient way in which to grow revenues and cut costs. On the revenue side of the equation, companies found that sales force automation (SFA) helped them close more deals faster, and increased customer visibility allowed them to focus on customer retention as well as potential upsell and cross-sell opportunities. On the costs side, some have argued that functions such as HR and Talent Management were the first to be moved to the cloud as they were considered “non-core”.
As data volumes, global deployment, and end-user adoption grew, it became increasingly clear that out-of-the-box CRM or HCM functionality was not going to cut it, and that customization options would be necessary. Out of this necessity evolved the world of Platform-as-a-Service (PaaS). Similar to the concept of SaaS, a PaaS environment involves built-in scalability, reliability, security, databases, interfaces to web services and a container with development tools for building custom apps. Salesforce.com was one of the early creators of this new cloud ecosystem with its Force.com platform. This platform, which now numbers over 220,000 apps (as of the publication of this blog post) provides numerous options to customize a CRM deployment as well as build websites, and numerous productivity-enhancing and vertical specific apps from the ground up that tied into the core CRM functionality.
While the PaaS ecosystem provides a great avenue to build custom apps and increase cloud application adoption, too often it is tied into the code-base of the dominant SaaS player that brought it into existence. As a result, other non-CRM and non-HCM functions such as PLM, SCM, BI, and ERP still largely remain in the on-premises world.
This is where iPaaS, or integration PaaS comes into play. Each of these other non-CRM functions is an important part of the value chain, whether upstream, or downstream. PLM and SCM systems for instance interact frequently with ERP systems. BI and analytics software have multiple touch points with all these systems. Integrating all these systems together and tying them to specific customer records in the CRM system has been such a time-consuming task that most SaaS providers simply chant the mantra of “web services” when asked by customers how they can connect various SaaS ecosystems together. Web services typically accomplish a very specific business process and specific task between two different SaaS applications, and the web services APIs do not lead to repeatability. In fact, a Slashdot blog on the API economy mentioned that there were some 5,000 APIs estimated by the end of 2012 and some 30,000 estimated in the next four years.
The proliferation of APIs along with SaaS adoption only strengthens the need for an integration PaaS that abstracts the underlying orchestrations of these APIs to end users. An integration PaaS, allows developers to build full (or partial, if desired) native connectivity to every single object within an application, whether SaaS or not. By building native connectors, every permutation and combination of objects between different SaaS applications is possible, thereby increasing the possibility for companies to choose those SaaS apps that fit their business function or department. With increasing confidence of the existence of the integration PaaS, companies will continue to adopt SaaS apps in other LOBs, and not just the mainstream CRM or HCM categories. This in turn spurs the other SaaS category providers to invest more in R&D and come out with even more advanced functionality.
With the increased innovation occurring across all SaaS applications, we can expect more and more complex use cases involving larger amounts of data. All of this coupled with custom apps built on competing PaaS platforms will only further increase the use of an integration PaaS to achieve cloud data integration.

Delivering IT Value with Master Data Management and the Cloud

Over the last few years most enterprises have implemented several (if not more) large ERP and CRM suites. Although these applications were meant to have self-contained data models, it turns out that many enterprises still need to manage “master data” between the various applications. So the traditional IT role of hardware administration and custom programming has evolved to packaged application implementation and large scale data management.  According to Wikipedia: “MDM has the objective of providing processes for collecting, aggregating, matching, consolidating, quality-assuring, persisting and distributing such data throughout an organization to ensure consistency and control in the ongoing maintenance and application use of this information.” Instead of designing large data warehouses to maintain the master data, many organizations turn to packaged Master Data Management (MDM) packages (such as Informatica MDM). With these tools at hand, IT shops can then build true Customer Master, Product Master (Product Information Management – PIM), Employee, or Supplier Master solutions.
MDM solutions vary by industry in terms of tactical approaches taken – e.g., pharmaceutical/life sciences will adopt semi-batch, database-centric approaches for master physician data to be deployed to sales forces, while financial services providers and online retailers will require near real-time, business process-centric solutions to compete in the business-to-consumer (B2C) online world. These different types of implementations require technical IT expertise in delivering an end-to-end solution.  Based on quarterly surveys of the MDM Institute Business Council™ (8,000+ subscribers to the MDM Alert newsletter engaged in MDM projects), the perennial top four business drivers for MDM initiatives are summarized as:
(1)    compliance and regulatory reporting;
(2)    economies of scale for mergers and acquisitions (M&A);
(3)    synergies for cross-sell and up-sell;
(4)    legacy system integration and augmentation; and
Note that this list represents business drivers, not technical initiatives. Ideally, the business analyst “owns” the data and is responsible for the initial definition of what the master data looks like (whether this is from a custom application or a packaged solution). In addition, they are responsible for the processes (not actual data entry) of inputting the data into the source systems. IT acts as “data stewards” – coordinators between various business groups. IT’s role should be project managers that phase in updates to the primary MDM. These data stewards must be equally savvy in data modeling as well as business processes.  IT must also be the technical gurus to glue applications and databases together. This also involves data quality processes, such as standardization, cleansing, validation, enrichment and matching.
Traditional MDM solutions have been implemented on premise, primarily as data hubs to various applications spokes such as Human Resources, PLM, ERP, and CRM applications. With the huge uptick of software as a service (SaaS) CRM providers such as salesforce.com, this requires MDM solutions to integrate data from the cloud.
While an on-premise model works well when most of the data is updated within the “four walls” of the enterprise, a hybrid cloud + on premise model may be better suited to a B2C environment when massive customer updates happen on a seasonal basis. In this case, a hybrid model will allow for extra cloud resources to be tapped in order to increase performance. In addition, with a hybrid model, sensitive data that may be legally prohibited from residing in the cloud can be kept on premise.
Should MDM be completely implemented in the cloud?
In this case, the master data model engine will reside in the cloud and will act as a hub between multiple SaaS applications and potentially on premise applications. A common scenario might be managing customer data between Salesforce CRM, Order fulfillment with UPS services, and on-premise ERP Receivables. Or replace the on-premise ERP solution with a cloud-based ERP such as NetSuite. In these cases, having MDM in the cloud might be the right approach. A cloud-based solution also makes sense for piloting a longer term MDM project. So look for a vendor that provides both on-premise and cloud-based MDM solutions for maximum deployment flexibility.
The Hybrid IT organization continues to evolve with new responsibilities. Cloud-based solutions tend to free up the IT staff from the more routine data center operations to get more involved with business activities such as Master Data Management. IT will play an important role in managing MDM solutions. Although, they don’t “own” the data, the technical requirements for implementing a solution remain in the IT domain. And acting as a data steward to capture the business requirements of what data needs to be managed and formulate the detailed rules and processes will become a key role. IT will also need to decide between on-premise and cloud-based architectures for the enterprise.
—-
Mercury Consulting is a trusted technology advisor with deep expertise in cloud applications. We offer strategic guidance to senior executives to select the right cloud solution and services assistance to help enterprises accelerate their adoption of cloud solutions.
Mike Canniff is a faculty member of Management Information Systems at the University of Pacific – Eberhardt School of Business. He has worked in the Information Technology field for over 20 years beginning with IBM as a software engineer and as Vice President, Development for Acuitrek Software. Mike has specialized his career research in the areas of Enterprise Application Integration and Electronic Commerce systems. He has published several papers on Electronic Commerce and Business Process Management best practices.

Electronic Trading Systems Moving to the Cloud

More and more business applications are moving from the desktop to the cloud, and electronic trading applications are no different.
Over the last five or ten years, application vendors have established several advantages of running major applications, even mission-critical applications like salesforce.com, over the cloud.
These advantages include:

• Easier and smoother upgrades, which provides much better adaptability and agility in the face of changing market and business conditions, plus a better user experience,
• Better scalability, with newer technology advances, and
• Better portability across a wide array of device types, including smartphones and tablets (especially in the last 2-3 years).

Recent improvements in Web technology, such as HTML5 WebSockets, are helping to speed this transition along by providing several throughput and latency advantages over earlier iterations of Web technology, and even over native Windows applications. Now, application architects can freely choose the technology that provides a better path for growth, agility, and scalability, which is often a Cloud-based solution.
As I write this, a few of our customers who provide electronic trading solutions to their clients are making the strategic move to develop a next generation application based in the Cloud. The main driver for one customer was to be able to take on more clients more quickly and therefore grow the business faster by increasing marginal revenue and profitability. They found that the list of challenges with a thick desktop client to be just too big for growing the business as quickly as they wanted to — or needed to.
Messaging middleware, especially peer-to-peer solutions such as Informatica Ultra Messaging, can be a very important piece of a Cloud-based application. The peer-to-peer “nothing in the middle” model provides applications not just ultra-high performance (whether for high throughput or low latency), but also near-linear scalability, true 24×7 reliability and availability, and business and IT agility. These qualities tie directly to the advantages listed above.
Cloud-based applications, of course, must also contend with the Internet and all that comes with that: support for various browsers and platforms (and versions of each), scalability and bandwidth issues, and mobile devices like smartphones and tablets. New web technologies like HTML5 WebSockets from Kaazing are best positioned to take care of the path from server to the smartphone or tablet, and with JMS connectivity to Ultra Messaging on the back end, can provide a Cloud-based application with a lean, scalable and agile infrastructure, usually with less hardware.

SAP HANA Architecture

In this article we will discuss about the architecture overview of the In-Memory Computing Engine of SAP HANA. The SAP HANA database is developed in C++ and runs on SUSE Linux Enterpise Server. SAP HANA database consists of multiple servers and the most important component is the Index Server. SAP HANA database consists of Index Server, Name Server, Statistics Server, Preprocessor Server and XS Engine.

1. Index Server contains the actual data and the engines for processing the data. It also coordinates and uses all the other servers.
2. Name Server holds information about the SAP HANA databse topology. This is used in a distributed system with instances of HANA database on different hosts. The name server knows where the components are running and which data is located on which server.
3. Statistics Server collects information about Status, Performance and Resource Consumption from all the other server components. From the SAP HANA Studio we can access the Statistics Server to get status of various alert monitors.
4. Preprocessor Server is used for Analysing Text Data and extracting the information on which the text search capabilities are based .
5. XS Engine is an optional component. Using XS Engine clients can connect to SAP HANA database to fetch data via HTTP.

Now let us check the architecture components of SAP HANA Index Server.

SAP HANA Index Server Architecture:

1. Connection and Session Management component is responsible for creating and managing sessions and connections for the database clients. Once a session is established, clients can communicate with the SAP HANA database using SQL statements. For each session a set of parameters are maintained like, auto-commit, current transaction isolation level etc. Users are Authenticated either by the SAP HANA database itself (login with user and password) or authentication can be delegated to an external authentication providers such as an LDAP directory.
2. The client requests are analyzed and executed by the set of components summarized as Request Processing And Execution Control. The Request Parser analyses the client request and dispatches it to the responsible component. The Execution Layer acts as the controller that invokes the different engines and routes intermediate results to the next execution step. For example, Transaction Control statements are forwarded to the Transaction Manager. Data Definition statements are dispatched to the Metadata Manager and Object invocations are forwarded to Object Store. Data Manipulation statements are forwarded to the Optimizer which creates an Optimized Execution Plan that is subsequently forwarded to the execution layer.
   
   • The SQL Parser checks the syntax and semantics of the client SQL statements and generates the Logical Execution Plan. Standard SQL statements are processed directly by DB engine.
   • The SAP HANA database has its own scripting language named SQLScript that is designed to enable optimizations and parallelization. SQLScript is a collection of extensions to SQL. SQLScript is based on side effect free functions that operate on tables using SQL queries for set processing. The motivation for SQLScript is to offload data-intensive application logic into the database.
   • Multidimensional Expressions (MDX) is a language for querying and manipulating the multidimensional data stored in OLAP cubes.
   • The SAP HANA database also contains a component called the Planning Engine that allows financial planning applications to execute basic planning operations in the database layer. One such basic operation is to create a new version of a dataset as a copy of an existing one while applying filters and transformations. For example: Planning data for a new year is created as a copy of the data from the previous year. This requires filtering by year and updating the time dimension. Another example for a planning operation is the disaggregation operation that distributes target values from higher to lower aggregation levels based on a distribution function.
   • The SAP HANA database also has built-in support for domain-specific models (such as for financial planning) and it offers scripting capabilities that allow application-specific calculations to run inside the database.
   The SAP HANA database features such as SQLScript and Planning operations are implemented using a common infrastructure called the Calc engine. The SQLScript, MDX, Planning Model and Domain-Specific models are converted into Calculation Models. The Calc Engine creates Logical Execution Plan for Calculation Models. The Calculation Engine will break up a model, for example some SQL Script, into operations that can be processed in parallel. The engine also executes the user defined functions.
   
3. In HANA database, each SQL statement is processed in the context of a transaction. New sessions are implicitly assigned to a new transaction. The Transaction Manager coordinates database transactions, controls transactional isolation and keeps track of running and closed transactions. When a transaction is committed or rolled back, the transaction manager informs the involved engines about this event so they can execute necessary actions. The transaction manager also cooperates with the persistence layer to achieve atomic and durable transactions.
4. Metadata can be accessed via the Metadata Manager. The SAP HANA database metadata comprises of a variety of objects, such as definitions of relational tables, columns, views, and indexes, definitions of SQLScript functions and object store metadata. Metadata of all these types is stored in one common catalog for all SAP HANA database stores (in-memory row store, in-memory column store, object store, disk-based). Metadata is stored in tables in row store. The SAP HANA database features such as transaction support, multi-version concurrency control, are also used for metadata management. In distributed database systems central metadata is shared across servers. How metadata is actually stored and shared is hidden from the components that use the metadata manager.
5. The Authorization Manager is invoked by other SAP HANA database components to check whether the user has the required privileges to execute the requested operations. SAP HANA allows granting of privileges to users or roles. A privilege grants the right to perform a specified operation (such as create, update, select, execute, and so on) on a specified object (for example a table, view, SQLScript function, and so on). The SAP HANA database supports Analytic Privileges that represent filters or hierarchy drilldown limitations for analytic queries. Analytic privileges grant access to values with a certain combination of dimension attributes. This is used to restrict access to a cube with some values of the dimensional attributes.
   
6. Database Optimizer gets the Logical Execution Plan from the SQL Parser or the Calc Engine as input and generates the optimised Physical Execution Plan based on the database Statistics. The database optimizer which will determine the best plan for accessing row or column stores.
7. Database Executor basically executes the Physical Execution Plan to access the row and column stores and also process all the intermediate results.
8. The Row Store is the SAP HANA database row-based in-memory relational data engine. Optimized for high performance of write operation, Interfaced from calculation / execution layer. Optimised Write and Read operation is possible due to Storage separation i.e. Transactional Version Memory & Persisted Segment.
   
   • Transactional Version Memory contains temporary versions i.e. Recent versions of changed records. This is required for Multi-Version Concurrency Control (MVCC). Write Operations mainly go into Transactional Version Memory. INSERT statement also writes to the Persisted Segment.
   • Persisted Segment contains data that may be seen by any ongoing active transactions. Data that has been committed before any active transaction was started.
   • Version Memory Consoliation moves the recent version of changed records from Transaction Version Memory to Persisted Segment based on Commit ID. It also clears outdated record versions from Transactional Version Memory. It can be considered as garbage collector for MVCC.
   • Segments contain the actual data (content of row-store tables) in pages. Row store tables are linked list of memory pages. Pages are grouped in segments. Typical Page size is 16 KB.
   • Page Manager is responsible for Memory allocation. It also keeps track of free/used pages.
9. The Column Store is the SAP HANA database column-based in-memory relational data engine. Parts of it originate from TREX (Text Retrieval and Extraction) i.e SAP NetWeaver Search and Classification. For the SAP HANA database this proven technology was further developed into a full relational column-based data store. Efficient data compression and optimized for high performance of read operation, Interfaced from calculation / execution layer. Optimised Read and Write operation is possible due to Storage separation i.e. Main & Delta.
   
   • Main Storage contains the compressed data in memory for fast read.
   • Delta Storage is meant for fast write operation. The update is performed by inserting a new entry into the delta storage.
   • Delta Merge is an asynchronous process to move changes in delta storage into the compressed and read optimized main storage. Even during the merge operation the columnar table will be still available for read and write operations. To fulfil this requirement, a second delta and main storage are used internally.
   • During Read Operation data is always read from both main & delta storages and result set is merged. Engine uses multi version concurrency control (MVCC) to ensure consistent read operations.
   • As row tables and columnar tables can be combined in one SQL statement, the corresponding engines must be able to consume intermediate results created by each other. A main difference between the two engines is the way they process data: Row store operators process data in a row-at-a-time fashion using iterators. Column store operations require that the entire column is available in contiguous memory locations. To exchange intermediate results, row store can provide results to column store materialized as complete rows in memory while column store can expose results using the iterator interface needed by row store.
10. The Persistence Layer is responsible for durability and atomicity of transactions. It ensures that the database is restored to the most recent committed state after a restart and that transactions are either completely executed or completely undone. To achieve this goal in an efficient way the per-sistence layer uses a combination of write-ahead logs, shadow paging and savepoints. The persistence layer offers interfaces for writing and reading data. It also contains SAP HANA 's logger that manages the transaction log. Log entries can be written implicitly by the persistence layer when data is written via the persistence interface or explicitly by using a log interface.

Distributed System and High Availability

The SAP HANA Appliance software supports High Availability. SAP HANA scales systems beyond one server and can remove the possibility of single point of failure. So a typical Distributed Scale out Cluster Landscape will have many server instances in a cluster. Therefore Large tables can also be distributed across multiple servers. Again Queries can also be executed across servers. SAP HANA Distributed System also ensures transaction safety. Features

• N Active Servers or Worker hosts in the cluster.
• M Standby Server(s) in the cluster.
• Shared file system for all Servers. Serveral instances of SAP HANA share the same metadata.
• Each Server hosts an Index Server & Name Server.
• Only one Active Server hosts the Statistics Server.
• During startup one server gets elected as Active Master.
• The Active Master assigns a volume to each starting Index Server or no volume in case of cold Standby Servers.
• Upto 3 Master Name Servers can be defined or configured.
• Maximum of 16 nodes is supported in High Availability configurations.

Name Server Configured Role	Name Server Actual Role	Index Server Configured Role	Index Server Actual Role
Master 1	Master	Worker	Master
Master 2	Slave	Worker	Slave
Master 3	Slave	Worker	Slave
Slave	Slave	Standby	Standby

Failover

• High Availability enables the failover of a node within one distributed SAP HANA appliance. Failover uses a cold Standby node and gets triggered automatically. So when a Active Server X fails, Standby Server N+1 reads indexes from the shared storage and connects to logical connection of failed server X.
• If the SAP HANA system detects a failover situation, the work of the services on the failed server is reassigned to the services running on the standby host. The failed volume and all the included tables are reassigned and loaded into memory in accordance with the failover strategy defined for the system. This reassignment can be performed without moving any data, because all the persistency of the servers is stored on a shared disk. Data and logs are stored on shared storage, where every server has access to the same disks.
• The Master Name Server detects an Index Server failure and executes the failover. During the failover the Master Name Server assigns the volume of the failed Index Server to the cold Standby Server. In case of a Master Name Server failure, another of the remaining Name Servers will become Active Master.
• Before a failover is performed, the system waits for a few seconds to determine whether the service can be restarted. Standby node can take over the role of a failing master or failing slave node

SAP HANA - An Introduction for the beginners

SAP HANA: High-Performance Analytic Appliance (HANA)is an In-Memory Database from SAP to store data and analyze large volumes of non aggregated transactional data in Real-time with unprecedented performance ideal for decision support & predictive analysis.

The In-Memory Computing Engine is a next generation innovation that uses cache-conscious data-structures and algorithms leveraging hardware innovation as well as SAP software technology innovations. It is ideal for Real-time OLTP and OLAP in one appliance i.e. E-2-E solution from Transactional to high performance Analytics. SAP HANA can also be used as a secondary database to accelerate analytics on existing applications.

Hardware Innovations - Leading to HANA

In real world we have so many variety of data sources, e.g. Unstructured Data, Operational Data Stores, Data Marts, Data Warehouses, Online Analytical Stores, etc. To do analytics or information mining from this Big Data at real time we come across the hurdles like Latency, High Cost and Complexity.
Disk I/O was the Performance bottleneck in the past, whereas in memory computing was always much faster than that. Earlier, however, the cost of in-memory computing was prohibitive for any large scale implementation. Now with Multi-Core CPU and high capacity of RAM, we can host the entire database in memory. So now CPU is waiting for data to be loaded from main memory into CPU cache - and that's what is the Performance bottleneck today.
This is a total paradigm shift; Tape is Dead, Disk is Tape, Main Memory is Disk & CPU Cache is Main Memory. HANA is optimized to exploit the parallel processing capabilities of modern multi-core/CPU architectures. With this architecture, SAP applications can benefit from current hardware technologies.

Memory Overview - Where we stand

Let us have a quick look on Multi-Core CPU Caches, Main Memory i.e. RAM & traditional Hard Disk with respect to response time.

• L1 cache - Primary & within core. SRAM - Fastest. L1 cache | ~ 1ns | 64k
• L2 cache – Intermediate & within core. DRAM - Slower. L2 cache | ~ 5ns | 256k
• L3 Cache – Shared across all cores. DRAM - Slowest. L3 cache | ~ 20ns | 8M
• Main Memory | ~ 100ns | TBs
• Hard Disk | > 1.000.000ns | TBs

HANA Hardware Requirement

HANA can be installed on many certified SAP hardware partners: Hewlett Packard, IBM, Fujitsu Computers, CISCO systems, DELL.

Currently SUSE Linux Enterprise Server x86-64 (SLES) 11 SP1 is the Operating System supported by SAP HANA.

A typical example of CPU and RAM can be 4 Intel E7-4870 / 40 cores and 512 GB RAM. SAP recommends a dedicated server network communication of 10 GBit/s between the SAP HANA landscape and the source system for efficient data replication.

HANA Database Features

Important database features of HANA include OLTP & OLAP capabilities, Extreme Performance, In-Memory , Massively Parallel Processing, Hybrid Database, Column Store, Row Store, Complex Event Processing, Calculation Engine, Compression, Virtual Views, Partitioning and No aggregates. HANA In-Memory Architecture includes the In-Memory Computing Engine and In-Memory Computing Studio for modeling and administration. All the properties need a detailed explanation followed by the SAP HANA Architecture.

Basic Concepts behind HANA Database

Extreme Hardware Innovations:

Main memory is no-longer a limited resource, modern servers can have 2TB of system memory and this allows complete databases to be held in RAM. Currently processors have up to 64 cores, and 128 cores will soon be available. With the increasing number of cores, CPUs are able to process increased data per time interval. This shifts the performance bottleneck from disk I/O to the data transfer between main memory and CPU cache.

In-Memory Database:

HANA fully leverages the hardware innovations like Multi-Core CPU, High capacity RAM availability. The basic concept is to cache the entire database into fast accessible Main Memory close to CPU for faster execution and to avoid disk I/O. Disk storage is still required for permanent persistency since Main Memory is volatile. SAP HANA, holds the bulk of its data in memory for maximum performance, but still uses persistent storage to provide a fallback in case of failure. Data and log are automatically saved to disk at regular save points, the log is also saved to disk after each COMMIT of a database transaction. Disk write operations happens asynchronously and as a background task. Generally on system start-up HANA loads the tables into memory.

Massively Parallel Processing:

With availability of Multi-Core CPUs, higher CPU execution speeds can be achieved. Multiple CPUs call for new parallel algorithms to be used in databases in order to fully utilize the computing resources available. HANA Column-based storage makes it easy to execute operations in parallel using multiple processor cores. In a column store data is already vertically partitioned. This means that operations on different columns can easily be processed in parallel. If multiple columns need to be searched or aggregated, each of these operations can be assigned to a different processor core. In addition operations on one column can be parallelized by partitioning the column into multiple sections that can be processed by different processor cores. With the SAP HANA database, queries can be executed rapidly and in parallel.

Hybrid Data Store:

Common databases store tabular data row-wise, i.e. all data for a record are stored adjacent to each other in memory. Row store tables are linked list of memory pages. Conceptually, a database table is a two-dimensional data structure with cells organized in rows and columns. Computer memory however is organized as a linear structure. To store a table in linear memory, two options exist:

• A row-oriented storage stores a table as a sequence of records, each of which contain the fields of one row.
• A column-oriented storage stores all the values of a column in contiguous memory locations.

Use of column store will help to prevent table scan of unnecessary columns while performing searching and aggregation operations on single column values stored in contiguous memory locations. Such an oper-ation has high spatial locality and can efficiently be executed in the CPU cache. With row-oriented storage, the same operation would be much slower because data of the same column is distributed across memory and the CPU is slowed down by cache misses. Column store is optimized for high performance of read operation and efficient data compression. This combination of both classical and innovative technologies of data storage and access allows the developer to choose the best technology for their application and, where necessary, use both in parallel.

OLTP and OLAP Database:

HANA is a hybrid database, having both read optimised column store ideally suited for OLAP and write optimised row store best for OLTP systems relational engines. Both the stores are In-Memory. Using column stores in OLTP applications requires a balanced approach to insertion and indexing of column data to minimize cache misses. The SAP HANA database allows the developer to specify whether a table is to be stored column-wise or row-wise. It is also possible to alter an existing table from columnar to row-based and vice versa.

Higher Data Compression:

The goal of keeping all relevant data in main memory can be achieved with less cost if data compression is used. Columnar data storage allows highly efficient compression. If a column is sorted, there will normally be several contiguous values placed adjacent to each other in memory. In this case compression methods, such as run-length encoding, cluster coding or dictionary coding can be used. In column stores a compression factor of 10 can typically be achieved compared to traditional row-oriented storage systems.

Building the Next Generation ETL data loading Framework

Do you wish for an ETL framework that is highly customizable, light-weight and suits perfectly with all of your data loading needs? We too! Let's build one together...

What is an ETL framework?

ETL Or "Extraction, Transformation and Loading" is the prevalent technological paradigm for data integration. While ETL in itself is not a tool, ETL processes can be implemented though varied tools and programming methods. This includes, but not limited to, tools like Informatica PowerCentre, DataStage, BusinessObjects Data Services (BODS), SQL Server Integration Services (SSIS), AbInitio etc. and programming methods like PL/SQL (Oracle), T-SQL (Microsoft), UNIX shell scripting etc. Most of these tools and programming methodologies use a generic setup that controls, monitors, executes and Logs the data flow through out the ETL process. This generic 'setup' is often referred as 'ETL framework'
As an example of an ETL framework, let's consider this. "Harry" needs to load 2 tables everyday from one source system to some other target system. For this purpose, Harry has created 2 SQL jobs, each of which reads data from source through some "SELECT" statements and write the data in the target database using some "INSERT" statements. But in order to run these jobs, Harry needs couple of more information - e.g.

• when is a good time to execute these jobs? Can he schedule these jobs to run automatically everyday?
• Where is the source system located? (Connection information)
• What will happen if one of the jobs fail while loading the data? Will Harry get an alert message? Can he simply rerun the jobs after fixing the issue of the failure?
• How will Harry know if at all any data is retrieved or loaded to the target?

Turns out that, Harry needs something more. He needs some kind of setup that will govern the job execution regularly. This includes - scheduling the jobs, executing the jobs, logging any failure/error information (and also alerting Harry about such failures), maintaining the connection information and even ensuring that Harry does not end up loading the duplicate data.
Such a setup is called "ETL Framework". And we are trying to build the perfect one here.

Critical Features of an ETL framework

In a very broad sense, here are a few of the features that we feel critical in any ETL framework

• Support for Change Data Capture Or Delta Loading Or Incremental Loading
• Metadata logging
• Handling of multiple source formats
• Restartability support
• Notification support
• Highly configurable / customizable

Good-to-have features of ETL Framework

These are some good-to-have features for the framework

• Inbuilt data reconciliation
• Customizable log format
• Dry-load enabling
• Multiple notification formats

Request for Proposal for the next-gen ETL framework

Based on the feature sets above, we are trying to build a generic framework that we would make available here for free for everyone's use.

However the list of features above are not complete. We are requesting our readership to send us RFP for the proposed ETL framework that would resolve the incapability / issues in their existing frameworks.