SAP BODS Designer Central Repository Not Showing Up When Adding

Product: SAP Data Services
Version: 4.2.x - 4.3.x

Problem Description

There are multiple BODS central repositories in the system.  Few of the central repositories are cloned from existing central repositories for following reasons:

1. Setting up more central repositories in identical BODS versions for current env
2. Setup another central repositories in identical BODS versions for different env. Issue occurs after 2nd attempt of refresh after DS Designer added the central repositories on 1st attempt
3. During upgrade, clone into a different DB user (Oracle), or logical database (MS SQL Server), then uses BODS Repository Manager to upgrade it without disrupting older BODS version

In DS Designer, menu Tools > Central Repository

After clicked "Add" button, it is either display a screen to add more central repository, or following message:

Even if it shows a list of central repositories to add, users are not able to see the desired central repository from the list.

DSMC central repository user and group setup has been done, even for secured central repository, when login to DS Designer as administrator user account.

Information to Gather

1. Login to central repository and determine the unique ID for the central repository.  This is called GUID, which will be added into local repository when users add it.  Run following SQL

select guid from al_version;

2. Repeat above for each of the central repository to get all the GUID

3. Login to local repository which failed to see other central repository

4. Run following SQL to show the central repository name and central repository GUID

select name, guid, object_key, object_type from al_lang where object_type = 5 and object_key in (select parent_objid from al_setoptions where parent_objid = al_lang.object_key and option_name = 'datastore_repotype' and option_value = 'central');

Sample output:

5. Explanation of above in local repository table AL_LANG

5.1. AL_LANG stores all object entries, including secure and non-secure central repositories

5.2. object_type = 5 is for datastores, secure repositories, non-secure repositories

5.3. al_SetOptions stores more detail for secure and non-secure central repository for Option_Name = datastore_repotype with Option_Value = 'central'

5.4. If you want to further filter by secure repository, then filter al_SetOptions by Option_Name = CENTRAL_REPO_SECURE, Option_Value = yes

Analysis

1. Compares the GUID from Step 1 with Step 4 for the same central repository name

2. Identify which central repository has the same GUID

3. This should be the central repository which is not visible in DS Designer's adding central repository screen

4. If you removed the central repository with the same GUID from DS Designer, then the list will be able to show all the central repository, even their GUID are identical

5. However, once you added one of those duplicate GUID into DS Designer, when try to add central repository again, you won't be able to see the central repository that has duplicate GUID

6. This is a consistent behavior in all local repositories

Root Cause

The AL_Version table was cloned from one central repository to another one, which leads to identical unique ID on column GUID.  Local repositories expect each central repository to have a unique GUID, else DS Designer assumes the central repository (with the same GUID) has been added, and will hide it.

Resolution

The supported approach is to use Repository Manager to re-initialize the central repository (all content will be lost), so that it will assign a new GUID.  Due to all central repository's content will be lost, if you want to keep its existing content, then find a local repository which can be used to check-out all content into it, then check back in to the central repository after it is initialized