In this module, we will cover the most common ways to create and get data into an OFM project. The structure of the project should be carefully designed before actually launching the first creation step.
Before getting to the actual data populating exercises, it would be useful to understand the OFM data table types and specifications. In this module you will learn to:
• Analyze table types
• Populate project with ASCII data files (OFM formats)
• Populate project with ASCII data files (PI/Dwights formats)
• Link the project with data from external ODBC sources
Note: Specifying incorrect table types when loading and/or linking data may result in losing or changing data.
Learning Objectives
In this module, you will successfully learn how to perform the following procedures within this workflow:
• Design your OFM tables
• Load OFM formatted data to a project
• Load selected PI/Dwights formatted data to a project
• Create OFM project from an existing Microsoft Access database
• Link OFM projects to external data source using the Linked Tables method
OFM Table Types
OFM data are stored in Microsoft Access tables, which follow relational
database design concepts. In addition, OFM tables have their own constraints and properties understanding their structures to correctly construct the project will not only save you time, but also guarantee the normal behavior and data integrity of your created project.
The following summary table provides information about the most commonly used OFM tables:
Table Type Number of
Keys Key Types Key Data
Types Comments MASTER 1 -
Completion Entity MUST also be of STATIC type. The master (entity) key (also called completion) is
At most 1 record per month per entity (day value is not considered)
Intra-day records per entity are allowed
Name key is the marker name
FAULT 2 Entity
Name Text
Entity key MUST be
“Wellbore”.
Name key is the fault name DEVIATION 2 Entity
The entity key could be of any level
The entity key could be of any level. Fields in lookup table MUST all be numeric.
XREF (cross
reference) 1 Entity The entity key could be of any
level WBD
(wellbore diagram)
This OFM table corresponds to 4 separate tables in Access, each one has different key requirement. For more information, please reference the OFM_DATA_WBD_XXX tables in Microsoft Access
OFM Project Creation
An OFM project can be created from a variety of methods:
• Directly built from its original Access database (in this case, OFM will create the system tables needed). The Access database in this case is called the native database.
• Linked to any ODBC compliant database using the Linked Table(s) method or ODBC (Open Database Connectivity).
• Built from a reasonable list of widely used templates and specially formatted data sources such as PI/Dwights dmp2 production files.
• Designed interactively via a step-by-step procedure, giving users the customization capability.
• Created using its most conventional method, ASCII flat files.
In this module, you will learn the ASCII flat files, the PI/Dwights dmp2 production files, the “native” Access, and the Linked Tables ways of creating OFM project.
OFM Loadable Text File Formats
In this section, you will learn the formats of OFM loadable text (ASCII) files for various table types. You will then create a new OFM project from the text files (provided in the installed program directory).
You MUST load the definition file and the (master) key data file (in that order) first. Most of the ASCII data files do not have to be loaded into your OFM project in order. Load the table definition file (for one or many tables) before loading the data files for those tables. If you choose to create the tables interactively (without using the definition files), they MUST exist before the data files can be loaded. This is also true if you want to load data into a group table (different entity key than the key in the master table), as you must define that group entity before OFM can load the data. For example, if you want to load production data at the reservoir level into a table called “RES_PROD”, the reservoir entity has to be associated (as a sort category, a foreign key, a wellbore, etc) before load time. The table definition for that table also has to be created beforehand.
Tips:
• Most of the time, each data file contains information of only one table.
• Multiple data files can be loaded into one table (appending).
• One definition file can hold information of one or more tables.
• Multiple files (definition and data) can be loaded to the project at the same time.
OFM automatically recognizes the table types if the text load files have some specific extensions. If the data files don’t have the commonly used extensions
that OFM expects, they will be treated as typical data files, and will be parsed into the specified tables. Here are some commonly used file extensions:
• *.def – table definition file
• *.xrf – cross-reference data
• *.par – parser (i.e. calculated variable) data
• *.dev – deviation data
We have constantly used the terms “definition file” and “data file”. Not all the tables in OFM require table definitions before load time. Those tables are called “non-defined” tables, in contrast to “defined” tables. They are:
• Deviation table
• Fault table
• Marker table Sort category (filter) table
• Pattern table
• Wellbore diagram table
• Parser table*
*Parser table is not a typical table in many ways, even though parser information does get stored in a table.
Note: Sometimes, map annotations files are regarded as “non-defined”.
However, there is no particular table for this type of information.
Viewing the (ASCII) Text Load Files
Definition File
OFM does expect the text files to follow certain file formats so that it can read and write the data to the database correctly. The files have to contain keywords to designate/separate all information stored in the files. A
comprehensive list of all keywords can be searched from the on-line help file provided with the application. In this procedure, we will study the
characteristics of some important table definition and data files in the “Demo”
project (usually located in \\…\Sample Databases\Demo Database\Text Load Files\ directory, where \\…\ denotes your installed OFM program directory)
1. Launch a text editor such as Notepad or WordPad. (If you have already associated the application with the file extension, you can open the file directly from Windows Explorer).
2. Select File>Open from the menu.
3. In the Open Window, select All Files or All Documents from the Files of Type drop-down list.
4. Locate the \Sample Databases\Demo Database\Text Load Files\ from your program directory.
5. Select Demo Definitions File.def.
6. Click Open. and the definition file displays as shown below (partial file only):
Notes:
• The *Tablename keyword marks the first instance beginning of the table definition. The file format is space separated; therefore whatever text between the two spaces (consecutive spaces are treated as one) follows this keyword will be the name of the table.
• The next word denotes the table type. In this case, the HEADERID table is a static table. More importantly it is the master table. A project can have as many static tables as desired, but there is only one master table. The next word specifies that HEADERID is a master table.
• In the fields/variables section, each field is assigned a data type, text or numeric (with size). As many other PC applications, OFM has several precision specific numeric types such as INT2, FLOAT, DOUBLE, etc …
• In the definition file, the field’s attributes can be assigned, identified by the keywords such as *pn for Plot Name, *pa for Plot Attribute, *u for Unit, and *mu for Multiplier. These attribute keywords can be searched from the help file.
• Project Settings keywords may appear in the definition file. *DateLabel
example, a Spanish speaking user can select to use FECHA for date;
the user has to insert *DateLabel FECHA at the top of this definition file. Another Project Settings keyword you may see quite often is
*Metric, which alerts OFM to treat the project as in Metric unit system.
• The static master table must be the very first table defined in the primary definition file (there may be many multiple definition files).
• If the table name, variable name, or even the attribute contains two or more words and has space(s) between them (or non-ASCII
characters), they should be enclosed in quotes (for instance, “MY WELL”) for OFM to treat it properly.
• Comment lines have to be started with either comment identifiers “//” or
“/*”. These lines are just descriptive information and will be ignored by OFM at load time. All texts are case-insensitive.
Let us now take a look at the definition of the monthly production table named MONTHLYPROD. Scroll down the file and locate the line “*TABLENAME MONTHLYPROD Monthly”. Notice the structure of the definition file.
In this case, MONTHLYPROD is the name of a MONTHLY table, which has fields like DAYS (an int4 type), OIL (a float type), and more…
Continue to scroll up and down the definition file to study other tables, their fields and attributes. Notice the CHOKE and the RES_PROD tables.
The CHOKE table is a DUALKEY SPORADIC table. Therefore, the
*DUALKEY keyword is required after the *Tablename line.
Likewise, RES_PROD is a GROUP level table. OFM needs to know at which level its data are associated to. Thus, the word GROUP is required, followed by the group name. In this case, it is RESERVOIR (a Sort Category).
TIPS – NUMERIC SIZE:
• Numeric Type Value Ranges:
• INT1: -128 to 127
• INT2: -32,768 to 32,767
• INT4: -2,147,483,648 to 2,147,483,647
• UINT1: 0 to 255 (unsigned int1)
• UINT2: 0 to 65,535 (unsigned int2)
• UINT4: 0 to 4,294,967,295 (unsigned int4)
• FLOAT: occupies 4 bytes, 7 decimal places, and values of approximately -3.4E-38 to 3.4E38.
• DOUBLE: occupies 8 bytes, 19 decimal places, and values of approximately -1.7E-308 to 1.7E308.
• Knowing the appropriate range of your variable data and assigning the proper size to the variable may help you save memory and improve performance.
TIPS - Reserved Keywords
There are reserved keywords that may be used in the text load files. They have special meanings to OFM and cannot be reused as variable names. The following table is a list of the most commonly used reserved keywords:
*DateLabel *KeyLength *ReadOff
Data Files
Static Data
1. On a text editor, open the Demo Key Data.xy file (in the Text Load Files directory).
2. Study the file (please scroll to the right to see all field data). The variable identifiers exist before the data section, right below the *tablename line.
OFM will parse data into the corresponding fields, based on the order they appear on that variable identifiers line.
Monthly Data
1. On a text editor, open the Demo Monthly Prod Data.prd file.
2. Notice the *KeyName line. This tells OFM which entity the next data section will be loaded into (until the next instance of *KeyName). Scroll down to see the rest of the file.
Daily Data
Open the Demo Daily Prod.dly file and view the data.
Sporadic Data
Open the Demo Sporadic Test Data.tst file and view the data.
Note: Sort Category table is a “non-defined” table. Its table definition is specified at the beginning of the file, enclosing between the *Define and the
*End_Define keywords.
In OFM 2005 (as well as in any versions after 2002), it is not mandatory to physically have a Sort Category table in the OFM project. Any field (preferably text field) from a static table can be used as a category. Further discussion on this will be given in the “Editing Sort Categories” section.
There are more text load files provided with the Demo project (as well as other projects in the Sample Databases directory). With the help of the instructors, class participants are encouraged to get familiarized with those files, especially the typical format of each file/table.
Exercise 5 Study Other Text Format Files
Continue to study the other text file formats in the “\…\Text Load Files\”
directory.
Creating Project from ASCII Text Files
In this section, you will learn to create a new project with text files provided in the Demo “Text Load Files” directory.
1. Start OFM.
2. Select File>New Workspace.
3. In the New OFM Workspace window, select the directory where you want you workspace and database files to be in, enter the desired workspace name, and select Create it from the data source specified below.
4. Make sure that ASCII Flat Files is selected at the Data Source select list.
5. Select either Access 97 or Access 2000 to create your OFM Access database (if you have Access 2000 installed in your computer and you are creating an Access 97 OFM database, you will be able to open the OFM database in Access but cannot make changes to its design. On the other hand if you have Access 97 installed and are creating an Access 2000 database, you will not be able to view the database). Ignore the Prompt for Project Filter … for now and leave it unchecked.
6. Click OK.
7. In the OFM Data Loader window, locate the \\…\Text Load Files directory to display it in the Look in list.
8. Add the following files to the Files to Load field, in this order, by either double-clicking the file, or highlight and click Add.
• Demo Definitions File.def
• Demo Key Data.xy
• Demo Monthly Prod Data.prd
• Demo Daily Prod Data.dly
• Demo Sporadic Test Data.tst
• Demo Calculated Variables Data.par
• Demo Deviation Data.dev
• Demo Basemap Anno Data.ano
• Demo Lease Info Data.xrf
• Demo Pressure Lookup Data.lku
• Demo DualKey Choke Data.dat
• Demo Monthly Loadby Reservoir Production Data.prd
9. When a file is added to the Files to Load list, OFM recognizes the extension and reflects the correct types in the Data Types list. If the load files do not follow the OFM naming convention, associate the appropriate data types for the files. Otherwise, the result may be incorrect.
10. Leave all other choices unchanged, and click Load to display the Basemap.
Note: If there is error(s) encountered during the loading, OFM will display a red error icon on the status bar (i.e. bottom right corner). Click on it and the error(s) will display. You can use the right-mouse menu to select and copy the error messages to a text editor to view/save.
Exercise 6 Create Files and New Workspace
Create the one definition file, one key data file, one monthly production file, and one daily production file for a simple four-well project. Create a new workspace and load the files to the project.
Using Batch Loading (optional)
Before proceeding to the next exercise, check your OFM installation directory for the *.bdl and the *.bpf files. If you do not have these files, ask your instructor to get a sample copy.
There are two types of batch data loading in OFM, via the Data Loader. One is used for loading multiple files to create a project, the other loading multiple
files to create multiple projects. The former batch file should have the *.bdl (e.g. Batch Data Loading) extension, and the latter should have the *.bpf (e.g. Batch Project File). This exercise demonstrates how to use the first feature.
1. Open the *.bdl (for example, Batch Data Load File.bdl) file that you have (or your instructor provides) in a text editor (WordPad or Notepad).
2. Edit (if needed) to verify the path/directory of the text files is correct. In this case, it would be \\…\…\Text Load File\ and then the full file names (again, \\…\ denotes the program directory).
3. Verify that the definition and key data files are the first two files in the list.
4. Repeat steps 1- 7 of the Creating Project from ASCII Text Files exercise.
5. Locate the Batch Data Load File.bdl file and click Add. OFM will read the file and deposit place the correct specified files into the Files to Load list (in the specified order).
6. Click Load to load the files as you did in the previous exercise.
7. The format of the *.bpf is very much the same as the format of the *.bdl file, except for each project, there is a line specifying the project name and directory. For example, a project named DemoData in the D:\Temp directory would be specified as: *Project “D:\Temp\DemoData.mdb”.
The project keyword line is followed by those lines (data files) seen in a *.bdl file. A different project is identified by a new *project line, then its own data files. To execute this batch loading procedure, from the File menu (no workspace opened), select Batch Load and select desired file to load. Again, verify the path and file names are correct to guarantee correct loading of data.
Do not forget to close the workspace for the next exercise!
PI/Dwights Text Format
OFM provides a direct method to load certain PI/Dwights production files (i.e.
dmp2, 298 fixed format) to create OFM projects. The following exercise demonstrates how to create an OFM project from the dmp2 production files.
1. Create a new workspace, assign the desired workspace name (please refer to step 1 – 3 in the previous exercise – Creating Project from ASCII Text Files).
2. Select PI/Dwights DMP2 Production file from the Data Source list.
3. Click OK. Locate the \\…\Sample Databases\PI Dwights Sample Files\
directory and select a file from the list of available files (i.e.
wcr_dmp2.dp2). Proceed to the next step.
4. Toggle on the Prod ID (Entity ID) (40 characters) button. If you do not want to keep the temporary load files, make sure the Delete Temporary Load Files option is checked.
5. Select the wells you want to load into the OFM project. You can choose to load all wells to the database.
Note: If you want to create a Xref (cross-reference) table at this time, you can do so. Click the corresponding button and specify the file name in the next dialog.
6. Click Next. It is recommended that you do not make changes (unless you know exactly the data) to guarantee the database integrity. In this step, you can specify the fields to be used as Sort Categories. Select the displayed fields to view their descriptions. Then select Next.
7. This step allows you to select which well test items to be included. Make the selection and click Next.
8. This last step gives you the option to select which monthly production data items to be included. Make the selection and click Finish.
9. With proper editions, your basemap should look like the following: (do not display the well name otherwise the basemap may look crowded).
30.0 30.0
Exercise 7
Exercise 8
Load PI/Dwights Production File
Create a new workspace and load another PI/Dwights production dmp2 file to the database.
Load PI/Diwghts 298 Production File
Create a new workspace and load a PI/Dwights 298 production file to the database.
Do not forget to close the workspace for the next exercise!
From an Existing Access Database
If an Access database has a structure that is compliant to the OFM database model, OFM can open it and converts it to an OFM database (by creating proper system and configuration tables in the opened Access database).
If you want to open a generic Access database (which does not necessarily comply with the OFM database model) in OFM, OFM would certainly try to open it. However, the program cannot ensure the normal behavior of the database and/or the project.
Note: Once the original Access database has been opened (and converted to an OFM database), it is primarily used by OFM. In this case, it is so-called a
Note: Once the original Access database has been opened (and converted to an OFM database), it is primarily used by OFM. In this case, it is so-called a