Home

Elixir Data Designer User Manual

1. 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 102 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 selecting the Merge from
2. Previous The data sources in the repository are listed in the DataSource window from which a data source can be selected 23 Composite DataSource 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 Discarding 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 Note Right clicking on the Datasource processor shows the options available one of which is to open the datasource User will then be able to open the datasource directly instead of searching for it in the repository 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 Flow 3 Drag an
3. 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 Study 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 Fis ARFF 7A FileSystem Random Uz Cache Aa JDBC F Reference P Composite Ta LDAP ES Tabular A Object Text Ey a Properties A XML lt gt 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 Employe
4. Filter Search filters enable you to define criteria for more efficient and effective searches LDAP filters are used to specify criteria for directory 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 re
5. 1 2 3 Field AID BID XPath Did 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 7 5 Merge Group 103 XML DataSource Figure 7 5 Merge Group Root XPath ROOTIA 1 2 3 4 Field AID BID CID XPath id Bi id Ic Merge No Merge NoMerge Group Space A1 B1 0 C1 A1 B1 1 c2 A2 B2 0 c3 A2 1B2 1 C4 A2 B2 2 c5 A3 B3 0 C6 A3 B3 1 Cc 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 also a vali
6. 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 discussed 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 amp 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 copyi
7. 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 2102 717432 lt 2102713519 lt 0A se 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 customer_id store_sales 10277 4 76 10277 48 10277 5 28 String data 37 me 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 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 Open the Sort properties and in the first screen click the Add button The Sort dialog box appears Composite DataSource Select the Country from the list of field names available in the Com
8. 40 Composite DataSource 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 If 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 Highlighting row s in Base tab and right clicking on it gives the option to add the selected row s to the Derived tab Column name s containing spacing or symbols will be contained within square brackets with open and closed inverted commas automatically when moved over but the Value of the column will have to be done manually Working with the Derivative processor Here s how to derive two new columns from the Stores data source using the Derivative processor 1 2 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 desig
9. 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 operations 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 1t 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 th
10. D Column Name Type Previous 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 6 5 Regular Expression Designer Figure 6 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 93 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
11. NOQUEVE 222 77 123 18 did not NOQUEUE de2424 NOQUEVE de24243 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 98 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 differe
12. Organization Result Note Please refer to Appendix B Samples for the sample files used in this example 134 LDAP DataSource Figure 11 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 11 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 135 LDAP DataSource Figure 11 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
13. Pool Connection Pool ElixirSamples MondrianFoodMart pool User Password lv Hide Password Previous Next Finish Working with a JDBC DataSource In this section we will review the different ways of adding a JOBC DataSource 16 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 as Microsoft Access 1 10 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 select
14. 123 Object DataSource Figure 10 1 Object DataSource Wizard DataSource Wizard Define Object DataSource Enter datasource name and define the scherna Name required Name Description l Guided JavaScript Builder m Column Name Previous Next Finish 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 10 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 me
15. Click the Set Value button The values entered in the fields of the expression builder will get displayed in the text box 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 Click the Set Value button The values entered in the fields of the expression builder will get displayed in the text box With this 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 l Using the same sort diagram as the previous section open the Sort properties and remove any previous sort and extract options In the first screen of the Sort Wizard click the Add button The Sort dialog box appears Select the customer_id from the list of field names available in the Combo box Select Descending Sort Order 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 35 Composite DataSource Figure 4 15 Sort Dialog Name customer_id v Sort
16. DBF Datasource Figure 17 2 Enter DBF Schema DataSource Wizard Define DBF Datasource Enter DBF schema Infer Schema Name Previous Finish Cancel User can choose to enter the column names and data types explicitly or infer the schema using the Infer Schema button Editing of column names and data types can be edited after inferring 150 Appendix A Dynamic Parameters Dynamic Parameters We generally need a way to control the dynamic behavior of the program by setting 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
17. EmpSheets and enter range as given below in the Refers to text box Sheet1 Sheet3 SA 1 SD 11 The above syntax includes the cells in sheet1 sheet3 and all the sheets between them After setting the values the Define Name dialog window appears as shown in Figure 5 8 Define Name Click the OK button in the Define Name dialog box and save the Excel file Figure 5 8 Define Name Define Name Names in workbook EmpSheets En Ad Delete EA Refers to sheet1 Sheet31 1 65536 5 5 Launch Elixir Data Designer 6 Choose a filesystem or folder and use the popup menu to select Add gt DataSource 7 The DataSource Wizard appears Select Excel DataSource and click on the Next button 8 The Define Excel DataSource screen appears Enter name as Excel 2 84 Excel DataSource Enter the URL for EmpDetail xls in the text box provided Alternatively by clicking the button on the right of the text field select the EmpDetail xls file from the File Open dialog 10 11 12 13 14 15 Enter EmpSheets in the Range field Select the First Row Header check box and click on the Next button Click on the Infer Schema button in the screen shown in fig Change the Data Type of Emp_Id and Emp_Sal from Double to Integer Figure 5 9 Completed DataSource Wizard Figure 5 9 Completed DataSource Wizard DataSource Wizard Define Excel Datasource Enter Excel datasource paramet
18. In our example we lookup in the Code 74 Composite DataSource 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 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 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
19. 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 JDBC datasource parameters Name required Name Description JDBC JNDI Pool Driver Suggestions sun idbc 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 fol
20. 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 me 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 data 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 Figure 4 3 DataSource Wizard DataSource Wizard DataSource Choose presentation options and data source Name DataSource DataSource ElixirSamples DataSourcerF ruitSales ds DataSources Schema bJ Department ds 3 Employee ds J FruitSales Summary ds LJ FruitSales ds LJ FruitSalesGenerator ds 3 Gender and Marital Status Code English ds 3 Gender and Marital Status Code ds 3 HighLowData ds o spec 3 KPLds
21. So the Cities with names starting with Ya are grouped followed by names starting with Wa and so on e Group On Year 1 6 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 In the first screen of the Sort Wizard click the Add button The Sort dialog box appears Select birth_date from the list of field names available in the combo box Select the sort order as Descending from the combo box 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 Figure 4 20 Completed Sort Dialog Name birth_date Sort Order Descending Group On Year Group Interval Derived Field Ok J Cancel 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 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 the
22. 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 65 SiN lt 10 218 ELSE A B and C are the label names assigned for those groups Alternate conditions can be described by using operators gt and Ranges are 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
23. 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 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 63 Excel Output 70 Composite DataSource JDBC Figure 4 63 Excel Output C D E E G H State City store grocery frozen meat OR Salern 27694 18669 78 5414 529 3609 686 WA Bellingham 28206 22271 15 3560 91 2373 94 WA Bremerton 39696 24389 92 9183 647 6122 431 CA Beverly Hil 23668 15336 75 5010 748 3340 499 CA Los Angele 23598 142
24. 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 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 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 acquir
25. 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 colour 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 8 10 Output Figure 8 10 Output Designer Script Data Res AO S Showing 1 records Emp_id Emp_Name Dept_Name Emp_Sal 6 Christopher Electronics 8000 114 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 The pull command propogates back to the filter which uses the properties to control the filter process The pull command terminates at the datasour
26. 1872 El Pintado Road Mexico 10 Supermarket 24 store 10 10 0 7894 Rotherham Dr orizaba v 11 Supermarket 22 Store 11 3 11 0 5371 Holland circle Portland 12 Deluxe Supermarket 25 store 12 12 0 1120 westchester P1 H1 13 Deluxe Supermarket 23 Store 13 13 0 5179 valley Ave Salem 14 small Grocery L store 14 14 0 3 4365 Indigo ct San Francisco 15 Supermarket 18 store 15 15 0 5006 Highland Drive Seattle 16 Supermarket 87 Store 16 16 0 5922 La Salle ct Spokane wA 17 Deluxe Supermarket 84 Store 17 17 0 490 Risdon Road Tacoma 18 Mid size Seely AI sn 18 18 0 6764 Glen Road Hidalgo 19 peluxe Supermarket 5 Store 19 19 0 6644 Sudance Drive vance 20 Mid size Grocery 6 Store 20 20 0 3706 Marvelle Ln victoria lt gt 2 3 5 6 7 Supermarket 3 Store 7 7 0 1077 wharf Drive Los Angeles CA 8 9 E Note e 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 From Repertoire 7 7 onwards user can enter any symbol in the Qualifier field instead of selecting from the drop down list 68 Composite DataSource Connection Pool This datastore writes records to the database that th
27. 1f any The user can also choose to enter the parameters manually 147 Cache Datasource Figure 16 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 148 Chapter 17 DBF Datasource Overview A DBF datasource is created by using a DBF file which is widely used in many applications which needs a simple format to store structured data DBF files have been adopted in many other applications and one good example is the shapefile which stores feature attribute data is also used in Elixir Repertoire DBF Datasource Wizard The DBF datasource wizard is shown in Figure 16 1 Cache DataSource Wizard Figure 17 1 DBF DataSource Wizard DataSource Wizard Define DBF Datasource Enter DBF datasource parameters 0 Name required Name Description URL f AS Previous Next Finish Cancel Name Enter the a unique name for the datasource in the text box Description Any additional description required to describe the data can be entered here URL The URL of the dbf file The next page of the wizard seen in Figure 16 2 Cache DataSource Parameters is for inferring the schema and editing it 149
28. 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 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 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 set to NULL 28 Composite DataSource 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
29. 30584 0 21938 000 5187 599 3458 399 Mexico DF San Andres Mexico DF Mexico City 36509 0 22450 026 8435 384 5623 589 Mexico Veracruz Orizaba 34791 0 26354 017 5062 189 3374 792 9 Mexico Zacatecas Hidalgo 38382 0 30350 714 4818 771 3212 514 Canada Canada BC Vancouver 23112 0 16417 973 4016 415 Victoria 34452 0 27463 353 4193 187 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 2677 610 2795 458 42 Composite DataSource 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 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 Sel
30. 6 1 Define Text DataSource ii dee de 90 6 2 Separator Type Properties se reer e a otr nicole ninia e E E EE NER 91 6 3 Fixed Width Type Propertie Siren iee N E EE EN E T EARE N E EOS 92 6 4 Regular Expression Type Properties coooccooccnccnncnnncnnoconccnnconnconncconccnnconnconncnnncnnnnnnronose 93 6 5 Regular Expression Designer coooccnoconoconocnnccnncnnncnnronccnnconnconnccnncrnncnnncrnncnnnrnnnnnnronase 93 6 6 Sample Text DataSource rene dino to diante in E a E A AEE E NESAS 95 6 7 Dynamic Parametros 96 6 8 Fixed Width Sample ooo dossier incio ovens E EA RO ER NEER NE EEEIEE EAE ETE 97 69 LCOS leona NS A E E AA 97 6 10 Regular Expression Designer sietnie ean n E A SE RRE tienen NEES 98 TALAR XML Tree a e a a E ET AR E EE EERE A ide 100 1 2 Dehine X MIS DataSource sader e E E EA EEEIEE E 101 T3 Choosing the XPath Builder iia a E E Ce E ida 102 vii Elixir Data Designer User Manual TAS MEPS PACO ics dss asii titanes Ea OEE EN EN 103 T gt Merge Group A ii OLE oia 104 TO XPath BUlder microsite att cretino ica R piu etaee hens desnsece dean S 105 Tre X Paths Completed renne ta pheteda worse EE E as teleur tek Maceven a cored tapi etedete estes Shy 106 LB XML Datas GUrCe SCHEMA arcos tii ce neteva dene vane cogg wed tease ten enete tenses sevenceeseuevenest 107 7 9 DataSource Results ii Sd eee aN 107 8 1 Properties DataSource Wizardsin nor aces secs n seu baceenethca dan yates R E a E sb Ges 108 822 Add
31. ActionBox bs gt E Docx o amp Hidden Folder _o E lava Files Previous Finish Cancel Name Enter a unique name for the Reference DataSource DataSource Select the DataSource for which the parameter values need to be set Click on Extract Parameters seen in Figure 9 2 Reference DataSource Wizard Page Two to extract parameters from the datasource or define parameters explicitly using buttons on the left Click on End to end the creation process 116 Reference DataSource Figure 9 2 Reference DataSource Wizard Page Two DataSource Wizard Reference Datasource Parameters Enter parameter values for this datasource Extract Parameters Previous Next Finish Cancel 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 9 3 Empdata xls Figure 9 3 Empdata xls A B C D 1 Emp_ld Emp_Name Dept Name Emp_Sal 2 1 Gerry Electronics 7400 302 Micheal E
32. Figure 5 1 Excel DataSource Wizard Figure 5 1 Excel DataSource Wizard DataSource Wizard Define Excel Datasource Enter Excel datasource parameters 0 Name required Name Description URL Range _ Has Row Header Header Row _ XLSX Format Previous Next Finish Cancel The Excel DataSource Wizard contains the Name Description URL and the Range fields e The DataSource Name is entered in the Name text box e Any extra description for the data source can be entered in the Description field e The URL of the file is specified in the text field 80 Excel DataSource e The data range must be specified in the Range field The records will be fetched based on the specified range by inferring the schema in the next screen Figure 5 2 Excel DataSource Wizard De Datasource Wizard Define Excel Datasource Define Excel scherna Infer Schema py Column Name Type Previous Next 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 Infer Schema will review the selected range of cells and attempt to determine the column types based on the cell types Where the types cannot be inferred they will default to String type You can edit the names and types after inferring if necessary Note You should not add remove or rearrange the columns
33. Integer 13 Designer Country String USA State City store grocery frozen String String Double Double Double OR Salem 27694 0 18669 784 5414 529 meat Double 3609 686 Script Data l amp Showing 25 records _ Count All Recor meat_percentage USA WA Bellingham 28206 0 22271 150 3560 909 2373 939 USA WA Bremerton 39696 0 24389 921 9183 647 6122 431 USA CA Beverly Hills 23688 0 15336 753 5010 748 3340 498 USA CA Los Angeles 23598 0 14210 378 5632 573 3755 048 USA CA Alameda USA OR Portland 20319 0 16231 745 2452 352 1634 901 USA CA San Diego USA CA San Francisco 22478 0 15320 950 4294 229 2862 819 USA WWA Spokane 30268 0 22062 867 4923 079 3282 053 USA WWA Tacoma 33858 0 22123 345 7040 792 4693 861 USA WA Yakima USA WA Walla Walla USA WWA Seattle 21215 0 13305 463 4745 722 3163 814 Mexico Yucatan Merida 30797 0 20141 202 6393 478 4262 318 Mexico Guerrero Acapulco 23593 0 17474 989 3670 806 2447 204 Mexico Zacatecas Camacho 23759 0 16844 440 4148 735 2765 823 Mexico Jalisco Guadalajara 24597 0 15011 512 5751 292 3834 195 Mexico Zacatecas Hidalgo
34. Join Wizard Join Identify fields to equate in the joined schema Name Join calor ae aa a a aS a cc ome ay 2 gender ging PS 3 marital status Stings O Previous Next Click the Finish button to close the Join Wizard and return back to the Designer window Select the Result and choose View from the popup menu The output is shown in the Figure 4 8 Inner Join Result Figure 4 8 Inner Join Result Caca B Data res ea Had gt Pa Showing 500 records pricustomer pristore_id pristore_sal geccustom secgender A 3 5 2 76 2 79 Hp a oo shi soo CI OS IS IS po o hs fs B FA M BoM BoM BoM Bo Ec M 32 pa desa B O FE pm Bo tM zo t eu OOOO EO B pps Pas B O FE pm F mM 7 7 7 7 7 7 7 7 7 njimi m 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 27 Composite DataSource 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 I
35. Mexico Zacatecas Hidalgo 38382 0 30350 7148 4818 77107 3212 51 405 13 USA OR Salem 27694 0 18669 7842 15414 52944 3609 68629 2 USA WA Bellingham 28206 0 22271 1505 3560 90965 2373 93977 3 USA WA Bremerton 39696 0 24389 9213 9183 64716 6122 431 44 6 USA CA Beverly Hills 23688 0 15336 7531 5010 74809 3340 49873 7 USA CA Los Angeles 23598 0 14210 3780 5632 573188 3755 04878 0 USA CA Alameda 11 USA OR Portland 20319 0 16231 7456 2452 35258 1634 90172 24 USA CA San Diego 14 USA CA San Francisco 22478 0 15320 9505 4294 22967 2862 81978 16 USA WA Spokane 30268 0 22062 8673 4923 07959 3282 053086 17 USA WA Tacoma 33858 0 22123 3454 7040 79275 4693 86183 23 USA WA Yakima 22 USA WA Walla Walla 15 USA WA Seattle 21215 0 13305 4630 47 45 72215 3163 81477 d Group On Substring l Ze Using the same Composite configuration as the previous illustration open the Sort properties Select the City from the list of field names available in the combo box Select the sort order as Descending from the Combo box 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 38 Composite DataSource The records are grouped based on the first 2 characters of the City field and sorted in descending order
36. Table Exists don t create Append Data _ Auto Commit _ Update If Record Exists Previous Next Finis Cancel 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 automatically Using CSV as an example the DataStore screen appears as shown in fig Figure 4 62 Custom Java Option Screen 69 Composite DataSource Figure 4 62 Custom Java Option Screen De DataStore Wizard Java DataStore Choose custom Java options Class com eliirtech data2 output CSVDataStore Name Value URL file C Custom append true qualifier separator Comma Previous Finish Cancel In order to implement your own DataStore you need to implement a DataListener interface This API is described in the section called Object DataSource APT
37. 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 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 45 Composite DataSource Figure 4 28 Filter Wizard Filter Wizard Filter Choose filter options for items in the schema Name Filter Colour Filter 1 Filter 2 Filter 3 JavaScript Column Name Type Condition customer_id Integer gender String marital_status String Previous 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 numbe
38. Type combo box Click the Next button e Enter the URL as file C Output xml choose an appropriate location for your operating system 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 localhost 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 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 78 Composite DataSource Oracle Before generating the Oracle JDBC DataStore the Oracle driver file must be copied to the ext folder on the client for the Designe
39. 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 Select rrom Score Click the Next button and then click the Infer Schema button Similarly add the Sales data source with SQL Select trom Salles Fact 1997 Again infer the schema Similarly add a Customer data source In the SQL window enter Selec om Customer and again infer the schema Add a Composite DataSource named Case Study Creating a Composite DataSource 75 Composite DataSource After adding the Composite DataSource it will open automatically We are going to create the diagram as shown in Figure 4 65 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 Figure 4 65 Case Study Composite Diagram primary b p primary gt gt DataStore Add the additional processors and connections as shown in Figu
40. 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 When Time SOL button is clicked there will be a pop up window displaying the number of records retrieved and the time taken to do so JDBC DataSource Figure 3 4 Define JDBC DataSource DataSource Wizard Define JDBC Datasource Enter SQL or use the Query Builder SQL required SQL CALLABLE Callable Statement Query Builder Edit SQL Convert Nulls to Empty Strings Time SQL Previous Next Finish Cancel Figure 3 5 Elixir Query Builder Elixir Query Builder Show Views Select Distinct Default Schema By Tables X Relationships Details 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 panel When this check box is selected the views present in the database are liste
41. 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 protocol 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 latest driver must be copied to the ext folder which can be downloaded from http www oracle com technology software tech java sqlj_jdbc htdocs jdbc9201 html http www oracle com technology software tech java sqlj_jdbc htdocs jdbc9201 html 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 e
42. 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 8 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 108 Properties DataSource Figure 8 2 Add Column EJ 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 DataSource Testing the Data Flow The Employee details are maintained in a text file which includes the department field of the employees Here s
43. 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 101 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 7 3 Choosing the XPath Builder Figure 7 3 Choosing the XPath Builder
44. do match so they are passed through as records Bill doesn t match 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 Ch
45. for this datasource Extract Parameters D i Emp1_All Previous 119 Reference DataSource 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 Ex1 data source In the data window click on the Load Data menu The output is displayed as shown in Figure 9 7 Sample Output It is seen that the data from the first sheet of the Excel file is displayed Figure 9 7 Sample Output Data Result E El 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 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 refe
46. 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 8 3 Text DataSource Parameters Click the Finish button to add the text data source to the repository 109 Properties DataSource Figure 8 3 Text DataSource Parameters DataSource Wizard Define Text Datasource Enter separator details Separator Tab 8 Semicolon Comma Space Other Qualifier Infer Schema Column Name Type Emp_ld string Emp_Name string Dept_Name string Empd_Sal string Previous Finish Cancel 6 Select the same location and use the popup menu to select Add gt DataSource 7 The DataSource Wizard appears Select Propert
47. 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 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 o
48. 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 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 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
49. 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 60 CVS Output Figure 4 60 CVS Output P Csv0utput csv Notepad File Edit Format View Help store_id stor e_type region_id store_name store_number store_street_address a 0 HeadQuarters 0 HQ 0 0 1 Alameda way Alameda CA 555553 1 Supermarket 28 store 1 1 0 2853 Bailey Rd Acapulco Guerre small Grocery 78 store 2 2 0 5203 Catanzaro way Bellingham Supermarket 76 Store 3 3 0 1501 Ramsey Circle Bremerton w 4 Gourmet Supermarket 27 Store 4 4 0 433 st George Dr Camacho small Grocery 4 Store 5 5 0 1250 Coggins Drive Guadalajara Gourmet Supermarket 47 store 6 6 0 5495 mitchell Canyon Road Deluxe Supermarket 26 Store 8 8 0 3173 Buena Vista Ave Meri Mid size Grocery 2 store 9 9 0
50. more than one State 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 60 Composite DataSource Figure 4 47 Add Hierarchy E Add Hierarchy E Name Schema Columns Hierarchy Elements Sales JP customer_id JP store_id On clicking the Next button in the Cube Wizard appears as shown in Figure 4 48 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 48 Cube Axes Screen Cube Wizard Cube Axes Define cube axes F Sales Column Dimensions JP customer_id E store_id Row Dimensions Previous Next Finish Cancel On clicking the Next button the Cube Wizard appears as shown in Figure 4 49 Cube Measures Screen There are two columns namely Name an
51. of Tabular DataSources 141 Chapter 14 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 DataSource Wizard is shown in Figure 14 1 Random DataSource Wizard Figure 14 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 subs
52. 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 often used close to the Result end of the flow so that they can propogate parameters to all upstream processors Properties 50 Composite DataSource 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 Cance
53. 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 DataSource 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 323 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 allP 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
54. 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 pattern 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 conv
55. 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 8 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 112 Properties DataSource Figure 8 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 condition 1 Add a Properties DataSource named Prop_Emp1 2 Add a column called Department with type String and value Electronics 3 Add a col
56. tit aa ed e A it EEE AOE EE E EE EEES 133 T13 LDAP DataSource Sehema enoi o a e R E EE E EE E A EE EEE 134 11 4 LDAP Datasource Wizard iii a aE E EE E E R T EEN a 135 11 5 Completed Datasource Wizardsi sneen ne EE E a EN a ENARE 135 11 6 Organization Result ici EE E RES E E E E Mie ENE SOEN 136 12 1 Filesystem DataSource Wizard crees opende nane apan cena E E nE aS EEPE SENESE 137 1222 File AttDutes id dr tude pedia 139 13 1 Tabular DataSource Wizard onies i peed hea bebe tongues E E a Nea 140 13 2 Tabular DataSource Wizard Page TWO ooocccnccnnconoconoconccnnncnncnnnccnncnnnconccnnconnccnnccnnioos 141 14 1 Random DataSource Wizard ssc nosens on a e a e a ieia 142 TA 2 Edit Colum a keno e e a N eet E N a ee 143 15 1 AREF DataSource Wizard irienn aeaea Svea E E E A E A EET E Ea 145 15 2 Define ARFF Schema A EEEE EEEE 146 16 1 Cache DataSource Wizard eoin onse ai e E N E E VEE A AS 149 16 2 Cache DataSource Parameters oocooccoccnoccoconcnoconcnoconcnnroncnoroncnoroncnnroncnnroncnnroncnnroncnnnos 150 174 DBF DataSource Wizardsin EE EA ANE EE ERTEN vie 149 17 2 Enter DBF Schema nireti ieee ie ed rE EE Aa 150 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
57. 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 DataSource 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 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 du
58. 09 17 1976 09 22 1979 09 17 1976 08 28 1976 04 17 1977 03 27 1977 04 01 1980 03 27 1977 03 07 1976 10 27 1977 07 16 1977 07 21 1980 07 16 1977 06 26 1977 02 16 1978 09 18 1978 09 23 1981 09 18 1978 08 29 1978 04 18 1979 04 13 1979 04 18 1982 04 13 1979 03 24 1978 11 13 1979 05 22 1979 05 27 1982 05 22 1979 05 02 1978 12 22 1980 02 06 1980 02 11 1983 02 06 1980 01 17 1979 09 06 1981 01 03 1981 01 08 1984 01 03 1980 12 14 1980 08 03 1982 01 09 1982 01 14 1985 01 09 1981 12 20 1981 08 09 1986 02 07 1986 02 12 1989 02 07 1986 01 18 1985 09 07 1994 09 27 1994 10 02 1997 09 27 1994 09 07 1994 04 27 Deriving a new column using the Java script function This time we ll calculate the percentage of Frozen area in the stores 44 Composite DataSource 1 After connecting the Stores data source with the Derivative processor using the Derivative procedure given above open the Derivative Wizard properties 2 The fields available in the data source are listed in the Base tab 3 Select the JavaScript tab window and enter the following code function percent percentage frozen store 100 FSA PSLCSINCaCeSp 4 Select the Derived tab window Click the Add button the Add Column dialog box appears 5 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 6
59. 10 38 5632 573 3755 049 CA Alameda OR Portland 20319 16231 75 2452 353 1634 902 CA San Diego CA San Franc 22478 15320 95 4294 23 2862 82 WA Spokane 30268 22062 87 4923 08 3282 053 WA Tacoma 33858 22123 35 7040 793 4693 862 WA Yakima WA Walla Wal WA Seattle 21215 13305 46 4745 722 3163 815 Yucatan Merida 30797 20141 2 6393 470 4262 319 Guerrero Acapulco 23593 1747499 3670 806 2447 204 Zacatecas Camacho 23759 16044 44 4140735 2765 824 Jalisco Guadalajar 24597 15011 51 5751 293 3834 195 Zacatecas Hidalgo 30584 21938 5187 599 3458 4 DF San Andre DF Mexico Cit 36509 22450 03 8435 384 5623 589 Weracruz Orizaba 34791 26354 02 5062 189 3374 793 Zacatecas Hidalgo 38382 30350 71 4818 771 3212 514 Bc Vancouver 23112 16417 97 4016 416 2677 611 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 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 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 Ea
60. 75362165 28636 600000000035 6 593737048123425 San Diego 27588 159999999956 6 815256916996036 26842 979999999978 6 632809488510002 San Francisco 2265 8399999999997 3 469892802450229 2175 3399999999965 3 237 11309523809 GOR 6982544000000018 5 526690708930452 72451 62999999976 6 542498645475868 Portland 26408 839999999927 6 673955016426567 28649 949999999957 6 651950313443222 L Salem 43416 599999999955 6 598267477203641 143801 679999999935 6 472835820895512 WA 131349 98000000056 6 469486282815375 132443 24000000066 6 466639324251778 Bellingham 2322 340000000001 3 435414201 1834337 241 6 89 3 4330823863636364 Bremerton 24979 130000000005 6 655776711963764 27917 170000000016 6 771081736599568 Seattle 28369 209999999926 6 595956754243182 24274 859999999957 6 6415485636114795 Spokane 25486 37999999994 6 712241243086631 24148 080000000016 6 707800000000004 Tacoma 37286 79999999995 6 73289996388587 37557 15999999998 6 651994332270631 Walla Walla 2184 610000000001 3 4081279251170065 2521 3600000000015 3 612263610315188 Yakima 110721 509999999997 l6 71 3531621790856 113607 72000000001 6 6 621761557177623 DataStore 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
61. 9 In the screen that appears click on the Infer Schema button Enter any range specified above in the Dynamic Parameters dialog box that appears and click the Finish button The schema will be inferred Change the Data Type of Emp_Id and Emp_Sal to Integer Finally click on the Finish button Figure 5 13 Completed DataSource Wizard Figure 5 13 Completed DataSource Wizard DataSource Wizard Define Excel Datasource Enter Excel datasource parameters Name Excel 3 Description URL file C DataDesigner EmpDetxIs Range Range v Has Row Header Header Row 1H _ XLSX Format Previous Click the Finish button in the DataSource Wizard The Excel data source is added to the repository Double click on Excel 3 ds Click the Load Data menu The Dynamic parameter dialog box pops up as shown in Figure 5 14 Dynamic Parameters Enter one of the range names previously defined Emp1_Small Emp1_All etc Click the Finish button 88 Excel DataSource Figure 5 14 Dynamic Parameters De Dynamic Parameters X Dynamic Parameters Enter dynamic parameter values Range Previous Finish Cancel 20 The Empl_Small range is used to fetch only selected part of the data from sheet1 of the Excel file as shown in Figure 5 15 Emp1_Small Result The Emp1_All range is used to fetch all the data in E
62. Absolute A B Relative Cc Here A is the main element having sub 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 100 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 7 2 Define XML DataSource Figure 7 2 Define XML DataSource DataSource Wizard Define XML Datasource Enter XML datasource parameters Name required Name Description Input Format XML Date Format yay MM dd Time Format HH mm ss Timestamp Format yyyy MimM dd HH mm ss Previous t Finish Cancel The unique name of the data source is entered in the Name text box and any extra
63. 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 cerura mye Eme mnre packy 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 is on the server Add a FileSystem JDBC Stored1 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_Cal1 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 12 JNDI Values Figure 3 12 JNDI Values Datasource Wizard Define JDBC Datasource Enter JDBC datasource parameters Context Factory required Name JNDI_calll Descript
64. COMA A s Sain Ue A VEE eee hues Deedee eee 109 9 3 Text DataSource Parameters nocna nena tates puccustose ten ceens Rae nales dh tears te 110 8 4 Properties DataSource oir pened eases AA a 111 8 9 Derivative Wizarde cateo de e iii daban 112 SOS Test OMPI sont hec tabi eS EE EE A EE eee tal TE east un ada E beet E A EEEN 112 O ena a e E EE RNE sewed EE EER REESE pA 113 8 8 Prop Empi 2 A AS 113 9 90 Filtet Wizard as 114 t KOLOD IH DII AREA SS EE A E E N ITO t 114 9 1 Reference DataSource Wizard eosam cenna ii e E ORT EE EEA EEEE NEER 116 9 2 Reference DataSource Wizard Page TWO oocoooconnconoconoconccnnncnnncnnnonoronocnnconnccnnccnnccnnioos 117 93 EmpdataiXlS a a a venues decir E E EE EAS 117 A Denne Name eea ietie aaa 118 9S5 Excel DataSource Samples a nE o EE e REEE EER votes E RE EENES 119 9 6 Reference a DataSource rren rri n a E a EE R E E E A E E 119 9 74 Sample OD PUE sie e n N e das a Ea E S 120 9 3 Filter Condition oe ei e ea EE A EERTE E E EET EE EEN EA a 121 9 9 Sample RETERENCEss 55 c00s aver enn o a EE EE E E EE E OESE E ESSE 121 10 1 Object DataSource Wizard idin eee E EEE ES 124 10 2 Guided JavaScript Builder kossar een e dhs cease Uy E E a a ndai 125 10 3 JavaScript Editon il o ii 126 10 4 Sample Data lterator was cvedevssevsussnutensy ses nacion vend nes sy suites nedon ER RN leiste 129 10 5Sample Data Class ui Ad S 129 11 1 LDAP DataSource Wizard rasene a eE RRE EEANN 132 1142 Security Parameters
65. Data in a Single Worksheet ooooooncccccnnccnnccnnccnnccnncnnnconoconaconaconanoninnnss 81 Adding Data in Multiple Worksheets oooccocccoccnoconnccnnccnnconoconoconconaconacnninnoss 83 Passing Dynamic Parameters soeone cece cnee cece ce eene E I SE E SES 86 6 Text DataSource tiara eo deviated A a oa gael caweeuweba sagen uate AEI EEA 90 OVERVIEW i So a audi Seid NO S atts 90 Text DataSource Wizatd tessen a o n E R TEE R EEE E E NEESS ES 90 Working with Text DataSource ninesi u r E E E a a E 94 Using Separator Characters siooni en a e a eeno an eaea E 94 Defining a URL with a Dynamic Parameter oocoooconoconncnnccnnnnnncnnnconocnnccnnconnccnnions 95 Using Fixed Width Columns nyess a eon r eS EE e ES EREE EE ASS 96 Using Regular Expressions miorina e e E e A E E 97 Using Start and Stop Expressions e seeeseseeereerereesrereeseerrerereesresrerererereerereee 99 TeXME DataSource ct A E N tents E E das 100 OS 100 XML DataSource Wizard cis a dia 101 UR SNA 103 Subtres Opti Zatlons id a ec t 104 O NAO 104 EDIFAC T oro r a A as 104 Search 1n XPath Builder psie a dee NE E A Sa 105 Working with XML DataSources oocoocccoccnoccnncnnccnnccnnconnccnnccnnconnconncnnncnnncnnncnnraninonos 105 8 Properties DataSource A O 108 OVENI EW an er e E e ae Fa OE SO See ae oe an A Se eg 108 Properties DataSource Wizard nereo reee e den E a E Ea EE ETE 108 Working with Properties DataSources oooccocccoccnccnnconnconnccnncc
66. 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 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 32 Composite DataSource 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 is 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
67. EKE E E EEEE EEEE E EOE E EE EEE ET EEE EEE 12 3 7 Define DataSource Schema pesses n ag rea i a EE EA E EEEE cree ee 13 3 8 Define DataSource Options cooccoccnnccnnccnnccnnconnconoconconnrnnrnnnrnnnrnn seca conncrnnccnncnnncnnncinass 14 3 9 Connection Pool Wizard ccsiccsseessaeasesoric ase decuesesetevsb sussnbsssbstesseeeDebeg EEE T ESERE EEST 14 3 10 Right click on Connection Pool 2 2 00 00 cece cece ce ence nce e eee eeceeeeeeeeeeceeeeeeeeaeeea esau sean eeaes 16 3 11 Datasource Wizard ada ease irte 16 3 12 INDI Values 0 A AAA A A A Ei 18 4 1 Composite DataSource sipir cines raro EPEE E PSE ATES EEE NEPO SPa EE ESPRE RASS 21 A2 A RN 22 4 3 DataSource Wizard simile ci cir it rante 23 4 4 Sample A O 24 AD JOM WAZ at oss soos 55 cg stag sa oes eis seg adesh oossaas os geds T 25 4 6 Sample Jom Flo W ociosa tas esa ies ii 26 Al Wizard rpm R 27 4 8 Inner Jom RU E A 27 4 9 O ter Join Result ecosistema foliar PESE ESEESE EES PETES EE ih 28 4 10 Cross Jom Result soni eo e ae ad EE 29 AAV SOLE Wizard TO 30 412 Add Sort Item nsaan n te eve org A 30 4 13 Extract Opuons sucio a Ratio aiii stars 31 4 14 Sample Sort FloW rriei re top debe da ae ia cece ens bee EEES EE EEEE E Et 34 4 15 SOtt Dialog untar sl rin 36 4 16 Group on Each Result oooooccnncnncccncnnnccnccnnccnnconnccnnconncnnncnnncnnnrnnncnnronrnnccnncinnccnnions 36 4 17 Groupon A Result srira comadssetatealeesotesgseestsniessotss dosedav ess scree EERST
68. EN EEE E Ei 66 AN Cbe RESUILE ien O O AS NA 67 4 59 Sample Datastore FLOW ista bi 68 EIA O des tevsacetveutaesdeeeds 68 4 61 DataStore Wizard Connection Pool cooccoccocnncnnconcnnconcnnconcnnconcnoroncnnroncnaroncnnrononaronos 69 4 02 Custom Java Option Sree N sep eein e ee husdssetten deers NE EE emmrentden es 70 4 03 Excel OUIpUt ii IS RI A 71 4 64 XMI NO 73 4 65 Case Study Composite Diagram ooocooccnnccnnccnnccnnconnconoconncnnncnnnnnnnnnnronnrnnncnnconnccnnccnnions 76 4 66 View Cube Outputs wssuss sretdyct owtnssuy a R E E semaseapteamee N EE E a aE 78 5 1 Excel DataS ource Wizard ninio a E Un ve unde E a a a a vow ces 80 32 Excel DataSource Wizard cereais n a E A E RE E E a a aaa 81 D3 EMPII xls x tvcisee eos O E dh Sh ered Ped esas led iered at Sheed et ted neh rele 82 A Define Name rito ween 82 5 5 Completed DataSource Wizard r rairs e E A a O EE over seen EE E 83 NOR Ult e in lis 83 37e AS RA EN E merase sie TaN EE R res alse 84 5S Define Name von yecd evs oeanreses evn wes OO gh Texel 84 5 9 Completed DataSource Wizard 0 2 0 0 0c ccc ecece cece ence ence ence eeceeeceeeeaeeea seca cess eeaeeeaeeeaeeeenees 85 MO RESUIE A Cot hae N sad acetate en 86 DAT EMPATE IA dl wees 86 A OR 87 5 13 Completed DataSource Wizard ssori a e aE a E R 88 3 14 Dyna mie Parameters eena R ans dese E e E EE E a a ANER 89 3 15 Empl Small Rest a a i ria Ee E E a e ii 89 3 10 Emp I 2AM R s lt san n e N A a ave ne ORE EE E E 89
69. ES EERE PEINE 37 4 18 Group on Range Result cisci eccessi pen cece cece cece ne ceneeeeeeeeeeeeeeeeeaesea seen sean seas esas 37 4 19 Group on Range by Country Result oooccoccnnccnnccnnconnconoconoconoconccnncnnconccnnconnccnncons 38 4 20 Completed Sort Dial coi ri rs 39 4 21 Derivative Wizard ira In AAN iio End 40 4 22 Add Column Dialog sssi erine aeae e e E ines ies ia 40 4 23 Sample Derivative Flo W oterco ones ipod trote dolencia dectas st oe arre eae danes riel 41 4 24 Completed Add Column Dialog ooccoccnccnnccnnccnnconnccnnccnnconnconncnnncnnncnnronnronccnnccnnccnnions 42 4 23 Derved Resuli aida rra Eine RRN ERSS 42 4 26 Completed Add Column Screen ooocooccnccnnccnnccnnccnnccnnconnconnconncnnnrnnncnnronronccnncinnccnnios 44 4 27 Date Manipulations Result senesini i E E R 44 428 Filter Wizard soiien ninne a e e e ae e E aeaa aa dere 46 4 29 Sample Filter Flo Wo pcia rta inn Va p PESTO deaedeeeeu secede sees teases Y 48 4 30 Filt r R s lt cestode toeten ee onene nobody ii 48 4 31 Concat Wizard cisco pibe Aten ses ro E E pa 49 4 32 Sample Concat FOW siirros eee a EE id dees ees ae is 50 4 33 Parameter Wizard cestos posesa roS e eo ERAN Esop E PAEO NE SAPETE KOSTETE ESPE REVES 51 4 34 Completed Properties DataSource oooccooccnnccnnconnconeconncononnncnnnronronccnnccnnccnnccnncnnncnnncos 52 4 33 Sample Parameter FlOW socorro nte rotos yh E E E pdas 52 4 30 Parameter Result tds 53 4 37 SubEloW Sample
70. Elixir Data Designer User Manual Release 8 0 Elixir R pertoire Elixir Technology Pte Ltd Elixir Data Designer User Manual Release 8 0 Elixir Technology Pte Ltd Published 2010 Copyright O 2004 2010 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 EA E si deadadhoens Gives wa salen sotea need cadets sane Shes seed TE 4 3 JDBE Data oli 7 JDBC Drivel miss dr rapto tore ies 7 ODBC Connectivity sri reo a o E TE E nera a sis 8 JDBC DataSource Wizards aenema enoa aAA a EEr AE EEEE CAAA TA id 9 JDBC DataSource Detalle 9 SOL ies e e a a T E Ea R E e E E E peek dae 10 Infer Schema ont aai A AA E E ESE 13 A E E E E EE 13 Connection POOIS ni I OaE E E a a E a a i 14 Working with a JDBC DataSource oooooccnnccnnconoconoconnconncnnonnnconnconccnnconncconconnconnconicinnss 16 Using the JDBC ODBC bridge driver ooo
71. Enter SQL or use the Query Builder SQL required SQL 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 jdk there is 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 For JDBC datasource created with Repertoire 7 7 onwards the Query Builder model will be retained after exiting from the wizard unlike in older versions whereby user will need to create the model again when opening the wizard after exiting an exit For JDBC datasource s created with Repertoire 7 6 and below rebuild the model in Repertoire 7 7 and save it The model will th
72. Object DataSource Overview The Object DataSource allows data to be extracted at runtime from Java objects and Enterprise Java Beans If your Java classes comply with standard bean accessor patterns eg getX XX and is Y Y Y 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 10 1 Object DataSource Wizard In this screen the Object DataSource name and schema have to be defined
73. Order Descending E Group On Each Value E Derived Field 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 Figure 4 16 Group on Each Result Designer Script Data ME customer_id store_id store_sales 10277 11 4 76 10277 11 48 10277 11 5 28 10277 11 6 28 10277 11 8 84 10275 13 1 86 10275 13 1 92 10275 13 4 36 10275 13 5 04 10275 13 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 b Group On All 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 36 Composite DataSource Figure 4 17 Group on All Result Script Data Designer customer_id store_id store_sales
74. Regular Expressions Here s how to extract data from a log file like Figure 6 9 Log file This can be achieved easily by using the Regular Expression Access type Figure 6 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 97 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 a
75. Services to start out of process applications a a Oy l Key Distribution Center Service Account A a Members can 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 136 Chapter 12 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 12 1 Filesystem DataSource Wizard Figure 12 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
76. Small and enter range as given below in the Refers to text box and click the Add button HSineere il AGI 8 DHE Enter name as Emp1_All and 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 and the range as given below in the Refers to text box and click the Add button HSineeic 2 l GAS IL 3 SDSS Enter name as Entire_Sheets and the range as given below in the Refers to text box and click the Add button After adding the columns the dialog window appears as shown in Figure 5 12 Define Name Click the Ok button in the Define Name dialog window Figure 5 12 Define Name Define Name Names in workbook Entire_Sheets _ o Emp1_All Close Add Delete xX El Refers to sheet1 Sheet21 1 65536 Sheet1 Sheet2 A 1 D 11 Save the EmpDet xls file Launch Elixir Repertoire Using the filesystem or folder popup menu select Add gt DataSource The DataSource Wizard appears Select Excel DataSource and click on the Next button The Define Excel DataSource screen appears Enter name as Excel 3 Enter the EmpDet xls URL in field provided Alternatively by clicking the button on the right of the text field select the EmpDet xls file from the Open dialog Enter Range in the Range field Select the First Row Header check box and click on the Next button 87 Excel DataSource 16 17 18 1
77. Source Figure 7 8 XML DataSource Schema Define XML Datasource Enter XML datasource schema or use the XPath builder XPath Builder Root XPath TelephoneBook Telephone Column Name Type XPath Merge 1 EntrylD String EntrylD Space Space B Street Sting AddressiStreet 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 7 9 DataSource Results Figure 7 9 DataSource Results LoginName Street Ci 127 Wehner127 Robert Stol Robert Stol 141 Wehrmann Seitersweg Seitersweg 149 Weick149 _ Im Erlich 21 _ Im Erlich 21 Weber Rei Liebigstr 15 Liebigstr 15 Seitersweg Sei Heinheime Haardtring Vilbeler ve Mettegang Robert Stol 107 Chapter 8 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 8 1 Properties DataSource Wizard Figure 8 1 Properties DataSource Wizard De Datasource Wizard Define Properties Datasource f Enter Properties
78. You can then open the Textsep ds data source and click Load Data to view 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 95 Text DataSource You will find that on clicking the Infer Schema button a dialog appears as shown in Figure 6 7 Dynamic Parameters Figure 6 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 o
79. a comma In this case all lines will be used as there is no filter criteria 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
80. a 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 DataSource 1 Add a 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 EN 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 the 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
81. aSource named Sort 3 Now select the Sales data source drag and drop it on the Composite diagram 4 Place a Sort processor on the diagram 5 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 the 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 34 Composite DataSource 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 i 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 In the extract screen select the top option and enter 6 in the Amount field
82. ad 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 Figure 11 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 132 LDAP DataSource Figure 11 2 Security Parameters DataSource Wizard Define LDAP Datasource Enter LDAP datasource security parameters Authentication Type Simple User Name Protocol L i _ Password v Hide Password Previous Ji 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 gen
83. after inferring the schema unless you make the corresponding changes to the Excel file Working with Excel DataSource In this section we will look at adding an Excel DataSource with a variety of alternative ranges Adding Data in a Single Worksheet Let s start with an Excel file EmpInfo xls that consists of Emp_Id Emp_Name Dept_Name and Emp_Sal as shown in Figure 5 3 Emplnfo xls 81 Excel DataSource Figure 5 3 EmpInfo xls A B c D Emp_ld Emp_Name Dept Name Emp_Sal 1 Gerry Electronics 7400 2 Michael Electrical 8500 3 Frank Computer 9000 4 Lawrence Mechanical 6800 5 Jennifer Sales 6500 6 Christopher Electronics 8000 7 Flora Computer 8600 8 Cathy Electrical 7500 9 Peter Sales 7000 10 Kendy Mechanical 5900 Here s how to define an Excel DataSource containing all the Employee details l 2 Before adding an Excel DataSource the range must be defined in the Excel file Open the Emplnfo xls file Select the Name gt Define under the Insert menu The Define Name dialog box pops up Enter name as Employee and enter the range as given below in the Refers to text box Sinescitl IAS ils opel Here 1 corresponds to the first cell in the file and as there are a total of 11 records in this Excel file including the header 11 corresponds to the last cell containing the final record The Define Name dialog box appears as shown in Figure 5 4 Define Name Click the OK button i
84. 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 146 Chapter 16 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 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 16 1 Cache DataSource Wizard Figure 16 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 16 2 Cache DataSource Parameters This page allows the user to select the duration of the cache and extract the parameters from the datasource
85. ard rooie e eo a pyeaveeed raton Eon Dee ea noni E ai 131 Working with LDAP DataSource ooccooccoccnnccnnconnconnccnnconnconncnnncnnnrnnnnnnronncnnninncinncinns 134 12 Filesystem DataS OUrce edgen ne aan daneses nba load ewe dace a a a A a tec ejes 137 OVERVIEW E E pve hen dp Tedd age tea Tes Sec dees See deve EE ANGES 137 Filesystem DataSource Wizard e oerder a e aee EE nn rro cena sven n EN EE 137 Filesystem Sch ma ti da As ee ena 138 FileSystem DataSource in DBFS FileSystem In Remote Designer ocoooccccccnccccccnnccnicnnss 138 13 Tabular DataSource iia 140 OVERVIEW sds O SNS 140 Tabular DataSource Wizard ireccio i i eE a E EE E E E ER ai 140 T4 R ndom Dat Sourcer ianen t a N a E a ea a A e a E aee EAAS 142 OVERVIEW enin E E EEA EER T E EE e EE E E pease EE a A ERS 142 Random DataSource Wizard oc gsceecctecs dentro E E EEE E E T EE E nE 142 EXPLOSION Jorissen A E oad Ry E T 143 Literal seyss O NA 144 NN 144 HARFE DataSoUICE cores an eE Aste inn eaten 145 OVA WU o EE EER vee faa aS Ne RS eee 145 AREF DataSource Wizard ii vetoes 145 16 Cache DataSQUrCE ii ii eee 147 IO O NS 147 Cache Datasource Wizard iii a adi 147 17 DBEDatasOUT CO ein ia 149 OVERVIEW ca A A dios 149 DBF Datasource Wizard encoooc co nanne e EEE A E phens bows E RE attend 149 As Dynamic Parameters id R E dr as ee ey 151 Dynamic Parameters escis ioen eoan e ea E dt a EE EEAS 151 Dynamic Parameter Elements ooooocccccnncnnccnnccnnccnnccnnccnncon
86. 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 JDBC DataSource Select the JNDI tab Enter the context factory Enter the provider URL Enter the resource name The resource points to the database Enter the user name and password if required 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 20 Chapter 4 Composite DataSource 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 T
87. ata source The column can be edited by selecting the Edit Column button The Move Up Move Down are used to reorder the columns The Remove Column button is used to delete a column Fixed Width 91 Text DataSource On selecting the Fixed Width access type and clicking the Next button the screen appears as shown in Figure 6 3 Fixed Width Type Properties On clicking the Add Column button the Add Column dialog window pops up Figure 6 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 6 4 Regular Expression Type Properties 92 Text DataSource Figure 6 4 Regular Expression Type Properties E Dat source Wizard x Define Text Datasource i Enter regular expression details Regular Expression Start Expression Stop Expression Infer Schema
88. ata 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 check 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
89. bo box Select the Sort Order as Ascending from the Combo box Select the Range as the Group on option Enter the value given below in the Range text box USA a Mexico b Canada c Mexico and c group records with Country name Canada Figure 4 19 Group on Range by Country Result Designer Script Data as Showing 25 records C Count A store_id Country State City store grocery frozen meat integer String String String Double Double Double Double 19 Canada BC Vancouver 23112 0 16417 9737 4016 41575 2677 61050 20 Canada BC Victoria 34452 0 27463 3539 4193 18761 2795 45840 8 1 4 5 Mexico Mexico Mexico Mexico Yucatan Guerrero Zacatecas Jalisco Merida Acapulco Camacho Guadalajara 30797 0 23593 0 23759 0 24597 0 20141 2029 17474 9897 16844 4408 15011 5121 6393 47822 3670 80615 4148 73549 5751 29269 4262 31881 244720410 2765 82366 3834 19512 12 Mexico Zacatecas Hidalgo 30584 0 21938 0009 5187 59945 3458 39963 21 Mexico DF San Andres 3 Mexico DF Mexico City 36509 0 22450 0263 8435 38421 5623 58947 10 Mexico Veracruz Orizaba 34791 0 26354 0175 5062 18949 337 4 79299 18
90. cccoccnnccnnccnnccnncnnnconoconncnnncnnacnnrcnaronicnnos 17 Using Callable Statement 2s5 5 cs csssesiseseesgScstsessseovadheedsaodpecveesdseesdcussseomsuseveds 17 Using JNDI Connectivity sestien aKo Eer cece ence EKES EREE SEENE ESEE EE EEE TER 19 4 Composite DataS oUr Ceasers oa s neste sas EEE betes sea EEE E EEE DTE 21 ON ET VICW ern one A A A E E ia 21 Adding a Composite DataSource s s sissors iess sirip st esas EIEEEI EEn EDE SVEO TEOSE ES Epp 21 Standard Diagram Operations oocooccnccnnccnnconnconnccnnconnconnconncnnncnnnrnnrnnnrnnncnnccnnccnnccnnioos 22 Moving the proc ssOTS 4 cnnan EE A E E E AG 22 Flow Connection Nodes enine en E e E EE E aTa 22 Data ProcessoiS cunas ora raid 22 DataSource POESIA nae A EEEE vole veaune covey iden eeu boots vocevevseseces peace 23 PIOW tod a ed RR Ria E A A A ide 24 JOM Proc ss t yrnensis ur A AAA E ds 24 Working With SAA sca sgscyetes dene EEE ES PEETER Ops Epa EPT OSERE 26 Sort PrOCESSO is 29 Group On Options tenisere rasero pt irte rios 31 Working With Sort processors occooccnnccnnconoconocnnncnnncnnncnnronncnnconnconnccnnccnnconacinncos 34 Derivative Processors romei des sd ran nadaa 39 Working with the Derivative processor ocoocooccoconcnoconcnoroncnnroncnnroncnoroncnncononaronos 41 Filter Processor isis mania tsatagas raton rra PIERA rd is ESIC canoes 45 Combining Filters orita ets Gales ii EE E TI 46 Working with Filters sia Sea ease aaa Indian ela Ria 47 Concat Process ii
91. ce View and map schema for LDAP Datasource Infer Schema f D Column Name Type Previous 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 11 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 11 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 11 6
92. ce which pushes the data back along the flow through the filter which has already been configured 115 Chapter 9 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 UL 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 9 1 Reference DataSource Wizard Figure 9 1 Reference DataSource Wizard DataSource Wizard Reference a Datasource Choose the datasource to reference Name required Name DataSource ElixirSamples 3 Cube Processor With Column Totals After Details ds o gt amp Dashboard o gt amp DataSource gt amp Report o E Resources amp 04_ Samples o amp
93. ch 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 Note The last page of the wizard for a JDBC datastore is for the user to specify the location to store records that failed to add which are compiled into a datasource 71 Composite DataSource 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
94. cuotas tot tran ea EREET EEEa ETES IEE ESE SEE geass vases 53 vi Elixir Data Designer User Manual 4 38 o A O RS 54 4 39 Remove Duplicates Processor sinsero isene a E E E a E S E ISe 55 4 40 Invert Data Processor re ers enan aE oh ea nan Ea Ea EAEE A EA EEE wea E KEA 56 44 Javascript Process ici peed EES is tad 57 442 Map Field Wizard A suas cone teaeve peewee sda wen snesh sua csene ten E E apes Sate san yterseonees 57 4 43 SQL Processor Wizard eccesso en a E E EE E EE E E EE Ea 58 4 44 Sample DataDrop Flo Warenne E nel EAEE 59 4 45 Sample DataDrop Sub Flow siiret aie a i a E E a EEE SEESE EEn 59 44G CUBE WiZatd O E NEEN S 60 4 41 Add Hierarchy dit a r r a EU ea os en ed 61 4 48 Cube ARES CEN sirio dale dao van dae deal 61 4 49 Cube Measures Sereemm re iot ia E EERE EERE EET ER EE ES 62 450 Add Measure Dialog rone eane n E PN EE S EERS SERAS EE SaS 62 4 51 Cube Show Row Grand Totals oocooccoccoccncnoconcnnconcnnconcnnroncnnroncnnroncnnroncnnroncnnroncnns 63 4 52 Cube Show Column Grand Totals seris srne eneses esnan ae pe EEAS n es 64 4 53 Cube Row Totals After Details ooooccoccoccncnoconcnoconcnnconcnnconcnoroncnoroncnoroncnaroncnaronos 64 4 54 Cube Column Totals After Details ooooocconncoccnnccnnconnconnconncnonononononnnnonnnccncconcnnns 65 4 33 Cube Axes Edit Leve bara ran pes iis ae E EE T pais E TEREE E Deis cee eee 65 4 56 Sample Cube FlOW s siin O 66 4 57 Inter Schema CO A EE
95. d 127 Object DataSource void endData IDataSource src This method must be invoked after sending all records and groups to indicate that no more information 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 S
96. d Tables and Views can be selected to form the basis of the query JDBC DataSource 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 Show button is enabled if there are multiple schemas in the database From there the user will be able to select specific schemas to be listed under the Query Builder dialog If the Select Distinct check box on the right panel is checked then only 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
97. d 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 31 Composite DataSource 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 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 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
98. d Function In this screen the Pre defined functions 61 Composite DataSource 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 49 Cube Measures Screen De Cube Wizard Cube Measures Define cube measures At least one measure is required D Name Function Pattern Previous Next Finish Cancel On clicking the Add button the dialog box pops up as shown in Figure 4 50 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 50 Add Measure Dialog Add Measure Name v function average y Cancel Sometimes you might want to choose your own column names 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 subs
99. d 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 HTTPS pages 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 HTTPS is only supported from Repertoire 7 3 onwards HTML parsing is not available when Subtree Optimization is enabled EDIFACT EDIFACT is similar to a text file and is slightly easier to read compared to XML From Repertoire 7 4 onwards EDIFACT can be used to be parsed into XML The rest of the steps to create a XML datasource will be just like the other input formats 104 XML DataSource Search in XPath Builder In the field with a magifying glass on the right user can enter values then click on the magifying glass to do a search The name o
100. d 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 Figure 4 4 Sample Flow ES da EEN 5 Use the View option on the Result popup menu to display the records in the Data tab User can inspect the values of respective fields by double clicking on the flow and double clicking on the desired field Information like the total number of records maximum value minimum value and the average value of the data field are listed Join Processor m 24 Composite DataSource 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 Elixir 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 LF Join Wizard Join Identify fields to equate in the joined schema Name Join if no matching secondary If multiple matching secondaries f Options Primary I Secon
101. d on the standard flow events after a JDBC connection is set up on the second page of Processor Wizard as seen in Figure 4 43 SQL Processor Wizard Figure 4 43 SQL Processor Wizard Processor Wizard SQL Connection Set the SQL connection parameters JDBC Driver required JDBC JNDI Pool Driver Suggestions User Password v Hide Password Previous Next 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 Trim This task removes the leading and trailing spaces of string data DataDrop Processor A DataDrop is a pass through output mechanism used to generate 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 SubFlow 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 SubFlow between a DataSource and a DataStore as shown i
102. dary C Cross Join all permutations of primary and secondary Keep primary fields Repeat primary for each secondary Prefix for primary fields Prefix for secondary fields Previous 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 colour of the Join processor can be selected The dialog consists of three tabs Options Primary and Secondary 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 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 thro
103. der 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 On the Remote Designer it works slightly differently Only JDBC drivers that are available which have green symbols will be listed 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 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 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
104. der check box After setting the properties the DataSource Wizard appears as shown in Figure 9 5 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 and 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 118 Reference DataSource Figure 9 5 Excel DataSource Sample x DataSource Wizard Define Excel Datasource Enter Excel datasource parameters Name Emp Range Description URL fileC YourDirectory Empdata xls Range range v Has Row Header Header Row 1 XLSX Format Previous 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 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 9 6 Reference a DataSource On clicking the Finish button the Ref Ex1 ds is added to the repository Figure 9 6 Reference a DataSource DataSource Wizard Reference Datasource Parameters Enter parameter values
105. e 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 8 5 Derivative Wizard 111 Properties DataSource Figure 8 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 8 6 Test Output It can be seen that the department Computer is mapped to CPT Figure 8 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 22 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
106. e Processor D 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 59 Composite DataSource 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 46 Cube Wizard Figure 4 46 Cube Wizard ET Cube Wizard Cube Hierarchies Define cube hierarchies Name Cube Colour D Name Elements Previous Finish 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 47 Add Hierarchy where the hierarchy elements can be added and a name can be assigned to 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
107. e 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 protection for the JDBC password Right clicking on the pool file will list a few options like in Figure 3 10 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 11 Datasource Wizard The rest of the pages of the wizard will be the same as creating a JDBC Datasource using the JDBC tab JDBC DataSource Figure 3 10 Right click on Connection Pool Repertoire Designer 7 2 0 Elixir Repository D ElixirSamples E Sample ES 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 QA Figure 3 11 Datasource Wizard DataSource Wizard Define JDBC Datasource Enter JDBC datasource parameters Name required Name Description JDBC JNDI
108. e connection pool is being connected to If a valid URL to a connection pool is selected the description of the connection pool will be displayed as seen in Figure 4 61 DataStore Wizard Connection Pool Table Exists don t create If this option is checked the Table entered in the Table field will be updated accordingly If not a new table will be created However an error will occur if there is a table of the same name in the database Append Data When checked data will be updated to the specified Table accordingly Commit Data Checking this option will enable data to be updated in the database whenever changes are made If not new records added will not be reflected in the database Update If Record Exists This option will only work if the destination table has primary key s defined When checked the system will check for the presence of record s based on the primary key If found it will perform an UPDATE If not it will perform an INSERT This option is only available to JDBC JDBC JNDI and Connection Pool Figure 4 61 DataStore Wizard Connection Pool DataStore Wizard JDBC Connection Pool DataStore Choose JDBC connection pool storage options Table Required Connection Pool ElixirSamples DataSource JDBC Connection Pool m Description Name MySQL comm Pool Driver com mysql jdbc Driver Url jdbc mysqi lt host dbname User Table Dialect lt Default gt y _
109. e 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 changed 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 66 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 77 Composite DataSource Figure 4 66 View Cube Output al Fo M o p e Sum store_sales Average store_sales Sum store_sales Average store_sales All USA 280226 21000000346 6 542602554224825 285011 92000000546 6 476660455392571 9 CA 79050 7900000001 6 592510215995338 80117 04999999964 6 4345875833266115 Beverly Hills 23288 109999999946 6 797463514302378 22462 129999999986 6 627952198288576 Los Angeles 25908 679999999942 6 70514492
110. e 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 Data 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
111. e 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 values 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 155 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
112. e 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 Page 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 Xx DataSource Wizard Define Text Datasource Enter separator details Separator Tab Semicolon Comma Space Other Qualifier Infer Schema D Column Name Type Previous Now to load the data of the Employee d
113. eate a FileSystem DataSource in the same DBFS filesystem For Root enter the path of the DBFS filesystem It should be something like repository lt DbfsFileSystemName gt In the next page of the wizard user will need to enter the name of the property entered in the file previously which was Creator Note that this is case sensitive Click Finish to confirm the changes Load the datasource By scrolling to the outmost right of the loaded data user will be able to find two additional columns They are Keywords and Creator The keywords and user property entered for the files will be displayed 139 Chapter 13 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 13 1 Tabular DataSource Wizard Figure 13 1 Tabular DataSource Wizard ET Datasource Wizard Define Tabular Datasource D Enter Tabular datasource parameters Name Tabular Sample Desc
114. ect Date as the Data Type Enter offsetYears first_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 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 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 the given date Click the OK button The offset_Smonths_before column is added to the Derivative Wizard 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 43 Composite DataSource Figure 4 26 Completed Add Column Screen Derivative Wizard Derivative Add items items to the derived schema Type Value Ahead_5 days Date of setDays first_opened_date 5 Ahead_3_years Date offset
115. ed columns is displayed in the SQL window Click the Next button Instead of using the Query Builder to build the SQL query the query Selece Cusicemei 10 Stora ic me Score sales iron sales 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 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 1 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 JDBC DataSource 9 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
116. efer 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 SAMOA E 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 Derivative 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
117. el Working with Cube Processor The averages of frozen and store for the stores in the different states of Mexico have to be compared 1 Add the JDBC data source Stores 2 Add a Composite DataSource named Cube and drag the Stores data source over the diagram 65 Composite DataSource 3 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 56 Sample Cube Flow Figure 4 56 Sample Cube Flow 4 Open the filter properties and configure it to keep Country equals Mexico 5 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 Average the store field 6 Click the Next button and infer the schema The screen appears as shown in Figure 4 57 Infer Schema Screen Click the Finish button Figure 4 57 Infer Schema Screen Cube Wizard Cube Schema Cache Manage cube schema cache Infer Schema Column Name State String Mexico Average frozen Double Mexico Average store Double Previous Next Finish Cancel 7 From the Cube popup menu choose View Cube The output appears as shown in Figure 4 58 Cube Result The averages of frozen and store for different states in Mexico are dis
118. en be retained for future use 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 r Column Name Ma ura we il L Previous Next Finish Cancel JDBC Options Auto Commit When checked new record that is added will be updated accordingly Read Only When checked the datasource will only read from the database once From then the datasource will load data based on the data that was first read Data will be retrieved at a faster speed as the driver assumes that no changes is made to the database JDBC DataSource Figure 3 8 Define DataSource Options DataSource Wizard Define JDBC Options Leave empty or zero to use the driver default Max Rows Fetch Size y Auto Commit y Read Only Previous Finish Cancel These 2 features are only available from Repertoire 7 3 onwards Connection Pools Connecting to a relational database can be time consuming especially across a network The job
119. equirements General Debug This task allows the user to monitor the flow of records through the console without having to stop the flow From there user will be able to know where the problem lies and resolve the issue Before being able to see the data flow through the console user will need to edit log config xml Look out for the following in the file and change INFO to DEBUG lt logger name com elixirtech data2 gt lt level value INFO gt lt logger gt This task is usually used after another processor which processes the data and it is at this stage where data gets messed up Therefore with the use of Debug it will be easier to point out the source of problem 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 55 Composite DataSource 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 column
120. eric interface to services which may be provided by a variety of different security mechanisms 4 Digest MD5 In 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 11 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 133 LDAP DataSource Figure 11 3 LDAP DataSource Schema 3 Datasource Wizard Define LDAP Datasour
121. ers Name Excel 2 Description URL file C DataDesigner EmpDetail xls Range Empsh eets Has Row Header Header Row 1 __ XLSX Format L Previous Cancel Click the Finish button in the DataSource Wizard The Excel DataSource is added to the repository Double click on the Excel 2 ds data source Click the Load Data menu The Excel file is loaded displaying the employee details present in all the Excel sheets as shown in Figure 5 10 Result 85 Excel DataSource Figure 5 10 Result Data Result Emp_ld Emp_Name Dept_Name Emp_Sal integen String String Integer 1 Gerry Electronics 7400 2 Michael Electrical 8500 3 Frank Computer 9000 4 Lawrence Mechanical 6800 5 Jennifer Sales 6500 6 Christopher Electronics 8000 7 Flora Computer 8600 8 Cathy Electrical 7500 9 Peter Sales 7000 10 Kendy Mechanical 5900 11 Joseph Sales 5000 12 Lara Computer 8700 13 Nancy Electrical 9200 14 James Electronics 8000 15 Vincent Mechanical 5300 111 Jig Computer 7400 112 Jag Electronics 8000 Passing Dynamic Parameters To control the behavior of Elixir Data Designer dynamically dynamic parameters are used The parameters are specified in place of a value or values while setting the properties of a datasource or Composite element and the
122. erting 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 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 152 Dynamic Parameters of presentation it is more common to request Name before Address To achieve this we declare a dependency be
123. f 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 multiple 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 Figure 4 9 Outer Join Result Designer Script Data Result gt E Ad 8 Showing 500 records pricustomer pristore_id pristore_sal seccustom secgender secmarital_
124. f the field and the value will be displayed in the bottom field The field contained the value will be highlighted in the tree structure Text and numbers are accepted in the Search field 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 could have a web server provide the XML data on demand 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 7 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
125. fferent format below are now skipped If you wanted only those items below 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 99 Chapter 7 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 7 1 An XML Tree Figure 7 1 An XML Tree
126. file comprising directory name and extension URL String The url of the file using the file protocol FileSystem DataSource in DBFS FileSystem In Remote Designer Here is how to use User Properties which is applicable to all files in a DBFS FileSystem on Remote Designer 1 2 Create a DBFS filesystem using Elixir Repertoire Server web interface Add files into the filesystem by dragging them from a folder in Windows Explorer or files in other filesystems while holding the Ctrl key right click on file in other filesystem and select Copy or create files in filesystem explicitly Right click on any individual file and select Edit File Properties A window which 1s similar to Figure 12 2 File Attributes will appear User can enter some keywords for easy reference 138 Filesystem DataSource Figure 12 2 File Attributes File Attributes FruitSales ds File Attributes Modify file property General Security User Properties Name Value Created Date 2009 01 21 08 49 32 GMT Description Hidden false MimeType textx datasource Modified Date 2009 01 21 08 51 11 GMT Name FruitSales ds Size bytes 2516 Keywords Finish Cancel In the User Properties tab user can add or modify user property For demostration we will add an additional user property with Creator for Name and Thomas for Value Click on Finish to confirm all changes Next cr
127. h Numeric Date Time Timestamp with as separator Example 1998 01 01 1998 12 31 Timestamp dates Less Than Not Less Than Example 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 complex 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 DataSource 4 Connect the Customer data source to the Filter processor and connect the output of the Filter
128. hat 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 Object 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 b
129. he only difference is that this option creates a new column after each group of Measure s Each additional column is the sum of the values in that particular group Figure 4 54 Cube Column Totals After Details Sek All 9 15 9 16 23 24 23 24 Count emp_id Countfemp_id Count emp_id Count emp_id Count emp_id d All Bachelors Degree F 1 Mio Graduate Degree TF 1 M0 High School Degree F 1 M1 Partial College FO Mio Partial High School F 0 OjJO 0O N N OG 09 O g0 OD 0 0 G O JO O OJO gt gt OjJO J0O O N N g0 O 4 3 1 1 1 2 4 0 3 0 gt 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 all these affect the column names Note In order for Row Totals After Details and Column Totals After Details to display when generating the Cube Show Totals must be checked as shown in Figure 4 55 Cube Axes Edit Level As for Show Row Grand Totals and Show Column Grand Totals it is not necessary to check the option Show Totals Figure 4 55 Cube Axes Edit Level Edit Level Name gender v Sort Order Ascending Group On Each Value Canc
130. he 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 Finish button Figure 4 1 Composite DataSource Datasource Wizard Define Composite Datasource Enter Composite datasource parameters Name required Name Description Leen Previous ex Finish 21 Composite DataSource 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 s
131. he Sales and Customer data sources 1 Add the JDBC data sources Sales and Customer using the procedure given in the previous chapter 2 Add a Composite DataSource with the name Join 3 Select the Sales data source drag and drop it in the designer window Similarly drag and drop the Customer data source onto the designer 4 Create a Join processor on the diagram 5 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 Figure 4 6 Sample Join Flow 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 Customer data sources you can explore the result of setting different options for the If multiple matching secondaries a If multiple matching secondaries Repeat 1 2 Using the flow described above open the Join properties and select the Discard option from the If no matching secondary Combo box Select the Repeat option from the If multiple matching secondaries combo box 26 3 Composite DataSource 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
132. he 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 in Figure 10 5 Sample Data Class Figure 10 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 129 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
133. hown 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 scherna Name Result Colour J Type Discard Compa String Fruit String 2000 Double 1999 Double 1998 Double 1997 Double The name of the Result can be entered in the text box On clicking the colour button the choose colour dialog box is invoked from which the colour 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 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 22 Composite DataSource D L D a XQ gt B DataSource Flow Join Sort Derivative Filter Concat Parameter dF a D gt O 2 SubFlow Note
134. i 49 Working with Concat Processor coooconoconocnnccnnncnncnnoronccnnccnnconnconnccnnccnncnnncnnncnnnes 49 Parameter Processors naer A A A AA 50 Working with Parameter processor ocoocccoccnoccnccnnconnconnccnnccnncnnnconnconcnnnroncnnnicnnos 51 SUDElOWPLOCESSO iii ss 53 PLOCES SOP isis ach ica II E E E AR PA at 54 Cleamsin Bs i ieS E edie ESE Hes Ses TE See dees EE E a Se iS 54 General O 55 DataDrop Processor cememisrii iris E ENE EE EEE EEA REES 58 Notes rap E AEE E A a E E a RER 59 Cube Proceso A A A AEE E E E Cone TE 59 Working with Cube Processor sic sscsisessecosssaseesscespevecesdseesssvsssesteedivedawodgeeseendges 65 DataS tore Processor Aaeeea ecects ta voce oslus as 67 CSV Bil e ie erae ae a E asian teas ast aa dss ban EE S a Ra ea ate ae 68 Connection Pr e devs ee seine ee codeine A edd 69 Custom Java DataS tone oo ev nds cla ai undoes ie a eee 69 lil Elixir Data Designer User Manual A 70 JDBC vivia RA o lage iii 71 NOME eraa ae a e aa a ssanebusasecvouthee ss es huene don sdactasbeaes R deeb sasserons 72 VOX Pile scat cst E o ts asada 72 XML Pl a dt tias 72 Composite JavaScript viii ds AS a Sees WS seat oe ceased eek 73 O 75 Excel DataSource ie i hcdeas seeds aed esas DAG bt 80 A comiecehanegeons tue ebpapedebeeehuge sue hiee disse sun E EERS TEEN 80 Excel DataSource Wizard ii dis 80 Working with Excel DataSource oooccocccnccnncnnncnnccnnccnnconnconnccnnconnconncnnncnnncnnnrnnncnarenoss 81 Adding
135. ies 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 8 4 Properties DataSource 110 Properties DataSource 11 12 13 14 15 16 17 Figure 8 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 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 th
136. ilter 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 73 Composite DataSource 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 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 Within 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 r
137. in 2 76 Composite DataSource e Inthe Options tab window enter If no matching secondary Discard record and If multiple matching secondary Repeat primary for each secondary 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 Cube 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 th
138. in the root XPath press Enter Figure 7 6 XPath Builder XPath Builder Ci TelephoneBook gt C Telephone o C Telephone gt 3 Telephone gt C Telephone o C Telephone o C Telephone gt C Telephone o C Telephone o C Telephone o C Telephone o C Telephone o C Telephone o C Telephone e e e e e e e e e e e e A AA 105 XML DataSource 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 When 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 Add
139. ion JDBC JNDI Pool Context Factory Provider URL Resource Name User Password Hide Password Previous Next Finish Cancel 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 18 JDBC DataSource call pack selemp 7 Where pack is the package name and selemp is the name of the function 10 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 www 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 a
140. ity1 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 153 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 loading 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 ente
141. 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 Software Inc and others All Rights Reserved You must accept the terms of that agreement to use this software 156
142. l 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 8 Properties DataSource 51 Composite DataSource 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 Tabular 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
143. lectrical 8500 4 3 Frank Computer 9000 5 4 Lawrence Mechanical 6800 6 5 Jenifer Sales 6500 716 Christopher Electronics 8000 87 Flora Computer 8600 9 8 Cathy Electrical 7500 109 Peter Sales 7000 11 10 Kendy Mechanical 5900 12 M 4 gt 1 Sheet1 Sheet2 1 Before adding an Excel 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 117 Reference DataSource 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 Sinaeie 2 ASL DSE 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 9 4 Define Name Click the Ok button Figure 9 4 Define Name Define Name 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 O Enter the Range as Range and select the First Row Hea
144. mation 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 59 Sample Datastore Flow Of course usually there will be a sequence of processors that act on the records as they pass through 67 Composite DataSource Figure 4 59 Sample Datastore Flow Note When trying to generate a Composite datasource with multiple Datastore processors by right clicking on the datasource in the Repository panel and selecting Generate user is able to select the Datastore processor to generate 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
145. me 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 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 this 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 f
146. menu 15 The Excel file is loaded displaying the employee details present in the Excel sheet as shown in Figure 5 6 Result Figure 5 6 Result Data Result y Emp_ld Emp_Name Dept_Name Emp_Sal Integen String String Integer 1 Gerry Electronics 7400 2 Michael Electrical 8500 3 Frank Computer 9000 4 Lawrence Mechanical 6800 5 Jennifer Sales 6500 6 Christopher Electronics 8000 F Flora Computer 38600 8 Cathy Electrical 7500 9 Peter Sales 7000 10 Kendy Mechanical 5900 Adding Data in Multiple Worksheets This time we will work with the file EmpDetail xls This consists of employee details in three worksheets as shown in Figure 5 7 EmpDetail xls 83 Excel DataSource Figure 5 7 EmpDetail xls Cc D 1 Emp_Id Emp_Name Dept Name Emp_Sal 2 1 Gerry Electronics 7400 3 2 Michael Electrical 8500 4 3 Frank Computer 9000 5 4 Lawrence Mechanical 6800 6 5 Jennifer 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 M 4 mM Sheetl Sheet2 Sheet3 Here s how to get the values across all the sheets 1 Before adding an Excel DataSource the range must be defined in the Excel file 2 Open Empdetail xls file 3 Select the Name gt Define under the Insert menu The Define Name dialog box pops up 4 Enter name as
147. n Figure 4 44 Sample DataDrop Flow 58 Composite DataSource Figure 4 44 Sample DataDrop Flow ED E mi sind 7 e Then open the SubFlow e g double click on it and draw the flow as shown in Figure 4 45 Sample DataDrop Sub Flow Figure 4 45 Sample DataDrop Sub Flow Country USA save USA DataDrop 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 input DataDrop is particularly useful for storing records that fail data cleansing or data integrity checks for subsequent processing or for debugging complex composite diagrams Note e When trying to generate a Composite datasource with multiple Datadrop processors by right clicking on the datasource in the Repository panel and selecting Generate user is able to select the Datadrop processor to generate e From Repertoire 7 7 onwards user can enter any symbol in the Qualifier field instead of selecting from the drop down list Note L4 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 Cub
148. n placed on the diagram Properties The editable properties are shown in Figure 4 21 Derivative Wizard 39 Composite DataSource Figure 4 21 Derivative Wizard Derivative Wizard Derivative Add items items to the derived schema Name Derivative Colour Base Derived JavaScript Column Name Discard store_id Integer Country String State String City String store Double grocery Double frozen Double meat Double OJD on amp oo ho eS Previous Next Finish Cancel There are three tabs in the Derivative properties Base Derived and JavaScript e 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 e Onclicking 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 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
149. n the Define Name dialog box Save the Excel file Figure 5 4 Define Name Define Name Names in workbook Employee ooo a L E Refers to Sheet1 A 1 D 11 T Launch Elixir Repertoire Choose a filesystem or folder and use the popup menu to select Add gt DataSource The DataSource Wizard appears Select Excel DataSource and click on the Next button The Define Excel DataSource screen appears Enter name as Excel 1 Enter the URL for Emplnfo xls in the text box provided Alternatively by clicking the button on the right of the text field select the Emplnfo xls file from the File Open dialog 10 Enter Employee in the Range field 82 Excel DataSource 11 Select the First Row Header check box and click on the Next button 12 The screen appears as shown in fig Click on the Infer Schema button Change the Data Type of Emp_Id and Emp_Sal from Double to Integer Figure 5 5 Completed DataSource Wizard Figure 5 5 Completed DataSource Wizard DataSource Wizard Define Excel Datasource Enter Excel datasource parameters Name Excel 1 Description URL file C DataDesigner Emplnfo xls Range Range Has Row Header Header Row L XLSX Format Previous Finish Cancel 13 Click the Finish button in the DataSource Wizard 14 The Excel Data Source is added to the repository Click the Load Data
150. 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 can 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 131 LDAP DataSource Figure 11 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
151. nconnconocnnronccnnrcnnnnnn 151 Dynamic Parameter Names essa nn onne an n IE OE E E EERENS 151 Dynamic Par meter Types iko sor a is A 151 Ordering Dynamic Parameters cosirer Re e aE REE REESS 152 Dynamic Parameters with a Nested DataSource cece cece cece eeceeeceeeea teen sean eeaes 153 Example Declaration of Dynamic Parameters cee cece ceeeeeeeee teen teen cece eens eene eens 154 B Samples sit RES ede Ii 156 AyallaDll 3 2 Sith des ofaaee ea hoa anio Soule debe daastt vas besdeen acco sontam da cion 156 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 DataSource cocido inca iio pies le n 5 2 4 Define Text DataSource Page One ooconoconccnnccnnocononnnrnnoronccnnccnnconncrnnconncrnncrnaronornnrnnoss 5 2 5 Define Text DataSource Page TWO ooocooccnnccnnconoconconoconncnncnnnronncnnccnnconnccnnccnncnnnconacnnnss 6 3 1 Create New DataSource ciscscsissoesis O 8 3 2 Select Database eiii ii is aa 9 3 3 JDBE DataSource Wizard issscatada icies cierras 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 ssri roerei rnai eera ceases EESE Arnal DEESSET EPA RIE 11 3 6 Callable Tod icons s E
152. ner window appears as shown in Figure 4 23 Sample Derivative Flow Figure 4 23 Sample Derivative Flow data base ae data result p H Derivative t b Deriving a new column using a formula Here s how to calculate the percentage of meat available in the stores 1 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 appears 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 41 Figure 4 24 Completed Add Column Dialog Edit Column meat_percentage tog meatistore 100 store_id Country State City store grocery rozen meat 7 Click the OK button and the column is added to the Derived tab window 8 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 Composite DataSource to the datasource displaying the percentage of meat space available in the stores Figure 4 25 Derived Result store_id
153. ng 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 source 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 e Generate Data When clicked data in the datasource will be generated and displayed The Elixir Data Designer Workspace The Composite DataSource workspace consists of three tabbed panels Designer Script and Data 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 JE gt A ElnirsamplesDataSource Composite ds 2 m Designer Script Data ri L S ala FISiticl alio Alz a B Zoom 100 w B al amp rom 3 Elixir Repertoire
154. nique 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 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 DataSource Figure 4 41 Javascript Processor Processor Wizard JavaScript Code Enter JavaScript codes to handle data events startData startGroup processRecord endGroup endData Previous Finish Cancel e Map Field Using Map Field wecan reference to another datasource and use the field s in that datasource and make our Composite datasource contain the field s in that selected datasource The field name can be amended if desired In the Map Field Wizard choose a datasource from the repository to reference Then add the fields to map Figure 4 42 Map Field Wizard Processor Wizard Map Field Map source field name to reference field name Reference Data Source Source Field Mapped Field Finish e SQL 57 Composite DataSource This task will allow the user to send any SQL commands to the database base
155. nnconnconncnnncnnncnnncnnroniconos 109 Testing the Data Blow serios dr detritos E E dows senohy EEES 109 Passing Parameters to the FIOW coretan oe e E a 113 O Reference DataSQUICE tacos fous haan teueacnss debian arteria lleida a 116 COVELVICW E be oe ah a ada Ali 116 Reference DataSource Wizard 0 csseesecssecesereesneeeceeres eros AE Epoa e e 116 Working with Reference DataSource ooooccoccnoconnconnconnconnconnconncnnncnnncnnronnrnnccnncinniinns 117 Wrapping an Excel DataSource 0 1 cece cee cence cece eee ce seca ceca tena eeue een eeneeennees 117 Wrapping a Composite DataSource 0 0 0 0 cece cece cece cence neceeeeeeeceeeeeeeaeeeaeeenes 120 10 Object DataSource Sn e n a E ae enero icons 123 OVEIVI aa A Ri 123 A O 123 Object DataSotirce Wizard occ ssh cutee A A ee abun 8 ee 123 Guided JavaScript Builder skosie nopee pons iebonpsuanesep tee dospes secs paneeabene 124 Manual Creta Miu ee ls ne ase ee eee 125 Object DataSource A Plas ie erran d ves ieck hes dae pesado nsganadedecssuocee AE AE 127 PushContext SS 127 Datalistenen estais dde 127 Data Pi IA Sates ae yin Say aaah Sora bed 128 A sacs cose saga veae aesg wok jaadties idee a a RE daae ES 128 Working with Object DataSources 20 00 00 cee cece cerca ceca ceca ronnccnnconnconnconnciniss 128 11 EDAPDataSQUice occa ses soredes n A liar necesa cite AE ETTE 131 OVEIVIEN AS A IDEN 131 Elixir Data Designer User Manual LDAP DataSOurce Wiz
156. 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 Figure 4 10 Cross Join Result Designer Script Data Result mo a 8 showing 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 4 M M 3 15 2 79 5 F S 3 15 2 79 6 F S 3 15 3 36 1 F M 3 15 3 36 2 M S 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 Sort Processor a 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 29 Composite DataSource 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 Ex
157. nt format Notice that the line Unmatched Entries is not in the output 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 di
158. 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 separated 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 y
159. 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 9 Connection Pool Wizard The Test Connection button will only be enabled when the driver is being specified and will allow the user to test whether the connection to the database is available A window will pop up to indicate if the connection has succeeded or failed Figure 3 9 Connection Pool Wizard Connection Pool Wizard Define JDBC Connection Enter JDBC datasource parameters Name required Name Description JDBC Driver Suggestions URL User Password Hide Password Test Connection Previous Next Finish 14 JDBC DataSource 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 Elixir Repertoire Server contains the necessary driver for your chosen database Note Dynamic substitutions such as password
160. oolean processRecord DataRecord record void endGroup DataGroup group This method must be invoked before sending any records or groups to allow the listener to prepare 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 processe
161. oose a file system or folder and use the popup menu to select Add gt DataSource 2 The DataSource Wizard appears Select the Text DataSource and click the Next button 3 The Define Text DataSource screen appears 94 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 6 6 Sample Text DataSource Click the Next button Figure 6 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
162. ou 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 144 Chapter 15 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 15 1 ARFF DataSource Wizard Figure 15 1 ARFF DataSource Wizard DataSource 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 15 2 Define ARFF Schema 145 ARFF DataSource Figure 15 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
163. ource 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 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 10 4 Sample Data Iterator 128 Object DataSource Figure 10 4 Sample Data Iterator JavaScript Generator Select the class and method that will provide the iterator Data Iterator Provider com dummy test Company Method Iterator Type a erator ge y jay Iterator getEmployeesAsList wa util List J util A hashCode0 int void void f ja etEmployeesAsEnumeration java util Enumeration j Ah en 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 t
164. played 66 Composite DataSource Figure 4 58 Cube Result ya 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 CSV Connection Pool Custom Java DataStore Excel JDBC JDBC JNDI Text and XML From Repertoire 7 3 onwards Clob and Blob data types can be written to the database using the DataStore processor 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 transfor
165. plicate 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 DataSource Figure 4 39 Remove Duplicates Processor LF Processor Wizard X Remove Duplicates Select the fields that determine duplication _ Input Sorted Select All Deselect All Name Type Test store_id Integer Country String State String City String store_sqft Long grocery_saft 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 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 r
166. ppears Enter a unique name such as Text_RegExp The URL in this case 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 CLAS 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 6 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 6 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
167. pression 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 Figure 4 11 Sort Wizard Sort Wizard Sort Add sort details Name Sort Colour D Name Sort Order Group On Group Data Previous Next 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 Cancel Inthe Name Combo box the field name of the data source based on which the sorting has to be done is selected 30 Composite DataSource 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 algori
168. ption 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 selected 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 33 Composite DataSource 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 e 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 Ho
169. r 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 DataStore 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 Elocalhost 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 79 Chapter 5 Excel DataSource Overview Excel is a spreadsheet application developed by Microsoft By adding an Excel DataSource you can access the data directly from the Excel file From Repertoire 7 4 onwards XSLX format Excel 2007 format will be supported A range of data can be loaded including support for both single and multiple sheet 3D ranges An Excel DataSource can be created simply by right clicking on an Excel file on the repository and select Define as an Excel DataSource Excel DataSource Wizard A wizard will guide you through the process of creating a new Excel DataSource as shown in
170. r items in the schema Name Filter Color Fitter 1 Fitter 2 Filter 3 JavaScript Column Name Type When Condition 1 Emp_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 3 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 values the screen appears as shown in Figure 9 9 Sample Reference On clicking the Finish button the Ref Com1 ds is added to the repository Figure 9 9 Sample Reference DataSource Wizard Reference Datasource Parameters Enter parameter values for this datasource Extract Parameters ss 7000 Previous Finish Cancel 4 Repeat the process to create Ref 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 121 Reference DataSource 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 122 Chapter 10
171. r 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 Emp1_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 154 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 Rang
172. r 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 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 9 8 Filter Condition Click the Finish button 120 Reference DataSource Figure 9 8 Filter Condition ET Filter Wizard E Filter Choose filter options fo
173. re 4 65 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 In the Options tab window enter If no matching secondary Discard record If multiple matching secondary Repeat primary for every secondary 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 only 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 ey 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 Jo
174. re 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 JNDI uses the connection pooling technique to connect to JDBC datasources Connection pooling is a technique that can 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
175. ress element and drag these in turn over the lt New gt column You should now see Figure 7 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 Figure 7 7 XPaths Completed XPath Builder Root XPath TelephoneBook Telephone rm 1 2 3 4 Lastn D ee Field EntryiD LoginName Telephone Country Firstname XPath EntryiD LoginName Address T Address C IN Date_of_Birth Merge Space Space Space Space IN Company_Name 127 Wehner127 06150 413 Germany O Salutation 141 Wehrmann 06150 413 Germany 149 Weick149 06150 413 Germany Email 165 Wegener1 06150 435 Germany EI Address 171 Weden171 06150 438 Germany G Street 177 Weber177 06151 438 Germany IN City 183 Weber183 06151 438 Germany D ZIP 189 Weber Rei 06151 438 Germany 195 Webler195 06151 438 Germany y Country E Germany Telephone v Wedel207 Weden213 Germany City Weber219 06151 437 Germany Darmstadt You can now choose Ok to close the XPath Builder Back in the wizard you can see in Figure 7 8 XML DataSource Schema the chosen fields along with the default type String which is correct in this case If your values are integers or dates etc you can adjust the data types or any of the other information here 106 XML Data
176. ription 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 shown in Figure 13 2 Tabular DataSource Wizard Page Two 140 Tabular DataSource Figure 13 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
177. rs 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 Combining Filters All criteria entered on a Filter tab must be true for the record to be passed through For example using ElixirSamples DataSource 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 records where CompanyName is B regardless of the value of Fruit 46 Composite DataSource Table 4 1 Filter Criteria When Condition Remarks e Equal Not Equal e More Than Not More Than Type Comparable types String Numeric Date Time 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 wit
178. s described below Figure 4 40 Invert Data Processor Processor Wizard Invert Data Determine inversion column and retained fields Column store_id Field Name Name s Istore_id Integer Country String State String City String store Double grocery Double frozen Double meat Double A IDE Donpnoni Previous Next Finish 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 Gi ie do N y al Go os g 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 Field 2 6 A iS 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 u
179. sen 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 allows 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 143 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
180. sers very flexible filtering and extraction options DataSource Wizard The first Text DataSource properties screen is shown in Figure 6 1 Define Text DataSource Figure 6 1 Define Text DataSource E 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 MbM 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 90 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 te
181. 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 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
182. te 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 the 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 C
183. text box This should be a unique name Description Any extra description that is used to describe the data source 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 137 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 Extension String The file extension not including the Directory String The directory containing the file Size Long The file size in bytes LastModified Long The numeric value of the last modified date LastModifiedDate Timestamp The timestamp value of the last modified date Path String The full path of the
184. 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 2003 XP Vista 2008 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 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 ft ODBC for Oracle Dar Preis rene 1 Ah 1 ll Mona sar 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
185. 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 7 4 Merge Space Figure 7 4 Merge Space Root XPath ROOTIA
186. 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 For example Enter your Id Dynamic Parameter Types e 6 A plain text field the default e password A password field so the typed characters are masked e choice A 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 1s 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 151 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
187. ther file URL that conforms to the same schema ie has the same number 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 96 Text DataSource 8 Add a column named Eno with data type String and width 3 9 Add a 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 6 8 Fixed Width Sample Click the Finish button Figure 6 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
188. thm 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 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 Figure 4 13 Extract Options EF Sort Wizard X Extract Set extraction value as a string or through the expression builder Expression Builder 8 Top O Bottom Amount o C Percent Level oH Set Value Extract Previous Next Finish Cancel e 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 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 e The level field indicates the level at which the extraction algorithm applies A value of O indicates that it applies over all records A value of 1 indicates the top n records of each group level 1 will be retaine
189. thod from the list of available methods the method name and the Iterator type is displayed in the corresponding text fields 124 Object DataSource Figure 10 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 10 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 windo
190. titution 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 14 2 Edit Column 142 Random DataSource Figure 14 2 Edit Column Add Column Name CompanyName Data Type String Value Type Expression O Auto Increment start O Range to e Values Trinity Diamond 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 cho
191. titution 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 e 1 0 2 gt Oregon US Married another index eg 62 Composite DataSource 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 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 sho
192. 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 functions 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 s
193. to verify the correct data is returned 130 Chapter 11 LDAP DataSource Overview LDAP is a Lightweight Directory Access Protocol It is a protocol for accessing 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 11 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
194. tore_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 DataSource 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 shown 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 dat
195. triggered by name from 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 1s 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 Mas currenti mesas als Seas cal Dare 0 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 the output file with a ds extension
196. tween 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 Singapore 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_sta
197. ugh 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 25 Composite DataSource 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 fields 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 t
198. umn called Salary with type Integer and value 7500 The values are shown in Figure 8 8 Prop_Empl Figure 8 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 113 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 8 9 Filter Wizard Click the Finish button Figure 8 9 Filter Wizard Da Filter Wizard E Filter Choose filter options for items in the schema Name Filt_Emp Filter 1 Filter 2 Filter 3 JavaScript Column Name when Condition
199. 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 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
200. ur 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 e 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 records 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 e 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 Add the JDBC data source for Sales using the procedure given in the previous chapter 2 Add a Composite Dat
201. value is assigned during the generation process The generation process may prompt for parameter entry Parameters are defined as name value pairs where the name must be unique in the scope of the parameter Refer to Appendix A Dynamic Parameters for further information If we need a different range of data to be loaded each time then changing the ranges values in the DataSource Wizard will be a time consuming process Dynamic parameters can be specified for the Range in the DataSource Wizard and the values are obtained when the DataSource is used The file EmpDet xls consists of employee details in two worksheets as shown in Figure 5 11 EmpDet xls If you want to fetch the values in all the sheets of the Excel file using different range values here s the process Figure 5 11 EmpDet xls ABE C as 1 Emp_Id Emp_Name Dept Name Emp _Sal 2 1 Gerry Electronics 7400 3 2 Michael Electrical 8500 4 3 Frank Computer 9000 5 4 Lawrence Mechanical 6800 6 5 Jennifer Sales 6500 T 6 Christopher Electronics 8000 8 7 Flora Computer 8600 9 8 Cathy Electrical 7500 10 9 Peter Sales 7000 11 10 Kendy Mechanical 5900 M 4 gt bi Sheet1 Sheet2 1 Before adding an Excel DataSource the ranges must be defined in the Excel file 86 Excel DataSource 10 11 12 13 14 15 Open the EmpDet xls file Select Name gt Define under the Insert menu The Define Name dialog box pops up Enter name as Emp1_
202. vears firsi_opened_date 3 loffset_20days_before Date offsetDaysffirst_opened_date 20 loffset_Smonths_before Date offsetMonthsifirst_opened_date 5 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 Figure 4 27 Date Manipulations Result Ahead_3_years offset_20days_before offset_5months_before Date Date Date Date 1951 01 24 1951 01 29 1954 01 24 1951 01 04 1950 08 24 1955 03 18 1955 03 23 1958 03 18 1955 02 26 1954 10 18 1957 04 13 1957 04 18 1960 04 13 1957 03 24 1956 11 13 1957 11 24 1957 11 29 1960 11 24 1957 11 04 1957 06 24 1958 09 23 1958 09 28 1961 09 23 1958 09 03 1958 04 23 1959 06 14 1959 06 19 1962 06 14 1959 05 25 1959 01 14 1968 03 25 1968 03 30 1971 03 25 1968 03 05 1967 10 25 1969 06 28 1969 07 03 1972 06 28 1969 06 08 1969 01 28 1969 07 24 1969 07 29 1972 07 24 1969 07 04 1969 02 24 1970 04 02 1970 04 07 1973 04 02 1970 03 13 1969 11 02 1970 05 30 1970 06 04 1973 05 30 1970 05 10 1969 12 30 1971 05 21 1971 05 26 1974 05 21 1971 05 01 1970 12 21 1974 08 23 1974 08 28 1977 08 23 1974 08 03 1974 03 23 1976
203. w 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 125 Object DataSource Figure 10 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 126 Object DataSource You can use the JavaScript Builder to generate a first cut and edit it if you prefer Remember t
204. with Keep Row Totals above this option inserts a total into the output Each value at the outmost left of tree is the sum for the particular column Figure 4 52 Cube Show Column Grand Totals De 15 16 23 24 23 24 Count emp_id Count emp_id Count emp_id Count emp_id Countiemp_id Bachelors Degree F 13 1 0 1 3 2 M9 0 3 0 3 0 Graduate Degree lel 1 2 0 1 0 mM 10 0 2 0 1 1 High School Degree F 7 1 1 1 0 0 M7 1 D 1 1 0 Partial College Fi 0 3 1 3 0 M5 0 0 0 0 1 Partial High School F 5 0 0 0 3 0 mM 2 0 1 0 a 0 4 Row Totals After Details Similar to Show Row Grand Totals this option sums the total but only after the row s of each group of Measure s as shown in Figure 4 53 Cube Row Totals After Details Figure 4 53 Cube Row Totals After Details 15 16 123 24 23 24 2 Count emp_id Count emp_id Count emp_id Count emp_id e Bachelors Degree 9 Graduate Degree 2 High School Degree 9 Partial College 9 Partial High School O 0 Ojuo O a me ml wlwlo OG IO O 2 OG 2 N O O O OD gt jojo jujoju O Ni O0 0j 0 1 0 1 1 0 1 1 1 2 0 0 0 0 0 0 4 64 Composite DataSource e Column Totals After Details This options works the same way as Row Totals After Details T
205. wn 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 Show Row Grand Totals When outputting records to subsequent processors or Result an additional row is inserted to represent the row total of the tree Here s an example Figure 4 51 Cube Show Row Grand Totals All 15 16 ey 23 24 23 24 23 2 Countfemp_id Countfemp_id Counttemp_id Count emp_id Count emp_id O 14 12 115 E Bachelors Degree F 1 as Mo Graduate Degree F 1 M0 High School Degree F 1 M1 Partial College F0 MO Partial High School F 0 M0 4 0 13 2 ofjojujo m ojo oO oOjO O O N 2 OS O M PRM nN O 0 v 63 Composite DataSource Show Column Grand Totals As
206. xcel file sheetl as shown in Figure 5 16 Empl1_All Result Similarly the Emp2 range is used to fetch only selected part of the data from sheet2 of the Excel file and the Entire_Sheets range are used to fetch all the records from both the sheets Figure 5 15 Emp1_Small Result Electronics 7400 Michael Electrical 8500 Frank Computer 9000 Lawrence Mechanical 6800 Jennifer Sales 6500 Figure 5 16 Emp1_All Result Data Emp_ld Emp_Name Dept_Name Emp_Sal nteger String String nteger Gerry Electronics 7400 Michael Electrical 8500 Frank Computer 9000 Lawrence Mechanical 6800 E 5 gt emnifer Sales 6500 6 Christopher Electronics 7 Flora Computer 8 Cathy Electrical 9 Peter Sales 1 Kandy Mechanical 89 Chapter 6 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 u
207. xt 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 JDBC DataSource ODBC Connectivity Before using the JDBC ODBC bridge to connect to data sources the ODBC connectivity must be established 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
208. xt box Alternatively by clicking the button on the right of the text box the file can be selected from the Open dialog window Note 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 6 2 Separator Type Properties appears The separator details are entered in this screen Figure 6 2 Separator Type Properties DataSource Wizard Define Text Datasource Enter separator details Separator Tab Semicolon Comma Space Qualifier y Infer Schema Column Name a ar 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 can be selected or entered in 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 d
209. 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 if they weren t already provided for you A Sample of the Tabular DataSource XML file is shown in Figure 4 64 XML Output 72 Composite DataSource Figure 4 64 XML Output E XmlOutput xml Windows Internet Explorer CAGA T E E xXmlOutput xml x X F Search web Af d El Y Favorites A PC Heath O Viewpoint v Web Search 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 type Double gt lt ds column na

Elixir Data Designer User Manual

Contents

Download Pdf Manuals

Related Search

Related Contents