Creating Metadata Activity

 

 

 

Step

Action

1. 

Make sure the Pelican project is open; "Pelican" appears in the blue band at the to of the ETL window. Begin by expanding the Metadata node.

2. 

Right-click the Db Connections node.

3. 

Click the Create connection menu item.

4. 

The Database Connection window appears.

 

Enter foodmart into the Name field.

5. 

Click the Next button.

6. 

Click the DB Type drop-down.

7. 

The foodmart database is a PostgreSQL database. You can specify either PostgreSQL or PostgresPlus. 

 

Click the PostgresPlus list item.

8. 

Enter jasperdb into the Login field.

9. 

Enter password into the Password field.

10. 

Enter localhost into the Server field.

11. 

Enter foodmart into the DataBase field.

12. 

Enter public into the Schema field.

13. 

Check to see if you have correctly configured the connection.

 

Click the Check button.

14. 

If the connection is not successful, check carefully for typos, then test again.

 

Click the OK button when the connection tests successfully.

15. 

Click the Finish button to save the configuration and close the window.

16. 

Confirm the location of the database connection within the metadata repository.

 

In the Repository panel, expand the Db Connections node.

17. 

Note: The foodmart database connection is in the Db Connections folder, and appears as foodmart 0.1.

 

Task 1: Create a database connection, is complete.

18. 

Next, use the connection to retrieve several table schemas from the foodmart database. In subsequent activities, you use these schemas to read database tables.

 

Expand the foodmart 0.1 node.

19. 

Right-click the foodmart node.

20. 

Click the Retrieve Schema menu item.

21. 

Note: The Schema window appears. To retrieve table schemas from the database, you establish one or more filters to match against table names in the steps that follow. 

22. 

Note: Add the following filter expressions using the % wildcard character to match the table schemas to be retrieved.

 

1. customer%

2. product%

3. sales_fact%

4. store

23. 

Add the first filter: customer%

 

Click the New button.

24. 

Enter customer% into the Filter Name field.

25. 

Click the OK button.

26. 

Note: The customer% filter appears.

27. 

Enter product% into the Filter Name field.

28. 

Click the OK button.

29. 

Create the sales_fact% filter.

 

Click the New button.

30. 

Enter sales_fact% into the Filter Name field.

31. 

Click the OK button.

32. 

Lastly, create the store filter.

 

Click the New button.

33. 

Enter store into the Filter Name field.

34. 

Click the OK button.

35. 

Note: The four filters appear in the Set the Name Filter pane.

36. 

Remove the filter that matches everything.

 

Click the % list item.

37. 

Click the Remove button.

38. 

The filters have been specified.

 

Click the Next button.

39. 

Note: The Schema window appears.

40. 

Now, select the table schemas to be retrieved.

 

Expand the foodmart node.

41. 

Expand the public node.

42. 

Select the table schemas to be retrieved.

 

Click the store option.

43. 

Click the sales_fact_1998 option.

44. 

Click the product option.

45. 

Click the product_class option.

46. 

Click the product_sales option.

47. 

Click the customer option.

48. 

Click the customer_sales option.

49. 

Note: The table schemas to be retrieved from the foodmart database are selected.

50. 

Click the Next button.

51. 

Note: The schemas appear in the Schema pane.

52. 

Next, add the table schemas to the metadata repository.

 

Click the Finish button.

53. 

Navigate to the table schemas in the repository to confirm their location.

 

In the Repository panel, expand the Table schemas node.

54. 

Note: The table schemas for the foodmart database are in the Table schemas folder.

 

Task 2: Create table schemas, is complete.

55. 

Collapse the Db Connections node.

56. 

Note: The Db Connections node is collapsed.

57. 

Note: Next, create a generic schema. A generic schema is not associated with a database table.

58. 

Right-click the Generic schemas node.

59. 

Click the Create generic schema menu item.

60. 

Note: The Create new generic schema window appears, displaying the first page of the wizard.

61. 

Note: Requirements for the generic schema include six columns: Firstname, Lastname, Street, Postalcode, City, and State. The maximum column length for each is 150 characters. You set these in the steps that follow.

62. 

Provide the name of the generic schema.

 

Enter customerList into the Name field.

63. 

Click the Next button.

64. 

Note: The second page of the wizard appears. This page defines the columns that the schema describes.

65. 

The default name is metadata.

 

Click in the Name field to delete the text.

66. 

Enter customerList into the Name field.

67. 

Define the columns in the schema.

 

At the lower left, click the Add button (with the plus sign on it).

68. 

Note: A new row appears in the Description of the Schema pane.

69. 

Click to delete the text.

70. 

Enter FirstName into the Column field.

71. 

Enter 150 into the Length field.

72. 

Note: A new row appears in the Description of the Schema pane.

73. 

Enter Lastname into the Column field.

74. 

Enter 150 into the Length field.

75. 

Click the button.

76. 

Note: A new row appears in the Description of the Schema box.

77. 

Enter Street into the Column field.

78. 

Enter a valid value e.g. 150 into the Length field.

79. 

Click the button.

80. 

Note: A new row appears in the Description of the Schema pane.

81. 

Enter Postalcode into the Column field.

82. 

Enter 150 into the Length field.

83. 

Click the button.

84. 

Note: A new row appears in the Description of the Schema pane.

85. 

Enter City into the Column field.

86. 

Enter 150 into the Length field.

87. 

Click the button.

88. 

Note: A new row appears in the Description of the Schema pane.

89. 

Enter State into the Column field.

90. 

Enter 150 into the Length field.

91. 

Note: The required columns with field lengths have been added, and the description of the schema is complete.

92. 

Click the Finish button.

93. 

The generic schema is located in the repository.

 

Expand the Generic schemas node.

94. 

Expand the customerList 0.1 node.

95. 

Expand the customerList node.

96. 

Note: The generic schema appears in the metadata repository.

 

Task 3. Create a generic schema, is complete.

97. 

Congratulations!

 

You completed the following three tasks:

 

1. Create a database connection

2. Create table schemas

3. Create a generic schema

End of Procedure.