User:Mrohatg2 - Revision history

Mrohatg2: /* Implementation */

2015-12-18T20:12:50Z

Implementation

← Older revision		Revision as of 20:12, 18 December 2015
Line 432:		Line 432:
	Due_Dates Table		Due_Dates Table

	~~Questionare~~ Table		Questionnaire Table

	Teams Table		Teams Table

Mrohatg2: /* Implementation */

2015-12-18T20:11:15Z

Implementation

← Older revision		Revision as of 20:11, 18 December 2015
Line 411:		Line 411:
	Partial loading is implemented with the help of timestamps and checksums.		Partial loading is implemented with the help of timestamps and checksums.

	Timestamp: ~~The expertiza database has a timestamp column which saves the timestamp that row was last modified at. There~~ is a ~~variable~~ '~~maxtimestamp~~' ~~that saves the value of the~~ time ~~the rows from the experts database were last loaded~~ in the ~~PRML database~~. ~~While doing~~ the ~~partial load, the query checks this variable and loads~~ only the ~~rows whose timestamp is~~ greater than the '~~maxtimestamp~~'. ~~This way only~~ the updated ~~or the new entries are loaded~~ in the ~~PRML~~ table.		Timestamp: One way to perform partial loading is to have a 'modified' time stamp in the source table. The destination table should mirror this 'modified' time stamp. Then while loading the destination table we extract only those records from the source table which has a 'modified' time stamp value greater than the latest 'modified' time stamp value in the destination table. The advantage is that we need not fetch all records from the source table. Only those records which needs to be updated in the destination table will be retrieved. (refer load_prml_ModifiedTimeStampTable transformation)

	~~Checksums~~: ~~The~~ checksum is ~~also a way~~ of ~~implementing partial load, like~~ the ~~timestamp~~. ~~In this case~~ the checksum ~~of the expertiza database~~ is ~~compared with the PRML database~~ checksum~~. Only~~ the ~~rows whose checksum does not match,~~ or ~~does not exist is loaded~~.		Checksum: Another way to perform partial loading is using checksum. A checksum is computed for each record, on selected field values, of the source table and added to the destination table while initial loading. Loading which follow would again compute the checksum for source table records and if there is no matching checksum in the destination table an update or an insert would be performed. (refer load_prml_checksumtable transformation)

	Full Loading: All the entries from the expertiza table are transformed and loaded in the corresponding PRML table. This is generally done for tables ~~will~~ less number of entries.		Full Loading: All the entries from the expertiza table are transformed and loaded in the corresponding PRML table. This is generally done for tables with less number of entries.
	Eg: Courses		Eg: Courses

Mrohatg2: /* Implementation */

2015-12-16T18:31:53Z

Implementation

← Older revision		Revision as of 18:31, 16 December 2015
Line 425:		Line 425:

	ModifiedTimeStamp Table - Partial Load		ModifiedTimeStamp Table - Partial Load

			Task Table

			Criteria Table

			Due_Dates Table

			Questionare Table

			Teams Table

	Jobs are being performed in a sequential order in the current project. If the tables do not depend on one another they can be performed in a parallel way to reduce time.		Jobs are being performed in a sequential order in the current project. If the tables do not depend on one another they can be performed in a parallel way to reduce time.

Mrohatg2: /* Implementation */

2015-12-16T18:22:21Z

Implementation

← Older revision		Revision as of 18:22, 16 December 2015
Line 426:		Line 426:
	ModifiedTimeStamp Table - Partial Load		ModifiedTimeStamp Table - Partial Load

	Jobs are being performed in a sequential order in the current project. If the tables do not depend on one another they can be performed in a ~~parellal~~ way to reduce time.		Jobs are being performed in a sequential order in the current project. If the tables do not depend on one another they can be performed in a parallel way to reduce time.
	Initially the entries in the table are buffered so that only around 8000 rows are processed at a time.		Initially the entries in the table are buffered so that only around 8000 rows are processed at a time.

Mrohatg2: /* Implementation */

2015-12-16T18:20:07Z

Implementation

← Older revision		Revision as of 18:20, 16 December 2015
Line 407:		Line 407:
	'''Loading:'''		'''Loading:'''
	The transformed data when moved from the staging area to the PRML database is called as loading. There are three types of loading implemented on the tables depending on the size of the table – initial load,partial loading and full loading.		The transformed data when moved from the staging area to the PRML database is called as loading. There are three types of loading implemented on the tables depending on the size of the table – initial load,partial loading and full loading.
	~~Initial~~ Loading:The ~~first~~ time the ~~data is loaded~~ from the ~~expertiza~~ database to PRML database, it is the ~~initial load~~.		Partial Loading: Only the new or updated entries in the expertiza table are transformed and loaded in the corresponding PRML table. This is generally done for table with very large number of entries. Eg: Reviews

			Partial loading is implemented with the help of timestamps and checksums.

			Timestamp: The expertiza database has a timestamp column which saves the timestamp that row was last modified at. There is a variable 'maxtimestamp' that saves the value of the time the rows from the experts database were last loaded in the PRML database. While doing the partial load, the query checks this variable and loads only the rows whose timestamp is greater than the 'maxtimestamp'. This way only the updated or the new entries are loaded in the PRML table.

	~~Partial Loading~~: ~~Only~~ the ~~new or updated entries in~~ the expertiza ~~table are transformed and loaded in~~ the ~~corresponding~~ PRML ~~table~~. ~~This~~ is ~~generally done for table with very large number of entries~~. ~~Eg: Reviews~~		Checksums: The checksum is also a way of implementing partial load, like the timestamp. In this case the checksum of the expertiza database is compared with the PRML database checksum. Only the rows whose checksum does not match, or does not exist is loaded.

	Full Loading: All the entries from the expertiza table are transformed and loaded in the corresponding PRML table. This is generally done for tables will less number of entries.		Full Loading: All the entries from the expertiza table are transformed and loaded in the corresponding PRML table. This is generally done for tables will less number of entries.
	Eg: Courses		Eg: Courses

	Full ~~and partial loading is implemented with~~ the ~~help of timestamps and checksums~~.		'''Expertiza tables which have been implemented'''

			Assignments Table - Full Load

			Course Table - Full Load

			ModifiedTimeStamp Table - Partial Load

			Jobs are being performed in a sequential order in the current project. If the tables do not depend on one another they can be performed in a parellal way to reduce time.
			Initially the entries in the table are buffered so that only around 8000 rows are processed at a time.

	== Testing ==		== Testing ==

Zganatr: /* Running the Project */

2015-12-04T23:38:12Z

Running the Project

← Older revision		Revision as of 23:38, 4 December 2015
Line 431:		Line 431:
	*Import to MySQL PRML database dump file.		*Import to MySQL PRML database dump file.
	*Download Pentaho's Data Integration(Kettle) Community Edition (its a copy deployment hence no installation is required).		*Download Pentaho's Data Integration(Kettle) Community Edition (its a copy deployment hence no installation is required).
	*		*Download MySql Connector jar file and copy to <pentaho_root_directory\data-integration\lib>
			*Start Spoon.bat if you are using a windows machine or Spoon.sh if you using Linux (pentaho_root_directory\data-integration)
			*Download the transformations and jobs attached
			*You can open a kettle transformation (.ktr file) using Spoon and run it (Make sure proper MySql DB connections are configured)
			*You may also open the kettle job (.kjb file) using spoon and run it (Make sure the path for transformations in the job are correct)

Zganatr: /* Implementation */

2015-12-04T23:25:43Z

Implementation

← Older revision		Revision as of 23:25, 4 December 2015
Line 403:		Line 403:

	'''Transformation'''		'''Transformation'''
	Many operations are performed on the data in the staging area so that the expertise schema can be converted to the PRML schema. This is called transformation of the data.		Many operations are performed on the data in the staging area so that the expertise schema can be converted to the PRML schema. This is called transformation of the data. An example of transformation is while mapping of participants table in the expertiza database to actors table in the PRML database. Each value of the id field is incremented by 1000 while inserting in the participants table.
	~~Some~~ of the ~~transformation performed for~~ the ~~project are:~~

	'''Loading:'''		'''Loading:'''

Zganatr at 22:21, 4 December 2015

2015-12-04T22:21:05Z

@@ Line 391: / Line 391: @@
 == Implementation ==
-We have used an Extract, Transform and Load (https://en.wikipedia.org/wiki/Extract,_transform,_loadETL) tool, [[Pentaho Kettle]] (http://community.pentaho.com/projects/data-integration/) to perform the data migration  from Expertiza to PRML database.
+We have used an Extract, Transform and Load [https://en.wikipedia.org/wiki/Extract,_transform,_load ETL] tool, [http://community.pentaho.com/projects/data-integration/ Pentaho Kettle] to perform the data migration  from Expertiza to PRML database.
 [[File:diagram.png]]
@@ Line 420: / Line 420: @@
 We have tested our conversion by using a set of sample expertiza data. We run the extract, transform and load methodology on it. Queries are run on the PRML database to check if it gives the same results as the expertiza database.
 Though the testing is not through, this is the only way the project can be tested.

Zganatr: /* Implementation */

2015-12-04T21:48:29Z

Implementation

← Older revision		Revision as of 21:48, 4 December 2015
Line 407:		Line 407:

	'''Loading:'''		'''Loading:'''
	The transformed data when moved from the staging area to the PRML database is called as loading. There are ~~two~~ types of loading implemented on the tables depending on the size of the table – partial loading and full loading.		The transformed data when moved from the staging area to the PRML database is called as loading. There are three types of loading implemented on the tables depending on the size of the table – initial load,partial loading and full loading.
			Initial Loading:The first time the data is loaded from the expertiza database to PRML database, it is the initial load.

	Partial Loading: Only the new or updated entries in the expertiza table are transformed and loaded in the corresponding PRML table. This is generally done for table with very large number of entries. Eg: Reviews		Partial Loading: Only the new or updated entries in the expertiza table are transformed and loaded in the corresponding PRML table. This is generally done for table with very large number of entries. Eg: Reviews

	Full Loading: All the entries from the expertiza table are transformed and loaded in the corresponding PRML table. This is generally done for tables will less number of entries.		Full Loading: All the entries from the expertiza table are transformed and loaded in the corresponding PRML table. This is generally done for tables will less number of entries.
	Eg: Courses		Eg: Courses
	Full and partial loading is implemented with the help of timestamps.
			Full and partial loading is implemented with the help of timestamps and checksums.

	== Testing ==		== Testing ==
	We have tested our conversion by using a set of sample expertiza data. We run the extract, transform and load methodology on it. Queries are run on the PRML database to check if it gives the same results as the expertiza database.		We have tested our conversion by using a set of sample expertiza data. We run the extract, transform and load methodology on it. Queries are run on the PRML database to check if it gives the same results as the expertiza database.
	Though the testing is not through, this is the only way the project can be tested.		Though the testing is not through, this is the only way the project can be tested.

Zganatr: /* Implementation */

2015-12-04T21:45:39Z

Implementation

← Older revision		Revision as of 21:45, 4 December 2015
Line 392:		Line 392:
	== Implementation ==		== Implementation ==
	We have used an Extract, Transform and Load (https://en.wikipedia.org/wiki/Extract,_transform,_loadETL) tool, [[Pentaho Kettle]] (http://community.pentaho.com/projects/data-integration/) to perform the data migration from Expertiza to PRML database.		We have used an Extract, Transform and Load (https://en.wikipedia.org/wiki/Extract,_transform,_loadETL) tool, [[Pentaho Kettle]] (http://community.pentaho.com/projects/data-integration/) to perform the data migration from Expertiza to PRML database.

	[[File:diagram.png]]		[[File:diagram.png]]

	The Pentaho Kettle tool extracts data from the expertise schema, performs transformation on it and load the data in the PRML format.		The Pentaho Kettle tool extracts data from the expertise schema, performs transformation on it and load the data in the PRML format.
	The tables are mapped as shown in the schema map above.		The tables are mapped as shown in the schema map above.