Advanced ETL Processor
Contents
1. SQL SELECT FROM C 1_presentation templates film ats Connector location C Program Files x86 Common Files QlikTech Custom Data Copyright 2012 DB Software Laboratory Page 13 of 17 ETL Tools QlikView Connector User Manual 6 Support Procedure We welcome your suggestions and comments to make our software better Please do contact us through email or visit our website For General Sales and License Queries Email to sales etl tools com For Technical Help Support Forum http www etl tools com etl forum html Email To support etl tools com Online Contact Form http www dbsoftlab com index php Contact Us While submitting a bug or problem please include the following to make it easier to solve the problem as soon as possible e All the symptoms of a Problem e Exact steps required to reproduce it e Version number e Operation system version e Database version e Log files e Script e Sql script to create tables e Screenshots We intend to resolve 90 per cent of the reported problems within 48 hours Most difficult issues or functionality extension are resolved within one week Copyright 2012 DB Software Laboratory Page 14 of 17 ETL Tools QlikView Connector User Manual 7 License Agreement ETL Tools QlikView Connector by DB Software Laboratory www etl tools com info etl tools com END USER LICENSE AGREEMENT FOR THIS SOFTWARE IMPORTANT READ CAREFULLY This End User License Agree2. SBETL tools com a PROE Efiicigney Petrormance oo ty Efficiency Performa QlikView Connector User manual DB Software Laboratory 2008 2012 ETL Tools QlikView Connector User Manual www etl tools com Contents MOUS ITS oi usec ices EE ase goeqaeed E E E ET 2 1 Introd ction seeiis eiere onien a EE E EAA goa ceasuanadda A ia a tiea 4 P rtormance sre Sug en dic cts a ase eg aa gs ep dang EEE E N aioe 4 Complex datai rer rt grasa cates Seely tose naan a a E sae as E n T ONG map ian Sead pans aaee ene aan 5 ZL UEACHOM ea KOLO ATA EEE vase aco E E ea nas gta ess E EE AE 6 2 4 1 Multiple Data Formats yi jsisivsssiaisiscstsassedelaasvdedecedenngiadsacvsd cacaadasea on ai e asiat 6 2 1 2 Multiple Databases and Table Processing 2 2 2 0sacaid deedeed dd itniaeins 6 2 1 3 Other Database Fatre Soses iiornecye i es a ee a e a e EE 6 2 2 Summary of the Extraction Process sssseseeseessesseesessresressetsesreesresesrrssresseseresresseseresees 6 2 3 Validation Processes iernii iasa S E EA ETE 7 2 4 Summary of Validation Processes isa cic5 ielacd cede Senda eed eden eoneeeee lees 7 Ded Transformation PTOCESS asscitsvcg sey ova n a a a E E A a 7 2 6 Powerful Data Transformation seseseesesseesseessessseeesseessseesseessersseeeseeessseessresseesseessseesssets 7 2 7 Summary of Transformation Processes cscccesscecssececsseeecseececseceeceececseeecseeeeseeeeaees 8 2 9 Loading Processen or entered id vies cai e A E aE
3. EE adele aed eats 8 3 ReguiremieniS o rhei e e a aaa E EE EE aN 9 4 Working with ETL Tools QlikView Connector 0s soesessseesseseesssessessessresseeseseesseeseese 11 5 Running QlikView load scripts eseesseeeeesesssessesseseresresstssresresstsstestesseestesteeseesteserssreseeseessreses 13 Connector location sssini n a ei aia AE a u Asiei oat TA Eaa siani iat 13 GY SUPPOTL Proced ra Taane N E a E E A ER 14 T License Agreement rre eer ates e E a E E E EE E EE R 15 Copyright 2012 DB Software Laboratory Page 2 of 17 ETL Tools QlikView Connector User Manual Copyright Copyright 2012 DB Software Laboratory Limited All rights reserved No portion of this document may be reproduced transmitted transcribed stored in a retrieval system or translated into any language in any form or by any means without prior written consent of DB Software Laboratory Limited Note to U S Government users Documentation and programs related to restricted rights use duplication or disclosure is subject to restrictions set forth in GSA FMSS Schedule Contract No GSOOK92AFS2505 PS05 License Information You have access to ETL Tools QlikView Connector software and documentation pursuant to the terms of a Software License Agreement granted by DB Software Laboratory Limited As a user of this software and documentation you are bound by the terms of the Software License Agreement All rights title and interest to this software
4. the parties agree that such invalidity shall not affect the validity of the remaining portions of this license COPYRIGHT The SOFTWARE PRODUCT is protected by copyright laws and international treaty provisions All title and copyrights related to the SOFTWARE PRODUCT including but not limited to any images photographs animations video audio music text and applets incorporated into the SOFTWARE PRODUCT the accompanying printed materials and any copies of the SOFTWARE PRODUCT are owned by DB Software Laboratory MISCELLANEOUS Should you have any questions concerning this EULA or if you desire to contact the author of this Software for any reason please contact DB Software Laboratory see contact information at the top of this EULA Copyright 2012 DB Software Laboratory Page 16 of 17 ETL Tools QlikView Connector User Manual LIMITED WARRANTY DB Software Laboratory expressly disclaims any warranty for the SOFTWARE PRODUCT The SOFTWARE PRODUCT and any related documentation is provided as is without warranty of any kind either express or implied including without limitation the implied warranties or merchantability fitness for a particular purpose or no infringement The entire risk arising out of use or performance of the SOFTWARE PRODUCT remains with you In no event shall DB Software Laboratory be liable for any damages whatsoever or refund any money including without limitation damages for loss of busine
5. 1 8629 1 or Separate download Only to work with driver higher DBF FoxPro Files SQL ODBC driver 2000 81 9041 40 Part of OS on W2K Only to work with XP Vista MS SQL Server 7 2008 Oracle Client 7 3 4 or higher Provided by Oracle Only to work with Oracle Databases Interbase client GDS32 DLL Only to work with Interbase or Firebird Databases Separate Downloads FoxPro ODBC driver http msdn microsoft com en us vfoxpro bb190233 aspx Office 2007 Data Access Components http www microsoft com downloads details aspx FamilyID 7554F536 8C28 4598 9B72 EF94E038C891 amp displaylang en Qlik View version 10 and above Copyright 2012 DB Software Laboratory Page 9 of 17 ETL Tools QlikView Connector User Manual Working with Oracle Oracle client 8 1 7 to load data into from Oracle oe client 9 to load data into from Oracle an client 10 to load data into from Oracle ee client 11 to load data into from Oracle Note Depending on the Requirements you may or may not need to have all components installed There is no need to install clients for MySql and PostgreSQL they are integrated into the software itself Copyright 2012 DB Software Laboratory Page 10 of 17 ETL Tools QlikView Connector User Manual 4 Working with ETL Tools QlikView Connector ETL Tools QlikView Connector provides the user interface for designing data transformations Edit selected transformation Cr
6. 15 of 17 ETL Tools QlikView Connector User Manual LICENSING There are two types of licenses available 1 A single computer usage license The user purchases one license to use the SOFTWARE PRODUCT on one computer 2 A SITE usage license The user purchases a single usage license authorising the use of SOFTWARE PRODUCT by the purchaser the purchaser s employees or accredited agents on an unlimited number of computers at the same physical site location This site location would normally be defined as a single building but could be considered to be a number of buildings within the same general geographical location such as an industrial estate or small town OTHER RIGHTS AND LIMITATIONS You may not reverse engineer decompile or disassemble the SOFTWARE PRODUCT except and only to the extent that such activity is expressly permitted by applicable law notwithstanding this limitation Any such unauthorized use shall result in immediate and automatic termination of this license and may result in criminal and or civil prosecution All rights not expressly granted here are reserved by DB Software Laboratory The SOFTWARE PRODUCT is licensed as a single product Its component parts may not be separated for use on more than one computer You may permanently transfer all of your rights under this EULA provided the recipient agrees to the terms of this EULA SEVERABILITY In the event of invalidity of any provision of this license
7. TL Tools QlikView Connector The beauty of the system is that it saves the drudgery and manual tasks normally required for tasks of this type such as the writing of code and all the transformations validations and general checks normally performed Using traditional methods operations of this type can only be performed in stages and not as one smooth operation For example the traditional method of importing data into QlikView is to write import script to extract data from the source database e g an Oracle database or some text files However the operation does not end there Any data imported has to be sorted duplicated validated transformed and loaded into QlikView This is only possible by creating code designed to achieve this process As you can see the process is not straightforward The ETL Tools QlikView Connector automates all these processes in a simple and transparent fashion and all without writing any code whatsoever As stated the tool to handle any kind of database including Oracle Microsoft Access SQL Server DB2 MySQL Excel spread sheets and a wide range of others It is an excellent tool for those organisations who work with data warehouses and where this involves working with a number of disparate databases Performance Fb Reader 3 Transformer Sf 1 writer 3146500 3146500 0 Processing Data Source DBSLCOMPUTER SOL2008 SELECT SalesOrderHeader SalesOrderID SalesOrderHeader RevisionNumber S
8. ale Started 27 03 2012 10 25 03 Time 00 03 44 Time Left 00 00 00 Rec Sec 14025 14 Records 3146500 It took little less than four minutes to generate 3 million records Copyright 2012 DB Software Laboratory Page 4 of 17 ETL Tools QlikView Connector User Manual Complex data QlikView can load data from almost anything all the user need to do is to write some code But what is your data look like this H 123456 ID CUST123 All Interactive 4 FALSE 654321 1 TRUE D James Camelon 12 B Gates St St francisco QLD 4000 US r007 A W NH I xXCVv1 5 I CVD2 2 SSSI DCP1 1 6 I HJTHM 3 Or like that Report ZMRZR125 CUSTOMER NAM Paqe E Stock Status Plant 0211 Sorted by Material Number Vendor 06876 1007357 REDUCER SPEED FALK 1060FCB3A20 50 1 6714 EA EA 1 1 L 12 E RATIO 20 50 1 RIGHT ANGLE 90DEG TRIPLE 1 O P 12 Item CC cs 1026229 PLUG TUBING STL P H 8PNTX S 760 EA EA 16 2 L 12 C HOSE CAGE 1 2 X 13 16 TUBE 37DEG FLARE 16 12 P 12 The ETL Tools QlikView Connector is the only connector which can handle this kind of data Copyright 2012 DB Software Laboratory Page 5 of 17 ETL Tools QlikView Connector User Manual 2 Key features The ability of the ETL Tools QlikView Connector to work with a number of disparate systems means that it is provided with a rich set of tools and functionality It is in effect an engineering environment for the movement of data into Qli
9. ale and F for female e Deriving a new calculated value e g sale amount qty unit price e Joining together data from multiple fields e Summarizing multiple rows of data e g total sales for each store and for each region e Generating surrogate key values e Transposing or pivoting turning multiple columns into multiple rows or vice versa e Splitting a column into multiple columns e g putting a comma separated list specified as a string in one column as individual values in different columns e Customised Transformation e Data generation e Running Totals e Powerful scripting language e System variables 2 8 Loading Process The data is loaded directly into QlikView or it can be saved for later use as QVX files Since it is using QlikView native format the loading is extremely fast Copyright 2012 DB Software Laboratory Page 8 of 17 ETL Tools QlikView Connector User Manual 3 Requirements Below is the list of Software that must be installed before installation of ETL Tools QlikView Connector Software Version Notes Microsoft Windows 98 or higher MDAC 2 6 or higher Part of OS on W2K XP Vista MS Access ODBC driver 4 00 6364 00 or higher Part of OS on W2K XP Vista Only to work with MS Access 95 2003 Databases MS Access 2007 12 00 4518 1014 or Separate download Only to work with MS ODBC driver higher Access 2007 Databases FoxPro ODBC 6
10. d grouping of data according to requirements Data can also be duplicated as required 2 6 Powerful Data Transformation In addition to the standard data transformation abilities the processor can also perform complex translation functions An example would be if an integer variable 1 then set a text variable to yes Once data is translated it is possible to join data into a completely new format or present it in a new way The ETL Tools QlikView Connector provides many flexible alternatives for data manipulation and these are not difficult to take advantage of The ETL Tools QlikView Connector also provides the ability to derive calculated values join data together from multiple fields summarise multiple rows at once or can split or merge columns at will The flexibility and power of the processor means that you can customize data transformation and conversion functions according to your requirements with a click of the mouse This saves hours and hours of coding Copyright 2012 DB Software Laboratory Page 7 of 17 ETL Tools QlikView Connector User Manual 2 7 Summary of Transformation Processes Transformation e 86 Transformation Functions e String Transformation e Number Transformation e Date Transformation e Sorting e Grouping e Deduplication e Field Selector e Union All e Translating coded values e g if the source system stores 1 for male and 2 for female but the warehouse stores M for m
11. eate new transformation Delete transformation ycremental Search from the list C 1_presentation T emplates Customers ats C 1_presentation T emplates inventory ats Generate QlkVi ew C 1_presentation T emplates Rental ats S cript WW Generated by ETL Tools QlikView Connector a 03 Customers 4 LOAD SQL SELECT 16 FROM C i_presentation Templates Customers ats 0008 Film 0003 LOAD 90109 SQL SELECT 00 1 FROM C i_presentation Templates Film ats C p r Generated QlikView Script Copyright 2012 DB Software Laboratory Page 11 of 17 ETL Tools QlikView Connector User Manual 1 To open existing transformation double click on the name Main Tool bar Available Objects Field Width Reader Toolbar Field No Reader Fields Reader data 2 To design new click plus Note When saving transformation we recommend using filenames without spaces Designing transformations For more information on how to design data transformations please read Advanced ETL Processor user manual Copyright 2012 DB Software Laboratory Page 12 of 17 ETL Tools QlikView Connector User Manual 5 Running QlikView load scripts To run load script copy generated script into QlikView CUSTOM CONNECT TO Provider AETLP QlikView_Connector exe XUserId FXPZaHB XPassword POLVJLA Customers LOAD SQL SELECT FROM C 1_presentation templates customers ats Film LOAD
12. kView We will now explain and define the variety of features which are provided as part of the toolset Let us first have a look at the data extraction process 2 1 Extraction Process 2 1 1 Multiple Data Formats The extraction process can handle a variety of data formats including multiple delimited or fixed width text files The power of this system however is in its ability to find files to load using mask 2 1 2 Multiple Databases and Table Processing It can easily interpret and manipulate Microsoft Access data from a number of different databases Again the end user can use a mask to find the tables to load the data from It does also apply to Excel and DBF FoxPro files 2 1 3 Other Database Features The ETL Tools QlikView Connector also has other useful database features such as the ability to connect to any Object Database Connectivity ODBC database ODBC was intended to enable developers access to any data through any application regardless of the DBMS used for managing that data ODBC boasts platform independence since it has been purposefully designed in a way that makes it distinct from database systems programming languages and operating systems The ETL Tools QlikView Connector utilises this technology to great effect 2 2 Summary of the Extraction Process e Multiple Delimited or Fixed width Text files e Multiple Excel files Multiple Excel Spreadsheets e Multiple MS Access Databases Multiple Tables e Mu
13. ltiple DBF Files e Any ODBC compliant database e Full support for OLE DB e Direct connection to Oracle SQL Server MySql PostgreSQL SQLite SQL Server Compact and Interbase Firebird e Multiple Tables e File System e POP3 Servers e XML File e QOQVX files Copyright 2012 DB Software Laboratory Page 6 of 17 ETL Tools QlikView Connector User Manual 2 3 Validation Process The ETL Tools QlikView Connector has a robust validation process built in The types and nature of the validations taking place can be tweaked and configured by the user A full range of validation functions are included Validations can be performed on the basis of data type lists of values and regular expressions which can be individually changed according to requirements 2 4 Summary of Validation Processes Validation e 190 Validation Functions e String Validation e Number Validation e Date Validation e Time Validation e Validation Against list of values e Regular Expressions Post codes Phone Numbers VAT Numbers ETC 2 5 Transformation Process The process of transformation involves the manipulation of data so that it can be converted from one format or data type to another The ETL Tools QlikView Connector provides a useful set of built in transformation functions Transformations can be performed on strings numbers dates coded values and whole chunks of data The transformation functionality allows for the sorting an
14. ment EULA is a legal agreement between you either an individual or a single entity and DB Software Laboratory for the SOFTWARE PRODUCT identified above which includes computer software and may include associated media printed materials and online or electronic documentation By installing copying or otherwise using the SOFTWARE PRODUCT you agree to be bound by the terms of this EULA If you do not agree to the terms of this EULA you may be subject to civil liability if you install and use this SOFTWARE PRODUCT SOFTWARE PRODUCT LICENSE The SOFTWARE PRODUCT is protected by copyright laws and international copyright treaties as well as other intellectual property laws and treaties The SOFTWARE PRODUCT is licensed not sold INSTALLATION AND USAGE Once SOFTWARE PRODUCT is installed you may use it for 30 days After evaluation period ends you have to purchase a license or stop using the SOFTWARE PRODUCT If this is an EVALUATION VERSION of the SOFTWARE PRODUCT you may copy and distribute an unlimited number of copies of the SOFTWARE PRODUCT provided that each copy shall be a true and complete copy including all copyright and trademark notices and shall be accompanied by a copy of this EULA If this is a REGISTERED VERSION of the SOFTWARE PRODUCT you may install and use it for your personal use only You may not reproduce or distribute the SOFTWARE PRODUCT for use by others Copyright 2012 DB Software Laboratory Page
15. remain with DB Software Laboratory Limited Requests for copies of this publication and for technical information about DB Software Laboratory products should be made directly to DB Software Laboratory Limited Disclaimer All information in this manual is subject to periodic change and revision without notice While every effort has been made to ensure that this manual is accurate DB Software Laboratory Limited excludes its liability for errors or inaccuracies if any contained herein Registered Marks Any products or services mentioned or depicted in this document are identified by the trademarks or service marks of their respective companies or organisations Edition Information This document refers to ETL Tools QlikView Connector version 2 0 0 1 Copyright 2012 DB Software Laboratory Page 3 of 17 ETL Tools QlikView Connector User Manual 1 Introduction QlikView connector is a type of software designed for loading data into QlikView dashboards There are several connectors available to the QlikView users today All they do is bring the data into QlikView But they do not do anything with the data The ETL Tools QlikView Connector is the only QlikView connector which can perform complex data transformation and validation while loading data into QlikView The technology behind ETL Tools QlikView Connector is based on Advanced ETL Processor Any user who worked Advanced ETL Processor will feel very comfortable using E
16. ss profits business interruption loss of business information or any other pecuniary loss arising out of the use of or inability to use this product even if DB Software Laboratory has been advised of the possibility of such damages Because some states jurisdictions do not allow the exclusion or limitation of liability for consequential or incidental damages the above limitation may not apply to you SUPPORT AND MAINTENACE The annual maintenance fee is 20 percent of initial software license cost Users with a fully paid annual maintenance fee get the following benefits Priority Support Free software enhancements updates and upgrades during the maintenance period Advanced and exclusive notification of software promotions Maintenance Owner ONLY product promotions ENTIRE AGREEMENT This is the entire agreement between you and DB Software Laboratory which supersedes any prior agreement or understanding whether written or oral relating to the subject matter of this license Thank you for using the ETL Tools QlikView Connector DB Software Laboratory Copyright 2012 DB Software Laboratory Page 17 of 17
Download Pdf Manuals
Related Search