Resolving Issues during the Bfile to WSM Sync

For this conversation, we will be looking at information in both real time data and WSM data. In order to acquire the necessary information to analyze this, you will need access to the LOWIS Client and the SQL Server database at a minimum.

In general, wells will fail to sync to WSM because of a discrepancy in the API numbers or the LWNAME value. The reason why is that these are meant to be identifiers, so it is challenging for us to automatically identify which is identifier is correct. Therefore, the focus of this document will be to know how to correctly verify the data in WSM matches what is in BFile as well as how to modify them should these ever be different.

Data

In general, we are interested in four pieces of data:

• API 10
• API 12
• API 14
• Well Name

Real Time

The first step is know where this information can be found. For the BFile data, we are going to be looking at the LOWIS Client. Specifically, we are interested in the Well Group Configuration grid under the Configuration sub-menu in the start menu. This view contains all of the defined wells in the BFile database. A defined well is one that is currently accessible in LOWIS. When you delete a well, it is flag as undefined, among other things. This view contains all of the real time information that we are interested in.

• API 10 = Unique Wellbore ID (API 10)
• API 12 = Unique Wellbore ID (API 12) 
• API 14 = Unique Wellbore ID (API 14)
• Well Name = Well Name

This table also contains some important information about the WSM connection. These columns might be hidden, but the aclPrimaryKey and WellCompletionXRefKey are both columns with very useful information.

WSM
On the WSM side, there are our tables that may be updated with information during the nightly sync from BFile:

• WellCompletionXRef – Completion 
• SubAssembly – Subassembly
• Assembly – Assembly as a whole
• Well – Well at the surface

The tables are related to each other with specific key columns, but they all contains API data that is expected to match up.

• WellCompletionXRef 
• API 10 – wxrAPI10 
• API 12 – wxrAPI12 
• API 14 – wxrAPI14
• SubAssembly
• API 12 – sasSAID
• Well
• API 10 – Weluwbid

The expectation is that the API numbers everywhere match up. The rows of the different tables need to match the corresponding key. For instance, in WellCompletionXRef there is a wxrFK_SubAssembly table, which will match a certain row in the SubAssembly table on the sasPrimaryKey column. These rows should have a matching wxrAPI12 and sasSAID.

The Link

The way that these are linked together to display information in the LOWIS Client is determined by the production.nav.xml file. In this file, there will be a function call that looks something like this:

That last one there is what we are interested in. The line WELUWBID =* WELUWBID means that the well data and WSM data is being linked together based on whether the Well.Weluwbid value matches with the real time’s API 10, the Unique Wellbore ID (API 10) column on the Well Group Configuration grid. Some customers join WSM and real time data on the WLCOMPXREF, the WellCompletionXRefKey column on the Well Group Configuration grid, and the wxrPrimaryKey column of the WellCompletionXRef table. Either way is fine, but you need to be aware of which one your system is using.

The above section describes how the client pairs a well in the navigator with its WSM data, but the nightly sync from BFile to WSM only links the data based on the WLCOMPXREF equaling wxrPrimaryKey.

To Sum Up:

One more comment before we move on. Both job and component information is linked to the Assembly table. If you have multiple wells with the same API 10, then they should all display the same component and job data.

Analysis

Now that you know where all the data is, we need to discuss how to analyze the situation.
In the cslift\lift\database\log folder, there is a log file generate during the nightly sync from BFile to WellCompletionXRef. The log file ends with the date it was generated like so, BfileWSMSync_01222016.log, following this format: BfileWSMSync_MMDDYYYY.log.
This log file contains a record of all of the data that was updated for a given well, and it will display script generated and SQL generated errors if a well is not updated due to a failure. I am not going to get into each error that you may see, but essentially there are only a few types of problems.

• Mismatch of APIs
• Incorrect table links
• Failing a unique constraint
• Duplicates in BFile

Mismatch of APIs
The most common error that you will receive is that a set of API numbers is incorrect or cannot be updated. This can happen because of a number of reasons. What is important to remember is the process.
There is a certain flow to information that I will now attempt to demonstrate. For our example, we are going to look at well: MFU-400. As you can see below, the API 10, 12, and 14 values exist in BFile and it has been synced to WSM because the WellCompletionXRefKey has a value.

Here is our data in BFile. The assumption is that all data in BFile is what we want to see in WSM.

So, during the nightly sync, what row of the WellCompletionXRef table is it going to try and update the API 14 to 42003319070000? The one on whose wxrPirimaryKey is ONC000ZL.

Does the SubAssembly have a matching API 12 value? If not, then this would also need to be updated. So, what row of the SubAssembly table is it going to try and update the API 12 to 420033190700? The one on whose sasPrimaryKey is ONC0020O.

Does the Well table have a matching API 10 value? So, what row of the Well table is it going to try and update the API 10 to 4200331907? This one is a bit more tricky because you have the link from SubAssembly to Assembly and a link from Assembly to Well. So, it would look a something like this:

For this example, they are all the same. The sync would not have to change anything.

However, what if the API 12 had been modified? Then what all would need to the modified?

The wxrAPI12 and the wxrAPI14 on the WellcompletionXRef as well as the sasSAID in the SubAssembly table would all have to be changed.

Incorrect Table Links

What if the WellCompletionXRef data is accurate, but the SubAssembly data is not accurate? What if there is already a SubAssembly row that has the API 12 you want to change it to? A better solution in this situation would be to modify the link between WellCompletionXRef and SubAssembly, modifying the data so that the WellCompletionXRef record is linked to the SubAssembly record with the appropriate API 12.

Anytime you update a link like this, it is important to remember that there may be other row in other table that are also looking at the record that you just updated, and they might also need to be modified. Take the SubAssemly in our example table. The below query show all of the tables that may be linked to that SubAssembly record.

You would need to make sure to update all of the links from these tables to the new SubAssembly, not just the WellCompletionXRef’s wxrFk_SubAssembly. If these come up empty, then you do not have to worry about it.

Failing a Unique Constraint

The SQL database has some explicit constraints on its columns. Mainly you will come across are unique constraints. For instance, you cannot insert two rows into the WellCompletionXRef table with the same API 14 value or the same LWNAME. You cannot insert two rows into the SubAssembly table with the same API 12 value.

I also want you to be aware of the fact wells synced to WSM stay in WSM even if they have been deleted from the BFile database. Suppose you add a well in LOWIS with a specific well name, sync it WSM, and then delete it. If you add a well with the same well in BFile, the there will be no problem. However, you will be unable to sync the well to WSM because there is already a well with that well name.

Duplciates in BFile

Any data in BFile must be accurate before you can send it over to WSM. If there is more than one well with the same API 14 number in BFile, then it will give you an error before even attempting to bring the information over to WSM.

Resolving Conflicts

By now, you should have an understanding of where the information is coming from and how it is compared as well as how data mismatches can be identified. Once you have identified a data mismatch, the solution should now be far behind.

The goal of any conflict resolution, as far as this topic is concerned, is to have all the data in WSM match the data in BFile. If you do that, then you have succeeded.

A potential solution might be to manually update it actual API values in WellCompletionXRef, SubAssembly, and\or Well.

A potential solution may include modifying a link between tables, rather than the data itself. In this instance, please remember to update all other links to the record being changed where appropriate.

A solution may mean modifying the well name of a row in WellCompletionXRef, so that a well of that name in Bfile can be added.