MIS450 Data Mining

Write My Assignment FREE

MIS450 Data Mining

Module 4, Option #1 – Critical Thinking Assignment: Database and Data Warehouse Creation and Database Connections

Part I: Create a new database and a new data warehouse in PostgreSQL.

1. Expand the PostgreSQL item on the tree listing.

2. Right-click to select the Databases item and then select Create -> Database.

3. The Create Database dialog window appears:

• Enter js in the Database name.
• Enter any comments you feel are appropriate.
• Click Save. The database js now appears in the tree listing on the left side of the screen.

4. Right-click js and select Query tool.

5. Open the js.sql file using a text editor; then:

• Select all of the SQL statements.
• Copy and paste the SQL into the Query tool window.
• Run the SQL.
• Click the lightning bolt to execute the SQL script. You should receive a Query Returned Successful message.
• Clear the Query tool window.

6. Open the js_data.sql file using a text editor; then:

• Select all of the SQLstatements.
• Copy and paste the SQL into the Query tool window.
• Run the SQL.
• Click the lightning bolt to execute the SQL script. You should receive a Query Returned Successful message.
• Clear the Query tool window.

7. Right-click to select the Databases item and then select Create -> Database. The Create Database dialog window appears:

• Enter js_dw in the Database name.
• Enter any comments you feel are appropriate.
• Click Save. The database js now appears in the tree listing on the left side of the screen.

8. Right-click to select js_dw and then select Query tool.

9. Open the js_dw.sql file using a text editor; then:

• Select all of the SQL statements.
• Copy and paste the SQL into the Query tool window.
• Run the SQL.
• Click the lightning bolt to execute the SQL script. You should receive a Query Returned Successful message.
• Clear the Query tool window.

At this point, the js database and tables are created and populated; also, the js_dw database and tables are created. Use these procedures and SQL scripts as an example when creating the database and tables, and populating the databases and data warehouse.

Part II: Establish Database Connections

The following procedures establish the database connections between the Jigsaw Operational Database and the Jigsaw Data Warehouse. You will also execute some ETL transformations for populating the data warehouse. First, initiate PentahoETL by launching Spoon.bat.

1. Click the View tab.
2. Right-click to select the Database Connection option and then click New.
3. Fill in the Data Connection dialog window with the proper settings:

• Connection Name: Jigsaw Operational Database
• Connection Type: PostgreSQL
• Access: Native (JDBC)
• Settings:
  • Hostname: localhost
  • Database Name: js
  • Port Number: 5432 (default)
  • User Name: postgres
  • Password: your password from the PostgreSQL

4. Click the Test button. If everything is okay, you will see a Database Connection text box indicating the connection is OK.
5. Click OK to close the Test window.
6. Click OK to close the Database Connection window.
7. Right-click to select the Jigsaw Operational Database Connection in the tree, and then click Share. Sharing the connection enables access in all transformations. Shared connections are in bold text.
8. Right-click to select the Database Connection option and then click New.
9. Fill in the Data Connection dialog window with the proper settings:

• Connection Name: Jigsaw Data Warehouse
• Connection Type: PostgreSQL
• Access: Native (JDBC)
• Settings:
  • Hostname: localhost
  • Database Name: js_dw
  • Port Number: 5432 (default)
  • User Name: postgres
  • Password: your password from the PostgreSQL

10. Click the Test button. If everything is okay, you will see a Database Connection text box indicating the connection is OK.
11. Click OK to close the Test window.
12. Click OK to close the Database Connection window.

10. Right-click to select the Jigsaw Data Warehouse Database Connection in the tree, and then click Share. Sharing the connection enables access in all transformations. Shared connections are in bold text.

Execute Transformations

The following procedures will execute transformations that will populate the js_dw from the js database. Use these transformation examples to assist in developing the transformations for the Portfolio Project Milestone in Module 6 and the final Portfolio Project in Module 8.

From Pentaho – Transformation #1:

1. Click the File tab and then select open.
2. Browse until you find lk_manufacturers.ktr.
3. Open the transformation.
4. Double-click each step in the transformation. Ensure the database connection is populated with your connection names.
5. Execute the transformation. You should receive a message window with inputs and outputs for each step in the transformation. If you do not receive a red line through one of the steps, then the transformation was successful. If you receive a red line, recheck your connection settings.

From Pentaho – Transformation #2:

1. Click the File tab and then select open.
2. Browse until you find lk_regions.ktr.
3. Open the transformation.
4. Double-click each step in the transformation. Ensure the database connection is populated with your connection names.
5. Execute the transformation. You should receive a message window with inputs and outputs for each step in transformation. If you do not receive a red line through one of the steps, then the transformation was successful. If you receive a red line, recheck your connection settings.

From Pentaho – Transformation #3:

1. Click the File tab and then select open.
2. Browse until you find sample_ft_sales.ktr.
3. Open the transformation.
4. Double-click each step in the transformation. Ensure the database connection is populated with your connection names.
5. Execute the transformation. You should receive a message window with inputs and outputs for each step in the transformation. If you do not receive a red line through one of the steps, then the transformation was successful. If you receive a red line, recheck your connection settings.

The post MIS450 Data Mining appeared first on My Assignment Online.

Expert Help With This Assignment — On Your Terms