Home

Elixir Data Designer User Manual

Contents

1. Previous Next Finish Cancel Enter a unique name for the Reference DataSource in the Name text box Select the DataSource for which the parameter values need to be set The parameter names will be extracted and automatically displayed You then need to enter the corresponding value for each key After entering the values and clicking the Finish button the Reference DataSource is added to the repository 102 Reference DataSource Working with Reference DataSource The different ways of using a Reference DataSource are given below Wrapping an Excel DataSource In this illustration an Excel DataSource has been added in which a parameter has been specified for the range and the values are expected to be entered during the loading of the Excel file Here s how to add a Reference DataSource and use it to supply values automatically to the parameterized Excel data source The sample file Empdata xls consists of employee details in two worksheets as shown in Figure 8 2 Empdata xls Figure 8 2 Empdata xls A B C D 1 Emp_ld Emp_Name Dept Name Emp_Sal em 1 Gerry Electronics 7400 302 Micheal Electrical 8500 4 3 Frank Computer 9000 5 4 Lawrence Mechanical 6500 6 5 Jenifer Sales 6500 7 6 Christopher Electronics 8000 8 7 Flora Computer 8600 9 8 Cathy Electrical 7500 10 9 Peter Sales 7000 11 10 Kendy Mechanical 5900 12 mM 4 gt mi Sheet1 Sheet2 1 Before adding an Excel
2. Previous Finish Cancel There are four columns in the sort table Name Sort Order Group On and Group Data Field The columns will be sorted based on the values assigned to these columns On clicking the Add button a Sort dialog pops up as shown in Figure 4 12 Add Sort Item The following options are available in the Sort dialog box Figure 4 12 Add Sort Item Name Sort Order Group On Derived Field Inthe Name Combo box the field name of the data source based on which the sorting has to be done is selected e The Sorting order as none ascending or descending has to selected from the Sort Order combo box There are two new sort orders simple ascending and descending sort These uses a more simplistic algorithm is to compare unicode character values literally The benefit of this is that it doesn t ignore spaces e The options available in the Group On combo box is based on the data type of the selected field The Group On options are discussed below e Finally on clicking the OK button the Sort options specified in the Sort Wizard are added in the corresponding columns On clicking the Next button the Extract screen appears as shown in Figure 4 13 Extract Options The main part of this screen is the Expression Builder 30 Composite DataSources Figure 4 13 Extract Options LF Sort Wizard X Extract Set extract
3. 6 Select the same location and use the popup menu to select Add gt DataSource 7 The DataSource Wizard appears Select Properties DataSource and click on the Next button 8 Enter a unique name such as Prop_Emp 9 Click the Add Column button to invoke the Add Column dialog window Enter column name as Dept_Name same column name as in the text data source Data type as String and the value as Computer After entering the values the Add Column dialog window appears 10 On clicking the Ok button the column is added to the Wizard The screen appears as shown in Figure 7 4 Properties DataSource 96 Properties DataSource 11 12 13 14 15 16 17 Figure 7 4 Properties DataSource Datasource Wizard Define Properties Datasource Enter Properties datasource parameters Name Description Prop_Emp D Column Name Value 1 Dept_Name Computer Previous Cancel On clicking the Finish button the Prop_Emp data Source is added to the repository Select Properties Samp use the popup menu to add a Composite DataSource called Comp1 On selecting and double clicking on the Comp1 ds data source the composite designer window opens Select and place the properties data source Prop_Emp on the diagram Select the derivative processor from the Action bar of the designer window and place it on the diagram
4. grocery Double Showing 25 records C Count A frozen Double meat Double 19 Canada BC Vancouver 16417 9737 4016 41575 267 7 61050 Canada BC Victoria 27 463 3539 4193 18761 2795 45840 Mexico Yucatan Merida 307970 20141 2029 6393 47822 4262 31881 Mexico Guerrero Acapulco 23593 0 17474 9897 3670 80615 2447 20410 Mexico Zacatecas Camacho 23759 0 16844 4408 4148 73549 2765 82366 Mexico Jalisco Guadalajara 24597 0 15011 5121 5751 292689 3834 19512 Mexico zacatecas Hidalgo 30584 0 21938 0009 5187 59945 3458 39963 Mexico DF San Andres Mexico DF Mexico City 36509 0 22450 0263 8435 38421 5623 58947 Mexico Veracruz Orizaba 34791 0 26354 0175 5062 18949 3374 79299 Mexico Zacatecas Hidalgo 38382 0 30350 7148 4818 77107 3212 61405 USA OR Salem 27694 0 18669 7842 5414 52944 3609 68629 USA WA Bellingham 28206 0 22271 1505 3560 90965 2373 93977 USA WA Bremerton 39696 0 24389 9213 9183 64716 6122 43144 USA CA Beverly Hills 23688 0 15336 7531 5010 74809 3340 49873 USA CA Los Angeles 23598 0
5. 0c cece cece cece cece cece cece cen eeeeeeeeeeeeeaeeeaesages 134 Dynamic Parameters with a Nested DataSource ocoooccnnconoconoconoconocnnccnnronocnnccnnconninnno 135 Example Declaration of Dynamic Parameters cee cece eee eeeeceeece secu ceca seen eeneeees 136 Bi Samples mesilla ordre a de ueeawest eee ire toawr Seats me Mawes tenes 138 Availability csc feast so sheet ves ai ni stage meses bbs T EA 138 List of Figures Lz Set Security Options sort peralta ron E e EE 2 2 1 Basic Data Workspace co reno iii EEA OE EE ge eed oe beta aye de 3 2 2 Composite Data Workspace 3 55 csscesscesccssSscttseasssoieegsedtecussousee vashecteedssecessseeeteensssbsossasveoss 4 2 33 NEW DataSQUICE rerin thine iobeds EER EEA vars e 5 2 4 Define Text DataSource Page One 20 00 0000 cee ceee a erena i eaa E e E RES 5 2 5 Define Text DataSource Page TWO ooocooccnnccnnconoconconoconncnncnnnronncnnccnnconnccnnccnncnnnconacnnnss 6 3 1 Create New DataSource ciscscsissoesis O 8 3 2 Select Database 9 3 3 JDBE DataSource Wizard joss csechedes satis savbertissesattenes pis Sande dass Peet TSS 9 3 4 Define JDBC DataSource ic 2255 soii eoe r E EE EE TEOT EEEE EEEE OEE EES 11 3 5 Elixir Query Builder sriain vo deadtsanes shdvaad seaside EE EPEa PESES ESTOP EEI SSOR sects eds ese 11 3 6 Callable Tab ici eben EERE E cook sd EEE EEE E EENES bat EEEE EEEE 12 3 7 Define DataSource Schema ps sssri Dorian a E UA teas
6. 0 cece cece ence ne ce ence eeeeeeeaeeeaeeea eens eeneeegs 38 4 20 Completed Sort Dialog avoir oes cease ceva ben bee ches eett ages betwen ERA DO Vash naan oases rt yea peed 39 421 Derivative WIZARD verter nn seb ADA Mert eres 39 4 22 Add Column Dialog A riere reiia cused sesbetdasiotacsacsoteedbesdseudsseesesdaesstee desserts 40 4 23 Sample Derivative Flo Wi cooocosic siponcic oie cds a eene Mescgestieectes iaa 41 4 24 Completed Add Column Dialog 0 0 0 0 0c cece cece ence a E eeu eegs 41 4 23 Derived Res l ii A A A Sec cam Sa ia 42 4 26 Completed Add Column Screens cccscscccscendsseccasciscosesesseevaadienvieesseeoesnapeessesssseveesdses 43 4 27 Date Manipulations Result sse eiiean en eire AKEE EE EEE CEE EEEE EEEa 44 428 Filter Wizard m cesriren ni a A A E EE TE T A RE Paes 45 4 29 Sample Filter FIOW 222 sscccec seevs cogs basis enii ene er EE EEEE EEEE EEE EE Ee 48 4 30 Filer Result oni annaa ea Sink ead Sense votes Sos E N es Pup dente E SA P EAEE NRE Sa 48 Ad Conca Wizardsin rnes kei ee rect estes E E TEE EA R 49 4 32 Sample Concat FOW pociones E T E E eE ad 50 4 33 Parameter Wizard e e esate A A A A E E 51 4 34 Completed Properties DataSource oooocccoccnnccnnconoconoconncononononnnconoronccnnconnccnnccnncnnncnnniss 52 4 35 Sample Parameter FlOW 25 5 4 S500 iones te onde nbs EEES KEES EEES cusepeuedeeboneeess 52 4 36 Parameter Result anadir pele dire ss rated nie A E E ct TEE 53 4 37 SuDFlOW Samp
7. 119 LDAP DataSource Figure 10 4 LDAP Datasource Wizard Define LDAP Datasource Enter LDAP datasource parameters Name Organization Description Host MyHostName Distinguished Name ou people de example de com Scope One Level Filter Port Timeout ms Batch Size Figure 10 5 Completed Datasource Wizard Define LDAP Datasource View and map schema for LDAP Datasource SLOT preferredDeliveryMethod telexNumber op sing ay ceo gt 8 searchGuide_ String 9 postaiCode_ String 10 businessCategory String 11 facsimieTelephoneNumber Sing inatonallgNNumber lrn previous neo inn concer 120 LDAP DataSource Figure 10 6 Organization Result Resut A amp 2 Showing 36 records C Count All Records description streetAddres String String String Builtin account for administering the computerfdomain ESSE SEE A ee eee workstations and servers joined to the domain ll domain users Designated administrators ofthe enterprise Members in this group can modify group policy for the domain Built in account for guest access to the computer domain Group for the Help and Support Center IIS Worker Process Group uilt in account for anonymous access to Internet Information Services Built in account for Internet Information Services to start out of process applications a a Oy l Key Distribution Center Service Account A a Members ca
8. store_id Integer Country String State String City String store Double grocery Double frozen Double COPANO On amp co ho ae meat Double Previous Next Finish Cancel 39 Composite DataSources There are three tabs in the Derivative properties Base Derived and JavaScript The Base tab will display all the fields present in the data source connected to the Derivative processor input In the Derived tab there are 4 columns Column Name Type and Value which contain input data required for deriving the new column On clicking the Add button the dialog box pops up as shown in Figure 4 22 Add Column Dialog The following options are available Figure 4 22 Add Column Dialog Add Column Unknown store_id Country State Available City Fields store grocery frozen meat Cancel 1 The name of the new column must be entered in the Name text box 2 The data type of the derived column will be selected from the combo box 3 The formula that will be used to derive the column or the JavaScript function name used to perform computations will be entered in the Value text box On clicking the Ok button in the Add Column dialog box the derivative column is added to the Derivative Wizard I
9. 14210 3780 5632 57318 3755 04878 USA CA Alameda USA OR Portland 20319 0 16231 7456 2452 35258 1634 901 72 USA CA San Diego USA CA USA WA San Francisco Spokane USA WA d Group On Substring Tacoma Yakima Walla Walla 22478 0 30268 0 33858 0 21215 0 15320 9505 22062 8673 22123 3454 13305 4630 4294 22967 4923 07959 2862 81978 3282 05306 7040 79275 4745 72215 4693 86183 3163 81477 1 Using the same Composite configuration as the previous illustration open the Sort properties 2 Select the City from the list of field names available in the combo box 3 Select the sort order as Descending from the Combo box 4 Select the Substring as the Group On option Enter the value as 0 2 in the text box Click the Ok button and Finish and then view the result The records are grouped based on the first 2 characters of the City field and sorted in descending order So the Cities with names starting with Ya are grouped followed by names starting with Wa and so on e Group On Year 1 Instead of the Sales data source specified in the sort procedure given above drag and drop the Employee data source and connect it to the Sort processor Then open the Sort Properties 2 Inthe first screen of the Sort Wizard click the Add button The Sort dialog box a
10. Data Output and Loading The different data store types available in Elixir Data Designer include XML File JDBC Excel File and CSV File In addition through the Custom Java DataStore user defined stores can be supported Datasource Security At the last page of the DataSource Wizard the user can set security options as seen in Figure 1 1 Set Security Options Figure 1 1 Set Security Options DataSource Wizard Security Set security options _ Read Only _ Hide Details _ Encrypted Previous Ne Finish Cancel e Read Only When selected and saved the next time a user opens this datasource the user will not be able to edit any details of the datasource like name description and column names e Hide Details When this option is selected and saved the next time when this datasource is opened the user will only be able to see the name and description of the datasource Encrypted This option is to be used with either Read Only option or Hide Details option or both Checking the checkbox will prompt the user to enter a password then re enter to confirm the password Both passwords must be the same After this is done if another user would like to edit any selections he will need to enter the password Chapter 2 The Elixir Data Designer Workspace Overview The Elixir Repertoire application consists of three parts the Elixir Repository Action Bar and Workspace These are discus
11. 3 15 3 36 3 F M 3 15 4 26 3 F M 3 15 4 46 3 F M 3 15 48 3 F M 3 15 4 96 3 F M 3 15 5 32 3 F M 3 15 5 56 3 F M 3 15 5 76 3 F M 3 15 6 39 3 F M 3 15 6 81 3 F M 3 15 6 84 3 F M 3 15 7 35 3 F M 3 15 18 88 3 F M 3 15 8 91 3 F M 3 15 9 33 3 F M 5 14 1 08 5 F S 6 15 5 44 6 F S This query fetches all primary records which have one or more matching secondary records The primary record is repeated once for each secondary match If there is no secondary match the primary record is discarded b If multiple matching secondaries Keep This query fetches all primary records which have one or more matching secondary records The primary record is only output once merged with the first secondary match Subsequent secondary matches are ignored If there is no secondary match the primary record is discarded c If multiple matching secondaries Discard This query fetches all primary records which have exactly one matching secondary record The primary record is output once merged with the secondary match If there is no secondary match or multiple secondary matches the primary record is discarded Outer Join Outer join is a type of join in which both matching and non matching rows are returned The values of all columns from the unmatched table in non matching rows are set to NULL Using an Outer join on the Sales and Customer data sources you can explore the result of setting different options for the If multip
12. CompanyName The default selection will be company B Further the datasource Fruit can be either e The name of a Graphic Object within a Composite Diagram Data Designer e The name of a Data Source within a Report Report Designer e The name of a View using view syntax Dashboard Designer e The name of a datasource in the Repository All Also further parameters can be passed in if the datasource requires parameters Company Name lookup Fruit CompanyName User Jon Password XXX B Ordering Dynamic Parameters By default parameters are presented in alphabetical order This means if you have parameters defined for Name and Address then the Address will appear first Often we want to control the order 134 Dynamic Parameters of presentation it is more common to request Name before Address To achieve this we declare a dependency between the parameters If the name of the parameter includes a string embedded in angle brackets lt string gt then this indicates that the parameter is dependent on that named parameter and should be shown after it To ensure Address appears after Name we can therefore define Name Address lt Name gt this declares Address depends on Name and should therefore be shown after it Note the text in angle brackets is not shown on the UI If we need to give type and or default values we include the separator as usual Address lt Name gt choice Sing
13. P s USA CA Los Angeles 23598 0 14210 378025591175 5632 5731846452945 3755 048789763 none ysa Ca alameda or ce pe 11 USA OR Portland 20319 o 16231 745689288167 2452 3525864270996 1634 90172428473 24 USA CA San Di ego ee ee 14 USA CAN San AE E babe Q 15320 950534854783 4294 2296790871305 2862 81978 16 USA wa Spokane 30268 0 22062 86733556811 4923 079598659134 3282 0530657727563 17 USA WA Tacoma 33858 0 22123 34541128549 7040 792753228707 4693 8618354858045 33 i usa i wa yakima ts tile Pn bo e lepsa wa walla Wallats t n n e 15 USA wA seattle 21215 0 13305 463072916533 4745 72215625008 3163 8147708333872 E mexico 3 yucatan Merida 30797 0 20141 20296341721 6393 478221949674 4262 31881463 Mexico Guerrero Acapulco 23593 0 17474 989748573556 3670 8061508558662 2447 204 4u Mexico Zacatecas Camacho 23759 0 16844 440836110287 4148 735498333827 2765 823 izh Mexico zacatecas s Hidalgo 24597 0 15011 512183016413 5751 292690190152 3834 1 12 Mexico Zacatecas Hida go 30384 0 21938 00090630884 5187 599456214696 3458 399 21 mexico DE san andres AE nde Mexicon prr mexico City 36509 0 22450 026302559232 8435 38421846446
14. QVELVIEW aria ni tc dr ces venus EE EE OEN E ANES 102 Reference DataSource Wizard ieies mesedera E e E E OA EA 102 Working with Reference DataSource oocoooccconoccnnnccnnnccnnnnccnncononnconnnncnnnccnnnnccnnarcnnneos 103 Wrapping an Excel DataSource ocooccocccnconoconnconnconnconnconncnnncnnncnnronaronccnnccnninnno 103 Wrapping a Composite DataSource 0 00 cece cece cee cee ce eeceeeeeeeeaeeea seen sean sean eees 106 9 Object DataSOurCe ita e Ge aah sae Nas ass 108 OVERVIEW cialis doler yieela aa tae eave obs A ade a IAE ds ir dacdenb aden stuns 108 The Class pata enne a iaa bebas 108 Object DataSource Wizard esep esnaera to Utrera aereas densas 108 Guided JavaScript Bulder cui oo a debe bs 109 Manual Creation O 110 Object DataSource APE cuida pE S A a dba e deals 112 POSO Els E E 112 D tabistener nemisni A Pes ane fae eee EE AR 112 DataGroups mr db lia dano riada 113 DataRecord ii io 113 Working with Object DataSources coooccoccnocnnocnnccnnccnnconnccnnconnconncnnncnnncnnnrnnrcnaronicnnos 113 10 CDAP DataSource iii rd is 116 OVERVIEW 655 cnn d orth cageus ion inner ae nine ir anterior a f rita iria artis iio 116 LDAP DataSource Wizard oooocoocccocnnconocrnnoroncnnnronnronnrrnnonnne rn r ono rnnr nro E EE AEN 116 Working with LDAP DataSource 20 00 0000 eee eee cee cee ceeeceeecaeeea seca eeae eeu eeneeenneennees 119 11 Filesyst m DataS ource ica a I ea en 122 QUEL VIEW oeaan fargo eer ees il tn cidad 122 F
15. URL is automatically entered in the URL Text box The parameters in the URL text box can be altered according to your JDBC vendor s requirements b INDI Context Factory The context factory accepts information about how to create a context such as a reference and returns the instance of the context Provider URL Provides the URL of the resource to bind to Resource Name This identifies the resource name that binds to the data source c Pool Connection Pool The connection pool from the Repository that will provide the connection to the database See the section called Connection Pools later in this chapter for more details Timeout The amount of time in milliseconds that the DataSource should wait for a connection to become available A value of 0 means wait forever Resource Name This identifies the resource name that binds to the data source User The user name is entered if required Password The password is entered if required When the data source parameters have been entered click Next to see the screen as shown in Figure 3 4 Define JDBC DataSource In this window there are two tabbed panes namely the SQL tab and the Callable tab An SQL Statement can be entered in the SQL editor Stored procedures can be defined in the Callable tab The Query Builder button can also be used to build a query using visual tools JDBC DataSource Figure 3 4 Define JDBC DataSource Datasource Wizard Define
16. and click the Finish button Select the DataStore and select Generate from the popup menu The XML file will be generated and saved in the specified location MySQL Before generating the MySQL JDBC DataStore the MySQL driver file must be copied to the Elixir Repertoire ext folder The tool must be launched once the jar is in place as the jar is only loaded at startup Note if using Elixir Repertoire Remote then the ext folder is on the server Invoke the DataStore Wizard and set the following properties e Select JDBC from the Type combo box Click the Next button e Select MySQL in the suggestions combo box e On selecting the Driver Suggestion the Driver class name and the URL are automatically displayed in the corresponding text boxes The URL is entered as jdbc mysql ocalhost 3306 test Where test is the dbname localhost can be replaced by the IP address of MySQL server if it isn t running on the same machine and 3306 is the port number e Enter DataOutput as the table name Select the DataStore and choose Generate from the popup menu The MySQL JDBC DataStore is generated and saved in the specified location 74 Composite DataSources Oracle Before generating the Oracle JDBC DataStore the Oracle driver file must be copied to the ext folder on the client for the Designer or on the server for the Remote This ensures the Oracle driver is loaded into the class path when the tool is launched Invoke the Dat
17. type Double gt lt ds column name meat type Double gt lt ds schema gt lt ds data gt lt ds record gt lt ds field gt 13 lt ds field gt lt ds field gt USA lt ds field gt lt ds field gt OR lt ds field gt lt ds field gt Salem lt ds field gt lt ds field gt 27694 0 lt ds field gt lt ds field gt 18669 784255237577 lt ds field gt lt ds field gt 5414 529446857454 lt ds field gt lt ds field gt 3609 6862979049693 lt ds field gt lt ds record gt lt ds record gt 4 My Computer Note If you need to send some sample data to Elixir generating a Tabular DataSource using the XMLDataStore is ideal because the data source file has no dependency on your database repository or file system configuration Custom Java DataStore The Custom Java DataStore can be used to write data into any Java API whether it be a proprietory protocol or even a mail or JMS queue Actually the built in DataStore types are pre defined instances of the Java DataStore We can test this by using the CSV datastore as an example Open the DataStore Wizard and select Custom Java DataStore then click Next The Custom Java DataStore screen appears Enter the class name as com elixirtech data2 output CS VDataStore The URL append qualifier and separator parameters appear in the table below The CSVDataStore is written using standard JavaBean naming conventions so the available accessors can be extracted
18. will be available as the output For large datasets a huge number of records could be generated There are two options Keep and Discard provided in the Combo box for If no matching secondary If the keep option is selected then an Outer join can be performed on the data sources If the Discard option is selected then an Inner join can be performed on the data sources Similarly three options Repeat Keep and Discard are provided for If multiple matching second aries These control how multiple matches are handled If the primary record matches three secondary records the system can a pass through three records matching the single primary with each of the secondaries in turn Repeat b just pass through the primary with just the first secondary Keep c not pass through the record at all Discard or The Primary prefix and the Secondary prefix can be entered in the text boxes if required The Primary prefix and Secondary prefix are used to modify the field names in the merged output record based on the primary and secondary names in case they conflict For example an Employee DataSource with an Id and Departmentld would conflict with a Department DataSource with its own Id If both Ids need to be passed through then one or both of them should be given a prefix to prevent duplicate names The fields present in the primary data source are displayed in the Primary tab when the data source is connected with the Join processor The fie
19. DataSource Wizard Figure 7 1 Properties DataSource Wizard T Datasource Wizard Define Properties Datasource f Enter Properties datasource parameters Name required Name Description D Column Name Type Value Previous Next Finish Cancel The name of the DataSource is entered in the Name text box Any extra description that is used to describe the data source can be entered in the Description text box There are five buttons They are Add Column Edit Column Move Up Move Down and Remove Column buttons The Add Column button is used to add a new column On clicking the Add Column button the Add Column dialog window pops up as shown in Figure 7 2 Add Column The column name should be entered in the text box The Data type should be selected from the combo box The value is entered in the Value text box On clicking the Ok button the column is added to the Wizard 94 Properties DataSource Figure 7 2 Add Column EF Add Column Name Data Type Unknown v Value Cancel The Edit Column Move Up Move Down and the Remove Column buttons are used to edit reorder or delete columns Working with Properties DataSources The Properties DataSource can be used directly or indirectly through a parameter processor The examples given below illustrates the direct use of Properties DataSo
20. Je customer_id store_id store_sales 10277 11 4 76 10277 11 4 8 10277 11 28 10277 1 6 28 0277 1 8 84 0275 3 1 86 0275 3 1 92 0275 3 4 36 0275 3 5 04 0275 3 5 46 10275 13 9 66 10275 13 15 72 10274 6 2 88 10274 6 2 92 10274 6 3 4 10274 6 4 8 10274 6 5 64 10274 6 5 73 String data 1 Instead of the Sales data source specified in the sort procedure given above drag and drop the Stores data source and connect it to the Sort processor As usual the Sort processor output is connected to Result 2 Open the Sort properties and in the first screen click the Add button The Sort dialog box appears 3 Select the Country from the list of field names available in the Combo box 4 Select the Sort Order as Ascending from the Combo box 5 Select the Range as the Group on option Enter the value given below in the Range text box USA a Mexico b Canada c The output is shown in Figure 4 19 Group on Range by Country Result It is seen that the records are grouped into a b and c based on the values of the Country in ascending order Where a group includes all the records having Country name as USA b group includes records with Country name Mexico and c group records with Country name Canada 37 Composite DataSources Figure 4 19 Group on Range by Country Result Designer Script Data store_id Country integer String State String City String
21. Remember the location of the jar file as we will need to add it to the class path in the Object DataSource 2 Create a new Object DataSource called Ob ject 1 and enter the classpath for ObjectDB jar 3 Click the Next button and choose the Guided JavaScript Builder On the builder select com dummy test Company from the tree as the Data Iterator provider class The methods associated with this class are listed in the table on the right Select getEmployeesAsList method from the list of available methods The method and the corresponding Iterator type is displayed in the text fields After selecting the method and Iterator type the screen appears as shown in Figure 9 4 Sample Data Iterator 113 Object DataSource Figure 9 4 Sample Data Iterator JavaScript Generator Select the class and method that will provide the iterator Data Iterator Provider corn dummy test Company Method Iterator Type a erator ge Oy jay Iterator getEmployeesAsList wa util List J util A hashCode0 int void void f ja etEmployeesAsEnumerationg java util Enumeration j Ah nem rms Jem Click on the Next button The classpath element is displayed as a tree view in the window Expand the tree view and select the Employee class file which is the data provider class The selected Data Class name is automatically displayed in the Data Class text box After entering the class details the screen appears as shown
22. TO D tiyatiye Wizard korsen cores ica df a ne E EENE ETA SE REEE ORDRES 98 T6 Test OUtP t aiser Los aN ae E a T E Ue nde E SESS 98 O e NN 99 ES Props EMPLEA A A AS ad sii 99 TI Filtet A ER 100 ESO OUDIT a da RS et Se ee Ree 100 8 1 Reference DataSource Wizards isnon anena a e a e oA E I e N enS 102 8 2 Empdati X S 2d isa e 103 A RR 104 S 4 Excel DataSource ample uv A GR ee ee 104 8 3 Referenced DataSource sun iciayue dose steed sa iria ln yasbe E a EE ob ass 105 9 0Sample OU A A LIE a IAE 105 A sens ooseshssugaesdbseedess RE E E EE EN ENERE 106 8 9 sample References niina A E E E on EE N EE eee EATS 107 vii Elixir Data Designer User Manual 9 1 Object DataSource Wizard s ge dscites S555 ewe ass pbestebedeowausenySevetos EEA EN E ESER Sp Ea 109 9 2 Guided JavaScript Builder ii ii hk beat Be E NESO 110 9 3 JavaScript Edil Reside aes seta e e a tees inte dau eteedy cea tacaseeetuns T eae eaei 111 9 4 Sample Data Hera i 114 93 Sample Data Clas scott stan gvorsermesesbnadeeosy ESE A Seeees peste EEES 114 10 1 LDAP DataSource Wizard eri sd ia 117 102 Security Parameters ceisir hoa geeudase great dee deb taceouedthes doruadenseuetdor aubedenseeeh edn 118 10 3 LDAP DataSource Schema oocooccnccccnncnoconcnoconcnoroncnoroncnoroncnnroncnnroncnnroncnnroncnaroncnanos 119 10 4 EDAP Datasource Wizard i noeneen ro veh acapieca thes naaa HERA gues seen tes penne deed senweoe nies 120 10 5 Completed Datasource Wizafd scsi r
23. always tested left to right and the first one that matches is chosen You can leave a condition off the last range expression to form the default value A First lt E Pass Fail This example will return First for input A Pass for inputs like B C D and Fail for all other inputs Substring When this option is selected from the list then the Start End text box appears In this text box the starting and ending character of the string is specified For instance if the syntax specified in the text box is 0 3 then the grouping will be based on 3 characters starting from the first character of the string indexes are zero based There is also an option to add a derived field that holds the substring values so that subsequent processors can use them without having to derive them again To use this feature just enter a unique name for Derived Field If you inspect the output schema of the Sort you will see the additional field is listed Year When this option is selected and a value is entered in the Group Interval text box the records are grouped according to the Years of the specified field and the group interval Suppose the years in the date field are starting from 2000 2020 and the Year option is selected from the Group On combo box and the value 2 is entered as group interval then the records containing years 2000 2001 2002 2003 2004 2005 etc are grouped together Month When this option is selected and a value
24. continue Figure 3 1 Create New Data Source Create New Data Source Select a driver for which you want to set up a data source Microsoft Access Driver mdb Microsoft Access Treiber mdb Microsoft dBase Driver dbf Microsoft dBase YFP Driver dbf Microsoft dB ase Treiber dbf Microsoft Excel Driver xls Microsoft Excel Treiber xls Microsoft FoxPro YFP Driver dbf Microsoft ODBC for Dracle hai ar Mirine Ae 1 Manoa einen Back Cancel 3 The ODBC Microsoft Access Setup dialog box pops up Here the Data Source name is entered in the text box Next click the Select button and select the path to the database from the dialog box as shown in Figure 3 2 Select Database JDBC DataSource Figure 3 2 Select Database Select Database Database Name Directories OK MyD atabase mdb c datadesigner Cancel MyDatabase mdb Ben E gt DATADESIGNER Help l Read Only T Exclusive List Files of Type Drives Access Databases m y c y Network 4 The selected database should now have been added to the list of existing databases Click the OK button in the ODBC Data Source Administrator dialog to complete this task JDBC DataSource Wizard The JDBC DataSource Wizard allows a new JDBC DataSource to be created The wizard is shown in Figure 3 3 JDBC DataSource Wizard Figure 3 3 JDBC DataSource Wizard DataSource Wizard Define JDBC Datasource Enter JDB
25. directory jdk jre lib ext whereas for the jre it is just jre lib ext Put the JDBC driver in this ext directory before launching the Remote tool This step is only required if you want to use the Query Builder from the Remote tool The JOBC ODBC bridge provided by Sun is already included in the Java distribution and doesn t require any additional configuration JDBC DataSource Infer Schema After entering the SQL or callable statement click the Next button The page appears as shown in Figure 3 7 Define DataSource Schema In this screen the schema can be inferred from the data query Click the Infer Schema button If a connection to the database can be made the inferred fields and their corresponding data types will be listed Figure 3 7 Define DataSource Schema Datasource Wizard Define JDBC Datasource Enter schema or infer from a data query Infer Schema Column Previous Next Finish Cancel Connection Pools Connecting to a relational database can be time consuming especially across a network The job of a connection pool is to maintain a set of connections ready for use You can create a connection pool in the Repository by choosing Add gt Connection Pool from a folder or filesystem popup menu A wizard will appear as shown in Figure 3 8 Connection Pool Wizard The Test Connection button will only be enabled when the driver is being specified and will allow the use
26. distinct records of the table are selected no duplicate records will be retrieved Instead of using the Query Builder to create a SQL statement the SQL statement can be entered directly in the SQL window Elixir Data Designer allows stored procedures to be invoked including passing parameters to the database server subject to database support If you need to use a stored procedure the callable statement syntax has to be entered in the SQL Window Then the Callable Statement checkbox should be selected On selecting the check box the Callable tab is activated In the Callable tab as shown in Figure 3 6 Callable Tab the type of the output parameter must be specified Figure 3 6 Callable Tab Datasource Wizard Define JDBC Datasource Enter SQL or use the Query Builder SQL required Callable _DataType Previous Next Finish Cancel Note If you are using the Remote version of Elixir Repertoire remember that all data queries and operations are performed by the Elixir Repertoire Server If you use localhost in your connection URL localhost will refer to the server not the client Because all data operations are done on the server you don t need a client side version of the JDBC driver To add the JDBC driver to your system class path find the location where Java is installed This may be either the development kit jdk or runtime jre For the dk there is
27. few options to tune the cube process and output Collapse Empty Rows This option will remove any rows from the cube that have no records contibuting to them For example if Singapore Male has no records then only Singapore Female would be shown If empty records are not collapsed the output will be a permutation of non hierarchical row dimensions Hierarchies by definition see strict hierarchies above do not have empty rows Collapse Empty Columns This option will remove any columns from the cube that have no records contibuting to them Note that use of this option will alter the output schema the columns indicate the fields each record will contain Care should be taken when using this option as errors will result if subsequent steps depend on the presence of columns that are removed The only benefit in removing empty columns is to improve presentation For example users of Elixir Report Cube Table can make use of this option to better utilize the available width of the report Keep Row Totals When outputting records to subsequent processors or Result additional records can be inserted to represent the totals and subtotals each level of the tree These additional records are identified by having a null in the corresponding level column Here s an example Country Gender Count employeeid Singapore Female 10 Singapore Male 8 Singapore null 18 US Female 7 US Male 10 US tare IL Ey ASS As you can see the nul
28. instance if the Week of Year is selected as the Group On option and the value of group interval is entered as 3 then the grouping output is 0 2 3 5 etc Where 0 2 corresponds to the first two weeks of the years as 0 corresponds to a null value and 3 5 includes the next three weeks of the year so on If suppose the Week of the Year grouping has to be done for a particular interval of year then the years must be grouped first followed by the week of the year grouping Sunday is always considered as the first day of a week while using Week of Month option Week of Month When this option is selected and a value is entered in the Group Interval text box for a particular field then the records are grouped based on the Week of the month and the group interval For instance if the Week of Month is selected as the Group On option and the value of group interval is entered as 2 then the grouping output is 0 1 2 3 4 5 etc Where 0 1 fetches only the first week of the month since 0 corresponds to a null value and 2 3 groups the second and third week of the month If suppose the Week of the month grouping has to be done for a particular interval of year and month then the years must be grouped first followed by months Finally the week of the month grouping is done Sunday is always considered as the first day of the week while using Week of Month option Day of Year When this option is selected and a value is entered in the Group Interval text box for
29. is on the server Add a FileSystem JDBC Storedl using the procedure given in the previous chapter Alternat ively the data source can be added to an existing FileSystem Now select the folder and choose Add gt DataSource from the popup menu Select the JDBC DataSource type and click the Next button Enter the data source name JDBC_Ca11 Select Oracle Thin_driver as the driver suggestion The driver and URL are assigned automatically The URL can be altered according to the requirements To make use of the stored procedure created in the Oracle server from the client system the IP address of the system has to be specified instead of localhost The user name and password are entered After entering all the details the JDBC data source screen of the DataSource wizard appears as shown in Figure 3 11 JNDI Values 17 JDBC DataSource Figure 3 11 JNDI Values Datasource Wizard Define JDBC Datasource Enter JDBC datasource parameters Context Factory required Name JNDI_call Description JDBC JNDI Pool Context Factory E Provider URL Resource Name User Password Hide Password 9 Click the Next button In the SQL window enter the syntax given below and select the IsCallable check box The Callable procedure tab becomes active call pack selemp Where pack is the package name and selemp is the name of the function 1
30. is specified in the Group Interval text box the records are grouped based on the Month of the specified field and the group interval For instance 1f the Month is selected as the Group On option and the Group Interval is entered as 3 the months are sorted as January March April June etc irrespective of the years If suppose the Month grouping has to be done for a particular group interval of years then the years must be grouped followed by the grouping of months Quarter When this option is selected and a value is entered in the Group Interval text box the records are grouped based on the quarter function of the specified field For instance if the Quarter 1s selected as the Group On option then the records are categorized into four quarters The first quarter is represented as Q01 January March The second quarter is represented as Q11 April June The third quarter is represented as Q21 July September and the fourth quarter is represented as Q31 October December If suppose the quarter grouping has to be done for a particular interval of years then the years must be grouped followed by the quarter grouping 32 Composite DataSources Week of Year When this option is selected and a value is entered in the Group Interval text box for a particular field then the records are grouped based on the number of weeks in a year and the group interval Since there are a total of about 52 weeks in a year the grouping is done accordingly For
31. records 33 Composite DataSources corresponding to first 4 minutes of the hour starting from the Oth minute and 4 7 returns the records having fourth to seventh minute of the hour Second When this option is selected and a value is entered in the Group Interval text box for a particular field then the records are grouped based on the seconds As there are 60 seconds in a minute the grouping is done accordingly For instance if the Second Group On option is selected and the group interval is specified as 0 3 4 7 etc Where 0 3 returns the records corresponding to the first 4 seconds of the minute starting from the Oth second and 4 7 returns the records having fourth to seventh second of the hour Working With Sort processors 1 2 Add the JDBC data source for Sales using the procedure given in the previous chapter Add a Composite DataSource named Sort Now select the Sales data source drag and drop it on the Composite diagram Place a Sort processor on the diagram Connect the Sales data source to the Sort processor and the output of the Sort to the Result The diagram should appear as shown in Figure 4 14 Sample Sort Flow Figure 4 14 Sample Sort Flow Sorting records by applying Sort Order Options Ascending 1 Open the Sort processor properties 2 In the first screen of the Sort Wizard click the Add button The Sort dialog box appears 3 Select store_sales from the list of field names available in th
32. shown in Figure 12 2 Tabular DataSource Wizard Page Two 124 Tabular DataSource Figure 12 2 Tabular DataSource Wizard Page Two ET Datasource Wizard Define Tabular Datasource NA Enter Tabular values D Row Name Address 1 Elixir Singapore ESy x ce a Previous Next Finish Cancel This page allows you to enter records that conform to the schema defined on the previous page In the sample above a single record Elixir Singapore has been added The Tabular DataSource also supports security options such as read only hide details and encryption These are accessed from the third wizard page By applying these options you can protect the contents of the data source from being modified or even viewed outside the tool Note See DataDrop and DataStore for details of how to automate the creation of Tabular DataSources 125 Chapter 13 Random DataSource Overview The Random DataSource is a self contained data source that generates records that conform to a defined structure You can choose the number of records generated and use this data source for testing large volumes of data without pre creating all of the records You can also connect this data source to a data store to generate records into JDBC Excel CSV etc in order to create repeatable test cases for use in optimizing data flows and report rendering Random DataSource Wizard The Random DataSo
33. solution easier to maintain Wrapping a Composite DataSource In this example we will work with a text file containing employee details and filter the records with a Composite wrapped with a Reference Datasource 1 Add a new Text DataSource called Employee and set the URL to reference the sample Empinfo txt Select First line is header option check box and select the Access type as Separator Character Click the next button In the screen that appears enter Qualifier as Select Semicolon option as the Separator Click the Infer Schema button The fields of the text data source are inferred On clicking the Finish button the text data source is added to the repository Now add a Composite DataSource called Compo Drag and drop the Employee ds from the Repository over the Composite diagram Add a Filter processor to the diagram and connect it to the Employee datasource Open the Filter properties and select Filter 1 tab window In the row corresponding to Emp_Sal field select Less Than option from the combo box of When column Enter condition as Salary The screen appears as shown in Figure 8 7 Filter Condition Click the Finish button Figure 8 7 Filter Condition Ny Filter Wizard Filter Choose filter options for items in the schema Name Filter Color Filter 1 Filter 2 Filter 3 JavaScript Column Name Type When Condition 1 Emp
34. to make it easier to determine which flow corresponds to which test Processor i Elixir Data Designer provides a generic processor that allows certain specific data processing tasks to be executed These tasks are designed to be easily customized in different versions of the tools so you may find additional options available The standard processors are Cleansing e Remove Duplicates This task removes duplicate records A duplicate record is determined by a set of key fields being the same The set may contain a single field for example an identity card number or a collection of fields for example name and address The fields are chosen by selecting a checkbox next to the appropriate items in the Test column of the schema table as shown in Figure 4 39 Remove Duplicates Processor 54 Composite DataSources Figure 4 39 Remove Duplicates Processor LF Processor Wizard Remove Duplicates Select the fields that determine duplication L Input Sorted Select All Deselect All Name store_id Country String State String City String store_sqft Long grocery_sqft Long frozen_sqft Long meat_sqft Long OOOO Previous Finish Cancel Whenever subsequent records are identified as being duplicates of those already processed the later records are always discarded This means only the first record with a given set of key fields
35. type field is selected for grouping then the options that are listed in the Group On Combo box are Each Value Substring Range Count and All Date Data 31 Composite DataSources If the Date data type field is selected for grouping then the options that are listed in the Group On combo box are All Count Each Value Year Quarter Month Week of Year Week of Month Day of Year Day of Month Day of Week Range Each Value When this option is selected then consecutive records after sorting with the same selected field value are grouped together All When this option is selected then all the records are grouped together irrespective of the selected field value Count When this option is selected then the records are grouped with a fixed number of records in each group For example 20 records with Count 3 would yield 7 groups The first six groups would contain three records each 18 in total and the last group would only contain two Range When this option is selected the range text field appears The syntax for specifying the range is condition label for example lt x A Where x is the threshold value based on which grouping is done and A is the label name that is assigned for the group A number of ranges can be specified For example KS aN lt 1110918 159 A B and C are the label names assigned for those groups Alternate conditions can be described by using operators gt and Ranges are
36. with field names you can use this 7thProcessor syntax to reference processors with non conformant names Case Study In this case study we will use the Sales Customer and Stores tables of the Mondrian Database in conjunction with a variety of processors and generate the output into different file formats The data manipulations we will illustrate are extraction merging filtering derivation caching and transformation Before we begin you should ensure the Mondrian datasource is configured as described in the section called Using the JOBC ODBC bridge driver Adding the DataSources Launch Elixir Repertoire Choose or create a new file system or folder for this case study and from the popup menu choose Add gt Datasource In the DataSource Wizard that appears select the JDBC DataSource Click the Next button In the Define JDBC DataSource screen enter the DataSource name as Store Select the JIDBC ODBC bridge Sun JVM as the driver specification Enter the URL as jdbc odbc MondrianFoodMart Click the Next button In the SQL window add the following query Selec rrom Score Click the Next button and then click the Infer Schema button Similarly add the Sales data source with SQL Select tron Salles Paci 1997 Again infer the schema Similarly add a Customer data source In the SQL window enter Select mom Customer and again infer the schema Add a Composite DataSource named Case Study Creating a Composite DataSo
37. 0 Select the Callable tab Specify the out type in the text box as given below oracle jdbc driver OracleTypes CURSOR The out type is database dependent In this case a return cursor is specified as given above Some databases do not need a return cursor so the out type need not be specified for them For more details on stored procedures with JDBC refer to this article in JavaWorld The link is http w ww javaworld com javaworld jw 01 2000 jw 01 ssj jdbc p2 html 11 Click the Add button in the Callable tab and enter the Data type and values of the corresponding parameters In this case three input parameters of integer Data type are needed 12 Click the Next button and click Infer Schema to view the schema for the data source Click the Finish button The data source is added to the repository 13 Open the JDBC_Call ds data source Click on the Load Data menu and verify that only the records with columns with the specified input values are fetched Using JNDI Connectivity JNDI the Java Naming and Directory Interface allows applications to access various naming and directory services via a common interface Like JDBC Java Database Connectivity JNDI is not a service but a set of interfaces it allows applications to access many different directory service providers using a standardized API JDBC DataSource JNDI uses the connection pooling technique to connect to JDBC datasources Connection pooling is a technique that c
38. 0235 799999999981 6 569833119383813 5214 830000000006 6 518537500000008 5020 970 19655 420000000002 6 6002081934184025 10522 859999999991 6 639028391167187 9132 560 10710 010000000004 6 7528436317780605 4839 350000000003 6 647458791208796 5870 660 7714 890000000005 6 639320137693636 3739 0399999999995 6 548231173380034 3975 850 384 220000000005 6 628563734290848 3206 530000000004 6 779133192389015 4177 689 15653 189999999997 6 530325406758447 8014 909999999997 6 634859271523176 7638 279 3694 700000000006 6 82472527472528 4753 369999999999 6 949371 345029238 3941 330 16190 950000000017 6 494564781387893 75741 20000000006 6 429643463497459 8616 830 265304 6000000043 6 470451377448978 127343 77000000051 6 471706560959522 127960 8 1590 5999999999997 3 4280172413793095 778 8399999999998 3 401048034934497 811 7600 3394 800000000006 6 539151515151522 2684 2199999999975 6 530948905109483 2710 580 You can choose a few different kinds of output for the records we have processed for this walkthrough we have chosen XML MySQL and Oracle XML Invoke the DataStore Wizard and set the following properties e Select XML file from the Type combo box Click the Next button Enter the URL as file C Output xml choose an appropriate location for your operating system
39. 1_All On clicking the Finish button the data from the first worksheet is displayed in the window If instead of Range Emp1_All the parameter Range or Range is entered i e the default value is not specified Then on clicking the Load Data menu the Dynamic Parameter dialog with a blank text field appears Instead of the range specified in step 16 the parameter is entered as given below in the Range text box S Range password Emp2_Al11 Click the Finish button in the DataSource Wizard 136 Dynamic Parameters 10 11 12 13 Select and double click on Emp Range ds The Data window opens On clicking the Load Data menu in the data window you will see that the password field contains the some text which correspond to the default value Emp2_All On clicking the Finish button the data from the second worksheet is displayed in the window If instead of Range password Emp2_All the parameter Range password is entered i e the default value is not specified Then on clicking the Load Data menu the Dynamic Parameter dialog box with a blank password field appears If instead of the range specified in step 16 the parameter is entered as given below in the Range text box S Range choice Emp1_A11 Emp2_A11 Emp_A11 Click the Finish button Select and double click on Emp Range ds The Data window opens On clicking the Load data menu in the data window you will see a combo box contains three val
40. 2 Select and place the Employee ds data source in the designer window Using the flow processor connect the output of the Employee ds data source to the input of the Derive Dept processor Select Result and choose View from the popup menu The output will be displayed in the data window as shown in Figure 7 7 Full Output It can be seen all the records containing the Computer department are to CPT and the Other departments are mapped to others Thus using the above procedure it becomes easier to check the validity of the derived columns 98 Properties DataSource Figure 7 7 Full Output Designer Script Data Result y OH amp Showing 10 records Emp_Id Emp_Name Dept_Name Emp_Sal Department 1 Gerry Electronics 7400 Others 2 Micheal Electrical 8500 Others 3 Frank Computer 9000 CPT 4 Lawrence Mechanical 6800 Others 5 Jenifer Sales 6500 Others 6 Christopher Electronics 8000 Others 7 Flora Computer 8600 CPT 8 Cathy Electrical 7500 Others 9 Peter Sales 7000 Others 10 Kendy Mechanical 5900 Others Note Therefore if you want to test a flow with specific values you can use a Properties data source to provide them then switch back to the real data source for release Passing Parameters to the Flow Here s how to use a Properties DataSource along with the Parameter processor to pass parameter values for checking a Filter conditio
41. 23 1961 09 23 1958 09 03 1958 04 23 1959 06 14 1962 06 14 1959 05 25 1959 01 14 1968 03 25 1971 03 25 1968 03 05 1967 10 25 1969 06 28 1972 06 28 1969 06 08 1969 01 28 1969 07 24 1972 07 24 1969 07 04 1969 02 24 1970 04 02 1973 04 02 1970 03 13 1969 11 02 1970 05 30 1973 05 30 1970 05 10 1969 12 30 1971 05 21 1974 05 21 1971 05 01 1970 12 21 1974 08 23 1977 08 23 1974 08 03 1974 03 23 1976 09 17 1979 09 17 1976 08 28 1976 04 17 1977 03 27 1980 03 27 1977 03 07 1976 10 27 1977 07 16 1980 07 16 1977 06 26 1977 02 16 1978 09 18 1981 09 18 1978 08 29 1978 04 18 1979 04 13 1979 04 18 1982 04 13 1979 03 24 1979 05 22 1979 05 27 1982 05 22 1979 05 02 1980 02 06 1980 02 11 1983 02 06 1980 01 17 1981 01 03 1981 01 08 1984 01 03 1980 12 14 1978 11 13 1978 12 22 1979 09 06 1980 08 03 1981 08 09 1985 09 07 1994 04 27 1982 01 09 1982 01 14 1985 01 09 1981 12 20 1986 02 07 1986 02 12 1989 02 07 1986 01 18 1994 09 27 1994 10 02 1997 09 27 1994 09 07 Deriving a new column using the Java script function This time we ll calculate the percentage of Frozen area in the stores I After connecting the Stores data source with the Derivative processor using the Derivative procedure given above open the Derivative Wizard properties The fields available in the data source are listed in the Bas
42. 3 0 32 0 550 23 0 B jOrange 323 0 32 0 550 230 _ C Orange 323 0 32 0 43 0 787 0 SubFlow Processor alle The SubFlow processor serves two purposes in Elixir Data Designer Firstly it allows complex flows to be abstracted by defining modular sub flows to aid comprehension of the diagram In this role a SubFlow acts like a subroutine call In addition SubFlow allows a single flow to be split into multiple branches based upon conditions Each sub flow may then perform a different sequence of actions for example storing the records to disk deriving values where data is missing etc In this role a SubFlow acts like a switch statement in a programming language The SubFlow processor is selected from the menu bar of the Designer window and then placed on the diagram The only editable properties of a SubFlow are name and colour Double click on the graphic or choose View Diagram from the popup menu to open the nested diagram You need to join the Input to the Output with one or more flows so that the records can be passed back to the parent flow When acting as a subroutine there is typically a single flow of processors between Input and Output When acting as a switch there may be several flows in parallel The flow in Figure 4 37 SubFlow Sample shows a switch style flow Figure 4 37 SubFlow Sample Country USA save USA DataDrop input save Others DataDrop To switch back to the parent diagram
43. 4 4 WA Bremerton 39696 24389 92 9183 647 6122 431 5 CA Beverly Hil 23688 15336 75 5010 748 3340 499 6 CA Los Angele 23598 14210 38 5632 573 3755 049 TICA Alameda 8 OR Portland 20319 16231 75 2452 353 1634 902 9 ICA San Diego 10 CA San Franc 22478 1532095 4294 23 2862 82 11 WA Spokane 30268 22062 67 4923 08 3282 053 12 WA Tacoma 33856 22123 35 7040 793 4693 862 13 WA Yakima 14 WA Walla Wal 15 WA Seattle 21215 13305 46 4745 722 3163 815 16 Yucatan Merida 30797 201412 6393 478 4262 319 17 Guerrero Acapulco 23593 1747499 3670 806 2447 204 18 Zacatecas Camacho 23759 16044 44 4148 735 2765 624 19 Jalisco Guadalajar 24597 15011 51 5751 293 3834 195 20 Zacatecas Hidalgo 30504 21938 5187599 3458 4 21 DF San Andre 22 DF Mexico Cit 36509 22450 03 8435 304 5623 589 23 Veracruz Orizaba 34791 26354 02 5062 189 3374 793 24 Zacatecas Hidalgo 38382 30350 71 4818 771 3212 514 25 BC Vancouver 23112 16417 97 4016 416 2677 611 26 BC Victoria 34452 27463 35 4193 188 2795 458 Note Different versions of Excel and Excel compatible readers like OpenOffice have different limits for the maximum number of rows allowed in a single sheet Elixir Data Designer limits the rows on one sheet to 31999 JDBC File This datastore writes records to a JDBC database The appropriate driver and database URL need to be entered along with a user name and password if required The Table name may either by a literal table name like Sample or it may be
44. 43 alshamil net j15LRol03599 POS j15LRol03599 POSSIBLE ATTACK fro 15LADIO3650 coll 115LdDIO3650 collect premature EOM 15LADIO3650 coll 115LdDIO3650 collect unexpected clo NOQUEUE adsl 15 NOQUEVE ads 158 17 84 asm b 15LpBI03800 disc 15LpBI03800 discarded NOQUEUE 50106 NOQUEUE 0106080046be0cbce j15M3NI03950 disc j1 5M3NI03950 discarded NOQUEUE 200 11 NOQUEVE 200 115 201 203 did NOQUEUE 72 2 8 NOQUEUE 72 2 85 244 suite224 j15MMdl0 4116 rule j1 5MMdl0 4116 ruleset check_rept ar j15MZ6104184 POS 115MZ6104184 POSSIBLE ATTACK fro NOQUEUE 203 67 NOQUEUE 203 67 89 18 adsl dyn NOQUEUE 143 16 NOQUEVE 143 167 89 200 fiberte i 12104200 lo i 12104 ostinpu annel from ox Cancel On clicking the Infer Schema button the columns are inferred based on the groups in the regular expression 84 Text DataSource 12 13 Click the Finish button The Text_RegExp data source is added to the repository Select and double click on the Text_RegExp ds data source Use the Load Data button to ensure the output is correct We have split the data into two fields but this particular log has two different formats embedded in it The text at the bottom below Unmatched Entries is in a different format Notice that the line Unmatched Entries is not in the ou
45. 5623 58947897 10 Mexico Veracruz orizaba 34791 0 26354 017516087722 5062 189490347367 3374 792 18 Mexico Zacatecas Hidalgo 38382 o 30350 7148717144 4818 771076971359 3212 51405 19 Canada Bc Vancouver s 323112 0 16417 973746533728 4016 4157520797635 2677 6105013 20 canada BCc Victoria 34452 0 3 27463 353980007432 4193 1876119955405 2795 4584079 lt gt Note The Append Data option allows data to be appended to the end of an existing CSV file Obviously the same separator and qualifier options and data schema should be retained for each addition to the file Excel File This datastore writes records to an Excel XLS file By default all records will be written to a single sheet However if the output records are grouped the Sheet Group Level option can be used to force subsequent data to a new sheet For example if there are two levels of grouping and the level value is set to 1 then each start of a new level one group will start on a separate worksheet Each sheet will have a header if the Column Header check box is selected An example with column headers is shown in Figure 4 55 Excel Output 65 Composite DataSources Figure 4 55 Excel Output C D E E G H 1 State City store grocery frozen meat 2 OR Salem 27694 18669 78 5414 529 3609 686 3 WA Bellingharr 28206 22271 15 3560 91 2373 9
46. B C A combo box with choices A B and C e date A date field with a popup calendar The date type provides a number of variants to control how the date is presented to the user Within the tool the string representation of dates always follows the international standard format yyyy MM dd Here are some examples of date syntax and their meaning the indicates a comment and is not part of the syntax e date system locale long format e date system locale long format e date fr french locale long format e date fr_CA french canadian locale long format e date long system locale long format 133 Dynamic Parameters e date medium system locale medium format e date short system locale short format e date yyyy dd MM system locale custom format e date fr short french locale short format As you can see date can take up to two parameters locale format Both parameters are optional The locale is defined using the appropriate language country and variant codes separated by underscores LA_CO_VA Again you can omit values from the end to accept the default LA_CO or just LA The language codes are defined at http www loc gov standards iso639 2 englangn html while the country codes are defined at http www iso ch iso en prods services iso3 166ma 02iso 3166 code lists list en1 html The format should either be long medium or short or else a custom p
47. C datasource parameters Name required Name Description JDBC JNDI Pool Driver Suggestions sun jdbc odbc JdbcOdbcDriver URL jdbc odbe Sample User Password lv Hide Password Previous Next Finish Cancel JDBC DataSource Details Define JDBC DataSource is the first screen of the DataSource wizard In this screen JOBC DataSource parameters can be entered Name A unique name to identify the DataSource e Description Extra description for the data source JDBC DataSource SQL JDBC connection Three methods of connection are available JOBC JNDI or Pool a JDBC Driver Suggestions The type of the JDBC driver can be selected from the Driver Suggestions combo box A green or red symbol next to each driver indicate whether the driver is available for use If the specific driver jar file is copied to the ext folder before Elixir Repertoire is launched a green symbol will appear next to that driver indicating that the driver has been loaded and is available for use Driver When the type of the Driver is selected from the Driver Suggestions combo box the default Driver is automatically entered in the Driver Text box The Driver class name in the text box can be altered in case your DBMS vendor modifies the class name URL When the type of the Driver is selected from the Driver Suggestions combo box the default
48. Connect the output of Prop_Emp to the input of the Derivative processor Open the Derivative Properties and select the Derived tab and click on the Add Column button In the Add Column dialog enter the name as Department String as the data type and value as dept On clicking the Ok button the column is added to the wizard After entering the properties the processor window appears as shown in Figure 7 5 Derivative Wizard 97 Properties DataSource Figure 7 5 Derivative Wizard ET Derivative Wizard Derivative Add items items to the derived schema Name Derive Dept Color Base Derived JavaScript Column Name Type Discard 1 Dept_Name String Previous Next Finish Cancel 18 Select the JavaScript tab and enter the following script in the window if Dept_Name Computer dept CPT lse dept Others 19 Click the Finish button 20 Connect the output of the Derive Dept processor to the Result Select Result and choose View from the popup menu The output will be displayed in the data window as shown in Figure 7 6 Test Output It can be seen that the department Computer is mapped to CPT Figure 7 6 Test Output Designer Script Data Result y Dept_Name Department Computer CPT 21 Now delete the flow between the Prop_Emp data source and the Derive Dept processor 2
49. DataSource the ranges must be defined in the Excel file Open the Empdata xls file with Microsoft Excel and select Name gt Define under the Insert menu The Define Name dialog box pops up Enter name as Emp1_All and enter range as given below in the Refers to text box and click the Add button Sheet1 A 1 D 11 Enter name as Emp2_All and the range as given below in the Refers to text box and click the Add button Sineeic 2 AS 1 D96 Enter name as Emp_All and the range as given below in the Refers to text box and click the Add button Sheet1 Sheet2 A 1 D 11 After adding the columns the dialog window appears as shown in Figure 8 3 Define Name Click the Ok button 103 3 Reference DataSource Figure 8 3 Define Name Define Name 2 X Names in workbook Refers to Save the Empdata xls file Now move to Elixir Repertoire and create a new Excel DataSource called Emp Range Enter the Empdata URL in text field provided or click the button to the right of the text field to select the Empdata xls file from the Open dialog window Enter the Range as Range and select the First Row Header check box After setting the properties the DataSource Wizard appears as shown in Figure 8 4 Excel DataSource Sample Now click on the Next button In the screen that appears click on the Infer Schema button Enter any range specified above in the Dynamic Parameters dialog box that appears an
50. Digest MD5 the LDAP server sends data that includes various authentication options that it is willing to support plus a special token to the LDAP client The client responds by sending an encrypted response that indicates the authentication options that it has selected The response is encrypted in such a way that proves that the client knows its password The LDAP server then decrypts and verifies the client s response User Name This property is used to specify the identity of the principal for authenticating the caller to the service Password This property is used to specify the credentials of the principal for authenticating the caller to the service By default any text entered here is shown as asterisks unless you turn off Hide Password Protocol The value of this property is a string that specifies the security protocol for the provider to use The protocol is entered in the text box When you click on the Next button the screen appears as shown in Figure 10 3 LDAP DataSource Schema On clicking the Infer Schema button the settings you have entered so far are used to query the LDAP server and extract the schema attributes The fields and the corresponding data types are displayed in 118 LDAP DataSource Figure 10 3 LDAP DataSource Schema 3 Datasource Wizard Define LDAP Datasource View and map schema for LDAP Datasource Infer Schema f D Column Name Type Prev
51. Elixir Data Designer User Manual Release 7 3 Elixir R pertoire Elixir Technology Pte Ltd Elixir Data Designer User Manual Release 7 3 Elixir Technology Pte Ltd Published 2008 Copyright 2004 2008 Elixir Technology Pte Ltd All rights reserved Solaris Java and all Java based trademarks and logos are trademarks or registered trademarks of Sun Microsystems Inc Microsoft and Windows are trademarks of Microsoft Corporation Table of Contents 1About Elixir Data Designer cortado ie a E ira vano rte pi sh Meee ete yey 1 InirOduictl Oni ip 5 2 acest fates ches A eee dentine ovens oR 1 Features of Elixir Data Designer cintas otitis esoo 1 Datasource Security 5 5 05 esi tai E KE EE eels 2 2 The Elixir Data Designer Workspace ocoooccnnconoconcconncnnncnnncnnnonoronocnnccnnconnconnccnnccnnconncnnneos 3 OVA A A ede Gente ea OTTES 3 Case Study sis ve 3ssssschse pace eee EOI EEE O EEEE TERA I cadets sane Sheed NEEESE SETERO TEES EE D 4 3 JDBC DataSource ne 7 JDBC Drivel S hipe ee dr E E E a E A E Ea 7 ODBC Connectivity sri dys a p TE E eee EEE E OE EEEE a E EEES 7 JDBC DataSource Wizards eean eano i aA a EEI AE EEEE CAAA TA ATOA nd EESHA 9 JDBC DataSource Detalle 9 SOL reas a e aa a T E Ed a E A E peeks dae 10 Inf r Schema irean AA AA A e 13 Cs O 13 Working with a JDBC DataSource ooooocccnccnnconnconoconcnnocnnocnncnnoconccnnconnccnncnnnconnconaciniss 16 Using the JDBC ODBC bridge driver ocoocccnccnnc
52. F S 3 15 2 79 6 F S 3 15 3 36 1 F M 3 15 3 36 2 M 5 3 15 3 36 3 F M 3 15 3 36 4 M M 3 15 3 36 5 F S 3 15 3 36 6 F S 3 15 4 26 1 F M The combination of all the records from both data sources are displayed For instance if there are 25 records in the Customer table and 40 records in the Sales table a total of 1000 25 40 records are fetched by using Cross Join Processor The Sort processor is used to sort data source records using specified criteria in the ascending or descending order It includes a Group On option to group the records depending on their values or specified ranges Unlimited levels of sorting and grouping can be done on a data set The Group Range used for delimiting groups can be specified for a field of type number date or string Elixir Data Designer provides an Expression Builder to assist in the extraction of ranges of records Using this the top n or bottom m records present in the data source can be retained These values need not be constants they can be variables and also include percentages The Sort processor is selected from the menu bar of the Designer window and then placed in the designer window workspace Properties The editable properties are shown in Figure 4 11 Sort Wizard 29 Composite DataSources Figure 4 11 Sort Wizard Sort Wizard Sort Add sort details Name Sort Colour Sort Order Group On Group Data
53. JDBC Datasource Enter SQL or use the Query Builder SOL required SQL Callable Callable Statement Convert Nulls to Empty Strings Query Builder Edit SQL Previous Finish Cancel Figure 3 5 Elixir Query Builder Elixir Query Builder Show Views Select Distinct Default Schema E Tables X Relationships Column Details soL Cancel e Clicking the Query Builder button in the SQL window opens the dialog box as shown in Figure 3 5 Elixir Query Builder There are three panels in this dialog window The panel on the left side lists the tables and relationships present in the database There is a Show Views check box present on top of the left JDBC DataSource panel When this check box is selected the views present in the database are listed Tables and Views can be selected to form the basis of the query In the right panel fields of the selected tables and views are displayed The fields to be included in the query can be selected When the fields are selected the properties of the selected fields are displayed in the Details tab of the lower panel Similarly the field names are included in the SQL statement that is displayed in the SQL tab of the lower panel If the Select Distinct check box on the right panel is checked then only
54. Similarly add another Reference Datasource called Re f Ex2 which also references Emp Range Enter Emp2_All in the Range text field On clicking the Finish button the Ref Ex2 ds is added to the repository Similarly add a Reference DataSource called Ref Ex3 as above but enter Emp_A11 in the Range text field Now we have three sample Reference DataSources we can look at how they work Open the Ref Ex data source In the data window click on the Load Data menu The output is displayed as shown in Figure 8 6 Sample Output It is seen that the data from the first sheet of the Excel file is displayed Figure 8 6 Sample Output Data ote Empld Ename Dname Esal 1 Gerry Electronics 7400 2 Micheal Electrical 8500 3 Frank Computer 9000 4 Lawrence Mechanical 6800 5 Jenifer Sales 6500 6 Christopher Electronics 8000 7 Flora Computer 8600 8 Cathy Electrical 7500 9 Peter Sales 7000 10 Kendy Mechanical 5900 105 Reference DataSource Opening Ref Ex2 and loading the data shows the data from the second sheet of the Excel file is displayed Repeating the same action on Ref Ex3 shows the data from both the worksheets of the Excel file is displayed Thus all the three Reference DataSources refer to the same Excel file but parameterize it in different ways This means there is only one place to define common information such as the Excel file location making the
55. The records in the data source can be viewed by clicking on the Load Data menu in the Data Window Note Please refer to Appendix B Samples for the sample files used in this chapter Using a Callable Statement To make use of Stored procedure in the JDBC data source a stored procedure must first be created in the database Here s how to create a stored procedure using Oracle f Create a new table emp with columns Eno number Dno number Dname varchar2 12 and Esal number We want to create a stored procedure on the table to fetch records with specific value of Eno or Dno or Esal A package is created using the code below CREATE OR REPLACE PACKAGE pack AS TYPE empRowType IS REF CURSOR return empSrowtype FUNCTION selemp enumber in number dnumber in number esalary in number RETURN empRowType End pack Compile the package The package body is created using the following code CREATE OR REPLACE PACKAGE BODY pack AS FUNCTION selemp enumber in number dnumber in number esalary in number RETURN empRowType IS myemp empRowType BEGIN OPEN myemp for select from emp wher Eno enumber or Dno dnumber or Esal esalary Scudo MYEmD James EE aC Compile the package body Make sure the Oracle driver classes12 jar is in the Elixir Repertoire ext folder Launch Elixir Repertoire so that the driver is loaded Note if using the Remote software the ext folder
56. This is where the Pattern field is useful By default if you leave the Pattern field blank you will get column names as descibed above If you enter a Pattern description it will be used to generate the column names There are a number of substitution parameters available Assuming the default column name was US Oregon Married Count employeeid here are some samples of the available substitutions Pattern e blank gt US Oregon Married Count employeeid backwards compatible gt US Oregon Married Count employeeid all as before e 0 gt US extract using index 61 Composite DataSources 1 0 2 gt Oregon US Married another index eg hierarchy gt US Oregon Married without the measure measure gt Count employeeid without the hierarchy hierarchy measure gt US Oregon Married Count employeeid all field gt employeeid just the field name fn gt Count just the measure fn fn of field gt Count of employeeid varying formatting Note It is important that any pattern you define should result in a unique column name for each column in the cube This usually means that you should include the elements of the hierarchy and the measure somewhere in your pattern Failure to ensure unique column names may prevent subsequent processor steps from accessing data from those fields with duplicate names The next wizard page provides a
57. XPath Builder De Datasource Wizard Define XML Datasource 4 Enter XML datasource schema or use the XPath builder XPath Builder Root XPath i Name Type XPath Merge Previous Next Finish Cancel There are two panel windows in the XPath Builder The tree structure view of the XML file will be displayed in the left panel The value of the fields selected in the XML file will be displayed in the text field below the panel The Root XPath is specified in the text box provided at the top of the right panel Select the field to be displayed and then drag and drop them in the Field and XPath cells of the columns Alternatively enter the name of the Field and XPath in the corresponding cells Select the Merge type from the combo box Then field values are displayed in the column On clicking the Ok button the selected fields are added to the Wizard Method 2 This method can be used if the XPath of the columns are already known In this method the Root XPath is entered in the text box There are five buttons They are Add Column Edit Column Move Up Move Down and Remove Column buttons 88 XML DataSource On clicking the Add Column button the Add Column dialog window appears The name of the column is entered in the text box The data type of the column is selected from the Data Type combo box The XPath is entered in the text box Finally on selec
58. _ld String 2 Emp_Name string 3 Dept_Name String 4 Emp_Sal String Less Than Salary Previous Next Finish Cancel Connect the output of the Filter processor to the input of the Result Now add a Reference DataSource named Ref Com1 Select the Compo data source from the DataSource combo box Enter 7000 as the value for the Salary parameter After entering the 106 Reference DataSource values the screen appears as shown in Figure 8 8 Sample Reference On clicking the Finish button the Ref Com1 ds is added to the repository Figure 8 8 Sample Reference Sy Datasource Wizard Reference a Datasource Enter reference parameters and properties Name lRef Comt DataSource Ref Samp Compo Key Value Salary 7000 Previous Next Finish Cancel 4 Repeat the process to create Re f Com2 and enter 8000 as the Salary value 5 Open Ref Com1 ds and confirm that only the records of employees whose salary is less than 7000 are displayed 6 Similarly open Ref Com2 ds and check that only the records of employees whose salary is less than 8000 are displayed Thus both the Reference DataSources refer to the same Composite DataSource but parameterize it in different ways 107 Chapter 9 Object DataSource Overview The Object DataSource allows data to be extracted at runtime from Java objects and Enterprise Java Beans If your J
59. a AA amp Moves we EEEE abet TEE 47 Concat PROCESSOR scsi sles carios arameo sansa sy ous Sean RPTE TE EINAR 49 Working with Concat Processor oocoocccoconnconnccnnconoconconacnnncnnronnronacnnccnnccnnccnnions 49 Parameter Processor ins sissies toi sPathadse agree sn hewraans rior diras nth 50 Working with Parameter processor oooccnoconoconocnnncnnncnnnnnnronnconconnconnccnnccnnconnconncos 51 SUDElOW PrOCESSOL viciosas rincon iran AAA EP OEA Raro TAEAE 53 PLOCES SE a bee 54 Cleansing erni oroesi a aries bat tarea rotos hoi 54 Generale ois O 55 DataDrop Processor s5 csssssssicescee ic dec ocss spa Paas PTE Eneo i ia ise EEST ESEESE oa EOG 58 Not eatin ee ee st 58 Cube PIOCESSOF A a E 59 Working with Cube Processor ocoooconoccnoccnoccnocnnccnoronocnnccnnconnccnnccnncnnnconncnnncnnnes 63 DataS tore Processor aiana r maLa venus rindo aria 64 CSV File eii ig edo ee obs beech oh ebb sds ia diss E des beet Meee esti ass 65 Excel Fleitas DATA Ans 65 JDBC File niate a a SE EE E Sons Sta et egos EEr E 66 NOM EANA EPEE A A da 66 iii Elixir Data Designer User Manual TE Pl A a 67 XMP Pale sota a a Ds NA 67 Custom Java Datastore srono decades steels se caue tosses ela rin vette detona 68 Composite JavaScript murcia it ieee Sore 69 Case StU GY sass diese sess Sones dubs coders sean sh sveed tees wuts denotes gebos E E E EA tis iaa osa 71 Text Datas OULCE iii O ds aoa 76 O 76 Text DataSource Wizard iio a SES ai 76 Working
60. a from another DataSource it could be a primitive data source like JDBC or XML or another Composite can be extracted using this processor It can be created in one of two ways e A DataSource in the repository can be selected dragged and dropped on the designer diagram e Selecting the DataSource processor from the menu bar of the designer and clicking on the diagram creates a DataSource graphic which can then be connected to any repository datasource Properties The editable properties are shown in Figure 4 3 DataSource Wizard 22 Composite DataSources Figure 4 3 DataSource Wizard DataSource Wizard DataSource Choose presentation options and data source Name DataSource Colour DataSource ElixirSamples DataSource FruitSales ds DataSources Schema 3 Department ds 3 Employee ds G FruitSales Summary ds J Fruitsales ds J FruitSalesGenerator ds 3 Gender and Marital Status Code English ds 3 Gender and Marital Status Code ds 3 HighLowData ds o amp JDBC 3 KPLds Previous Finish Cancel The data sources in the repository are listed in the DataSource window from which a data source can be selected The name of the fields and the corresponding data type of the selected data source are displayed in the schema tab The unwanted fields can be discarded by selecting the appropriate check box present in the Discard column D
61. a particular field then the record are grouped based on the days of the year and the group interval Since there are 365 days in a year the grouping is done accordingly For instance if the Days of Year is option is selected and the group interval is specified as 4 then the grouping output is 0 3 4 7 etc Where 0 3 fetches the records corresponding to the first 3 days of the year as 0 corresponds to null value and 4 7 the fourth to seventh day of the year If suppose the day of the year grouping has to be done for a particular interval of year then the years must be grouped first followed by the days of the year grouping Day of Month When this option is selected and a value is entered in the Group Interval text box for a particular field then the records are grouped based on the days of the month and the group interval For instance if the Day of Month option is selected and the group interval is specified as 1 then the grouping output is 1 2 30 or 31 depending on the total number of days in the month If suppose day of the month grouping has to be done for a particular interval of the months of a year then the years are grouped first by the month grouping Finally the Day of Month grouping is done Day of Week When this option is selected and a value is entered in the Group Interval text box for a particular field then the records are grouped based on the days of the week and the group interval For instance if the Day of Week option is selec
62. a user can now save a lot of complex joins filters and concatenations which is required previously The scripts are to be entered in the text field in the respective tabs as shown in Figure 4 41 Javascript Processor 56 Composite DataSources Figure 4 41 Javascript Processor Processor Wizard JavaScript Code Enter JavaScript codes to handle data events startData startGroup processRecord endGroup endData Previous Finish Cancel SQL This task will allow the user to send any SQL commands to the database based on the standard flow events after a JDBC connection is set up on the second page of Processor Wizard as seen in Figure 4 42 SQL Processor Wizard Figure 4 42 SQL Processor Wizard Processor Wizard SQL Connection Set the SQL connection parameters JDBC Driver required JDBC JNDI Pool Driver Suggestions URL User Password Hide Password Previous Finish Cancel The output of the SQL Processor is the same as the input and records passing through are not being modified as the user is only interacting with the database With this the user can load data in bulk into the database faster 57 Composite DataSources DataDrop Processor Note A DataDrop is a pass through output mechanism used to generate
63. aStore Wizard and set the following properties Select JDBC from the Type combo box Click the Next button Select Oracle in the suggestions combo box On selecting the Driver Suggestion the Driver class name and the URL are automatically displayed in the corresponding text boxes The URL as jdbc oracle thin localhost 1521 ELX Where localhost should be replaced by the IP address of the Oracle server if it isn t running on the same machine The number 1521 is the port number and ELX is the database name Enter DataOutput as the table name Select the DataStore and choose Generate from the popup menu The Oracle JDBC DataStore is generated and saved in the specific location 75 Chapter 5 Text DataSource Overview Text In Elixir Data Designer the data from a text file can be accessed using the Text DataSource The tool provides encoding support which includes multi lingual formats so text data sources which are encoded in different formats can be added Also text data sources with fields having different date time and timestamp formats can be added A Text DataSource can be created simply by right clicking on an CSV file on the repository and select Define as a Text DataSource In addition to straightforward field based access regular expressions are supported to give power users very flexible filtering and extraction options DataSource Wizard The first Text DataSource properties screen is shown in F
64. actory 5 Enter the provider URL 6 Enter the resource name The resource points to the database 7 Enter the user name and password if required 8 The rest of the steps involved in connecting to the JDBC data source via JNDI using the Data Source wizard are similar to those followed in the above procedure using JDBC drivers 19 Chapter 4 Composite DataSources Overview A Composite DataSource allows you to design a data flow using various data manipulation components in a network to get a personalized view of your data Snapshots of the data can then be stored in various formats for further analysis Elixir Data Designer provides a visual interface that allows the interactive creation and manipulation of data By using the various processing tools present in the data designer aggregation and transform ation of data can be performed easily The output can be inspected at the end of every operation applied Adding a Composite DataSource The Composite DataSource can be used to combine and control other DataSources To add a Composite DataSource to a repository filesystem you need to 1 Select the FileSystem choose Add gt DataSource from the popup menu and choose the Composite DataSource type then click the Next Button 2 The Define Composite DataSource screen appears as shown in Figure 4 1 Composite DataSource In this screen enter the name of the Composite DataSource and description if any Click the Fin
65. age One Datasource Wizard Define Text Datasource Enter Text datasource parameters Name required Name Description File URL Encoding UTF 8 v Date format vvvy MM dd Time format HH mm ss Timestamp format yyyy MiM dd HH mm ss First line is header Access type Separator Character v Previous Next Finish Cancel The Elixir Data Designer Workspace The screen as shown in Figure 2 5 Define Text DataSource Page Two appears In this screen click the Infer Schema button You should see the field types from the employee text file appear Click the Finish button The Employee ds file appears in the repository under the chosen file system Figure 2 5 Define Text DataSource Page Two Datasource Wizard Define Text Datasource Enter separator details Separator Tab Semicolon Comma Space Other Qualifier None y Infer Schema Column Name Type D yp Previous Next Finish Cancel Now to load the data of the Employee data source the manager has to click the Load Data icon in the Data window To print the data just click the Print Data icon Only the first five hundred records are processed in this table The record count will automatically stop at this limit If you have a larger number of records you can select the Count All Records chec
66. ages Dave EE a E 13 3 8 Connection Pool Wizard siessen cs tecse reno cenrt oee aS corse gevenebents cobs cuacgebesyecoveene 14 3 9 Right click on Connection Pool 23 cocido cpoosoonrr deaesaseoiabapeesiacsess odes ista ise seais 15 3 10 Datas rce Wizard a do 16 3 1 1 JNDI Valles cocidas ies pan da E shave teas S 18 4 1 Composite DataSource osseo cds ieee cds eee TEE EEA VEEE EE Tas 20 4 2 Result Wizard id a Arde 21 43 DataSource Wizard ds 23 4 4 Sample FLOW sesei crete ninia porra ias pare cates sa PIES opos 24 IA E O aewves 24 4 6 Sample Jom FlOW A ad asestecdeasscsagepecta sss ves ieesasestss daeeieesadestevdsesdeersssotesdseeseases 26 ALT JOM WiZards ON 26 4 8 InnerJom Result s teiisors aah cities tata paran casa ea plete 27 4 9 Outer Jom Result sssr eie oe es ee A id 28 4 10 Cross Jom Result csss3 svciesedessees oecosenes scence sh cote lancia 29 ATT SOG Wizard ices Asides ccete ec bert tees beh bec eee AL ray noun nots wege Peo ue beanies ees AREA Ea ESTIRA 30 4 12 Add Sort Lema ra pitta eatig E E eases pastes eatiashy panna ds 30 4 13 Extract Options soie notario te area 31 4 14 Sample Sort FIOW secs oes its ir rt daa raised paint 34 ANS Sort Dial O iii lis ii is 35 4 16 Groupon Each Result cional rata E EEES E 36 4 17 Group on All Res lt ooccoinn e e EEE ETE EEIE EEEE EEE S 36 4 18 Group on Range Result oigre pornire sereni a ees PE TESTE E PSE ESEP SE SEERE ESS 37 4 19 Group on Range by Country Result
67. also a valid XPath won t work in this mode Note You must ensure the Root XPath is a simple absolute xpath for this mode to work When the data source encounters an element in the XML source with the designated root path a subtree is then constructed from that element and the descendants Hence full XPaths can still be used for the extraction of fields though they only have access to the subtree When this mode is used only the subtree needed to process one record exists in memory at a time This greatly reduces the memory requirements HTML The XML DataSource can also be used to read HTML In this mode the HTML is parsed into well formed XML that can be queried using XPath expressions The HTML parser can handle pages that are not well formed eg with missing close tags or unquoted attributes and will clean the tree so that it can be used for effective data acquisition HTML parsing is not available when Subtree Optimization is enabled Working with XML DataSources This example uses the sample file Telephone Mod xml You can either place this file in the repository or reference it from any location on your disk When the file contents are fairly static it is preferable to place the file in the repository so that it can be deployed to the server and maintained along with the data source If the file contents are dynamic you might prefer to reference the file in situ rather than copy it into the repository Alternatively you
68. amond Fruit Mountain View TBSO Bris Cancel This dialog allows you to specify the name and type of the column and then choose how the value should be constructed Value Type allows you to choose Expression Literal or Script Expression The Expression option provides three modes either an auto increment value a value range or a set of predefined values Auto increment The auto increment option will generate an incremental progression of values in any of the available data types The first value will be the logical zero element which is 0 for numeric types false for Boolean and 1970 01 01 for Dates the beginning of the Unix epoch When a limit of the data type is reached eg for byte at 127 the value will be reset to the minimum supported value so for byte 128 and the sequence will proceed Range If the range option is chosen you should enter the first and last allowed values These values may include substitution strings For example for an Integer between 5 and 10 enter First 5 and Last 10 Range has special behaviour for Strings Doubles and Floats String The first and last strings are padded with spaces as necessary to make them the same length Then for each character a random value is chosen between inclusive the corresponding character position from each string so H and W might give P for example but never A or Z Here s an example String Range S1000000A to S29999992 This al
69. ams L4 58 Composite DataSources The Note graphic provides a simple way to annotate Composite diagrams They carry no semantic meaning so they can be attached as comments to describe the diagram for those viewing or maintaining it Cube Processor 5 Elixir Data Designer provides a Cube component which allows you to generate data results of the your choice by defining multi level dimensions and multiple measures using the pre defined functions like sum average count max min etc The Cube processor is selected from the menu bar of the Designer window and then placed in the designer window workspace The properties of the Cube processor are given below Properties On opening the properties of a Cube processor the dialog appears as shown in Figure 4 45 Cube Wizard Figure 4 45 Cube Wizard ET Cube Wizard Cube Hierarchies Define cube hierarchies Name Cube Colour D Name Elements revious Next Finis Cancel The Cube Hierarchies screen contains the Name and Elements column where the names of each hierarchy and the corresponding hierarchy elements will be listed There are three buttons on the screen namely Add Edit and Remove which can be used to add edit or delete a hierarchy On clicking the Add button the dialog box pops up as shown in Figure 4 46 Add Hierarchy where the hierarchy elements can be added and a name can be assigned to
70. an be composed of attributes using the LDAP syntax For example e CN CommonName e L LocalityName e O OrganizationName e OU OrganizationalUnitName e C CountryName e STREET StreetAddress If there are domain components com and example then the base for the com example domain is dc com dc example Similarly if the organizational unit comes under the domain com example then to access people within the organization you would use ou people dc example dc com 116 LDAP DataSource Figure 10 1 LDAP DataSource Wizard 3 Datasource Wizard Define LDAP Datasource Enter LDAP datasource parameters Name required Name Description Host Base Scope One Level E Filter Default LDAP Source Setting Port 389 Timeout ms 0 Batch Size 0 Previous Next Finish Cancel Scope Scope defines the set of information used to search for data You may choose one of three values Object One Level or Subtree Choosing Object will only return data held by the object identified by the Base Choosing One Level will return data held by children of the object identified by the Base this is the default Finally Subtree will return data from all children in the subtree below the Base Filter Search filters enable you to define criteria for more efficient and effective searches LDAP filters are used to specify criteria for director
71. an be used to share database connections among requesting clients When a connection has been created and is placed in a runtime object pool an application can use that connection again Each application does not have to perform the complete connection process every time it uses a connection When an application closes a connection the connection is cached in the runtime object pool again Connection pooling permits an application to use a connection from a pool of connections that do not have to be reestablished for each use By using pooled connections applications can realize significant performance gains because they don t have to perform all of the tasks that are involved in establishing a connection This can be particularly significant for middle tier applications that connect over a network or for applications that repeatedly connect and disconnect The nodes in a JNDI namespace are known as contexts The root node is known as the initial context Initial contexts are created by initial context factories Elixir Data Designer provides enhanced support for JDBC DataSources deployed with JNDI The basic system requirements for using JNDI connections are e The vendor package providing connectivity for JNDI Factory context and the supporting classes Here s what to do 1 Use the filesystem or folder popup menu and select Add gt DataSource 2 Choose the JDBC datasource and click Next 3 Select the JNDI tab 4 Enter the context f
72. and type of fields and the data source will load it correctly Using Fixed Width Columns Here s how to add a text file having columns of fixed width 1 2 Use the file system or folder popup menu to select Add gt DataSource The DataSource Wizard appears Select the Text DataSource and click the Next button The Define Text DataSource screen appears Enter a unique name such as Text_Width Enter the URL in the text box provided Alternatively click click the button to the right of the text field to invoke the chooser In this case we will select the DateJoin txt file Select the First Line is Header check box Select Fixed Width as the Access type and click the Next button 82 Text DataSource 8 Add a column named Eno with data type String and width 3 9 Adda second column named Date Join with type String and width 12 10 The columns are added to the Wizard The screen appears as shown in Figure 5 8 Fixed Width Sample Click the Finish button Figure 5 8 Fixed Width Sample Datasource Wizard Define Text Datasource Enter fixed width details D Column Name 1 Eno 2 Date Join Previous Finish Cancel 11 The Text DataSource is added to the repository Select and double click on the Text_Width ds data source By clicking the Load Data menu you can verify the output Using Regular Expressions Here s how to extract data from a log file like Fig
73. apore UK UK for example Dynamic Parameters with a Nested DataSource Suppose a dynamic parameter is specified in the SQL query while adding the JDBC data source to fetch the records of a specific city from the Stores table Here s how a composite data source is added to the repository which references the Stores data source and values are passed to the dynamic parameters 1 Make sure the Mondrian Database is available eg through ODBC 2 Add a JDBC DataSource called Stores and choose the appropriate driver and URL for your database If you are using Mondrian through JOBC ODBC as described in the section called Using the JOBC ODBC bridge driver then enter the URL as jdbc odbc MondrianFoodMart 3 Click the Next button Enter the following SQL query in the SQL tab window SAC ID IL SPI EINK IL store store_id store store_country AS Country store store_state AS State store store_city AS City store meat_sqft store grocery sqft store frozen_sqft store store_sqft FROM store Where store_city like S Enter City 4 Click the Next button and choose Infer Schema On the Dynamic parameter dialog enter any city name and click the Finish button The schema will be displayed Click the Finish button to add the data source to the repository 5 Now add a Composite DataSource called Dynamic Comp On clicking the Finish button the Composite data source is added to the repository and opened 6 Drag and drop th
74. ar DataSource SampleFruit which includes a column listing each fruit name Drag and drop SampleFruit onto the Composite diagram Place a Filter on the diagram and use a Flow to connect SampleFruit DataSource processor to the Filter processor Invoke the Filter Properties and in the Filter 1 tab select Matches from the When combo box in the Fruit row Enter condition as FruitChoice where FruitChoice is the dynamic parameter we will get from the Properties datasource For more information on dynamic parameters refer to Appendix A Dynamic Parameters Click the Finish button Place a Parameter processor on the diagram Connect the Filter output to the data input of the parameter processor Drag and drop the SampleProperty onto the diagram Connect the SampleProperty data source with the params input of the Parameter processor Finally connect the output of the Parameter processor to the Result The diagram appears as shown in Figure 4 35 Sample Parameter Flow Figure 4 35 Sample Parameter Flow 52 Composite DataSources 13 Select Result and choose View from the popup menu The output is shown in Figure 4 36 Parameter Result As the Value of FruitChoice in the property DataSource is Orange the records corresponding to Orange are fetched Figure 4 36 Parameter Result Designer Script Data Result BHAC CompanyName Fruit 2000 1999 1998 1997 A Orange 32
75. are Inc and others All Rights Reserved You must accept the terms of that agreement to use this software 138
76. are likely to be repeating the same tests many times while you tune the processor logic Cache Datasource Wizard The Cache DataSource Wizard is shown in Figure 15 1 Cache DataSource Wizard Figure 15 1 Cache DataSource Wizard DataSource Wizard Cache a Datasource Choose the datasource to cache Name required Name DataSource ElixirSamples gt E Dashboard E DataSource gt amp Report gt E Resources amp Elixiiorkspace Previous Next Finish Cancel Name Enter the DataSource name in the text box This should be a unique name Datasource The URL of the datasource used to create the Cache datasource The second page of the wizard is shown in Figure 15 2 Cache DataSource Parameters This page allows the user to select the duration of the cache and extract the parameters from the datasource if any The user can also choose to enter the parameters manually 131 Cache Datasource Figure 15 2 Cache DataSource Parameters DataSource Wizard Cache Datasource Parameters Enter parameter values for this datasource Cache Type Memory Duration No Expiry v Extract Parameters Name a Previous Finish Cancel 132 Appendix A Dynamic Parameters Dynamic Parameters We generally need a way to control the dynamic behavior of the program by settin
77. ars Enter the name as meat_percentage in the text box provided Select the Data type of the column as long Enter the formula meat store 100 in the value text box The dialog box is shown in Figure 4 24 Completed Add Column Dialog Figure 4 24 Completed Add Column Dialog Edit Column meat_percentage Long meatstore 1 00 store_id Country State Available City Fields store grocery frozen meat Cancel Click the OK button and the column is added to the Derived tab window Click the Finish button and view the Result The output is shown in Figure 4 25 Derived Result A new column meat_percentage has been added to the datasource displaying the percentage of meat space available in the stores 41 Composite DataSources Figure 4 25 Derived Result Designer Script Data Showing 25 records _ Count All Recor A p as State City String String OR Salem frozen meat Double Double 5414 529 3609 686 grocery Double 18669 784 meat_percentage 27694 0 WA Bellingham 28206 0 22271 150 3560 909 2373 939 WA Bremerton 39696 0 24389 921 9183 647 6122 431 CA Beverly Hills 23688 0 15336 753 5010 748 3340 498 CA Los Angeles 23598 0 14210 378 5632 573 3755 048 CA Alameda OR Portland 20319 0 16231 745 2452 352 1634 901 CA San Die
78. as Microsoft Access 1 To add a Microsoft Access DataSource to the Elixir Repository we need to first register the ODBC source as described in the previous section Select the Microsoft Access Driver mdb from the list of drivers Enter the name MondrianFoodMart in the Data Source Name text box Locate your copy of MondrianFoodMart mdb and set the path accordingly Launch the Elixir Repertoire software Choose a file system or folder and Add a DataSource choose JDBC and click the Next button Enter name Sales in the text box Choose DataSource type JDBC By default the JOBC ODBC_Bridge Sun JVM is selected as the Driver Suggestion Enter the URL jdbc odbc MondrianFoodMart and the DataSource name as Sales Click the Next button Click the Query Builder button Select the Sales table from the list of tables and double click on it Select the Customer_id Store_id and Store_sales fields from the table and click the OK button The query including the selected columns is displayed in the SQL window Click the Next button Instead of using the Query Builder to build the SQL query the query select Customer acl Suores 10 amo Store seles meto sales JDBC DataSource 10 can be entered directly in the SQL tab window Click the Next button In the Define DataSource schema screen click the Infer Schema button The schema is inferred from the data query Click the Finish button The Sales ds data source is added to the repository
79. ata Designer includes Multiple Data Source Support This tool is capable of manipulating and merging data from many data sources including custom data sources Standard data source support includes JDBC XML Microsoft Excel and a variety of text formats Built in Filtering When only a particular subset of data is required it can be extracted using the Filter processor available in the Composite Designer More complex filtering can be performed with the help of scripts Data Derivation By using the Derivative processor a new field can be derived by performing operations on the existing columns in the data source Multi level Sorting The data can be sorted and grouped using the Sort processor About Elixir Data Designer Data Aggregation Using the Composite Join processor any two data sources can be joined together A comprehensive set of join operations is supported including inner join outer join and cross join Multi Dimensional Data Transformation The OLAP Cube control can be used to perform complex data operations with hierarchies dimensions and multiple measures using standard operations like max min count etc Caching The Cache processor is used to cache the data for a specified amount of time so that the user can avoid unnecessary repeated data retrieval from the original source The related staging feature stores the data temporarily when all the processes are completed This helps to flush optimise the memory
80. ataSource 2 The DataSource Wizard appears Select the Text DataSource and click the Next button 3 The Define Text DataSource screen appears 80 Text DataSource 10 11 12 13 Enter a unique name such as Text sep Enter the URL in the text box provided Alternatively by clicking the button to the right of the text field select the file from the Open dialog window Select ASCII as the Encoding option as the EmpInfo txt file is in ASCII format The Date format Time format and Timestamp format text fields can have default values Select the First line is header check box Select the Separator Character Access type After setting the properties the screen appears as shown in Figure 5 6 Sample Text DataSource Click the Next button Figure 5 6 Sample Text DataSource Datasource Wizard Define Text Datasource Enter Text datasource parameters Name Textsep Description File URL file Your Directory Here Emplnfo bd Encoding UTF 8 Date format yay MM dd Time format HH mm ss Timestamp format yyyy MM dd HH mm ss 7 First line is header T Access type Separator Character w Previous Next Finish Select Semicolon as the Separator Select option from the Qualifier combo box Click the Infer Schema button and click the Finish button You can then open the Textsep ds data source and click Load Data to view
81. ative and create a new field called Location which will overwrite the existing one with the following value Countries lookup Code Location EN View the result and you will see the Location changes from UK to United Kingdom because of the lookup The syntax of lookup is ProcessorName lookup fieldl value field2 The function looks up the first record where field1 contains value and returns the corresponding contents of fie1d2 Null will be returned if it is not found In our example we lookup in the Code column for a value UK and return the value of the EN column which is United Kingdom UK in English If we wanted the French name we would lookup Code Location FR and get back Le Royaume Uni Note Code and EN are strings but Location is a field be sure not to quote it it will be substituted with the Location value of the current record when the function is invoked You can now combine JavaScript lookups with other features such as Dynamic Parameters to create Countries lookup Code Location Language choice EN FR EN 70 Composite DataSources which prompts the user for their chosen language EN or FR once and then proceeds to use the chosen language substitution for each record encountered Lookup works with any uniquely named processor not just data sources This means you could perform an operation on Countries before looking up the value As
82. attern Remember the format only affects how the date is presented to the user not the internal string representation of the date Dynamic parameters are always strings This means the result substituted for a date type will not be a date object only a string representation in ISO format as mentioned earlier If such a string is substituted in JavaScript you will be performing subtraction instead 2005 01 01 2003 In order to convert the string to a date you need the following form of JavaScript showing a french date here asDate S Enter French date date fr long There are two important parts to this example Firstly notice that the substitution is enclosed within quotes This ensures the result is substituted as a string not as a series of number subtractions Secondly the string is passed to the asDate function which is pre defined within the tool for converting strings in ISO date format into actual Date objects e lookup Lookup choices from a datasource It is good to give users a choice of valid values but the options are often dynamic Using the lookup type you can fetch values from a datasource to populate a combo box on the parameter UI Here s an example Company Name lookup Fruit CompanyName B This will create a parameter on the UI with label Company Name with a combo box that reads its values from the data source called Fruit using the field called
83. automatically Using CSV as an example the DataStore screen appears as shown in fig Figure 4 57 Custom Java Option Screen 68 Composite DataSources Figure 4 57 Custom Java Option Screen De DataStore Wizard Java DataStore Choose custom Java options Class com elixitech data2 outputCSYDataStore Name Value URL filesCvCustom append true 7 qualifier i Iseparator Comma Previous lext Finish Cancel In order to implement your own DataStore you need to implement a DataListener interface This API 1s described in the section called Object DataSource APT When you have coded and compiled your class it should be placed in a jar in the ext directory so that it can be loaded next time the tool is started If you modify the jar you will need to restart Elixir Repertoire for the jar to be re loaded If you are using the Remote tool then the ext directory is on the server and you will need to restart the server Now you can enter your class name in the Custom Java DataStore wizard and enter any values that you have exposed through get and set methods Upon choosing Generate your class will be invoked to process the records Composite JavaScript Composite DataSources have a JavaScript tab in addition to the Data tab and the diagram Any scripts written in this JavaScript editor will be executed once when the Composite is about to begin processing Therefore t
84. ava classes comply with standard bean accessor patterns eg getX XX and isYYY the values can be easily extracted JavaScript is used to coordinate the data access so that it can be maintained within the interactive Elixir Data Designer environment and doesn t need a separate compile cycle The Classpath In order to access user defined classes they must be packaged as a jar and placed with any dependent jars in the ext directory of your installation This directory is scanned when the program starts so you will need to start the program after adding the jars to the appropriate location In the stand alone Repertoire Designer the ext location is the directory called ext within the Repertoire install directory On the server or when running Remote the ext location is the directory called ext within the RepertoireServer install directory Note that when running Remote the ext jars remain on the server but class and method names from the jars are provided to the Remote tool to allow browsing of the classes within the Guided JavaScript Builder in the Remote GUI which is described below Object DataSource Wizard The First Screen of the Object DataSource Wizard appears as shown in Figure 9 1 Object DataSource Wizard In this screen the Object DataSource name and schema have to be defined 108 Object DataSource Figure 9 1 Object DataSource Wizard DataSource Wizard Define Object DataSource Enter datasource name and define
85. b element B which in turn has sub element C There are two root paths as given below e Absolute Path The absolute path starts from the root of the document 86 XML DataSource e Relative Path The relative path starts from each of the currently defined node s Some basic syntax of the XPath are given below e A B C All the C children of B which are children of the root node A e C The C root node e C All C nodes in the document The Name of the file is entered in the Name column The Description of the file is specified in the Description text box e id S_24 The node with ID value S 24 An absolute XPath starts from the root of the document tree As shown above an XPath can be absolute or relative Note For more information on XPath refer to the website http www w3schools com xpath xpath_functions asp XML DataSource Wizard The XML DataSource Wizard properties are shown in Figure 6 2 Define XML DataSource Figure 6 2 Define XML DataSource 3 Datasource Wizard Define XML Datasource Enter XML datasource parameters Name required Name Description URL lsa XML Subtree Optimization Parse HTML Date Format vvyy MM dd Time Format HH mm ss Timestamp Format yyyy MM dd HH mm ss Previous Nex Finish Cancel The unique name of the data source is entered in the Name
86. bject DataSource Figure 9 3 JavaScript Editor Ef Datasource Wizard Define Object DataSource View Edit the Javascript initializing the data source Javascript Method Browser function pushTo cxt dl Previous Next Finish Cancel The JavaScript entered here must conform to the following pattern funceLon puSshle PushContext ext Datalactener cil fi dl put any imports here call start data to begin rendering the header sercar Dene a ranis y get the data to be iterated over var diter Company getEmployeesAslterator while dIter hasNext ff get the data values to supply dataObj diter next create a record instance to supply the values in rec this newRecordInstance get the Object to hold the values data rec getData fi11l in the values according to the schema data 0 dataObj getDateJoined data 1 dataObj getDeparment tt SEO call process record to begin rendering a detail dl processRecord rec call end data to begin rendering the footer dl endData this 111 Object DataSource You can use the JavaScript Builder to generate a first cut and edit it if you prefer Remember that each time you use the JavaScript Builder it will rewrite the JavaScript so you will lose any changes you have made Finally on clicking the Finish button the Objec
87. choose the Close button from the subflow toolbar The Input graphic on the SubFlow diagram allows you to set a series of tests that are applied to choose between the different parallel flows The wizard is shown in Figure 4 38 Input Wizard This screenshot is taken from the SubFlow Sample above and shows how those records with Country value USA are passed to the USA DataDrop part of the flow and the rest those with no test are passed to the Others DataDrop The Input tests proceed in order from top to bottom of the list The first test that returns true indicates the record is passed to that flow Subsequent tests are ignored An empty test is considered true and so all records that have not already tested true will pass to the first empty test flow 53 Composite DataSources Figure 4 38 Input Wizard LF Input Wizard X Input Choose order of tests and flows Name In Color Test Next Country USA USA DataDrop Others DataDrop Previous Next Finish Cancel ll The test order can be controlled by moving the rows up and down in the table If no test returns true the record will be discarded The tests will be shown on the diagram as labels attached to the appropriate flow You can reposition the labels by clicking on them and adjusting the positions of the handles Note The names of processors connected to the Input should be unique
88. cnnccnnccnnconnconeconncnnnrnnncnnccnaronicnno 16 Using a Callable Statement ooooccnoconocnnccnnocnnccnncnnnconccnnccnnconnccnnccnnccnnconncnnncnnnes 17 Using INDE Connectivity cis cisternas cines renfe aseo 18 4 Composite DataSources 5 1 2 erone eneee EEr EEEn EEAO EEE SEE EEE ID seta 20 OVERVIEW castes a a rip ET a V AE Patan eee a E E A RE A 20 Adding a Composite DataSource oocccoccnnccnnconnconeconeconononocnnconoronccnnconncconcnnnconncnnicinnss 20 Standard Diagram Operations ossis s serssiseesseesssseseesdee sins gesvetes dhs edasepsevebesdsevecesapescengs 21 Moving the processors senk tirne nE A E EEE EEE S tue EEE neers 21 Flow Connection Nodes saiv css sccasevpcessdataarsaen E E E A G sash 21 Date Processors e E EEEE ARSE A A E iTe 22 DataSource Processor aa arenales 22 Fl tod Wie As is ee ee ee ae ers 23 JOM Processie merih ias Tn rta eTE ETS Reg bs 24 Working with Jois sss cevecseieseete cbs do ree es inh E EEE VT EE E SOE Ee enS 25 SOL PLOCESSOM csias omiga RAI a eaa E TAR E CREET es 29 Group On Opuons cele tre E nE E E p REE 31 Working With Sort processors si ss scciss sssccassesecsesse essa ses ed rosa ep 34 Derivative ProCessOr isis 03 eeelias e A ven dewey date ander owe teak 39 Working with the Derivative processor cooococccncccnccnnconnccnnccnnconnconncnnncnnncnnroniconos 40 Filter Processor iw eenei echo ee E is 45 Combining Filters eximio rie bates atte sos reis 46 Working with Piltets ia
89. col which is then translated to a DBMS protocol by a server This net server middleware is able to connect its clients to different databases The specific protocol used depends on the vendor 4 Native protocol pure Java driver This driver converts JDBC technology calls into the network protocol used by a specific DBMS directly This allows a direct call from the client machine to the DBMS server and is a practical solution for intranet access To illustrate the configuration process for a driver here are the steps on how to use a MySQL DataSource The latest MySQL driver can be downloaded from http www mysql com The mysql jar file must be copied to the Elixir Repertoire ext folder Similarly for an Oracle DataSource the classes12 jar file provided by Oracle must be copied to the ext folder For a Postgres driver check here http jdbc postgresql org In each case regardless of the DBMS vendor the corresponding driver files are copied to the ext folder Elixir Repertoire must be restarted to load any new drivers added Note If you are using Elixir Repertoire Remote then the database drivers must be placed in the server ext folder instead This is because data operations are performed on the server The only exception is the Query Builder functionality which requires access to the database schema information See the note in the Query Builder section below describing how to configure the Remote tool to support this ODBC Connectiv
90. could have a web server provide the XML data on demand 90 XML DataSource Create an XML DataSource using the Add DataSource Wizard and give it a unique name For the URL value you can use repository some path reads from the repository file some path reads from any location on your machine or http host some path to get the data from a web server You can also use the file chooser to choose a file location Choose Next and click the XPath Builder button This will load the XML document from the URL you ve just provided You will see Figure 6 6 XPath Builder Enter the Root XPath that will identify the record locations For this example use TelephoneBook Telephone which will select each of the Telephone elements that are children of Telephone book After keying in the root XPath press Enter Figure 6 6 XPath Builder 3 XPath Builder TelephoneBook Root XPath gt 3 Telephone e AT e AT elephone elephone gt C Telephone Merge Space gt 3 Telephone e e e o 3 Telephone 3 Telephone o C Telephone Cancel Note In this example there is another inner Telephone element nested within Telephone so we can t use Telephone or it would select those too In any case XPath operations will be slower than so avoid them unless you need to select from a combination of depths in the XML tree W
91. d click the Finish button The schema will be inferred Change the Data Type of Emp_Id and Emp_Sal to Integer Finally click the Finish button Figure 8 4 Excel DataSource Sample ET Datasource Wizard E Define Excel Datasource 7 Enter Excel datasource parameters a Name Emp Range Description URL file C ffour Directory Here Empdata xls Range Range First Row Header Column Name Type IEA 1 Empld Integer 2 Ename String Edit Column 3 Dname String 4 Esal Integer Move Up Move Down Remove Column Previous Next Finish Cancel Click the Finish button to add Emp Range ds to the repository Choose Add gt DataSource again and this time select Reference DataSource and click the Next button 104 Reference DataSource In the Reference a DataSource screen that appears enter Name as Ref Ex1 and select the Emp Range from the DataSource combo box Enter Emp1_All as the value for Range After entering the values the screen appears as shown in Figure 8 5 Reference a DataSource On clicking the Finish button the Ref Ex1 ds is added to the repository Figure 8 5 Reference a DataSource Reference a Datasource Enter reference parameters and properties Name IR ef Ex1 DataSource Ref Samp Emp Range Key Value Range lEmp1_All Previous Next Finish Cancel
92. dy The details of employees working in a company are maintained in a file We want to view count and print out the records Note See Appendix B Samples for details of the sample files that support the case studies in this manual 1 Choose a file system or folder and select Add gt DataSource from the popup menu the DataSource Wizard appears as shown in Figure 2 3 New DataSource Select the Text DataSource from the DataSource types and click the Next button The Elixir Data Designer Workspace Figure 2 3 New DataSource Datasource Wizard New Datasource Choose a datasource type i ARFF i LDAP 2 p Composite YE Object FE Excel Properties XLS FileSystem 7s Random Aa JDBC Uz Reference Previous Next Finish Cancel The Define Text DataSource screen appears as shown in Figure 2 4 Define Text DataSource Page One On this page enter Employee in the Name field Click the browse button to the right of the File URL text box and select the path to the Employee txt file from the Open File Dialog window Alternatively if Employee txt is in the repository enter repository followed by the path to the file In all of the samples see Appendix B Samples the repository path is used and in this case it is repository DataDesignerUserManual data Employee txt Tick the First line is header check box Click the Next button Figure 2 4 Define Text DataSource P
93. e 64 Composite DataSources CSV File This datastore writes comma separated values to a file Each record will be output as a single line with the fields separated by a separator character There is no trailing separator on the end of the line In addition the output can wrap the output values with qualifiers These are usually single or double quotes which ensure that values which contain the separator aren t treated as multiple fields For example if the field value is Hello World and comma is used as a separator the CSV will be malformed unless a qualifier is used to delimit the field This datastore is not suitable for fields that contain embedded newline characters Any such values should be fixed with earlier processors before the datastore A sample output using semi colon separators with double quote qualifiers is shown in Figure 4 54 CVS Output Figure 4 54 CVS Output P CsvOutput csy Notepad DEAR File Edit Format View Help tore_id Country State City store grocery frozen meat 13 USA OR Salem 27694 o 18669 784255237577 5414 529446857454 3609 6862979049693 2 Pusa wa Bellingham 28206 0 22271 1505749192 3560 9096550484796 2373 93977003232 3 USA WA Bremerton 39696 0 24389 92139197458 9183 647164815251 6122 431443210167 e usa CA Beverly Hills 23688 o 15336 753169821777 5010 748098106934 3 3340 49873207
94. e Combo box 4 Select the Sort Order as Ascending from the Combo box Click the OK button The sort column is added to the Sort Wizard 5 Click Finish button and view the Result to see all the records are sorted in ascending order by store_sales Extracting records using Expression Builder a Top n records 1 2 Open the Sort properties and remove any previous sort options from the previous examples In the first screen of the Sort Wizard click the Add button The Sort dialog box appears Select the store_id from the list of field names available in the Combo box Select Descending Sort Order and click the OK button The sort column is added to the Sort Wizard On clicking the Next button the extract screen appears 34 Composite DataSources 5 In the extract screen select the top option and enter 6 in the Amount field 6 Click the Set Value button The values entered in the fields of the expression builder will get displayed in the text box 7 Click the Finish button and view the records produced the records are sorted in descending order by store_id and the top six retained b Percentage of bottom n records 1 Follow the same procedure as a above except in the extract screen select the bottom option and enter 25 in the Amount field Select the Percent check box 2 Click the Set Value button The values entered in the fields of the expression builder will get displayed in the text box With this
95. e Stores ds file onto the Composite diagram and connect it directly to the Result 7 Open the Stores Properties from the shape on the diagram and click the Next button to see the DataSource Properties Screen You will see the Enter City parameter Enter the value as Salem without the quotes and click the Finish button 8 Select the Result and choose View from the popup menu The output is displayed as shown in the fig A1 9 This is similar to that of passing dynamic parameters where Enter City Salem 9 Go back to the Stores DataSource Properties screen and change Salem to City1 10 Select the Result and choose View from the popup menu This time the Dynamic Parameters dialog appears 11 Enter Salem in the City1 text field and click on the Finish button The Result output is the same as when the value Salem was hardcoded 12 Repeat the process replacing City1 with City1 City2 13 When you view the Result the Dynamic Parameters dialog appears again this time with two fields Enter Sal in the City1 text box and em in the City2 text box The text values from City 1 135 Dynamic Parameters and City2 are concatenated by the City1 City2 substitution so the records corresponding to Salem are fetched again Example Declaration of Dynamic Parameters The Empdata xls consists of employee details in two worksheets which contain ranges Using dynamic parameters we can choose during loadi
96. e can be entered here Root Choose the root directory from where files will be chosen Strip Prefix This allows characters to be stripped from the beginning of the directory and path For example the file C Temp Sample ds with Strip Prefix value C will give a Directory of Temp and a Path of Temp Sample ds Note that the prefix is not stripped from URLs they remain absolute Recurse into subdirectories If this checkbox is enabled files will be selected from the root directory and any subdirectories recursively 122 Filesystem DataSource The records returned by this data source can be manipulated by subsequent processors For example to obtain only XML files you can filter by extension To get the files sorted by name size or date just add the appropriate sort processor Filesystem Schema The Filesystem Datasource returns records according to a fixed schema that contains the following fields Name String The file name without any extension the characters after the last e Extension String The file extension not including the e Directory String The directory containing the file e Size Long The file size in bytes e LastModified Long The numeric value of the last modified date e LastModifiedDate Timestamp The timestamp value of the last modified date e Path String The full path of the file comprising directory name and extension e URL String The url of the file using the
97. e combo box 3 Use the up and down arrows to order the inputs as desired for example to ensure Stores records come before Sales 4 Click Finish button in the Concat Wizard and review the output of Result All the fields in the two data sources are combined together and displayed Where no field data is available the processor inserts nulls Combining the common field of the data sources using the Intersection option Follow the procedure as outlined above but now choose the Intersection option from the Properties dialog Now when you review the Result output you will see that only one field exists in both datasource schemas store_id Parameter Processor gt The Parameter processor retrieves parameters from one input and applies them to another flow This allows flows to be developed and reused with different sets of parameters and for parameters to be maintained independent of the flows To understand the interaction of parameters we need to understand the sequence of a flow When we want to view data from a Result the request is sent from the Result back up the flow to the source or sources The source s then push the records back down the flow to the Result The Parameter processor intercepts the original request as it travels up the flow and inserts the necessary parameter values These are then available to processors further up the flow towards the source With this understanding we can see that Parameter processors are of
98. e tab Select the JavaScript tab window and enter the following code function percent percentage frozen store 100 return percentage Select the Derived tab window Click the Add button the Add Column dialog box appears Enter the name of the derived field as frozen_percentage Select long as the data type Enter the function name percent in the value text box and click the OK button The column is added in the Derived window of the Derivative Wizard Click the Finish button and view the Result A new column frozen_percentage has been added to the data source displaying the percentage of Frozen area in the stores 44 Composite DataSources Filter Processor Filter processor is a tool with which you can manipulate and group the database records to filter out those records which meet specific criteria In the Data Designer the filtering can be done by using the built in functions or using the Javascript function for more complex filtering Multiple levels of filtering are supported up to three filter conditions can be set for each field within a single processor The Filter processor is selected from the menu bar of the Designer window and then placed in the designer window workspace Properties The editable properties are shown in Figure 4 28 Filter Wizard Figure 4 28 Filter Wizard Filter Wizard Filter Choose filter options for items in the schema Name Filter Filt
99. e that this URL can access not only local files but any files accessible through HTTP FTP or from the repository use the repository prefix There are three different ways of extracting data from a text file Separator character Fixed Width and Regular Expression Separator Character On selecting the Separator Character access type and clicking the Next button the screen as shown in Figure 5 2 Separator Type Properties appears The separator details are entered in this screen Figure 5 2 Separator Type Properties De Datasource Wizard Define Text Datasource Enter separator details Separator Tab Semicolon 8 Comma Space Other Qualifier None y Infer Schema Column Name Type Previous Next Finish Cancel The type of separator is chosen from among the different Separator options If there is any special type of separator other than those specified then that particular separator must be entered in the Others text box The type of qualifier used in the text file is selected from the combo box The Infer Schema button is used to infer the schema On clicking the Add Column button the Add Column dialog window pops up The name of the new column is entered in the text box and the data type of the field is selected from the combo box The new column is added to the data source The column can be edited by selecting the Edit Column but
100. ection for the JDBC password Right clicking on the pool file will list a few options like in Figure 3 9 Right click on Connection Pool One of them will be to create a JDBC datasource Only the first screen of the wizard will be different as it will be using the Connection Pool to connect similar to Figure 3 10 Datasource Wizard The rest of the pages of the wizard will be the same as creating a JDBC Datasource using the JDBC tab Figure 3 9 Right click on Connection Pool Repertoire Designer 7 2 0 Elixir Repository DI g amp ElixirSarnples Sample MondrianFoodMart po Open Properties Create JDBC DataSource Copy URL Delete Build Jar Repertoire Designer 7 2 0 20071226 build 119 en_US Copyright 2007 Elixir Technology Pte Ltd Registered user Internal GA JDBC DataSource Figure 3 10 Datasource Wizard DataSource Wizard Define JDBC Datasource Enter JDBC datasource parameters Name required Name Description JDBC JNDI Pool Connection Pool ElixirSamples MondrianF oodMart pool User Password v Hide Password Cancel Working with a JDBC DataSource In this section we will review the different ways of adding a JDBC DataSource Using the JDBC ODBC bridge driver The JDBC ODEC driver is only available on Microsoft Windows platforms and allows you to connect to Microsoft ODBC interfaces such
101. ed for example Normal Warning Critical Each option here will have a one third chance of being selected for each record If you want more Normals then just add more to the list Normal Normal Normal Warning Critical Now Normal will occur with higher frequency than Warning or Critical If you need a comma to appear in one of your values you should escape it with a backslash For example Hello World Goodbye has two values the first being Hello World Once again you can use substition strings in the values Literal The literal type supports substitution and then sets the value into the record This can be used when you want a specific value to occur in every record Script The script type supports substitution and then evaluates the script using the JavaScript engine and then inserts the value into the record This can be used when you want a logical progression of values for example an auto increment of ID or a calculated value such as today s date For today s date it may be preferable to perform the calculation once and reuse the value for efficiency as the script will be run for each record generated 128 Chapter 14 ARFF DataSource Overview In Elixir Data Designer the data from an ARFF file can be accessed using the ARFF DataSource ARFF DataSource Wizard The ARFF DataSource Wizard is shown in Figure 14 1 ARFF DataSource Wizard Figure 14 1 ARFF DataSource Wizard DataSo
102. er 1 Filter 2 Filter 3 JavaScript Column Name Type Condition customer_id Integer gender String marital_status String Finish Cancel There are four tabs in the Filter Wizard Filter 1 Filter 2 Filter 3 and JavaScript All the Filter tabs have the following columns e Column This column contains the row numbers Name This column contains the field names Type This column contains the data type of the records When A combo box in each field from which the filter condition can be selected e Condition The filter value can be specified for the filter condition that has been set You can also use dynamic parameters here to supply values at runtime 45 Composite DataSources Combining Filters All criteria entered on a Filter tab must be true for the record to be passed through For example using ElixirSamples DataS ource FruitSales ds as the input to the filter setting Filter 1 to read e CompanyName Equals A e Fruit Equals Apple will ensure that only those records where CompanyName A AND Fruit Apple will be passed through However any records not matched by Filter 1 may still be matched by Filters 2 or 3 Within a tab the rule is AND Across tabs the rule is OR So if we enter in Filter 2 e CompanyName Equals B we will get those records where CompanyName A AND Fruit Apple OR CompanyName B Therefore we will also get all
103. et to NULL b If multiple matching secondaries Keep This query fetches all records from the primary datasource and connects them to the first matching record from the secondary datasource If no secondary record matches the output record contains NULL for those values c If multiple matching secondaries Discard This query fetches all records from the primary datasource If there is exactly one matching secondary itis merged If there is zero or more than one matching secondary then NULL is used for each secondary field in the output Cross Join A Cross Join merges each possible combination of records from the primary and secondary datasources The output from a cross join can be very large 1 Reusing the same Join composite open the Properties and select the Cross Join check box You will notice the combo boxes are now disabled as all combinations are generated 2 Click the Finish button and view the Result output The output is shown in the Figure 4 10 Cross Join Result 28 Sort Composite DataSources Figure 4 10 Cross Join Result Designer Script Data e a S pros pricustomer pristore_id pristore_sal seccustom secgender secmarital_ 3 15 2 76 1 F M 3 15 2 76 2 M S 3 15 2 76 3 F M 3 15 2 76 4 M M 3 15 2 76 5 F S 3 15 2 76 6 F S 3 15 2 79 1 F M 3 15 2 79 2 M S 3 15 2 79 3 F M 3 15 2 79 14 M M 3 15 2 79 5
104. f more complex JavaScript functionality is needed then the code can be entered in the Derivative JavaScript tab Note that any functions defined within this tab are only used within the scope of the Derivative they can t be accessed elsewhere If you need functions to be available throughout all uses of JavaScript they should be defined on the Composite Diagram Script Tab instead Working with the Derivative processor Here s how to derive two new columns from the Stores data source using the Derivative processor 1 Ze Add a JDBC data source Stores to the repository Add a Composite DataSource to the repository and open it Select Stores ds drag it into the Composite diagram and drop it Add a Derivative processor Connect Stores ds to the Derivative processor and connect the output to Result The designer window appears as shown in Figure 4 23 Sample Derivative Flow 40 Composite DataSources Figure 4 23 Sample Derivative Flow Deriving a new column using a formula Here s how to calculate the percentage of meat available in the stores l 7 8 After connecting the stores data source with the Derivative processor using the Derivative procedure given above invoke the Derivative Wizard by double clicking on the Derivative processor The fields available in the data source are listed in the Base tab Select the Derived tab In this window click the Add Column button The Add Column dialog box appe
105. file protocol 123 Chapter 12 Tabular DataSource Overview The Tabular DataSource is a self contained data source that is editable from within the data designer The data source contains both the data schema and the data records in XML format making it a very efficient mechanism for accessing small volumes of data quickly Furthermore the Elixir data stores are capable of generating into the tabular format allowing any data source to be quickly replicated in tabular form This enables users to work with data without needing a database connection Tabular DataSource Wizard The Tabular DataSource Wizard is shown in Figure 12 1 Tabular DataSource Wizard Figure 12 1 Tabular DataSource Wizard ET Datasource Wizard Define Tabular Datasource D Enter Tabular datasource parameters Name Tabular Sample Description D Column Name Type 1 Name String 2 Address String Previous Next Finish Cancel Name Enter the DataSource name in the text box This should be a unique name Description Any extra description that is used to describe the data source can be entered in the Description text box The remaining UI items on this page allow you to define the schema for the records you wish to create In the screenshot shown above you can see two columns defined Name and Address both of type String The second page of the wizard is
106. formance and memory use Cube e Inthe Cube Hierarchies screen click the Add button The Add Hierarchy dialog box pops up Enter name as Location Select store_country from the Schema column and click the gt button So the store_country is added to the Hierarchy Elements list Similarly add the store_state and store_city to the Hierarchy Elements list This creates a three level hierarchy On clicking the Ok button the hierarchy column is added to the Wizard Click the Next button e On the Cube Axes screen select gender and click gt button to add the field to the Column Dimensions list box Select Location and click gt button to add the field to the Row Dimensions list box Click the Next button e On the Cube Measures screen click the Add button The Add Measure dialog box pops up Select store_sales and the function Sum leave the Pattern blank Clicking Ok to add the measure to the Cube Similarly add a measure for Average store_sales then Click the Next button e The next screen allows cube options to be configured We will leave the options at their default values so click next again and infer the schema This operation may take a while as it is analysing hundreds of thousands of records If you have previously executed the flow then the cache will have already been created and the inference will be faster You should notice that after we ve inferred the schema that the flow connector from the Cube to the DataStore has chan
107. g the values of various parameters The dynamic parameters are specified while setting the properties of a program at design time and the value is assigned during the execution of the program A parameter is declared when you type something within a text field A field may include multiple declarations mixed with normal text eg My name is first last A parameter is defined when you supply a value Dynamic Parameter Elements Each dynamic parameter can have up to three elements name type and default value These parts are separated by characters which can be omitted when there are no subsequent elements Here are some examples e name This syntax is used to specify a parameter name e name type This syntax is used to specify the name and type of the dynamic parameter e name type value This syntax is used to specify the name type and value e name value If the type has to be excluded and just the default value has to be specifed the syntax is given as above Dynamic Parameter Names Dynamic parameter names can include any characters including spaces and be as descriptive as you like For example Name or Please enter your name Reserved characters such as and should be escaped by prefixing with a backslash 1 For example Enter your Id Dynamic Parameter Types e A plain text field the default e password A password field so the typed characters are masked e choice A
108. ged from a dashed line to a solid line indicating that a schema is now defined for this link We can test the process so far by selecting the Cube processor and choosing View Cube from the popup menu The output is displayed as shown in Figure 4 59 View Cube Output The sum and average of sales for the male and female customers for the different cities belonging to specific States of USA are displayed 73 Composite DataSources Figure 4 59 View Cube Output 9 SAI Sum store_sales 351831 2899999848 Average store_sales 6 511667827010264 F Sumistore_sales 273756 0400000038 M Average store_sales Sum stor 6 546368549428567 278075 2 351831 2899999848 6 511667827010264 273756 0400000038 6 546368549428567 278075 2 157655 420000001 26 6 514686776859556 78218 6000000001 6 594048221210596 79436 81 9 SAI 9 USA Lo CA 9 OR F WA DataStore Albany Beaverton Corvallis Lake Oswego Lebanon Milwaukie Oregon City Portland Salem W Linn Woodburn Anacortes Ballard 38871 2700000007 6 5853219840668 68193 67 000000011 6 634270843467275 70677 59 13806 250000000007 6 4636001872659214 6063 889999999997 6 605544662309365 7742 360 3342 190000000006 6 5836434108527175 4217 810000000001 6 7 26969696969699 5124 380 19483 649999999965 6 557943453382688 10046 95999999999 6 66686131 3868607 9436 689 1
109. go CA San Francisco 22478 0 15320 950 4294 229 2862 819 WA Spokane 30268 0 22062 867 4923 079 3282 053 WA Tacoma 33858 0 22123 345 7040 792 4693 861 WA Yakima WA Walla Walla WA Seattle 21215 0 13305 463 4745 722 3163 814 Yucatan Merida 30797 0 20141 202 6393 478 4262 318 Guerrero Acapulco 23593 0 17474 989 3670 806 2447 204 Zacatecas Camacho 23759 0 16844 440 4148 735 2765 823 Jalisco Guadalajara 24597 0 15011 512 5751 292 3834 195 Zacatecas Hidalgo 30584 0 21938 000 5187 599 3458 399 DF San Andres DF 22450 026 Mexico City 36509 0 8435 384 5623 589 34791 0 38382 0 23112 0 34452 0 26354 017 30350 714 16417 973 27463 353 Orizaba Hidalgo Vancouver Victoria Veracruz Zacatecas 5062 189 4818 771 4016 415 4193 187 3374 792 3212 514 2677 610 2795 458 Deriving new columns using the various Date functions Date manipulations usually require values such as 5 days ahead 3 years ahead 20 days before and 5 months before to be calculated Just like the previous illustration connect Stores ds to a Derivative processor After connecting open the Derivative processor by double clicking on the processor or right clicking on it and select Properties The fields available
110. he given date Click the OK button The offset_5months_before column is added to the Derivative Wizard 9 After entering the Column the Derivative Wizard appears as shown in Figure 4 26 Completed Add Column Screen Click the Finish button in the Derivative Wizard Figure 4 26 Completed Add Column Screen Derivative Wizard Derivative Add items items to the derived schema Name Derivative Colour Base Derived JavaScript D Column Name Value Ahead_5_days offsetDays first_opened_date 5 Ahead_3_years offsetYears first_opened_date 3 offset_20days_before offsetDays first_opened_date 20 offset_5months_before offsetMonths first_opened_date 4 Previous Next Finish Cancel 10 The output is shown in Figure 4 27 Date Manipulations Result It can be seen that the offset dates are derived and displayed in new columns added to the data source 43 Composite DataSources Figure 4 27 Date Manipulations Result Designer Script Data Ct IE offset_20days_before offset_Smonths_before Date Date Date Date 1951 01 29 1954 01 24 1951 01 04 1950 08 24 Timestamp 1951 01 24 1955 03 18 1955 03 23 1958 03 18 1955 02 26 1954 1 0 18 1957 04 13 1960 04 13 1957 03 24 1956 11 13 1957 11 24 1960 11 24 1957 11 04 1957 06 24 1958 09
111. he pull command propogates back to the filter which uses the properties to control the filter process The pull command terminates at the datasource which pushes the data back along the flow through the filter which has already been configured 101 Chapter 8 Reference DataSource Overview The Reference data source acts as a reference to another data source and supplies parameters to it In other words a Reference DataSource is a proxy data source with preset parameter values The Reference DataSource points to a database and personalizes it for each user environment for example supplying different queries or user id and password The difference between a Reference DataSources and Dynamic parameters is that the user doesn t need to see any UI or explicitly enter any values each time the DataSource is loaded The data source designer has done all the work A Reference DataSource can be used to wrap any kind of DataSource including a Composite or even another Reference DataSource to fix certain parameters without the need for a complex Composite Properties Parameter Processor combination Reference DataSource Wizard The Reference DataSource Wizard is shown in Figure 8 1 Reference DataSource Wizard Figure 8 1 Reference DataSource Wizard ET Datasource Wizard Reference a Datasource Enter reference parameters and properties Name required Name DataSource v Key Value
112. hen you press Enter you will see a number of empty grey rows appear in the XPath Builder table We now need to choose what attributes we want to extract Expand the XML tree shown on the left to see the children of the first Telephone element Drag the EntryID element over the first column in the table and let go You should now see the EntryID values have been filled in to that column Repeat the process dragging LoginName into the lt New gt column This shows the LoginName values and adds another lt New gt column for your next drop You can also enter the XPath expressions directly into the table if you need more complex constructs The next elements we want are the Street and City from inside Address Therefore expand the Address element and drag these in turn over the lt New gt column You should now see Figure 6 7 XPaths Completed Note the builder has inserted the correct XPaths Address Street and Address City for you based on the Root XPath we defined earlier 91 XML DataSource Figure 6 7 XPaths Completed y Email ra Losimame Sres y y Street ield LoginName City ci LoginName Address St AddressiCity Space Space Space C zP 3 TEE Ey Country y Telephone E Fax City Darmstadt You can now choose Ok to close the XPath Builder Back in the wizard you can see in Figure 6 8 XML DataSource Schema the chosen fields along with the default type String which is co
113. his is a good place to define any functions and import any standard JavaScript libraries that you want to use throughout your Composite flow All other JavaScript locations for example in Derivatives and Filters etc will execute once for every record that flows through them It is inefficient to keep defining the same function over and over again so move the functions themselves into the JavaScript tab and then just call the functions from the processors as needed For example to filter a set of records so that only those with a SaleDate equal to today s date are retained it would be useful to have an isToday date script Here s one that can be put in the Composite JavaScript tab function isToday date var today new Java util Date return today year date year amp amp today month date month amp amp today day date day This function can now be called from inside a filter processor by choosing When type JavaScript with a Condition of isToday SaleDate Where SaleDate is the name of the field holding the date value for testing 69 Composite DataSources Note Fields of date type are actually java util Date instances These are different from JavaScript Date instances you need to make sure you are comparing like with like You might notice that java util Date doesn t have a year attribute but today year still works because JavaScript will automatically invoke the appropriate get method for us W
114. iggered by name from 66 Composite DataSources the Elixir Runtime Engine For example if you want to ensure a cache is loaded connect it to a None DataStore Now you can generate this datastore any time you want the cache to be loaded Text File This datastore writes expanded text templates to a file By defining a template for StartData StartGroup ProcessRecord EndGroup and EndData you can control exactly how the text file is constructed This is a flexible and fast alternative to using a Report Writer for producing text output A template is expanded by performing substitution of values using the familiar syntax JavaScripts can be embedded too by using syntax For example to add the current timestamp to the output you could use a template The current time is jJava util Date The StartData template is invoked first so this is a good place for a header If there are any groups in the output the StartGroup and EndGroup templates will be expanded at the appropriate point Each individual record will be expanded using ProcessRecord XML File This datastore writes XML or information transformed from XML to a file By default the records along with the schema are written out in Tabular DataSource format a simple XML structure that embeds the values directly into the data source itself so that it has no other dependencies You may take the XML file produced and use it with other tools or by naming
115. igure 10 2 Security Parameters the default type none has been changed to simple so that you can see the available options If your server doesn t require any authentication for read access you can leave the authentication type as none 117 LDAP DataSource Figure 10 2 Security Parameters 3 Datasource Wizard Define LDAP Datasource Enter LDAP datasource security parameters Authentication Type simple v User Name Password v Hide Password Protocol Previous Next Finish Cancel Authentication Type The value of this property is a string that specifies the authentication mechanism s for the provider to use The following values are defined for this property 1 none If this option is selected from the combo box then no authentication is required It is referred to as anonymous bind 2 simple If this option is selected then a clear text password is used This type of bind sends an unencrypted user name and password to the LDAP server for verification and can be secured only by using a secure channel to transmit the password eg SSL 3 GSSAPI GSSAPI stands for Generic Security Services Application Programming Interface An application level interface API to system security services It provides a generic interface to services which may be provided by a variety of different security mechanisms 4 Digest MD5 In
116. igure 5 1 Define Text DataSource Figure 5 1 Define Text DataSource ET Datasource Wizard Define Text Datasource Enter Text datasource parameters Name required Name Description File URL Encoding UTF 8 v Date format yay Mhl cdd Time format HH mm ss Timestamp format yyyy MM dd HH mm ss First line is header Access type Separator Character w Previous Finish Cancel The Name of the data source is entered in the Name text box and any extra description that is used to describe the data source can be entered in the Description text box By default the text files are in ASCII format If a text file with an alternate encoding needs to be accessed then the encoding type should be selected from the Encoding combo box 76 Text DataSource The format of any dates in the text file is entered in the Date format text field The format of any times in the text file is entered in the Time format text field The format of any timestamps date and time in the text file is entered in the Timestamp format text field By default the date time and timestamp formats are specified according to ISO standards The URL of the file containing the text is specified in the File URL text box Alternatively by clicking the button on the right of the text box the file can be selected from the Open dialog window Not
117. ilesystem DataSource Wizard cee eee cece cee ceeeceeeceeeca seca eeea cena eean eeu ceneeeneeeenees 122 MESAS A Ga a swanaisns tee geshy eon EE dened eetudes EAEAN 123 12 Tabular DataSource ia A a E e deeds de aa eas ini 124 OVERVIEW since cicadas e wed ten sy wet has doesent fiin 124 Tabular DataSource Wizard osh e E A EaR I EAEE E EAEEREN T EE OINAS 124 I3 Random DataSource scessescecasassnseaesekescdeesstemosssteecdbepteauaeeas N E eee s EE RA SRS 126 OVELVICW MiS 126 Elixir Data Designer User Manual Random DataSource Wizard moreso enoe esenp aen eE aE PEE ITEA EEr EEEE n EEs 126 EXPTE ODMs E EE E EEE E E N E o a ates 127 Literal nenia a foun am tue tie a E A age E E E a aA 128 STe ni n E E E N T E ts elven he E E A Staalen ts oaks 128 14 AREF DataS ure e a E E E Oer RN EE EREE EE ESI S 129 QUELVICW A E EE E eR vais 129 ARFE DataSource Wizard son ois ts pcdasesseet Ne dasggebdact eure gongeuyddergauetdan E 129 15 Gache DatasOurce ii el roda doin 131 OVERVIEW seas ovoutesseysueasigs E E RE dre O E EE E NAE EEES 131 Cache Datasource Wizard cesos nora e Gos Rt E E E E E RN ade 131 A Dynamic Parameters coser onne e a E E E eE ao Es Ee NE NEES 133 Dynamic Parameters tii Ea A a 133 Dynamic Parameter Elements r aep nE E EEES EER REEE TEESE 133 Dynamic Parameter Names ooocooccnncnnccnnconnconnccnncnnnconncrnncnnnrnnnrnnronnronncnnccnninnno 133 Dynamic Parameter NE 133 Ordering Dynamic Parameters
118. ilter options for items in the schema Name Filt_Emp Filter 1 Filter 2 Filter 3 JavaScript Column Name when Condition 1 Emp_ld String 2 Emp_Name String 3 Dept_Name String Equals Department 4 Emp_Sal String More Than s Salary Previous Next Finish Cancel Select the Parameters processor from the action bar and place it in the workspace of the designer window Change the color of the processor Connect the output of the Filter processor to the input of the Parameters processor Select and place the Prop_Empl data source in the designer window workspace Connect the Prop_Empl DataSource with the params input of the Parameters processor and the output of the Parameters processor to the Result Select Result and choose View from the popup menu The output is displayed as shown in Figure 7 10 Output Figure 7 10 Output Designer Script Data Res AO S Showing 1 records Emp_id Emp_Name Dept Name Emp_Sal 6 Christopher Electronics 8000 100 Properties DataSource 15 The values passed by the records of the Prop_Empl data source are matched with the dynamic parameters in the filter condition Based on this the records of the text data source satisying the filter condition are fetched As the flows are pull push the data store pulls data from the source this is when the parameters get added T
119. imensions Row Dimensions Previous Next Finish Cancel 60 Composite DataSources On clicking the Next button the Cube Wizard appears as shown in Figure 4 48 Cube Measures Screen There are two columns namely Name and Function In this screen the Pre defined functions and the name of the fields using these functions will be listed There are three buttons namely Add Edit and Remove to add edit or remove the cube measure columns Figure 4 48 Cube Measures Screen IT Cube Wizard Cube Measures Define cube measures At least one measure is required D Name Function Pattern Previous x Finish On clicking the Add button the dialog box pops up as shown in Figure 4 49 Add Measure Dialog In this dialog box the Field name Function and Pattern can be selected Each measure applies a function to a particular field for example Average Salary The name of the measure is given by the values of the dimensions that form the output column For example Male Average Salary and Female Average Salary would be the column names for a Cube with a Gender column dimension If there are two dimensions eg Country Gender then you would get column names like Singapore Male Average Salary Figure 4 49 Add Measure Dialog Add Measure Name Ww function average y Cancel Sometimes you might want to choose your own column names
120. in Figure 9 5 Sample Data Class Figure 9 5 Sample Data Class JavaScript Generator Select the class that will be returned by the iterator Data Class com dummytest Employee amp ersobjectDBTest o E META INF 2 6 com e dummy E test Company class L g Employee class Click the Finish button The Data Class fields will be automatically added to the Schema Definition table The Next screen shows the JavaScript code that has been generated 114 Object DataSource CUASCLOGM jOUSIALS Exa y Cll cul StEauciE Dece elas p importClass Packages com dummy test Company Company getEmployeesAsIterator while dIter hasNext var diter rec data dataObj data data data data data data data data 0 Op Un OSS La IS a 7 this newRecordInstance rec getData dl da da da da da da da da ta0bj ta0bj ta0bj ta0bj ta0bj ta0bj ta0bj ta0bj cet mese 0 2 getDateJoined getDeparment getDesignation getName getID hashCode getClass KEOS ie Lim y Apr SS reco name dl endData this 8 On clicking the Finish button the Object data source is added to the repository 9 View the data to verify the correct data is returned 115 Chapter 10 LDAP DataSource Overview LDAP is a Lightweight Directory Access Protocol It is a protocol for acce
121. in the data source are listed in the Base tab Select the Derived tab In this window click the Add button Enter the name of the column as Ahead_5_days Select Date as the Data Type Enter offsetDays first_opened_date 5 in the Value text box This function will calculate 5 days ahead of the day in the given date Click the OK button The Ahead_5_days column is added to the Derivative Wizard Click the Add button the Add Column dialog box pops up Enter name of the column as Ahead_3_years Select Date as the Data Type Enter offsetYears fisrt_opened_date 3 in the Value text box This function will calculate 3 years ahead of the year specified in the given date Click the OK button The Ahead_3_years column is added to the Derivative Wizard Click the Add button to add a new column The Add Column dialog box pops up Enter name of the column as of fset_20days_before Select Date as the Data Type Enter 42 Composite DataSources offsetDays first_opened_date 20 in the Value text box This function will calculate 20 days before the day specified in the given date Click the OK button The offset_20days_before column is added to the Derivative Wizard 8 Click the Add button to add another new column Enter name of the column as offset_5months_before Select Date as the Data Type Enter offsetMonths first_opened_date 5 in the Value text box This function will calculate 5 months before the month specified in t
122. inferred from a field in the record If there is a field called CompanyName with record values of A B C etc then using CompanyName as the table name will put the records into tables A B and C Each record could be appended to a different table Because table names may include spaces and other characters particularly if they are read from field values the datastore will wrap each string with double quotes like this when generating SQL codes It may be useful to avoid this quoting if your table name also includes a schema component for example myschema table This is because myschema table is the name of a table in the default schema whereas myschema table is a table called table in the myschema schema The latter is probably what you want but we can t be sure The solution is to explicitly quote the table name yourself If quotes are already present detected by an initial double quote mark the datastore will not add any more Therefore if the table name is given as myschema table then the datastore will not introduce any extra quotes This allows you to choose either interpretation of the table name explicitly This discussion also applies for MySQL table names except the back tick C quote is used instead of double quotes throughout None This datastore discards the records This datastore is a useful trigger for priming data caches or in conjunction with data drops This is because only DataStores or Result can be tr
123. ion value as a string or through the expression builder Expression Builder Top Bottom Amount o Percent Level 04 Set Value Extract Previous Next Finish Cancel The Top option is selected if the top n number of records has to be retained e The Bottom option is selected to retain the bottom n number of records e Inthe Amount field the value of n has to be specified Where n is the number of records to be sorted e If the Percent check box is selected then the Amount value represents a percentage of the total number of records flowing through The level field indicates the level at which the extraction algorithm applies A value of 0 indicates that it applies over all records A value of 1 indicates the top n records of each group level 1 will be retained The value can vary between O and the number of grouping levels identified on the previous page e Having edited all the values clicking the Set Value button will produce a command string in the Extract Text box This string can be edited manually for example to include dynamic parameters Group On Options The Group On combo box lists the various options available for each field based on its data type Numerical Data If a number type is selected for grouping then the options that are listed in the Group On combo box are Each Value Range Count and All String Data If the String data
124. ious Next Finish Cancel the table On clicking the Finish button in the data source Wizard the data source will be added to the repository Working with LDAP DataSource Here s how to access the organizational unit ou people of the DNS domain example com The steps given below are followed to add the LDAP data source and list the people l From a filesystem or folder popup menu choose Add a Datasource select LDAP DataSource types and click the Next button In the screen that appears enter the name of the DataSource as Organization Enter the local host name or the IP address of the client system in the Host text field Enter ou people dc example dc com in the Base text field Enter the port number of the server After setting the properties the screen appears as shown in Figure 10 4 LDAP Datasource Wizard Click the Next button In the screen that appears select none option from the Authentication Type combo box Click the Next button In the screen that appears click the Infer Schema button The attribute names and types are listed in the table as shown in Figure 10 5 Completed Datasource Wizard Click the Finish button Select and double click on the Organization ds data source On clicking the Load Data menu in the data window the output is displayed as shown in Figure 10 6 Organization Result Note Please refer to Appendix B Samples for the sample files used in this example
125. ir Data Designer supports all the three types of Joins inner join outer join and cross join The Join processor can be selected from the Designer menu bar and then placed on the diagram workspace The Join processor must get input data from two DataSources Properties The Join Processor properties are shown in Figure 4 5 Join Wizard Figure 4 5 Join Wizard ET Join Wizard Join Identify fields to equate in the joined schema Name Join Color Options Primary Secondary C Cross Join all permutations of primary and secondary If no matching secondary Keep primary fields x if multiple matching secondaries Repeat primary for each secondary M Prefix for primary fields Prefix for secondary fields Previous Finish Cancel The name of the Join can be entered in the text box provided The Choose Colour dialog box is invoked on clicking the Colour button from which the color of the Join processor can be selected The dialog consists of three tabs Options Primary and Secondary 24 Composite DataSources In the Options tab a check box is provided for the Cross join When this check box is selected the Cross Join is performed on the two data sources The cross join produces every combination of input records For example if the primary datasource provides ten records and the secondary datasource provides five records then fifty records 10x5
126. iscarding fields reduces the amount of memory needed to process the data Click the Next button to see any editable property values Property values can be assigned here for any dynamic parameters in the selected data source Refer to Appendix A Dynamic Parameters for a detailed explanation on passing parameter values Flow The Flow connector is used for linking the various processors to ensure a sequential flow of data If there is no valid flow of data the flow connector appears with dashed lines This happens when the flow is unable to read schema information from its input Here s how to use a flow processor to connect the Sales DataSource directly to the Result 1 Add a JDBC DataSource Sales using the procedure given in the previous chapter 2 Add a Composite DataSource named F low 3 Drag and drop the Sales DataSource in the Designer window 4 Select the Flow control and connect the output of the Sales DataSource processor to the input of the Result The data flow diagram appears as shown in Figure 4 4 Sample Flow 23 Composite DataSources Figure 4 4 Sample Flow ES E EEN 5 Use the View option on the Result popup menu to display the records in the Data tab Join Processor me The Join processor is used to aggregate two data sources using one of several join types e g Inner outer etc The join operator links the records from two data sources by matching values in specified columns Elix
127. ish button Figure 4 1 Composite DataSource Datasource Wizard Define Composite Datasource Enter Composite datasource parameters Name required Name Description Leen Previous ex Finish 20 Composite DataSources 3 The Composite DataSource is added to the repository and opened The Composite workspace will appear with three tabs Designer Script and Data In the Designer tab the Result box appears by default The result of an operation performed in the data designer can be viewed using this graphic This can be done by connecting the data output of any processor to the input of the Result box When other tools such as Elixir Report Designer connect to the data designer they will use the data provided by Result All graphical shapes on the Composite diagram have a Popup menu When the Properties menu item is selected from this menu a Wizard will appear to allow editing of the item The Result Wizard appears as shown in Figure 4 2 Result Wizard You can also double click on a shape to open the corresponding wizard Figure 4 2 Result Wizard Result Wizard Result This is the result schema Result Colour Column Name e Type Discard Company String Fruit string 2000 Double 1999 Double 1998 _ Double LJ 1997 Double Finish Cancel The name of the Result can be entered in the text box On clicking the color button the choo
128. iteria like the abc in the earlier example There are plenty of books and on line resources which will give full details of regular expression syntax So far we have only looked at one regular expression There are two more Start and Stop These are optional but if supplied will turn on and off record processing If a Start expression is supplied all lines will be discarded until this Start expression is matched Subsequent lines will be processed as described previously until the Stop expression if supplied is matched Subsequent lines will again be discarded A brief example will show how useful this is Summary Jon Total Bill Total Details Jon Jon First Jon Second Bill Bill First Bill Second Bill Third Comments None Nothing Given the above text file we can extract just the details by using these three expressions Start Details Stop Comments Regular Expression Of course you leave out the quotation marks when entering expressions into the text fields This configuration will ignore all the Summary values even though they match the Regular Expression criteria of two fields separated by a comma Once we reach the line which matches the start criteria Details the Regular Expression matching starts The next line just contains Jon which doesn t match the Regular Expression requirements and so is discarded Jon First and Jon Second do match so they are passed through as records Bill doesn t match
129. ithin a Composite processor script it is possible to refer to the fields of the current record by name for example SaleDate in the example above If the field name doesn t conform to JavaScript naming convention for example if it starts with a digit like 7Monkeys you can refer to it as this 7Monkeys Itisn t possible to refer to the fields in the Composite JavaScript tab because this code is executed before the first record is read Within a Composite processor script it is also possible to refer to other processors by name providing the name is unique You can use this technique to lookup results from parallel flows or processors and incorporate them into your record sequence The easiest way to describe this is through an example Suppose we have a data source called Countries with the following structure Code EN FR UK United Kingdom Le Royaume Uni SG Singapore Singapour US United States Les Etats Unis MY Malaysia La Malaisie This could be from any kind of data source but a Tabular DataSource is probably the simplest to create Create a Composite DataSource and then drag and drop Countries onto the diagram Now we repeat the process with another datasource called Source with this structure Name Location Jon UK Shih Hor SG Drop Source onto the diagram as well Now create a new Derivative and connect it so the records will flow Source gt Derivative gt Result Leave Countries unconnected Now open the Deriv
130. ity Before using the JOBC ODBC bridge to connect to data sources the ODBC connectivity must be established JDBC DataSource The Open Database Connectivity ODBC interface is a Microsoft standard for accessing different database systems from Windows The ODBC interface permits maximum interoperability an application can access data in diverse DBMSs through a single interface Furthermore the application will be independent of the DBMS from which it accesses data Users of the application can add software drivers which interface between Elixir Data Designer and a specific DBMS Note that Sun s JDBC ODBC interface is considered a reference implementation hence it is not tuned for performance The Microsoft ODBC Data Source Administrator manages database drivers and data sources To establish an ODBC Connection you need to 1 Click Start gt Settings gt Control Panel Different versions of Windows provide different graphical interfaces to the ODBC administration tools e Ona Windows 95 98 NT PC double click on the ODBC Data Sources 32bit icon to open the ODBC Data Source Administrator e Ona Windows 2000 XP PC double click on the Administration Tools icon and then double click on the ODBC Data Sources icon to open the OCBC Data Source Administrator 2 Clicking the Add button shows the dialog in Figure 3 1 Create New Data Source The appropriate driver can be selected from the list of User Data Sources Click Finish to
131. k box in the data window before the Load Data icon Now the total number of records present in the data source is displayed in the toolbar of the Data Window In this example there are only ten records so Count All Records won t affect the result Finally click the close icon on the right of the toolbar to close the view Note Please refer to Appendix B Samples for the sample files used in this example Chapter 3 JDBC DataSource Java Database Connectivity JDBC is a standard SQL database access interface providing uniform access to a wide range of relational databases JDBC also provides a common base on which higher level tools and interfaces can be built Elixir Data Designer supports reading of data using JDBC and provides an SQL query builder JDBC Drivers There are four categories of JDBC drivers 1 JDBC ODBC bridge plus ODBC driver This driver provides JDBC API access via one or more ODBC drivers ODBC native code and native database client code may be required on each machine that uses this type of driver 2 Native API partly Java driver A native API partly Java technology enabled driver converts JDBC calls into calls to the client API for Oracle Sybase Informix DB2 or other DBMS This driver is similar to the ODBC bridge driver as it requires some binary code to be loaded on each client machine 3 JDBC Net pure Java driver This type of driver translates JDBC API calls into a DBMS independent net proto
132. l records indicate the subtotals and totals for each level of grouping Keep Column Totals As with Keep Row Totals above this option inserts additional subtotals and totals into the output For columns these are added as additional columns in other words the schema contains more fields to hold the additional information On clicking the Next button in the Cube Wizard the Infer Schema screen appears The schema can be inferred by clicking the Infer Schema button You should infer the schema after any changes to the dimensions or measures including changing the measure patterns because these all affect the column names 62 Composite DataSources Working with Cube Processor The averages of frozen and store for the stores in the different states of Mexico have to be compared l 2 EA Add the JDBC data source Stores Add a Composite DataSource named Cube and drag the Stores data source over the diagram Select a filter and place it in the designer Next add a cube and connect the Stores to the filter then to the cube and finally to Result The diagram appears as shown in Figure 4 50 Sample Cube Flow Figure 4 50 Sample Cube Flow Open the filter properties and configure it to keep Country equals Mexico In the Cube Properties go to the Cube Axes page select Country as the cube column and State as the row On the Cube Measures page add a measure to Average the field frozen and add another measure to Ave
133. lds of the primary data source that are not required can be discarded to reduce memory use The fields present in the secondary data source are displayed in the Secondary tab when the data source is connected with the Join processor The fields of the secondary data source that are not required can be discarded Choose the appropriate key or keys in the secondary tab and associate them with the corresponding primary keys Records will be selected from the secondary data source where the identified keys match those of the primary Working with Joins Here s how to join the Sales and Customer data sources 1 2 Add the JDBC data sources Sales and Customer using the procedure given in the previous chapter Add a Composite DataSource with the name Join Select the Sales data source drag and drop it in the designer window Similarly drag and drop the Customer data source onto the designer Create a Join processor on the diagram Connect the Customer data source as primary and Sales data source as secondary to the Join processor and connect the output to the Result The diagram flow is shown in Figure 4 6 Sample Join Flow 25 Composite DataSources Figure 4 6 Sample Join Flow result Inner Join Inner join combines column values from one record of a data source with column values of a record from another or the same data source to form a single merged record of data Using an Inner join on the Sales and Cu
134. le cito fase eesge tweets a tend woe cen eb EE oa E EERE ie seeds 53 4 38 Input AA hus Eear iro r eros ER ovis E OVES EEEo das edeaaseovtedsissthes SAEPE EER SE GOES 54 vi Elixir Data Designer User Manual 4 39 Remove Duplicates Processor eyen prisot vai epes Ea E SOE p EE EERS sess esas sean eegs 55 4 40 Invert Data Proceso is Det edad eee ee 56 4 41 JAVASCLIPE Processor rerien ee haan E E E E a she EAEE E E E dewey 57 4 42 SOL Processor Wizard eiii ade eddie 57 4 43 Sample DataDrop FloW seo eoero ene ap aes pp syn oss E E E tarta tes poewteseabeedeyes vend 58 4 44 Sample DataDrop Sub Elis anh eases Meade ae aces 58 4 45 CUBE Wizard aero intro Arias 59 4 46Add Hierarchy da 60 A A O OS 60 4 48 Cube Measures CCOO ia 61 4 49 Add Measure Dial Bocina lila ae ctven son EA EE E R oun EAA e E NEARE 61 4 50 55 ample Cube Elo Wo eto ir ii si 63 451 Inter Schema CTO woes we noir postiar dins 63 4 32 Cube ReS lt ree a e Radek aed Te Tiel el teeta heed 64 4 53 sample Datastone Elo W draai orrs oon deigsensnqutacs Go Sad observed tad ie EE E E e 64 ASA CN SOUL EE E E E di moved ou teat Acetate iia 65 4 55 EXC O IR 66 4 56 XM OUI UU ses ect iy ils eter IA aa a Das 68 4 37 C stom Jaya Option SCKEEM ees sorio e eer ea Ena EE T R EE E E civb dase a dos 69 4 58 Case Study Composite Diagram eessseeessserrsresrrererrrrrsrrerrsrrsrreresrrresrreresrrerrrreseret 72 4 39 VIEW Cube Output sn woke rocas N Ee Seabee ng unos sp
135. le matching secondaries a If multiple matching secondaries Repeat 1 Return to the same Join diagram and edit the Join properties by selecting the option Keep from the If no matching secondary and Repeat from the If multiple matching secondaries Combo box 2 Enter pri in the Primary Prefix text box and sec in the Secondary Prefix text box 3 We will leave the secondary tab as before linking the customer_id fields between primary and secondary 4 Click the Finish button and view the Result output The output is shown in the Figure 4 9 Outer Join Result 27 Composite DataSources Figure 4 9 Outer Join Result Designer Script Data B Ad 8 Pa Showing 500 records pricustomer pristore_id pristore_sal seccustom secgender secmarital_ 3 15 2 76 3 F M 3 15 2 79 3 F M 3 15 3 36 3 F M 3 15 4 26 3 F M 3 15 4 46 3 F M 3 15 48 3 F M 3 15 4 96 3 F M 3 15 5 32 3 F M 3 15 5 56 3 F M 3 15 5 76 3 F M 3 115 6 39 3 F M 3 115 6 81 3 F M 3 15 6 84 3 F M 3 15 7 35 3 F M 3 15 8 88 3 F M 3 15 8 91 3 F M 3 15 9 33 3 F M 5 14 1 08 5 F S 6 15 5 44 6 F S This query fetches all primary records Each primary will be output as many times as there are matching secondary records However if there is no matching secondary record the primary record will still be output once with the secondary fields s
136. lex conditions using standard JavaScript Working with Filters 1 Add a new Composite DataSource named Filter 2 Add the JDBC data source for Customer and Stores by dragging and dropping them over the Composite diagram 3 Place the Filter processor on the diagram 47 Composite DataSources 4 Connect the Customer data source to the Filter processor and connect the output of the Filter to the Result The designer window appears as shown in Figure 4 29 Sample Filter Flow Figure 4 29 Sample Filter Flow ES gt a an i Filtering records by setting the When condition in Filter tab window To start let s filter the records of married customers with customer_id less than 11 l 3 Open the Filter Properties and in the row corresponding to customer_id field select Less Than from the When column Enter the condition as 11 In the Marital Status row select Equals from the When column and enter the condition as M After setting the field properties the Wizard appears as shown in Figure 4 30 Filter Result Figure 4 30 Filter Result Designer Script Data Result customer_id gender marital_status 1 F M 3 F M 4 M M 7 F M 8 M M g M M Click Finish button and review the Result data Notice that only the records of married customers with customer_id less than 11 are shown Filtering records using the JavaScript functio
137. ll 1 Modify the previous example by selecting the All option from the Group On Combo box The output is shown in Figure 4 17 Group on All Result It is seen that all the records irrespective of the value of customer_id are grouped together in descending order This form of grouping is useful when you want to perform subsequent group operations using other tools in the Elixir toolchain for example a group based chart can show results collected from all records Figure 4 17 Group on All Result Script Data Designer customer_id store_id store_sales Integer Integer Double 4 76 48 5 28 6 28 8 84 1 86 1 92 c Group On Range Numeric data 1 Modify the previous example by selecting the Range option from the Group On Combo box Enter the value given below in the Range text box lt 10274 a lt 10275 b lt 10277 c 36 Composite DataSources The output is shown in Figure 4 18 Group on Range Result The records are grouped into a b and c based on the values of the customer_id in ascending order Where a group includes all records less than or equal to 10274 b group includes all the records less than or equal to 10275 and the c group includes all the records less than or equal to 10277 Figure 4 18 Group on Range Result Designer Script Data
138. lows realistic looking Singapore ICs to be generated The first letter will always be S S gt S the second leter will be either 1 or 2 1 gt 2 subsequent digits might be anything 0 gt 9 finishing with an uppercase character A Z representing the checksum which of course is random so it won t usually be correct Similarly String Range Mr Aaaaaaa to Mr Zzzzzz allows random names Of course you could separate Title as a set of Values see later Dr Mr Ms etc for even more flexibility 127 Random DataSource Doubles and Floats To generate a random floating point number between 1 and 10 you can enter First 1 and Last 10 as you would expect You will get a random number with the full precision of the data type eg 5 183456738923 If you want a number that represents an amount you might want something like 5 18 instead To obtain this specify the number of decimal spaces in the First and Last strings First 1 00 Last 10 00 will give you floating point numbers but only to two decimal places This option only works for up to four decimal places Thus if you want a full precision decimal in the range 0 5 to 1 5 you should specify First 0 50000 Last 1 50000 the five decimal places exceeds the limit of four so full precision will be used If you use 0 5 to 1 5 you will only get one decimal place of precision as described earlier Values The alternative to a range is to specify allowed values these should be comma separat
139. m Java DataStores The DataStore provides support for Command Line Invocation which allows Scheduling with any third party Scheduler such as Window Task Scheduler as well as an API for Application Integration If grouping is performed on a data source and the Excel DataStore is generated from it then the grouped data can be saved to multiple worksheets based on the grouping level The DataStore also supports XML post processing with an XSLT transformation An XSLT file contains a template that can transform the generated records into any textual output format The DataStore is selected from the menu bar of the Designer window and then placed on the diagram The DataStore Wizard consists of two screens In the first screen the name of the DataStore must be entered in the text field The DataStore type to be generated is selected from the list in the combo box The output fields of the data source are displayed in the DataStore Wizard The URL and the other settings of the file to be generated will be entered on the second screen of the DataStore Wizard The settings on the second screen vary with each output type To explore the use of DataStore connect one directly to a DataSource and generate the different outputs A sample is shown in Figure 4 53 Sample Datastore Flow Of course usually there will be a sequence of processors that act on the records as they pass through Figure 4 53 Sample Datastore Flow data ED PEE y DataStor
140. modification the records are still sorted in descending order but now the bottom 25 of the records are retained That is out of 24 records only 6 records are displayed Sorting records by applying Group On Options a Group On Each Value 1 Using the same sort diagram as the previous section open the Sort properties and remove any previous sort and extract options 2 In the first screen of the Sort Wizard click the Add button The Sort dialog box appears 3 Select the customer_id from the list of field names available in the Combo box 4 Select Descending Sort Order 5 Select the Each Value option from the Group On Combo box The dialog box appears as shown in Figure 4 15 Sort Dialog On clicking the Ok button the sort column gets added to the Sort Wizard Figure 4 15 Sort Dialog Name customer _id Sort Order Descending Group On Each Value Derived Field Ok Cancel 6 Click the Finish button in the Sort Wizard and view the Result The output is shown in Figure 4 16 Group on Each Result Records with the same consecutive values of customer_id are grouped together in descending order 35 Composite DataSources Figure 4 16 Group on Each Result Designer Script Data customer_id store_sales 10277 4 76 10277 4 8 10277 5 28 10277 6 28 10277 8 84 10275 1 86 b Group On A
141. n 1 Add a Properties DataSource named Prop_Emp1 2 Add a column called Department with type String and value Electronics 3 Add a column called Salary with type Integer and value 7500 The values are shown in Figure 7 8 Prop_Emp1 Figure 7 8 Prop_Emp1 Datasource Wizard Define Properties Datasource Enter Properties datasource parameters Description Prop_Emp D Column Name Type Value 1 Department String Electronics 2 Salary Integer 7500 Previous Finish Cancel 4 Click the Finish button to add the data source to the repository 99 10 11 12 13 14 Properties DataSource Add a Composite DataSource called Comp2 Open the Composite diagram and and drag the Employee ds onto it Add a Filter processor to the diagram as well Connect the output of the Employee DataSource to the input of the Filter processor Open the filter properties In the row corresponding to Dept_Name field select Equals option from the combo box of When column Enter the condition as Department In the row corresponding to Emp_Sal field select More Than option from the combo box of When column Enter condition as Salary After entering the values the page appears as shown in Figure 7 9 Filter Wizard Click the Finish button Figure 7 9 Filter Wizard Da Filter Wizard E Filter Choose f
142. n connectto the Oracle database as a DBA without a password T a l EEE icrosoft SharePoint role admin for web http Mercury Servers in this group can access remote access properties of users Designated administrators of the schema 121 Chapter 11 Filesystem DataSource Overview The Filesystem DataSource allows files to be processed as records This is useful for evaluating filesystem usage or determining which files to use as data sources For example an XML DataSource could use a parameterized URL to identify the file to load This data source may be fed URLs by the Filesystem DataSource in conjunction with the Properties processor to iterate over all XML files in a directory and build a concatenated set of records from the set of files identified Filesystem DataSource Wizard The Filesystem DataSource Wizard is shown in Figure 11 1 Filesystem DataSource Wizard Figure 11 1 Filesystem DataSource Wizard LF Datasource Wizard XK Define FileSystem Datasource Enter FileSystem values Name Sample Filesystem Description This is a sample filesystem that extracts the files in temp Root CiTemp Strip Prefix y Recurse into subdirectories Previous Next Finish Cancel Name Enter the DataSource name in the text box This should be a unique name Description Any extra description that is used to describe the data sourc
143. n in Figure 4 31 Concat Wizard Figure 4 31 Concat Wizard ET Concat Wizard Concat Choose concatenation type and order of output Name Concat Color Schema Union v reviou Next Finish Cancel The Schema combo box contains the Union and Intersection options to choose which fields are passed through to the output The data sources that are connected to the Concat processor are listed in the window list box The Up and Down arrows are used to move the selected data source up or down to change the order in which the records are concatenated Working with Concat Processor As an illustration let s concat the Stores and Sales data sources 49 Composite DataSources 1 Adda Composite DataSource named Concat 2 Select the Sales data source and drag and drop it onto the diagram Repeat for the Stores data source 3 Add a Concat processor to the diagram 4 Connect the Stores data source and Sales data source to the Concat processor Then connect the output of the Concat to the Result The diagram appears as shown in Figure 4 32 Sample Concat Flow Figure 4 32 Sample Concat Flow y eren dems Lm Combining all the fields of the data sources using the Union option 1 Open the Concat Properties and select the Union option from the Schema combo box 2 The names of the data sources are listed in the window provided below th
144. n is available The src should be this DataGroup DataGroups delimits a set of sorted records into groups DataGroup int level String name Construct a DataGroup at a particular level starting at one If groups are nested then child groups would be at level two etc The name may be any String the use depends on the DataL istener For example an Excel DataStore will use the group name as the name of the Sheet int getLevel Retrieve the group level int getName Retrieve the group name DataRecord A DataRecord supplies an array of objects conforming to the DataSource schema to the DataListener The ObjectDataSource supplies a method newRecordInstance to instantiate a record with the appropriate structure Note that records cannot be reused once a record has been passed to the DataListener you cannot modify it and pass it again Object getData Get the array backing this DataRecord so that you can set values before passing it to the DataListener You should set the values in the array according to their order in the schema zero based Working with Object DataSources There are two files namely the Company java and the Employee java file Here s how these files are to be used for providing data and the Iterator method while adding the Object Data Source 1 Review the sample files Employee java and Company java Both the files need to be compiled and jarred The jar has been created for you called ObjectDB jar
145. ng which range of cells to access ll Before adding the Excel DataSource we need to ensure the ranges are defined in the Excel file Open the Empdata xls file and select Name gt Define under the Insert menu The Define Name dialog box pops up Enter name as Emp1_All and enter the range as given below in the Refers to text box and click the Add button Sheet1 A 1 D 11 Enter name as Emp2_All and the range as given below in the Refers to text box and click the Add button Sheet2 A 1 D56 Enter name as Emp_All and the range as given below in the Refers to text box and click the Add button Sheet1 Sheet2 A 1 D 11 Click the Ok button in the Define Name dialog window and save the Empdata xls file Launch Elixir Repertoire and add a new Excel DataSource called Emp Range and enter the location of the Empdata xls file as the URL Select the First Row Header check box and enter the Range as given below in the text box Range Emp1_Al11 Click on the Next button In the screen that appears click the Infer Schema button When the Dynamic Parameters dialog pops up enter any valid range name from those specified above and click the Finish button The schema will be inferred Click the Finish button to add Emp Range ds to the repository After saving the DataSource opens On clicking the Load Data menu in the data window the Dynamic Parameters window appears You will notice that the text box contains the default value Emp
146. nly contain records where the store_country field has the value USA Derivative e Select the Derived tab and click the Add Column button to invoke the Add Column dialog box e Enter name as grocery_meat_sum Select Data Type as Integer Enter value as given below Amos ely mel e On clicking the Ok button the column is added to the wizard We ve defined a new column grocery_meat_sum which contains the sum of grocery and meat fields Join 2 e In the Options tab window enter If no matching secondary Discard and if multiple matching secondary Repeat 72 Composite DataSources e Choose the Secondary tab and select the store_id field in the primary column against the store_id field of the secondary data source and click the Finish button Again we have used an inner join so records are only fetched based on a match with the secondary input Because the secondary input filters out all countries except USA the Join will discard all non US customer sales from the primary datasource Cache e Enter the File name as Cache Sample e Select Cache from the Expiry combo box and choose days from the combo box and enter 1 in the text field Once the data is loaded into the cache it will be retained for one day This means the preceding flows including joins and derivatives will be performed at most once per day The join operations may be expensive and use a lot of RAM so caching the output will improve efficiency in terms of both per
147. ns Now let s find stores in Mexico with store_id less than 10 For a change we will use JavaScript though this can be solved just as easily with the Filter tabs 1 Ze Disconnect the Customer datasource and connect the Stores datasource to the Filter input Open the Filter Properties and remove any existing filter criteria Then select the JavaScript tab window Enter the following JavaScript syntax store_id lt 10 amp amp Country Mexico Note that because this expression is entered as a String you can use dynamic parameters to customize the expression at runtime Click the Finish button and review the Result data Notice that the records corresponding to Mexico with store_id lt 10 are fetched 48 Composite DataSources Note If in the above syntax instead of amp amp the Il symbols are specified then all the records corresponding to Mexico and all the records having store_id lt 10 are fetched Concat Processor m The Concat processor is used to concatenate the records of two or more data sources If the schemas of the inputs are different you can choose either the union of schemas fields which don t exist in a particular input will be null or the intersection of schemas only fields which exist in all schemas will be retained The Concat processor is selected from the menu bar of the Designer window and then placed in the designer window workspace Properties The editable properties are show
148. o cece cc ence ence ence ence rE E seca seen IER esas 120 10 62 Organization Resulltsc 503 a a nas tadas das SAE a E Ea AE A S E E ENAS 121 11 1 Filesystem DataSource Wizard oocooocnnccnnccnncnnnconccnnconnconnccnnccnncnnnconncnnnrnnncnnrnnnraninnnos 122 12 1 Tabular DataSource Wizard onore seed emye n e e e e sommes a E a 124 12 2 Tabular DataSource Wizard Page TWO ooocoooccnnconnconoconconocnnncnnccnnconaronccnnconnccnnccnnions 125 13 1 Random DataSource Wizard 0sereseverereveversversnevensravereseveneravenstevenesevenesevenes 126 13 2 Beit COMIN dd eet eed at OU seed hed att ol 127 14 1 ARFF DataSource Wizard 0 0 c cccsetesesenovcrstoscesesorentecvopotenecoeevensberstsvcsepevsussovetes 129 14 2 Define AREF Schema coi dd eee eos Toe E ee 130 15 1 Cache DataSource Wizard veritas irte tira 131 15 2 Cache DataSource Parameters coocoocnoccoconcnoconcnoroncnoroncnoroncnoroncnnroncnnroncnnroncnnroncnnnos 132 viii List of Tables 4 1 Filter Criteria Chapter 1 About Elixir Data Designer Introduction Elixir Data Designer is a tool used for creation and manipulation of data It allows direct interaction with the data flow through a diagrammatic interface Users of IT systems face common problems e Growing volume of data IT and business users are required to handle large volumes of data every day Manual processing is both time consuming and error prone leading to inefficient operati
149. ons and an accumulation of mistakes e Proliferation of data Business users work with different types of databases or applications This leads to more complexity in data retrieval and manipulation The solution to these problems is to personalize the data sources so that it becomes easier to manage them Elixir Data Designer is a powerful tool that can perform extraction aggregation transformation and loading of multiple data sources Further since the designer provides a visual method of interaction with these data sources a large amount of time can be saved while developing data manipulation strategies Elixir Data Designer provides on demand data for business users As this software is directly deployable from the web it can be easily accessed by business users to satisfy their everyday needs The tool also provides a flexible scripting mechanism allowing an IT support team to build custom solutions that satisfy complex requirements Through direct manipulation and inspection of data sources and flows a coherent view of the overall data architecture can be gained Time spent developing custom codes and programs can be avoided by using the tool to formulate the data processors and intercept the flow at any stage to ensure correctness Finally the output can be generated into a variety of different data formats including Microsoft Excel relational database tables and Elixir Report Designer Features of Elixir Data Designer Elixir D
150. ow you could move the Stop expression to the Start expression and now only records after the Start matches would be processed The Text DataSource can support the extraction of multiple chunks by using combinations of start and stop 85 Chapter 6 XML DataSource Overview In Elixir Data Designer data in XML files can be accessed by adding an XML DataSource The basic requirements to access the XML document is a well formed XML document and a XPath that is used to query the XML document locate and retrieve data The text data sources with fields having different date time and timestamp formats can be added Furthermore the URL of the files can be specified in the form of an XQuery to retrieve data from XML databases like Software AG Tamino or EAI solutions with XML output like BEA Liquid Data XPath XPath is a W3C specification for retrieving data from an XML file It is a query language for XML Elixir Data Designer provides an XPath Builder The features are given below e It provides a tree view structure of the XML source e Specifies the target records with Root XPath e Specifies columns to be added by dragging and dropping from the XML tree e The column name or the XPath of the columns can be changed e It provides full support for XPath The XML tree structure appears as shown in Figure 6 1 An XML Tree Figure 6 1 An XML Tree Absolute A B Relative Cc Here A is the main element having su
151. ppears 3 Select birth_date from the list of field names available in the combo box 4 Select the sort order as Descending from the combo box 5 Select the Year option from the combo box Enter the value 2 in the Group Interval text box The Sort dialog box appears as shown in the Figure 4 20 Completed Sort Dialog Click the OK button The sort column gets added to the Sort Wizard 38 Composite DataSources Figure 4 20 Completed Sort Dialog Name Sort Order Group On Group Interval 2 Derived Field birth_date Descending Year 6 Click the Finish button in the Sort Wizard and view the result The records are grouped based on the years and sorted in descending order So records having birth_date as 1978 1979 are grouped together followed by 1976 1977 and so on Derivative Processor oJ The Derivative processor is used to derive one or more new columns through computations on existing fields present in the data source The Derivative processor is selected from the menu bar of the Designer Window and then placed on the diagram Properties The editable properties are shown in Figure 4 21 Derivative Wizard Figure 4 21 Derivative Wizard Derivative Wizard Derivative Add items items to the derived schema Name Derivative Colour Base Derived JavaScript Column Name Discard
152. r to test whether the connection to the database is available A window will pop up to indicate if the connection has succeeded or failed JDBC DataSource Figure 3 8 Connection Pool Wizard Connection Pool Wizard Define JDBC Connection Enter JDBC datasource parameters Name required Name Description JDBC Driver Suggestions URL User Password v Hide Password Test Connection Previous ex Finish Cancel The information required on the first page is exactly the same as that on the JDBC wizard described above If you are creating a pool for use on a server the warning Driver class not found means the class isn t found on the local classpath the JDBC driver may already exist on the server You can still proceed despite the warning and you will need to verify the server classpath eg the ext directory of the Elixir Server software contains the necessary driver for your chosen database Note Dynamic substitutions such as password are not available in the Connection Pool because it may be shared by all users of the software and there is no common context from which the dynamic parameters could be obtained The next page of the Connection Pool Wizard allows the characteristics of the pool to be controlled The Connection Pool is built upon c3p0 http www mchange com projects c3p0 index html Max Idle Time s The time in seconds
153. rage the store field Click the Next button and infer the schema The screen appears as shown in Figure 4 51 Infer Schema Screen Click the Finish button Figure 4 51 Infer Schema Screen Cube Wizard Cube Schema Cache Manage cube schema cache Column Name State String Mexico Average frozen Double Mexico Average store Double Previous Next Finish Cancel From the Cube popup menu choose View Cube The output appears as shown in Figure 4 52 Cube Result The averages of frozen and store for different states in Mexico are displayed 63 Composite DataSources Figure 4 52 Cube Result pa Mexico Average frozen Average store Average frozen Average store 15433 532100415925 30376 5 5433 532100415925 30376 5 DF 8435 38421846446 36509 0 8435 38421846446 36509 0 Guerrero 3670 8061508558662 23593 0 3670 8061508558662 23593 0 Jalisco 5751 292690190152 24597 0 5751 292690190152 24597 0 Veracruz 5062 189490347367 34791 0 5062 189490347367 34791 0 Yucatan 6393 478221949674 30797 0 6393 478221949674 30797 0 _Zacatecas 4718 368677173295 30908 333333333332 4718 368677173295 30908 333333333332 DataStore Processor 2 A DataStore is an output mechanism used to generate the processed data into different file formats The different file types supported in Elixir Data Designer include XML Excel CSV JDBC and Custo
154. re 9 2 Guided JavaScript Builder J Guided JavaScript Builder X JavaScript Generator Select the class and method that will provide the iterator Data Iterator Provider required Data Iterator Provider Method Iterator Type Method Return Type Previous Next Finish Cancel Click on the Next button In this screen the data provider class of the Object DataSource is selected On selecting the Data Class from the tree the class file name is automatically entered in the Data Class text field Finally on clicking the Finish button the fields names and data type of the selected class are automat ically added to the schema definition table By clicking the Next button you can see the JavaScript that has been auto generated Manual Creation By clicking the Add Column button you can enter the Data class field name and select the corresponding data type from the combo box in the Add Column dialog window On clicking the OK button the column will be added to the Schema Definition table You then proceed onto the next screen Figure 9 3 JavaScript Editor Here you can manually enter the JavaScript code that contains the Iterator method to access the fields in the data source The Method Browser window lists all the methods and return types from which the methods to be included in the code can be selected and included manually in the JavaScript code 110 O
155. records where CompanyName is B regardless of the value of Fruit 46 Composite DataSources Table 4 1 Filter Criteria When Condition Remarks e Equal Not Equal More Than Not More Than Type Comparable types String Numeric Date Timestamp This condition uses a simple comparison of values which must both be of comparable types For example strings can be compared with strings but not with dates Numeric Date Timestamp with as separator Example 1998 01 01 1998 12 31 Less Than Not Less Example Than 10 1961 08 26 e Matches Not Matches Type This condition allows a regular expression to be used Use of operators like for OR String and amp amp for AND relationship This example will match USA or Mexico strings Example USIMex e In Range Not In Range Type In DataSet Not in DataSet Type Matching field type from another data source Example See remarks repository Sample DataS ource ds Field1 This retrieves the matching field from another data source for filtering values Null Not Null Nothing to enter Check whether if there is no content Note that zero value is not considered null e JavaScript Not JavaS cript Enter a script condition by specifying the matching type Example Field1 5IField2 USIMex where Field1 is numeric and Field2 is string This is useful for enter multiple conditions or comp
156. repare for receipt The src should be this the ObjectDataSource itself This method is called to indicate that the subsequent records are part of a group This method should not be called if the data is not already sorted and grouped Where data is grouped groups may be nested within other groups but all records must be within the innermost groups You are not allowed to have a group that contains both records and child groups Similarly if the top level is grouped it can contain no records outside of those groups Each record that the data source supplies is passed to the listener through this method call Usually the method will return true If the method returns false then it indicates that the listener does not want to receive any more records In this case you can choose to stop sending records additional ones will just be discarded anyway but must send the necessary endGroup and endData calls to gracefully terminate the operation The same applies if exceptions are caught you should still send the necessary symmetrical endXXX calls to balance the startXXX calls you have already made This method is called to indicate the end of a group Each call to startGroup should be matched with a corresponding call to endGroup after the necessary records have been processed 112 Object DataSource void endData IDataSource src This method must be invoked after sending all records and groups to indicate that no more informatio
157. ring store Double grocery Double frozen Double meat Double HoOoODoOooos Cancel Next Finish Previous The next step is to identify which of the old columns to keep those that should be mapped into the new inverted structure The resulting dataset will contain only as many records as are marked Keep Because each row becomes a column when inverted the system will need to determine the appropriate column type in the output schema If the selected columns are all of the same type then this type will be used Alternatively if the selected columns contain mixed types then the String type will be used in the output schema to ensure all values can be represented As an example assume a simple table like this Gil pal ON W WO o Sas Where A B C and D are the column names If we choose InvertColumn B FieldName Field Keep A D then the output after Invert Data will be Beli 2 6 A ie D 4 8 As you can see because two columns were selected for keeping there are two records in the output The old column names are assigned to be the values of the new column called Field the FieldName value and the unique values of the B column 2 and 6 become the column names in the new schema Javascript This task allows the user to perform any kind of record operation or schema re arrangment which might be too complicated for regular flows With this processor
158. rrect in this case If your values are integers or dates etc you can adjust the data types or any of the other information here Figure 6 8 XML DataSource Schema Define XML Datasource Enter XML datasource schema or use the XPath builder XPath Builder Root XPath TelephoneBook Telephone i Column Name Type XPath Merge 1 EntrylD String EntrylD Space S L ddressiStreet Space 4 city Sting AddressiCity Space When the wizard has closed the new data source will be loaded into the workspace You can test it by clicking the Load Data button The output should appear as shown in Figure 6 9 DataSource Results 92 XML DataSource Figure 6 9 DataSource Results LoginName Street Ci 127 Wehner127 Robert Stol Robert Stol Liebigstr 15 Liebigstr 15 Seitersweg Seiterswedg Weckbach Heinheime Heinheime Wedel207 Haardtring Haardtring Weden213 Vilbeler We Vilbeler Wye Mettegang Mettegang Robert Stol Robert Stol 93 Chapter 7 Properties DataSource Overview The Properties DataSource is used to provide a single record of data usually either to a Parameters processor or it can be used as a direct data source The Properties DataSource can also be used to check the flow of data Properties DataSource Wizard The Properties DataSource Wizard appears as shown in Figure 7 1 Properties
159. s Next Finish Cancel In this window the Regular Expression can be entered in the Regular expression text box Alternatively by clicking the button to the right of the text box a separate designer window appears as shown in Figure 5 5 Regular Expression Designer Figure 5 5 Regular Expression Designer ET Regular Expression Designer RegExp Syntax status The Regular expression is entered in the RegExp text box and the Test button is clicked to test the validity of the expression If the expression is not valid then syntax errors are displayed If there are no syntax errors then the fields are displayed in the window Finally on clicking the Ok button the expression is assigned in the Regular Expression text box Regular expression processing requires the entry of up to three regular expressions The first one named Regular Expression is mandatory This selects which records are included in the result and how 79 Text DataSource the field values are extracted from those records For example a regular expression such as 4abc will only pass through those records where the line begins with abc marks the beginning of the line and extract a single field matched by the rest of the line meaning a group which contains zero or more characters Similarly will extract two fields separated by a comma In this case all lines will be used as there is no filter cr
160. se color dialog box is invoked from which the color of the Result component can be selected All of the graphical shapes allow editing of their names and colours in this way Standard Diagram Operations Moving the processors Shapes on the diagram can be moved by dragging them with the mouse Alternatively you can use the cursor keys to move the selected elements Using a combination of Alt cursor keys gives smaller increments Flow Connection Nodes New nodes can be added to the flow by selecting Add Node from the flow s popup menu You can add a number of nodes to your flows to neaten the appearance of your diagram 21 Composite DataSources Data Processors Data aggregation and transformation is performed by making use of the Data Operation processors The Data Operation processors that are available in Composite designer environment are D ym D a n gt B DataSource Flow Join Sort Derivative Filter Concat Parameter qP Q a D gt O a SubFlow Note Processor DataDrop Cube DataStore Each processor and connector provides a popup menu and editable properties which can be accessed either through the popup menu or by double clicking the graphic on the diagram DataSource Processor gt The DataSource processor forms the input to the designer environment This processor provides a diagrammatic representation of the different types of data sources available in the data designer The dat
161. se will be the path to Server log you can use the Open dialog to find it Select Regular Expression as the Access type and click the Next button The Define Text DataSource screen appears The Regular Expression details are entered in this screen Click the button on the right of the Regular Expression text box The Regular Expression Designer Window appears In the RegExp text box enter the Regular Expression as given below 1481 3 0 This breaks the log into two chunks The first part 1 reads up to the first colon The second part reads everything else There is an explicit colon in the middle so this is not part of either chunk See a regular expression reference for more help in interpreting this syntax Click on the Test button to check the syntax status If there are no errors in the syntax then the columns are displayed in the window of the designer as shown in Figure 5 10 Regular Expression Designer The first column shows the original line the subsequent two columns in this case shown the portion of text that matches the groups in the expression On clicking the Ok button the syntax is assigned in the Regular Expression text box Figure 5 10 Regular Expression Designer E Regular Expression Designer RegExp Syntax status A B NOQUEUE 200 19 NOQUEVE 200 196 165 199 did n NOQUEUE 222 77 NOQUEVE 222 77 123 18 did not NOQUEUE de2424 NOQUEVE de242
162. sed in the Elixir Repertoire manual The data designer contributes new views to the Workspace The workspace of the basic data sources like Excel DataSource Text DataSource etc consists of a Data tab panel as shown in Figure 2 1 Basic Data Workspace Figure 2 1 Basic Data Workspace E Elixir Repertoire Joe Elixir Repository D amp lt ElmirsamplesDataSourceFFruitSale 2 m amp ElixirSamples ajj Data amp DataSource 3 ChartData ds Result Count All Records o amp Chinese 3 Composite Sa 3 CompositeErr 3 CompositeErr 3 CompositeErr J CompositeEm _ The Data tab provides the following functions e Load Data Initiates the data flow to process the data source records and render the output as a table within this data tab e Save Data Initially the Save Data icon is disabled It is only activated after data has been loaded Using this option data can be saved in different file formats e Print Data Initially the Print Data icon is disabled Once data has been loaded the option is enabled Using this option the data table can be printed out e Copy When a data table has been loaded rows can be selected for copying to the clipboard The set of data copied can be pasted into an editor and saved as an XML file e Count All Records This option is used to display the total number of records present in the data so
163. so it is skipped but Bill First Bill Second and Bill Third are passed through Finally we reach a line which matches the Stop expression so subsequent line processing is turned off which means None Nothing is discarded To summarize five records each containing two fields are extracted Joa mirst Jom Secomeo Wali mirst BL Secoimel Bill Third The Start Expression is entered in the Start Expression text box Alternatively on clicking the button to the right of the text box the Regular Expression Designer Window appears The Start Expression can be entered in the RegExp text box tested and added The Stop Expression is entered in the Stop Expression text box Alternatively on clicking the button to the right of the text box the Regular Expression Designer Window appears The Stop Expression can be entered in the RegExp text box tested and added The Infer Schema button is used to infer the text data source columns based on the number of groups in the regular expression Working with Text DataSource In this section we will discuss the different ways of adding the text data sources Using Separator Characters If you are given a text file in which the fields are enclosed within quotes and are separated by semicolon you can easily add it as a datasource In this example we will use the EmpInfo txt file from the Elixir Data Designer samples 1 Choose a file system or folder and use the popup menu to select Add gt D
164. ssing information directories such as organizations individuals phone numbers and addresses LDAP servers store data in a Directory Information Tree which is a hierarchical grouping of related data The LDAP Data source driver is built on JNDI API to provide access to the directories It provides the user the ability to filter and extract LDAP entries LDAP is based on the X 500 standard but is significantly simpler Unlike X 500 LDAP supports TCP IP which is necessary for any type of Internet access A typical LDAP server is a simple network accessible database where an organization stores information about its authorized users and what privileges each user has The standards are specified in RFC 1777 LDAP DataSource Wizard The LDAP DataSource Wizard is shown in Figure 10 1 LDAP DataSource Wizard Name Enter the DataSource name in the text box This should be a unique name Description Any extra description that is used to describe the data source can be entered in the Description text box Host The host name or IP address of the server is specified here Base The Base sometimes called Distinguished Name DN is used to uniquely identify entries in LDAP The base is always a fully qualified name that identifies entries starting from the root of the LDAP name space as defined by the server The following string type attributes represent the set of standardized attribute types for accessing an LDAP directory The Base c
165. stomer data sources you can explore the result of setting different options for the If multiple matching secondaries a If multiple matching secondaries Repeat 1 Using the flow described above open the Join properties and select the Discard option from the If no matching secondary Combo box 2 Select the Repeat option from the If multiple matching secondaries combo box 3 Select the secondary tab Select the customer_id field in the primary column against the customer_id field of the secondary data source as shown in Figure 4 7 Join Wizard Figure 4 7 Join Wizard yy Join Wizard Join Identify fields to equate in the joined schema Name Join Options Primary Secondary customer_id Integer gender String marital_status String Column Name Type Discard Previous Next Finish Cancel 4 Click the Finish button to close the Join Wizard and return back to the Designer window 5 Select the Result and choose View from the popup menu The output is shown in the Figure 4 8 Inner Join Result 26 Composite DataSources Figure 4 8 Inner Join Result Designer Script Data Result gt oS Showing 500 records pricustomer pristore_id pristore_sal seccustom secgender secmarital_ 3 15 2 76 3 F M 3 15 2 79 3 F M
166. t Data Source will be added to the repository Object DataSource API The pushTo method is the engine that drives all data source record generation It takes two parameters cxt a PushContext object and d1 a DataListener object This section will describe the available methods provided by these two objects and the supporting objects that they depend on PushContext The PushContext provides useful methods for other data sources but is unlikely to be useful within JavaScript as the functions it provides can be done directly String getParameter String name String substitute String s DataListener Get the value of a parameter You should use substitution in JavaScript instead of calling this method as substitutions are detected and will be included in prompts whereas calls to getParameter will not Performs substitution replacement on an input string Again this is not useful within JavaScript as you can use substi tution strings directly in the code A DataListener receives data from a DataSource for subsequent processing For Java use DataListener an interface and all the classes referenced by it are in the com elixirtech data2 package The methods listed are all public void startData IDataSource src void startGroup DataGroup group boolean processRecord DataRecord record void endGroup DataGroup group This method must be invoked before sending any records or groups to allow the listener to p
167. ted and the group interval is entered as 2 then grouping output is Monday Tuesday Wednesday Thursday Friday Saturday and finally Sunday That is the records corresponding to the fields having Monday and Tuesday as the days of the week are grouped together and so on If suppose the day of the week grouping has to be done for a particular interval of year and months then the years are grouped first followed by the month grouping Finally the Day of Week grouping is done The Hour Minute and Second options are listed in the combo box only if the date field selected for grouping contains time or if the time field is selected for grouping Hour When this option is selected and a value is entered in the Group Interval text box for a particular field then the records are grouped based on the hours For instance if the Hour Group On option is selected and the group interval is specified as 3 then the output is 0 2 3 5 etc Where 0 2 returns the records corresponding to the first 3 hours of the day starting from the 12th hour and 3 5 groups the next three hours of the day Minute When this option is selected and a value is entered in the Group Interval text box for a particular field then the records are grouped based on the minutes As there are 60 minutes in an hour the grouping is done accordingly For instance if the Minute Group On option is selected and the group interval is specified as 4 then the output is 0 3 4 7 etc Where 0 3 returns the
168. ten used close to the Result end of the flow so that they can propogate parameters to all upstream processors Properties 50 Composite DataSources The Parameter properties are shown in Figure 4 33 Parameter Wizard Figure 4 33 Parameter Wizard Parameters Wizard Parameters Choose name and colour Name Lookup Colour _ iterate Previous Finish Cancel Working with Parameter processor Let us filter records based on a parameter which is read from a different datasource 1 Create a Properties DataSource named SamplePropert y 2 Click Add Column and in the dialog enter field Name as FruitChoice Data Type as String and the Value as Orange Click the OK button The column is added to the DataSource Similarly any number of fields can be added After adding the fields the DataSource Wizard appears as shown in Figure 4 34 Completed Properties DataSource Click the Finish button For more information on working with Properties DataSource refer to Chapter 7 Properties DataSource 51 Composite DataSources 10 11 12 Figure 4 34 Completed Properties DataSource ET Datasource Wizard Define Properties Datasource Enter Properties datasource parameters Name SampleProperty Description Column Name Type Value 1 FruitChoice String Orange Previous Finish Cancel Add a Tabul
169. text box and any extra description that is used to describe the data source can be entered in the Description text box The format of date time and timestamp strings in the XML can be specified By default the date time and timestamp formats follow ISO standards If the date and time fields in the XML file follow a different format then that format has to be defined in the appropriate text boxes XML data can be supplied in one of two ways You can either enter the URL of an XML file or enter the XML directly into the wizard The URL approach should be preferred when the XML contents is large Where both URL and XML are specified the XML value will take precedence 87 XML DataSource Note Both the URL and XML entries allow for substitution to be used For XML this allows the XML contents to be passed in through dynamic parameters for example from a report To do this set the XML text to something like XML and then supply the XML parameter at render time There are two ways by which the columns in the XML file can be added There is a main Root XPath which allows you to specify from which node in the tree you would want to start your traversal The XPath is used to specify the XPath of the node with respect to the Root XPath Method 1 In this method the columns in the XML source can be added by clicking on the XPath Builder button shown in Figure 6 3 Choosing the XPath Builder Figure 6 3 Choosing the
170. th ROOTIA 1 2 3 Field AID BID XPath id Bi id Ci id Merge No Merge No Merge Space Al B1 C1C2 A2 B2 C3 C4C5 A3 B3 C6 C7 However use of Merge Group will add extra records to ensure each C id remains distinct This is shown in Figure 6 5 Merge Group 89 XML DataSource Figure 6 5 Merge Group Root XPath ROOTIA 1 2 3 4 Field AID BID Ic CID XPath id Bi id ic Merge No Merge NoMerge Group Space A1 B1 0 c1 at B1 4 C2 A2 B2 0 ci A2 B2 11 c4 A2 B2 2 c5 A3 B3 0 C6 A3 B3 1 C7 There are now two A1 records because there are two C nodes grouped within A1 Note that the value of the Group row is a zero based index of the number of the record within the parent Subtree Optimization The XML DataSource provides an option for processing huge XML files By default the entire XML Document is loaded into memory as XPath can only work fully when the tree is available As this is not practical when the source files are large there is a special Subtree Optimization mode that can be used instead When Subtree Optimization is enabled the XML file is read and processed sequentially This mode only works when the Root XPath expression is a simple absolute path to the root of the subtree that represents a record For example data customer record is an absolute path this will work However record which is
171. that a Connection can remain pooled but unused before being discarded Zero means idle connections never expire Min Pool Size The minimum number of Connections the pool will maintain at any given time Max Pool Size The maximum number of Connections the pool will maintain at any given time Max Statements The size of the pool s global PreparedStatement cache If Max Statements is zero statement caching will be enabled but no global limit will be enforced Max Connection Age s The time to live in seconds A Connection older than Max Connection Age will be destroyed and purged from the pool This differs from Max Idle Time in that it refers to absolute age Even a Connection which has not been much idle will be purged from the pool if it exceeds Max Connection Age Zero means no maximum absolute age is enforced JDBC DataSource Acquire Increment Determines how many connections at a time the pool will try to acquire when the existing connections in the pool are all in use Acquire Retry Attempt Defines how many times the pool will try to acquire a new Connection from the database before giving up If this value is less than or equal to zero the pool will keep trying to fetch a Connection indefinitely Acquire Retry Delay ms The time in milliseconds that the pool will wait between acquire attempts The final page of the Connection Pool Wizard allows the pool to be secured by encrypting the file to provide some prot
172. the data that flows through it into different file formats A DataDrop is closely related to a DataStore The only difference is that a DataDrop continues to provide records to subsequent processors whereas a DataStore is a terminal processor A DataDrop is particularly useful when used in conjunction with a Split Merge construct allowing different records to flow through different paths and be saved to different files For more details on the features of DataDrop review the DataStore section DataStore To explore the use of DataDrop connect a Split Merge between a DataSource and a DataStore as shown in Figure 4 43 Sample DataDrop Flow Figure 4 43 Sample DataDrop Flow EDS E p pro e EEN Then open the Split Merge subflow e g double click on it and draw the flow as shown in Figure 4 44 Sample DataDrop Sub Flow Figure 4 44 Sample DataDrop Sub Flow Country USA _ save USA DataDrop input save Others DataDrop In this case the SubFlow input splits the records into two streams The first stream has a condition attached Country USA the other stream is left blank so it gets all the other records Each of these streams passes through a different DataDrop so the records end up in one of two files All the USA stores are now separated DataDrop is particularly useful for storing records that fail data cleansing or data integrity checks for subsequent processing or for debugging complex composite diagr
173. the hierarchy A hierarchy indicates a strict relationship between the hierarchy elements each child can only belong to a single parent For example Country State City is a valid hierarchy because a City can t be in more than one State 59 Composite DataSources and a State can t belong to more than one Country Year Month is not a valid hierarchy because January can occur in many years Figure 4 46 Add Hierarchy ET Add Hierarchy E Name Schema Columns Sales JP customer_id store_id Hierarchy Elements On clicking the Next button in the Cube Wizard appears as shown in Figure 4 47 Cube Axes Screen In this screen the Column dimensions and Row dimensions can be added The elements that are added in the Column dimension windows will be displayed as the column fields in the output table The elements that are added in the Row dimensions window will be displayed as the row fields in the output table The hierarchy column added in the first screen can be made use of in this screen as a row or column field Use of a hierarchy is more efficient as the system relies on the strict definition of each child only having one parent However an incorrect use of hierarchy as illustrated by Year Month above will usually yield incorrect results Figure 4 47 Cube Axes Screen y Cube Wizard E Cube xes Define cube axes F Sales JP customer_id E store_id lt Column D
174. the output Defining a URL with a Dynamic Parameter Here s how to add a text file by passing a dynamic parameter to the URL l 2 Open the Add gt DataSource wizard to create a Text DataSource as before On the Define Text DataSource screen enter a unique name such as Text_URL Enter the URL as URL This indicates a dynamic parameter will be inserted here when we use the data source Follow the rest of the steps as described in the previous section 81 Text DataSource You will find that on clicking the Infer Schema button a dialog appears as shown in Figure 5 7 Dynamic Parameters Figure 5 7 Dynamic Parameters T Dynamic Parameters Dynamic Parameters Enter dynamic parameter values URL Previou Next Finish Cancel Enter the URL as given below modify according to the location of your Emplnfo txt file remember in URLs all slashes are even on Microsoft operating systems files C3 momo exe The columns in the data source are listed in the window Click the Finish button The Text_URL data source is added to the repository Select and double click on the Text_URL ds data source On clicking the Load Data menu the Dynamic Parameters dialog window appears Enter the URL in the text box On clicking the Finish button the output is displayed You can now substitute any other file URL that conforms to the same schema ie has the same number
175. the output file with a ds extension you can use it as a data source You can even write the file directly into the repository by using a repository URL so that Elixir tools can access it immediately By specifying the name of an XSLT transformation the XML data output can be readily transformed into another XML structure or any kind of text output You could use XSLT to produce the same output as the CSV or Text DataStore 1f they weren t already provided for you A Sample of the Tabular DataSource XML file is shown in Figure 4 56 XML Output 67 Composite DataSources Figure 4 56 XML Output E XmlOutput xml Windows Internet Explorer GA 7 E E XmlOutput xml x t X F Search web Af d El ge Favorites A PC Heath O Viewpoint v Web Search A Amazon we k EEx xmi fa amp ama i Page lt xml version 1 0 standalone yes gt lt ds datasource name DataStore type Data xmins ds http www elixirtech com DataSource gt lt ds schema case sensitive Yes date format yyyy MM dd time format HH mm ss timestamp format yyyy MM dd HH mm ss gt lt ds column name store_id type Integer gt lt ds column name Country type String gt lt ds column name State type String gt lt ds column name City type String gt lt ds column name store type Double gt lt ds column name grocery type Double gt lt ds column name frozen
176. the schema Name required Name Description Guided JavaScript Builder Py Column Name Previous Cancel The Name of the data source must be unique Any extra description that is used to describe the data source can be entered in the Description text box On this page you can either infer or edit the data class schema using one of two methods either enter the schema and JavaScript manually if you are an experienced Java developer or use the Guided JavaScript Builder Guided JavaScript Builder With the Guided JavaScript Builder button you are assisted by a wizard that helps you to automatically define the schema and the JavaScript Of course you can edit these manually later so this approach is a useful first step toward customizing the data interface On clicking the Guided JavaScript Builder the screen appears as shown in Figure 9 2 Guided JavaScript Builder In this screen the details of the Data provider Iterator class is entered The tree view showing the classes available in the ext jars is displayed on the left side of the screen Select the Data Iterator provider class The Methods and the corresponding Return Type of the class are listed in a table on the right On selecting the Iterator method from the list of available methods the method name and the Iterator type is displayed in the corresponding text fields 109 Object DataSource Figu
177. ting the Merge from the combo box and clicking the OK button the column is added to the Wizard The Edit Column button is used if the values in the column have to be altered Using the Move Up and Move Down button the selected columns can be reordered if the sequence in which they are displayed needs to be changed The Remove Column button is used to delete the column Use of Merge Whenever a relative XPath expression returns more than one result you have the option of how to combine the results into a single field The usual behaviour is to concatenate so there are merge options for using Space Comma Semicolon Newline and Tab as separators between the merged string components None will concatenate the strings directly with no separator No Merge will just use the first result returned and discard the rest There is one final option Group If you choose Group then all subsequent XPaths are relative to the Group and will generate multiple records sharing all common fields For example given this input lt gt versilom Y 0 RS lt ROOT gt lt A id A1 gt 15 Louis lt C id C1 gt lt A lt A gt lt A id A2 gt KIB acl UBAN KC aces s qe CEUCAN gt lt C osos lt A gt lt A id A3 gt lt B id B3 gt KC acevo gt lt C id C7 gt lt A gt lt ROOT gt Use of a Merge Space will concatenate all the C ids together as shown in Figure 6 4 Merge Space Figure 6 4 Merge Space Root XPa
178. ton The Move Up Move Down are used to reorder the columns The Remove Column button is used to delete a column Fixed Width 77 Text DataSource On selecting the Fixed Width access type and clicking the Next button the screen appears as shown in Figure 5 3 Fixed Width Type Properties On clicking the Add Column button the Add Column dialog window pops up Figure 5 3 Fixed Width Type Properties ET Datasource Wizard Define Text Datasource Enter fixed width details D Column Previous Finish Cancel The name of the column is entered in the text box and the data type of the field is selected from the combo box The width of the column is entered in the text box On clicking the OK button the new column is added to the data source Using the Edit Column Move Up Move Down and Remove Column button the columns can be edited re ordered or deleted as necessary Regular Expression On selecting the Regular Expression access type and clicking the Next button the screen appears as shown in Figure 5 4 Regular Expression Type Properties 78 Text DataSource Figure 5 4 Regular Expression Type Properties E Dat source Wizard x Define Text Datasource Enter regular expression details Regular Expression Start Expression Stop Expression Infer Schema D Column Name Type Previou
179. tput because it doesn t include a colon so it is automatically discarded We can discard the lines below this with a Stop expression Using Start and Stop Expressions Here s how to add a log file using only part of the data from the file To do this the Start Expression and Stop Expressions are used along with the Regular Expression 1 4 5 Create a data source as described in the previous example or modify the one you ve already created In the Regular Expression designer window click the button to the right of the Stop Expression text box The Regular Expression designer window appears In the Stop RegExp text box enter Unmatched Entries without the quotes You will notice a syntax error is reported because is a special character in regular expressions Insert a backslash before each to have them handled literally Unmatched Entries This will remove the syntax error warning and you can click on the Test button Only one record will be found by this expression the line which marks the start of the second log section On clicking the Ok button the syntax is assigned in the Start Expression text box On clicking the Infer Schema button the columns are inferred as before Click the Finish button If you view the output of this data source you will see that only the lines up to the Stop expression are processed those with a different format below are now skipped If you wanted only those items bel
180. ud E sted ete ass 74 5 1 Define Text DataSource sisi ches secs dessus ed Svinte debe ee Ried cede ised ee LR eee 76 5 2 Separator Type Properties mrss saeg ceed sens ceu Ses nE dacent ees head ovr iden stent EES RE E ENER 77 3 3 Fixed Width Type Properties cicides cda dl reia eee 78 5 4 Regular Expression Type Properties coooccocccnccnncnnncnnncnnccnnconoconnccnnconnconncnnncnnnrnnnnnnronase 79 5 5 Regular Expression Designer vaise ligini a e a E E NR EN 79 5 0 Sample Text DataSource rerea cito des iria den e E aE a S E Rn 81 5 7 Dynamic Parameters a tia aise 82 3 8 Fixed Width NS 83 SILOS Met al Den Qe Bw Solace abt 83 5 10 Regular Expression Designer herte irn aan a E EE A ES RS 84 Gel An AMET EE dd alive me E eee S 86 6 2 Define XMI DataSource si vcaassshvss dosh swore e israel Seegeos tun despa SEE EER 87 6 3 Choosing the XPath Burlder 26 3025 elaie iii 88 G4 NN 89 6 gt Merse Grou Deni dct tel E o io ata bet 90 RIN Sores tutes e E E e emacs E E EEE NEETA SE EIEEE ORS 91 6 7 lt XPaths Completed cintia Beas E EN AA 92 6 8 XML DataSource Schemas cs jase cosetscs desea ten suves Lacy cub E IE dial dr tirar 92 6 9Dat Source Results iii ii n ts 93 7 1 Properties DataSource Wizard 0s es05ssiser sss seen ana dees A RE N pe A EER E ERS 94 TA Add COMMINY e E a E E E ET R dal lay EN 95 1 3 Text DataSource Parameters oneee RE EE aE R RESA ERA EEE deb 96 TA Properties DataSource ii di EEE E E E E E 97
181. ues On selecting a range value from the list and clicking the Finish button the corresponding output is displayed If instead of the range given above the parameter is entered as given below is specified Range choice Emp1_A11 Emp2_A11 Emp_A11 Emp_A11 Then on clicking the Load Data menu the Dynamic Parameter appears with a default value of Emp_All in the combo box You can try changing to different values to see the different output results 137 Appendix B Samples Availability The Sample files for all the chapters are available in the file DataDesignerUserManual jar which accompanies this document If you place this file in the Elixir Repertoire ext directory before you launch the tool the samples will be available as a read only filesystem within the tool You can also extract the files using a jar or zip extraction tool For example using jar you could type jar xf DataDesignerUserManual jar For chapters 4 and 5 the examples are based on the MondrianFoodMart mdb The Mondrian FoodMart mdb can be downloaded from the Mondrian website at http source forge net projects mondrian You only need the data files which are available for a range of databases Please refer to the link given below for the license Note The software is subject to the terms of the Common Public License Agreement available at the following URL http www opensource org licenses cpl html Copyright C 2003 2003 Kana Softw
182. urce Testing the Data Flow The Employee details are maintained in a text file which includes the department field of the employees Here s how to derive a new column using the department field of the text data source and check the validity of the derived column using the Properties DataSource 1 2 Use the filesystem or folder popup menu to select Add gt DataSource The DataSource Wizard appears Select Text DataSource and click on the Next button In the screen that appears enter a unique name for the data source such as Employee Enter the URL in the text box or alternatively by clicking the button to the right of the text box select the EmpInfo txt file from the Open dialog window Select the First line is header check box Select the Qualifier as and separator as Semicolon On clicking the Infer Schema button the columns are inferred After setting the properties the screen appears as shown in Figure 7 3 Text DataSource Parameters Click the Finish button to add the text data source to the repository 95 Properties DataSource Figure 7 3 Text DataSource Parameters Datasource Wizard Define Text Datasource Enter separator details Separator Tab Semicolon Comma Space Other Qualifier y Infer Schema D Column Name Type 1 Empd_ld String 2 Emp_Name string 3 Dept_Name string 4 Emp_Sal String Previous Finish Cancel
183. urce After adding the Composite DataSource it will open automatically We are going to create the diagram as shown in Figure 4 58 Case Study Composite Diagram Select the Customer DataSource drag and place it on the diagram Repeat the process for the Sales DataSource and then the Store DataSource 71 Composite DataSources Figure 4 58 Case Study Composite Diagram on gt DataStore Add the additional processors and connections as shown in Figure 4 58 Case Study Composite Diagram In your version you will notice the link from Cube to DataStore is dashed because the tool cannot identify the schema for this flow until the cache has a schema inferred With the diagram created we can walk through the flow setting the various processor properties Join 1 e Inthe Options tab window enter If no matching secondary Discard If multiple matching secondary Repeat e Change to the secondary tab and select the customer_id field in the primary column against the customer_id field of the secondary data source This configuration corresponds to an inner join so records from the primary are only retained if a matching secondary record exists In this case only customers that have made purchases have sales records will be retained Filter e In the Filter 1 select Equals from the When condition column corresponding to the store_country field Enter condition as USA The output from this part of the flow will o
184. urce By default the Data Window displays up to a maximum of 500 records On selecting the Count All Records check box and clicking the Load Data icon the total number of records available in the data source is displayed on top of the Data Window Turning this option on will slow the response for huge data sources as the value can only be obtained after all processing has finished The Composite DataSource workspace consists of three tabbed panels Designer Script and Data The Elixir Data Designer Workspace Designer Tab The Designer tab is shown in Figure 2 2 Composite Data Workspace In order to show all the icons within the width of this document the repository tree on the left has been collapsed There are various Data Operation constructs present in the toolbar of the Designer tab The Designer window is the main window where the data flow is designed with the help of the Data Operation constructs Figure 2 2 Composite Data Workspace 27 Elixir Repertoire gt aif le Em E ElixirSamples DataSource Composite ds M 2 m Designer Script Data kURPDAW gt Beko as AD Be Zoom100 x BAS EM Script Tab The Script tab allows the creation of JavaScript functions to enhance the processing power of the Elixir Data engine Data Tab The third window is the Data tab which is identical to the tab shown in Figure 2 1 Basic Data Workspace for basic data sources Case Stu
185. urce Wizard Define ARFF Datasource Enter ARFF datasource parameters Name required Name Description URL Previous Next Finish Cancel Name Enter the DataSource name in the text box This should be a unique name Description Any extra description that is used to describe the data source can be entered in the Description text box URL Enter the URL of the directory where the ARFF file is placed The second screen of the ARFF DataSource Wizard is shown in Figure 14 2 Define ARFF Schema 129 ARFF DataSource Figure 14 2 Define ARFF Schema DataSource Wizard Define ARFF Datasource Define ARFF schema Infer Schema D Column Name Previous Finish Cancel Using the second page of the wizard you can either enter the column names and types explicitly or use the Infer Schema button Where the types cannot be inferred they will default to String type You can edit the names and types after inferring if necessary 130 Chapter 15 Cache Datasource Overview Cache datasource provides a caching mechanism to retain the data for a pre determined time to avoid repetition of data loading and processing The cache will remember the current set of records across multiple generations or views of the data It is useful to use a Cache datasource when there is a long operation during flow debugging when you
186. urce Wizard is shown in Figure 13 1 Random DataSource Wizard Figure 13 1 Random DataSource Wizard 3 Datasource Wizard Define Random Datasource Enter Random datasource parameters Name FruitSalesGenerator Description Count 200 D Column Name Type Value CompanyName String values Trinity Diamond Fruit Mounta Fruit String values Apple Banana Cherry Grape Date Date range 2001 01 01 2005 12 31 Quantity Integer jrange 1 20 Price Float range 5 00 1 20 00 Previous Finish Cancel Name Enter the DataSource name in the text box This should be a unique name Description Any extra description that is used to describe the data source can be entered in the Description text box Count The number of records that should be generated You can use substitution of dynamic parameters here so that the caller can supply the count value The remaining UI items on this page allow you to define the schema and value options for the records you wish to create In the screenshot shown above you can see five columns defined When a column is created or edited you will see Figure 13 2 Edit Column 126 Random DataSource Figure 13 2 Edit Column Add Column Name CompanyName Data Type String Value Type Expression O Auto Increment start Range to e Values Trinity Di
187. ure 5 9 Log file This can be achieved easily by using the Regular Expression Access type Figure 5 9 Log file 2 Server log Notepad Jom File Edit Format View Help 200 196 165 19 did not issue MAIL EXPN VRFY ETRN during conn a 222 77 123 18 did not issue MAIL EXPN VRFY ETRN during conne de24243 alshamil net ae 83 110 128 179 may be forged did n jJ15LRoIO3599 POSSIBLE ATTACK from 221 164 50 68 newline in string J15LdDI03650 collect premature EOM Error 0 315LdDI03650 collect unexpected close on connection from c 27bc71d5 5 NOQUEUE ads1 158 17 84 asm bellsouth net 68 158 17 84 did not issue j15LpBI03800 discarded NOQUEVE SO106080046be0Ocbc sc shawcable net 24 79 141 71 did not issu j15M3NI03950 discarded NOQUEVE 200 115 201 203 did not issue MAIL EXPN VRFY ETRN during con NOQUEVE 72 2 85 244 suite224 net 72 2 85 244 did not issue MAIL EXPN j15MmMdI04116 ruleset check_rcpt argl lt sophia elixirtech com gt relay m 315MZ6104184 POSSIBLE ATTACK from 4 22 154 43 newline in string ta NOQUEUE 203 67 89 18 adsl dynamic seed net tw 203 67 89 18 did not yr lel y ES 83 10 11 Text DataSource Use the filesystem or folder popup menu to select Add gt DataSource The DataSource Wizard appears Select the Text DataSource and click the Next button The Define Text DataSource screen appears Enter a unique name such as Text_RegExp The URL in this ca
188. will be passed through If the records are sorted such that duplicate records are adjacent you should tick the Input Sorted checkbox so that the system can use an alternate algorithm to reduce memory usage When the Input Sorted checkbox is selected each record is only compared against the previous record instead of all previous records resulting in faster performance and reduced memory requirements General Invert Data This task inverts the data records so that the rows become the columns and vice versa In order to convert the rows to columns one original column must provide the names of the columns in the new schema This column should contain unique values to ensure the resulting schema doesn t have duplicate column names Select the column that will provide the column names at the top of the wizard as shown in Figure 4 40 Invert Data Processor Conversely you might want the old column names to be retained as row values If so enter a field name in the next text field otherwise leave it blank Specifying a name will add a column in the output schema of type String whose values will be equal to the Keep columns described below 55 Composite DataSources Figure 4 40 Invert Data Processor Processor Wizard Invert Data Determine inversion column and retained fields Column store_id Field Name Name ro store_id Integer Country State String String City St
189. with Text DataSource srep ernearen e eaa pe TE NEE ERA ENNES EERS EKON 80 Using Separator Characters i ionis e Wan E E cid 80 Defining a URL with a Dynamic Parameter cece cece cece cence eeceeeea tenn eene eens 81 Using Fixed Width Columns 0 cece cece cnecc cece cen een cen eeneeeeeeeeeeeeeeeeeeaees 82 Using Regular Expressions eos nese saved desthsvdes snetee Beene eens ER SE HEE Seas 83 Using Start and Stop Expressions ooccooccnnccnnconnconeconoconocononnnncnnronrnnncnnconncinnconns 85 6 XML DataSource p inan e sae edad a detach cid ageladenaveee dada guvenertaoeadaeagveds 86 OVERVICW O RN Pat Sa ES E 86 XML DataSource Wizard ireira Ans sirenas RE PEEN EE EOE DEEPER TREPA Ee 87 Use of Merge ti A et ee ee 89 Subti s Optimizaon eses nena e Ee e e a a iia San E Ea das 90 O 90 Working with XML DataSources ooccocccnccnncnnncnnccnnccnnconnccnnccnnconnconncnnncrnnnnnnrnnnenuronoss 90 TProperties DataSource Hori A ias 94 OVERVIEW oeer a a a d it gn ret ae teed awe tien a E 94 Properties DataSource Wizard e cece cece cece eee eeeece cece cena cena eens ceneeneeeeeeeeeeeeeaeeeaees 94 Working with Properties DataSources ee ceee cece cece ceee ce eecn cece tena cena eeu eeneeeeeeeeeeeees 95 Testing the Data FLOW ica o oi 95 Passing Parameters to the FlOW ooooocccnccnnconnconoconconoconoconononcnnornnncnnconnrinncnnno 99 8 Reference DataSource eirese 5 outa cis oa A andado opel DA pea cea 102
190. y search Filters are optional if you leave it blank a default filter objectclass will be used In filter the matchingAttrs argument is converted into a string filter that is a conjunctive expression of the attributes from matchingAttrs For example when a matchingAttrs containing the attributes sn Geisel and mail no value given it is translated into the string filter amp sn Geisel mail The filter conventions are given in the ftp ftp isi edu in notes rfc2254 txt link ftp ftp isi edu in notes rfc2254 txt link Port The default port for connection is 389 The LDAP port number of the machine on which the Directory Services is running is specified in the text box Timeout This is the time interval allowed for certain operation to occur The LDAP timeout is the time limit required to wait for the result If 0 is specified then it means that the datasource will wait indefinitely Batch Size The value of the batch size property specifies the batch size of the search results returned by the server The batch size is specified in the Batch Size text box A setting of O means that the provider should block until all the results have been received If for instance the batch size is specified as 24 then the provider should block until 24 entries have been read from the server or until the enumeration terminates whichever produces fewer number of results On clicking the Next button the LDAP Security page will appear In F

Download Pdf Manuals

Related Search

Related Contents

Cables Direct 1TT-110  GE 169025 Fryer User Manual  ninja assassin  trendmobil scooter safari  Le journal de la Communauté Nord Ouest n°10    Fujitsu LIFEBOOK E752  UNIVERSITI TEKNOLOGI MALAYSIA  Fujitsu STYLISTIC Q704 128GB Black  

Copyright © All rights reserved. Failed to retrieve file