Precisely Enterworks

 View Only
  • 1.  How to add Primary key to Repository

    Posted 12 days ago

    We are getting errors at different times, for example when importing that records are duplicate in our Parts Repository. The repo was originally setup with two primary keys: Part Number and Supplier ID. It is clear now that that is not specific enough to avoid duplicates. By default it seems we cannot add a primary key to the Profile. Are there any work arounds that would allow us to add a primary key(s)? What are our options?



    ------------------------------
    Joseph McHugh
    MDM Solution Engineer
    Volvo IT North America
    NC
    ------------------------------


  • 2.  RE: How to add Primary key to Repository

    Employee
    Posted 11 days ago

    Joseph,

    There is a KB article on how to change the primary key of a populated repository:  https://customer.precisely.com/s/article/How-to-Change-the-Primary-Key-of-a-Populated-Repository-10044027616283

    -Brian



    ------------------------------
    Brian Zupke
    Precisely Software Inc.
    ------------------------------



  • 3.  RE: How to add Primary key to Repository

    Posted 11 days ago

    Hello Brian,

    I tried the steps in the KB article you referenced.  I exported the profile attributes.  I added 1 primary key, which will be our third key.  I re-import the file with the attribute to the profile and I get the error in the below image.  It seems to be complaining about duplicate column, but I took a look at the file in Excel and there are no duplicate attribute names.  Have you seen this error before?  Any idea what could be causing it?

    - Joe



    ------------------------------
    Joseph McHugh
    MDM Solution Engineer
    Volvo IT North America
    NC
    ------------------------------



  • 4.  RE: How to add Primary key to Repository

    Employee
    Posted 9 days ago

    Joseph,

    I suspect the issue is that according to EnterWorks, there is already a duplicate record in the repository.  It appears that in newer versions of EnterWorks, there has been additional logic checking for duplicates and preventing any update to an existing record.  Previously, only the attempt to create a duplicate record was prevented.  But in doing so, if duplicates have come about due to either preexisting before upgrading to a version that has this new behavior, or being created by changing the primary keys of a repository, it will not be possible to adjust them through UI or import operations.  While I pursue getting a change made to the product to only prevent the saving of a record if its primary key is being changed by that save, it will be necessary to update the PK_COL_X columns in the B_MASTER_REPOSITORY_ITEM table based on the values of those fields. 

    For example, if I had a repository Product_Staging that originally had just Product ID as the primary key but has been expanded to include Product Name and Product GUID attributes for a composite primary key, then I would need to generate UPDATE statements for each record to set the corresponding PK_COL_X columns to the current values.  Here's an example SQL query:

    select 'UPDATE B_MASTER_REPOSITORY_ITEM SET PK_COL_1=' + cast(p.Product_ID as VARCHAR) +
    ', PK_COL_2=' + isnull('''' + p.Product_Name + '''', 'null') +
    ', PK_COL_3=' + isnull('''' + p.Product_GUID + '''', 'null') +
    ' where ITEM_ID=' + cast(p.InternalRecordId as VARCHAR)
    from Product_Staging p

    While it's possible to have a simpler query that just does the update in a single transaction, the above approach allows for the updates for each record to be reviewed prior to copying them to the query window and executing them.  You may conclude that it is safe to just run an UPDATE command that joins B_MASTER_REPOSITORY_ITEM table with the snapshot table.

    If any of the new primary key columns are not in the snapshot table already, they will need to be added and the snapshot table rebuilt.  If there are issues with rebuilding the snapshot because of the change in primary key (I don't think there will be), then it may be necessary to extract the primary key values from the XML stored in the ATTR_DATA column of B_MASTER_REPOSITORY_ITEM.

    Please let me know whether this works for you and if you had to make any adjustments.  I will update the KB article based on your feedback.



    ------------------------------
    Brian Zupke
    Precisely Software Inc.
    ------------------------------



  • 5.  RE: How to add Primary key to Repository

    Employee
    Posted 9 days ago

    Joseph,

    Please disregard my previous response, at least for now.  I had misunderstood where you were having the problem.  Please open a Customer Support case with the details, including the original export attributes file (prior to making they key change) along with the modified file that was imported into the profile that failed to save.  Please also indicate you were directed to open the case by me and that I plan to address it.



    ------------------------------
    Brian Zupke
    Precisely Software Inc.
    ------------------------------



  • 6.  RE: How to add Primary key to Repository

    Posted 8 days ago
    PRE-00727319


    ------------------------------
    Joseph McHugh
    MDM Solution Engineer
    Volvo IT North America
    NC
    ------------------------------



  • 7.  RE: How to add Primary key to Repository

    Employee
    Posted 7 days ago

    Joseph,

    For the benefit of others who may view this thread, this issue was avoided by exporting the attributes for the Profile as the default CSV format and then ensuring the primary key attributes were sorted by their Is Primary Key number before saving the modified CSV file and importing into the Profile.



    ------------------------------
    Brian Zupke
    Precisely Software Inc.
    ------------------------------