| 17 | | Mapping databases together is the entry level for using ISO 15926. The basic task is to examine each attribute to determine what it means, then find the appropriate class in the RDS/WIP, which is ISO 15926 part 4 (15926-4). |
| 18 | | |
| 19 | | Benefits of using ISO 15926-4: |
| 20 | | |
| 21 | | * It's already there. (Creating a taxonomy of terms for something as complex as a petrochemical plant or refinery is not trivial.) |
| 22 | | * It's there next time too. (You don't have to keep reinvent it next time. Re-use is easier every time you use it.) |
| 23 | | * Other use it too. (When you share information with a partner that has used ISO 15926-4 before, the infrastructure is already in place. Both of you will have less adjusting to do.) |
| 24 | | |
| 25 | | === Scenario === |
| 26 | | |
| 27 | | In this scenario we will imagine an owner, an engineer, and a constructor exchanging information. The owner wants early information about each pipe line to populate its line list, the engineer is designing the plant with a 3D design tool, and the constructor wants to feed the piping components to a purchasing application. Each tool has its own database. It is your job to map the information to common ISO 15926-4 classes. |
| 28 | | |
| 29 | | Each participant requires two tables, one for pipe lines and one for valves. |
| 30 | | |
| 31 | | |
| 32 | | === [wiki:ISO15926HowTo_Tutorial_DBscripts Create Database Scripts] === |
| 33 | | |
| 34 | | The first step is to get a description of the database columns involved in the exchange. In our example, we will assume that a database administrator in each of the three organizations gives you a SQL script that will create the tables at that organization, and another script to load a couple rows of sample data. |
| 35 | | |
| 36 | | We prefer scripts that create the tables, rather than CSV file or spreadsheet, because the column names are sometimes ambiguous and the data types and sizes give valuable clues. We want sample data for the same reason; when the column names are ambiguous, looking at some sample data will give a clue. We prefer the data dumps in the form of scripts that will create the data rows because we invariably need to create and delete test data many times. |
| 37 | | |
| 38 | | Assumption: That you know enough of SQL databases to read the scripts and data dumps. This will be necessary in the real world because you may have to build the database loading scripts from a printed data dump if the database cannot generate it for you. |
| 39 | | |
| 40 | | You will only need to download one set of files. We have provided the files in three forms, for Oracle, SQL Server, and SQL Server 2005 Express, because that is the systems the authors of this page used. |
| 41 | | |
| 42 | | '''3D design system database''' |
| 43 | | * attachment:3D_CreateTables_Ora.sql |
| 44 | | * attachment:3D_LoadTables_Ora.sql |
| 45 | | * attachment:3D_CreateTables_SS.sql |
| 46 | | * attachment:3D_LoadTables_SS.sql |
| 47 | | * attachment:3D_CreateTables_2K5.sql |
| 48 | | * attachment:3D_LoadTables_2K5.sql |
| 49 | | |
| 50 | | '''Owner Line List database''' |
| 51 | | * attachment:LL_CreateTables_Ora.sql |
| 52 | | * attachment:LL_LoadTables_Ora.sql |
| 53 | | * attachment:LL_CreateTables_SS.sql |
| 54 | | * attachment:LL_LoadTables_SS.sql |
| 55 | | * attachment:LL_CreateTables_2K5.sql |
| 56 | | * attachment:LL_LoadTables_2K5.sql |
| 57 | | |
| 58 | | '''Constructor Material Control database''' |
| 59 | | * attachment:MM_CreateTables_Ora.sql |
| 60 | | * attachment:MM_LoadTables_Ora.sql |
| 61 | | * attachment:MM_CreateTables_SS.sql |
| 62 | | * attachment:MM_LoadTables_SS.sql |
| 63 | | * attachment:MM_CreateTables_2K5.sql |
| 64 | | * attachment:MM_LoadTables_2K5.sql |
| 65 | | |
| 66 | | === [wiki:ISO15926HowTo_Tutorial_Spreadsheet Creating a Mapping Spreadsheet] === |
| 67 | | |
| 68 | | The first step is to look at the three sets of data and create a cross-reference list for each table. We want to be able to show equivalent database rows in the same row of the list. A spreadsheet is an excellent medium. |
| 69 | | |
| 70 | | Create a pair of columns for each participant, and three columns for ISO 15926-4 information: |
| 71 | | |
| 72 | | * 3D Table |
| 73 | | * 3D Column |
| 74 | | * Line List Table |
| 75 | | * Line List Column |
| 76 | | * Material Table |
| 77 | | * Material Column |
| 78 | | * ISO 15926 Class Label |
| 79 | | * ISO 15926 Class ID |
| 80 | | * ISO 15926 Description |
| 81 | | |
| 82 | | Sample Spreadsheet |
| 83 | | * attachment:MappingSpreadsheet_Blank.xls |
| 84 | | |
| 85 | | Examine the columns in each database listing and arrange equivalent column names on the same row. The example below starts with the columns in the 3D table, with the Line List columns next, followed by the Material columns. |
| 86 | | |
| 87 | | Sample Spreadsheet |
| 88 | | * attachment:MappingSpreadsheet_Columns.xls |
| 89 | | |
| 90 | | The first thing you notice is that there is not an exact match between the column names, and that each database contains information that neither of the other two does. This is OK. With ISO 15926 we do not have to share all of our information with our business partners, only that which has value. |
| 91 | | |
| 92 | | |
| 93 | | === [wiki:ISO15926HowTo_Tutorial_RDSWIP_01 Mapping With the RDS/WIP] === |
| 94 | | |
| 95 | | The RDS/WIP is at this website: |
| 96 | | |
| 97 | | * rdl.rdlfacade.org |
| 98 | | |
| 99 | | For basic instructions in how to use the RDS/WIP: |
| 100 | | |
| 101 | | * [wiki:ISO15926HowTo_15926Tools_RDS_WIP Using the RDS/WIP (RDL Façade)] |
| 102 | | |
| 103 | | Basically, your task here is to search for ISO 15926-4 class that most closely matches the meaning of each of the column names in your three databases. The first thing you will notice is that the best choice is not immediately apparent. In the sample spreadsheet below, some of the data rows have more than one choice. Look at a few of them to see what the differences are. |
| 104 | | |
| 105 | | Sample Spreadsheet |
| 106 | | * attachment:MappingSpreadsheet_First.xls |
| 107 | | |
| 108 | | === [wiki:ISO15926HowTo_DiscoverTheRightClass Discovering the Right Class] === |
| 109 | | |
| 110 | | Here are some guidelines for selecting the best class: |
| 111 | | |
| 112 | | |
| 113 | | The sample spreadsheet below has had the duplicates removed, with the best choice left. |
| 114 | | |
| 115 | | Sample Spreadsheet |
| 116 | | * attachment:MappingSpreadsheet_Second.xls |
| 117 | | |
| 118 | | === [wiki:ISO15926HowTo_Tutorial_RDSWIP_02 Resolving Differences] === |
| | 16 | == [wiki:ISO15926HowTo_Tutorial_RDSWIP_02 Resolving Differences] == |
