Working with a Reference DataSource

Working with a Reference DataSource
Prev	Chapter 9. Reference DataSource	Next

The different ways of using a Reference DataSource are as follows:

Wrapping an Excel DataSource

In this illustration, an Excel DataSource has been added in which a parameter has been specified for the range. The values are expected to be entered during the loading of the Excel file.

Use the following process 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

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 -> Define under the Insert menu. The Define Name dialog box pops up.
Enter the name as Emp1_All and enter the range as follows in the Refers to text box. Click Add.
```
=Sheet1!A$1:D$11
```
Enter the name as Emp2_All and the range as follows in the Refers to text box. Click Add.
```
=Sheet2!A$1:D$6
```
Enter name as Emp_All and the range as follows in the Refers to text box. Click Add.
```
=Sheet1:Sheet2!A$1:D$11
```
After adding the columns, the dialog window appears as shown in Figure 9.4, “Define Name”. Click OK.
Figure 9.4. Define Name
Save the Empdata.xls file.
Open Elixir Repertoire and create a new Excel DataSource called Emp-Range. Enter the Empdata URL (file path) in text field provided, or click the browse button to the right of the text field, to select the Empdata.xls file from the Open dialog.
Enter the Range as ${Range} and select the First Row Header option. After setting the properties, the DataSource Wizard appears as shown in Figure 9.5, “Excel DataSource Sample”. Click Next. In the screen that appears click Infer Schema. Enter any range specified above in the Dynamic Parameters dialog box that appears and click Finish. The schema will be inferred. Change the data type of Emp_Id and Emp_Sal to Integer. Finally, click Finish.
Figure 9.5. Excel DataSource Sample
Click Finish to add the Emp-Range.ds file to the repository.
Choose Add -> DataSource again and select Reference DataSource. Click Next.
In the "Reference a DataSource" screen that appears enter the Name as Ref-Ex1 and select 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”. Click Finish, to add Ref-Ex1.ds to the repository.
Figure 9.6. Reference a DataSource
Similarly, add another Reference Datasource called Ref-Ex2 which also references Emp-Range. Enter Emp2_All in the Range text field. Click Finish, to add Ref-Ex2.ds to the repository.
Finally, add a Reference DataSource called Ref-Ex3 as above, but enter Emp_All in the Range text field.
Now we have three sample Reference DataSources. Open the Ref-Ex1 data source. In the data window, click 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
Open Ref-Ex2 and load the data to view the data from the second sheet of the Excel file.
Repeat the same action on Ref-Ex3 to view the data from both the worksheets of the Excel file.

Thus all the three Reference DataSources refer to the same Excel file, but parameterize it in different ways. This means there is only one place to define common information, such as the Excel file location, making the 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.

Add a new Text DataSource called Employee and set the URL to reference the sample Empinfo.txt file. Select the First line is header option. Select the Access type as Separator Character. Click Next.
In the screen that appears, enter the Qualifier as ". Select Semicolon as the Separator. Click Infer Schema The fields of the text data source are inferred. Click Finish to add the text data source to the repository.
Now add a Composite DataSource called Compo. Drag and drop the Employee.ds file 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 the Filter#1 tab. In the row corresponding to the Emp_Sal field, select the Less Than option from the combo box of the When column. Enter the condition as ${Salary}. The screen appears as shown in Figure 9.8, “Filter Condition”. Click Finish.
Figure 9.8. Filter Condition
Connect the output of the Filter processor to the input of the Result.
Now add a Reference DataSource named Ref-Com1. Select the Compo data source from the DataSource combo box. Enter 7000 as the value for the Salary parameter. After entering the values, the screen appears as shown in Figure 9.9, “Sample Reference”. Click Finish, to add Ref-Com1.ds to the repository.
Figure 9.9. Sample Reference
Repeat the process to create Ref-Com2 and enter 8000 as the Salary value.
Open Ref-Com1.ds and notice that only the records of employees whose salary is less than 7000 are displayed.
Similarly, open Ref-Com2.ds and check that only the records of employees whose salary is less than 8000 are displayed.

Both the Reference DataSources refer to the same Composite DataSource, but parameterize it in different ways.