Transforming Information into Intelligence

of 22
All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.
Related Documents
Transforming Information into Intelligence Management Management Gramm-Leach-Bliley Act IMLA-FATA OFAC SEC Rule 17a-4 USA Patriot Act DoD UK PRO MoReq I Correspondence
Transforming Information into Intelligence Management Management Gramm-Leach-Bliley Act IMLA-FATA OFAC SEC Rule 17a-4 USA Patriot Act DoD UK PRO MoReq I Correspondence Tracking Section 508 HIPAA PIPEDA E-Sign Act GPEA Claims Processing Sarbanes-Oxley Contract Management Correspondence gement Records Management Management Gramm-Leach-Bliley Act IMLA-FATA OFAC SEC Rule 17a-4 USA Patriot Act DoD UK PRO ance Correspondence Tracking Section 508 HIPAA PIPEDA E-Sign Act GPEA Claims Processing Sarbanes-Oxley Contract Management Correspondence Maximizing Database Investments Optimizing NCR Teradata Databases and Teradata Active Data Warehouses with Hummingbird Business Intelligence and Data Integration Solutions While every attempt has been made to ensure the accuracy and completeness of the information in this document, some typographical or technical errors may exist. Hummingbird cannot accept responsibility for customers losses resulting from the use of this document. The information contained in this document is subject to change without notice. This document contains proprietary information that is protected by copyright. This document, in whole or in part, may not be photocopied, reproduced, or translated into another language without prior written consent from Hummingbird. This edition published August Table of Contents Introduction...5 What is Hummingbird Genio?...5 What is Hummingbird BI?...6 What is Teradata?...7 Primary Benefi ts of Hummingbird Genio...8 Hub & Spoke Architecture With Decentralized Process Execution...9 Traditional engine-based Genio with staging Traditional engine-based Genio with in-memory streaming Leveraging source and target DBMSs to perform some transformations Loading directly from source DBMS into Teradata Loading Data from Flat Files on the Teradata Server into the Teradata DBMS Summary of Transformation Process Modes Metadata Management Flexible Development Environment Reusability Tracking Changes Impact Analysis Procedural Scripting Language Hummingbird Genio & Teradata Implementation & Optimization Strategies Connectivity to Teradata SQL Pass-Through Method Native SQL Grammar and Data Type Support Teradata Load & Unload Utilities...17 Loaders Fastload, MultiLoad, and TPump with or without Data Streaming...17 FastExport Table of Contents Enhancing the SQL BTEQ Capabilities Hummingbird Genio Support for Teradata Active Data Warehousing Confi guring ODBC Drivers for Best Performance Enhancing Data Processing Performance Table Load Operation Table Append Operation Table Update and Delete Operations Table Upsert Operation Reading High Volumes of Teradata RDBMS Data into Hummingbird Genio Rapidly Loading Data Inside Teradata Loading & Replenishing Teradata Warehouses from ERP & CRM systems Support for Non-relational Legacy Data Primary Benefi ts of Hummingbird BI...25 Simple Interface for Ad Hoc Querying Ready Made Reports with Wizards BI Server Scalable Enterprise Architecture Easy-to-Deploy, Scalable Web Solution Centralized Security and Administration Advanced Distribution and Scheduling Exception-Triggered Events Enterprise Enabled Enhanced Integration Between Hummingbird BI & NCR Teradata...28 Connectivity to Teradata Native Connectivity through CLI Connectivity through ODBC Estimating Teradata Resources when Querying Customizing the SQL APPENDIX I Connections Supported by Hummingbird Genio...30 APPENDIX II Glossary of Teradata Terminology...31 APPENDIX III Additional Resources Introduction The objective of this paper is to show how organizations can maximize the return on their investments in NCR Teradata warehouses by leveraging Hummingbird Business Intelligence and Data Integration solutions. The data stored in data warehouses represents great competitive advantage to the organizations that know how to leverage it for business intelligence. Business intelligence and data integration solutions address this issue, encompassing critical solutions, including data warehousing, data exchange, and query and reporting, to empower knowledge workers for better informed business decisions. Hummingbird offers solutions for optimized loading and replenishing of Teradata databases as well as a fully integrated, scalable solution for web and PC-based query, reporting, and OLAP functionality. Hummingbird Integration Suite and Hummingbird BI deliver an end-to-end solution that maximizes the value of the structured data stored in Teradata. Hummingbird s solutions deliver the right information, to the right people, at the right time in a quick and easy way. What is Hummingbird Genio? Hummingbird Genio (the data integration component of Hummingbird Integration Suite) is a powerful data exchange solution that spans the functional areas of data extraction, transformation, and loading (Genio) and enterprise application integration (EAI). As a foundational component of IT infrastructures, Hummingbird Genio provides accurate, consistent and timely information that connects any data source to any target system throughout the enterprise. It provides an innovative method for building and executing both simple and complex data transformations via a unique graphical scripting environment. As an information broker, Hummingbird Genio represents a new generation of data integration solution that transforms, enriches, and directs information across the entire spectrum of decision support systems and corporate applications. Hummingbird Genio provides IT professionals with unequaled control, flexibility and efficiency through its unparalleled modular architecture, superior transformation capabilities, complete change management, and data quality controls. The Hummingbird Genio architecture is neutral, non-obtrusive and compatible with any organization s infrastructure. The hub-and-spoke architecture of Hummingbird Genio addresses simple and complex data exchange processes within a proceduredriven graphical environment. Hummingbird Genio automates many data exchange tasks that normally require tedious programming, allowing IT professionals to rapidly develop data transformation routines for an immediate return on investment. Like Teradata, the highly productive environment of Hummingbird Genio allows the data management process to be effectively managed by a much smaller team of DBAs (Database Administrators) than more traditional approaches. 5 Hummingbird Genio is best suited for medium to large data warehouse projects and has a number of features that make it particularly effective in a Teradata environment to take advantage of Teradata s Massively Parallel Processing (MPP) Architecture. These include: 1. Ability to offload transformations to the source DBMS and/or Teradata target. 2. Support for native Teradata high-performance bulk loaders. 3. Optimized functionality to support Teradata s Active Data Warehouse strategy. 4. Hummingbird Genio is not a Cobol code-generator. Transformations developed in Hummingbird Genio are translated to native SQL. 5. Text is accessed natively, not through ODBC. This results in vastly higher performance and flexibility. 6. Hummingbird Genio has excellent support for extracting data from non-relational legacy systems, as well as from SAP R/3. Hummingbird Genio provides support for the native bulk loaders (FastLoad, MultiLoad and TPump) supplied by Teradata and can stream data directly into the loaders through memory to avoid staging to disk. Bulk loaders handle the significant increase in data storage requirements of the past few years, streamlining the database load process. The intuitive Load Wizard available with Hummingbird Genio facilitates the implementation of the bulk loaders and provides users with complete flexibility to configure Teradata external bulk loaders. Users can define mappings between text files and Teradata tables, set options regarding the use of the loader, generate control files, and create Hummingbird Genio objects to use the control files. What is Hummingbird BI? Hummingbird BI is a powerful business intelligence solution that enables users to access, analyze, and report on enterprise information faster. Hummingbird BI is a fully integrated and scalable solution that provides enterprise-strength query, reporting and OLAP functionality for desktop and Web-based users. Hummingbird BI allows for query and reporting on information stored in transactional databases, data marts, data warehouses, and enterprise resource planning (ERP) systems. Featuring a highly intuitive interface and ease of administration, Hummingbird BI provides centralized administration for all users from a single server, utilizing the same content, security, and user profiles. It even allows mobile knowledge workers using Palm OS-driven devices to access reports from virtually anywhere. Hummingbird BI enables users to better understand their business by providing rapid, dynamic, and accurate access to business information. With native connectivity to Teradata databases, centralized administration, and a central data repository for both desktop and web-based users, Hummingbird BI is the best solution to deploy business intelligence functionality to the widest audience. Once the Teradata warehouse environment has been established, key decision makers and knowledge workers are free to run simple to complex queries, generate high-resolution reports, conduct real-time analytical processing, and publish their findings to authorized users. 6 Hummingbird BI is comprised of four components. BI Web is a thin client solution that provides query, reporting, and OLAP capabilities over the Web. BI Query handles enterprise query and reporting on the desktop. BI Analyze is a desktop OLAP application for multidimensional analysis of corporate data. Finally, BI Server, an enterprise application server, provides security, scheduling, distribution, notification, and centralized administration services for all of Hummingbird BI. What is Teradata? Teradata is a highly scalable parallel database, offering 100 per cent linear scalability that supports thousands of tables, and billions of rows. Teradata is very easy to manage, does not require a large number of DBAs, and therefore offers a very low total cost of ownership. The Teradata database is targeted at companies in retail, banking, telecommunications, airlines, transportation, insurance, manufacturing, energy, and e-business industries that have very large volumes of data and large numbers of users requiring access to databases ranging in size from 10 Gigabytes (GB) to greater than 100 Terabytes (TB). Teradata provides organizations with the foundation for a data warehouse solution that accesses data from the database, and makes it available to all in the organization that require it. Teradata provides data warehousing that supports both traditional strategic decision-making, and tactical decision-making that requires frequent, if not near-continuous, online updating of the data warehouse. The latter is supported through the Teradata Active Warehouse strategy. The NCR MPP platform is designed around the shared nothing model, which is useful for growing systems. When hardware components such as disk or memory are shared system-wide, there is an extra tax paid, an overhead to manage and coordinate contention for these components. This overhead can place a limit on scalable performance when growth occurs or stress is placed on the system. Because a shared nothing configuration is able to minimize or eliminate the interference and overhead of resource sharing, the balance of disk, interconnect traffic, memory power and processor strength can be maintained. Because the Teradata database is designed around a shared nothing model as well, the software is able to scale linearly with the hardware. By having no single point of failure, Teradata ensures the high availability required by companies operating in an Internet-enabled, e-business environment, where organizations must be able to access customer details quickly, in order to support call center operations, or web-based services. It deploys a Redundant Array of Independent Disks (RAID) architecture, as well as other fault resistant measures, so that if a node fails, other nodes take over the workload. Load balancing capabilities ensure that the workload is distributed evenly throughout the system, and there is dynamic failure detection and recovery. 7 Primary Benifi ts of Hummingbird Genio Hub-and-Spoke Architecture The thrust of universal data integration is obviously to address the need for a solution capable of handling the many good reasons for moving data throughout the modern business enterprise. Hummingbird Genio is designed with this premise in mind and is built around the cornerstones of speed, efficiency, and ease of use. This is apparent from examining the product s fundamental architecture the Hub and Spoke. For data warehousing projects, a central engine, or information broker, serves as the hub of the solution. Its role in the solution is to automate and manage the flow of data all extraction, transformation, and loading processes. The hub can be thought of as a traffic controller of sorts, controlling the movement of data from disparate sources and ensuring its safe, reliable arrival at the data warehouse destination. Moreover, the engine serves to transform raw source data into valuable information to be used by knowledge workers, decision makers, and other decision support system users. Hummingbird Genio architecture is based on an extensible, component-based hub-and-spoke design. The centralized engine and metadata repository comprise the hub; the data sources and targets, between which the hub exchanges data, are the spokes. Unlike other hub-and-spoke Genio products, Hummingbird Genio can optimize data management processes by leveraging the local database capabilities and even bypass the hub altogether. This is critical in a large-scale Teradata data warehouse where the DBMS engine is significantly more powerful than can be expected from a hub server, and where the amount of data involved makes it prohibitive to always pass it through a central hub. As opposed to point-to-point data transfer architectures, the hub-and-spoke design of Hummingbird Genio connects source and target systems to a central hub. This greatly reduces the number of required nodes in the data exchange network and also greatly increases overall performance. Additionally, the hub-and-spoke architecture allows for greater flexibility, scalability, and reliability as hubs can be mirrored to provide fault tolerance and system availability. The Genio Repository can be hosted on a wide range of databases and acts as a central storage area for source and target metadata, source and target interface mappings, business rules, transformation rules, data validation rules, scheduling, and other information about the data exchange process. Hummingbird Genio sources can be midrange systems, mainframes, Windows NT-based servers, ERP and CRM systems, or even proprietary file systems. The benefit of a hub-and-spoke architecture with a centralized and open repository is that organizations can maintain full control of all data exchange processes, business rules, and metadata that make up any and all projects within the enterprise. Since the business logic is stored in a centrally managed repository for the entire Genio process, a developer can be sure that once a change is made, it is propagated throughout the entire system. This enhances environment management and empowers knowledge workers to make better, more efficient use of business intelligence and analytic applications. 8 The alternative to the hub-and-spoke-based data exchange solution is for organizations to develop separate hard-coded point-to-point interfaces using COBOL or SQL or shell scripts that patch together systems for data integration or extraction, transformation, and loading processes for data warehousing. Maintaining and modifying these band-aid solutions to meet changing organizational requirements becomes unwieldy. The hub-and-spoke-based solution ensures efficient application integration by providing reliable delivery of business messages in required formats while simultaneously managing the extract, transformation, and load processes demanded by data warehousing projects. With Decentralized Process Execution In order to avoid any potential bottlenecks at the hub, Hummingbird Genio is able to leverage the database engine to execute processes. In addition, Hummingbird Genio is able to leverage the use of Teradata s high-performance loading utilities (FastLoad, MultiLoad, and TPump) on the source, on an intermediate server where Hummingbird Genio resides, or on the Teradata target or any other DBMS server. When the Genio Engine is chosen for data processing, then Hummingbird Genio can stream the data directly into the Teradata loaders, eliminating the need to stage the data, a common performance bottleneck. The support for TPump, data streaming, and its flexibility in providing various forms of asynchronous data transfers, make Hummingbird Genio an ideal tool for a Teradata Active Warehouse. The range of options available with Hummingbird Genio provides users with great flexibility and enhanced performance, making it very competitive with other solutions on the market, most of which can execute only on the intermediate server and which usually require the data to be staged to an intermediate flat file. Not only can processes be executed on any of the servers (NT or UNIX where the transformation engine resides, or the source or target relational databases), but a native multi-threaded architecture allows the tasks carried out by Hummingbird Genio to be scaled over multiple physical servers for a hybrid solution. The following paragraphs describe the different models that can be used to execute transformation processes. 1. Traditional engine-based Genio with staging 2. Traditional engine-based Genio with in-memory streaming 3. Leveraging source and target DBMSs to perform some transformations 4. ELT Leveraging the Teradata DBMS to perform transformations 5. Loading directly from source DBMS into Teradata 6. Loading data from flat files on the Teradata server into the Teradata DBMS 9 1. Traditional engine-based Genio with staging In a traditional engine-based Genio tool, data is read from one or more data sources and brought to the server on which the Genio engine is running. The data is then transformed in the Genio engine. If the Teradata loader utilities are not supported then the data is transmitted one row at a time to Teradata via ODBC or WinCLI. If the Teradata loader utilities are supported (and this is the only feasible way to load Teradata!) then the data is staged to a disk-based flat file. Then the appropriate loader (usually FastLoad or MultiLoad) is invoked which reads data from the staging files and loads it into Teradata. Transformation Staged Data FastLoad MultiLoad TPump Source DB Hummingbird Genio Engine Teradata DB Figure 1 Traditional engine-based ETL with staging Hummingbird Genio can be used in this way; however, in subsequent sections we will see that in a Teradata environment there are a number of approaches one can take with Hummingbird Genio to significantly improve performance. 2. Traditional engine-based Genio with in-memory streaming Traditional Genio requires staging the data to disk on the server where the Genio engine is running. This can result in very high I/O overhead, require a large amount of disk space, and complicate the logistics since one must ensure that such disk space is available. To optimize performance and avoid these problems using traditional Genio, Hummingbird Genio passes the data directly to the Teradata loaders through memory, a process called streaming. Streaming is supported for all Teradata loaders FastLoad, MultiLo
We Need Your Support
Thank you for visiting our website and your interest in our free products and services. We are nonprofit website to share and download documents. To the running of this website, we need your help to support us.

Thanks to everyone for your continued support.

No, Thanks