Clarity

 View Only
Expand all | Collapse all

Data Warehouse Database Link Config Query

Jump to Best Answer
Suman Pramanik

Suman PramanikApr 10, 2015 10:11 AM

Suman Pramanik

Suman PramanikApr 10, 2015 10:50 AM

  • 1.  Data Warehouse Database Link Config Query

    Posted Apr 10, 2015 08:16 AM

    Hi guys,

     

    I have imported the base dwh image and restored to database ppm_dwh.

    When I try to set up the dblink I get an error:

     

    "Failed to update properties on the Jaspersoft server. Could not login to http://server:8080/reportservice with username ppmjasperadmin"

     

    Data Warehouse.PNG

     

    Can anyone please advise what I need to do? Thanks.



  • 2.  Re: Data Warehouse Database Link Config Query

    Broadcom Employee
    Posted Apr 10, 2015 08:32 AM

    Hi CMCN1982,

     

    The dataware house db link and Jaspersoft error is different. Can you please confirm that you have given the below rights

     

    MSSQL:

    GRANT ALTER ANY LOGIN TO PPM_DWH

    GRANT ALTER ANY LINKED SERVER TO PPM_DWH

     

    Common procedures can be used to create DBLINK for both Microsoft SQL and Oracle from CA Clarity PPM. The procedures can be called directly from the Data Warehouse schema if necessary.

     

    MSSQL:

    CMN_DBLINK_SP @P_DB_LINK_NAME VARCHAR(30),

    @P_CLARITY_DB_HOST_NAME VARCHAR(30),

    @P_CLARITY_SCHEMA_NAME VARCHAR(30),

    @P_CLARITY_USER VARCHAR(30),

    @P_CLARITY_PASSWD VARCHAR(30)

     

    Example: Microsoft SQL DBLINK Creation

    CMN_DBLINK_SP 'PPMDBLINK','<hostname>','NIKU','NIKU','NIKU'

     

    Troubleshooting steps

     

    MSSQL

    Follow these steps:

    1. Verify that the following privileges are granted to the PPM_DWH schema:

    GRANT ALTER ANY LOGIN TO PPM_DWH

    GRANT ALTER ANY LINKED SERVER TO PPM_DWH

    2. To verify that PPMDBLINK is working, run the following query from the PPM_DWH schema:

    SELECT count(1) FROM PPMDBLINK.niku.niku.srm_resources

    If the link works, data is returned.

     

    Regards

    Suman Pramanik



  • 3.  Re: Data Warehouse Database Link Config Query

    Posted Apr 10, 2015 08:51 AM

    Thank you Suman,

     

    I granted the ALTER ANY LOGIN and ALTER ANY LINKED SERVER rights to PPM_DWH

     

    CMN_DBLINK_SP @P_DB_LINK_NAME VARCHAR(30),

    @P_CLARITY_DB_HOST_NAME VARCHAR(30),

    @P_CLARITY_SCHEMA_NAME VARCHAR(30),

    @P_CLARITY_USER VARCHAR(30),

    @P_CLARITY_PASSWD VARCHAR(30)

     

    returns an error:

    Must declare the scalar variable "@P_DB_LINK_NAME"

     

    and

     

    CMN_DBLINK_SP 'PPMDBLINK','<hostname>','NIKU','NIKU','NIKU'


    returns:

    Could not find stored procedure 'CMN_DBLINK_SP'


    Can you please advise?




  • 4.  Re: Data Warehouse Database Link Config Query

    Broadcom Employee
    Posted Apr 10, 2015 10:11 AM

    Did the import worked fine?

     

    Regards

    Suman



  • 5.  Re: Data Warehouse Database Link Config Query

    Posted Apr 10, 2015 10:46 AM

    I deleted my first attempt and started again.

     

    The import and restore of ppm_dwh has completed successfully.

    Now I am at the setup login name stage.

     

    "Set up a valid login name for use by CA Clarity PPM. This login name and password are the values that you specify in CA Clarity System Administration to log in to the database. The login name and password can be any combination. Set the Microsoft SQL Server Database Schema name as niku. This name must be the Default Schema for the login user you created in the Microsoft SQL Server."

     

    So Im going to Security -> Logins -> New Login

    Login name: ppm_dwh

    SQL Server Authentication

     

    chose password.

     

    default database: should this be ppm_dwh or master?

     

    Where do I set the sql server database schema as niku>



  • 6.  Re: Data Warehouse Database Link Config Query

    Broadcom Employee
    Posted Apr 10, 2015 10:50 AM

    It should be DWH


    Regards

    Suman



  • 7.  Re: Data Warehouse Database Link Config Query

    Posted Apr 10, 2015 10:59 AM

    Ok, and where do I "set the Microsoft SQL Server Database Schema name as niku. This name must be the Default Schema for the login user you created in the Microsoft SQL Server." ?



  • 8.  Re: Data Warehouse Database Link Config Query

    Posted Apr 10, 2015 11:24 AM

    I should probably add that I have a separate server from my Clarity db.



  • 9.  Re: Data Warehouse Database Link Config Query

    Posted Apr 13, 2015 12:00 PM

    Hi guys,

     

    has anyone else run into this problem?

     

    I have granted all the necessary access to ppm_dwh

    but when I try to set up my ppmdblink I cannot get past this:

     

    CMN_DBLINK_SP @P_DB_LINK_NAME VARCHAR(30),

    @P_CLARITY_DB_HOST_NAME VARCHAR(30),

    @P_CLARITY_SCHEMA_NAME VARCHAR(30),

    @P_CLARITY_USER VARCHAR(30),

    @P_CLARITY_PASSWD VARCHAR(30)

     

    Msg 137, Level 15, State 2, Line 1

    Must declare the scalar variable "@P_DB_LINK_NAME".



  • 10.  Re: Data Warehouse Database Link Config Query

    Broadcom Employee
    Posted Apr 13, 2015 02:37 PM

    Hi cmcn1982,

     

    This query will not run because none of the variables are declared, this is normal. In fact, this is just an example of the query and the parameters you have to supply prior to running it in the Installation Guide. If you look further, here is the example:

     

    Example: Microsoft SQL DBLINK Creation

    CMN_DBLINK_SP 'PPMDBLINK','<hostname>','NIKU','NIKU','NIKU'

     

     

    So you have just to supply the correct parameters for it as per the example and run it on your DWH database,  and this should work fine. Keep in mind hostname is db hostname for Clarity, schema, user and password are also for Clarity, and your schema might be different from niku.

     

     

    Once the link creates fine, run this query to check if the link is working (provided your schema name is niku, otherwise please replace the first niku reference):

    SELECT count(1) FROM PPMDBLINK.niku.niku.srm_resources

     

    Please let me know how it goes.

     

    Kind Regards

     

    Nika Hadzhikidi

    CA Technologies

    Principal Support Engineer



  • 11.  Re: Data Warehouse Database Link Config Query

    Posted Apr 14, 2015 06:29 AM

    Thank you Nika.

     

    So if I understand correctly my command will look like this:

     

    CMN_DBLINK_SP 'PPMDBLINK','MyServerName','ppm_dwh','ppm_dwh','MyPassword'

     

    when I run this however I get:

     

    "Could not find stored procedure 'CMN_DBLINK_SP'"



  • 12.  Re: Data Warehouse Database Link Config Query

    Posted Apr 14, 2015 10:02 AM

    When I do a search for all stored procedures in the ppm_dwh database however, the cmn_dblink_sp stored procedure is listed.

     

    cmn_dblink_sp.PNG



  • 13.  Re: Data Warehouse Database Link Config Query

    Posted Apr 14, 2015 10:13 AM

    I suspect your Microsoft SQL Server installation does not have the correct server level collation; it is meant to be a case-insensitive collation setting (we in fact only support one collation, SQL_Latin1_General_CP1_CI_AS):  CA PPM 14.2 On Premise Installation Guide (restricted content): Configure SQL Server

     

    When you have a case sensitive server-level collation, it applies to the 'identifiers' in the database too (e.g. function names, stored procedures, and even table/column names, and some cross-database scoped variables), it isn't just a data setting.



  • 14.  Re: Data Warehouse Database Link Config Query

    Posted Apr 14, 2015 10:35 AM

    Thank you Nick.

     

    I checked the server collation and it appears to be ok:

     

    Server Collation.png

     

     

    Is there anything else I should be looking for?



  • 15.  Re: Data Warehouse Database Link Config Query

    Broadcom Employee
    Posted Apr 14, 2015 10:43 AM

    Hi CMCN1982,

     

    Not exactly, it's actually the Clarity database you would specify, example:

     

    CMN_DBLINK_SP 'PPMDBLINK','MyServerName','niku','niku','MyPassword'

     

    You basically have to create the link on ppm_dwh database to niku database. Since your database collation appears to be correct, could you please check you run it on the correct database. You have to run this statement on ppm_dwh, connected as ppm_dwh.

     

     

    Kind Regards

     

    Nika Hadzhikidi

    CA Technologies

    Principal Support Engineer



  • 16.  Re: Data Warehouse Database Link Config Query

    Posted Apr 14, 2015 11:01 AM

    Thank you for your patience Nika!

     

    I think I have it now.

     

    I have a separate server (I'll call it new_server here) from my database server (old_server)

     

    So in my command should I be pointing at my old_server or my new_server ?

    And I guess it will be the niku password on my db server I should use?



  • 17.  Re: Data Warehouse Database Link Config Query

    Broadcom Employee
    Posted Apr 14, 2015 11:28 AM

    Hi CMCN1982,

     

    Let's be more precise and say we need to link new database (DWH)  to the existing database Niku (Clarity). So you should use Clarity server hostname, schema, user and password as parameters, and execute the command on DWH database whilst connected with ppm_dwh user.

     

    Kind Regards

     

    Nika Hadzhikidi

    CA Technologies

    Principal Support Engineer



  • 18.  Re: Data Warehouse Database Link Config Query

    Posted Apr 14, 2015 11:50 AM

    Success! Almost!

     

    Ok so I think I have my dwh communicating with my db now.

     

    I ran the script to test the link and got results back:

    results.PNG

    In the NSA, when I try to save my parameters the status will not change to 'Available' however. Even though it would appear that my dwh and db are communicating?

     

    My port is 1433 and is definitely open.

    I believe my parameters are correct also.

     

    Vendor: SQL

    Login Name: I set this as ppm_dwh, the account is unlocked and password is ok.

     

    Hostname: I tried both my data warehouse server name and its IP here.

     

    Database name: Should this be the name of my Clarity db or my data warehouse db (ppm_dwh)

     

    Service name: ?



  • 19.  Re: Data Warehouse Database Link Config Query

    Posted Apr 14, 2015 11:58 AM

    I think I might need to set up an ODBC connection between my dwh server and my Clarity db server? Whatever I call this is what will be populated in the service name above?



  • 20.  Re: Data Warehouse Database Link Config Query
    Best Answer

    Posted Apr 14, 2015 12:39 PM

    I'm glad it's not the collation problem, firstly.

     

    An ODBC connection shouldn't be needed, if you check the contents of the cmn_dblink_sp then the procedure and parameters it uses to create the link is being done specifically with the 'sqlncli' provider (SQL Native Interface), it's not using an ODBC driven provider.

     

    So the parameters are (in order):

    1. A personal 'name' or reference for your link (e.g. 'PPMDBLINK')

    2. The hostname of the db server where your main 'niku' schema for Clarity resides (this has to be a hostname that your DWH database server recognises and can resolve).

    3. Clarity schema name of the db server from step 2 (which will be 'niku').

    4. The username / login of the db server from step 3

    5. The password for logging in as the user in step 4.



  • 21.  Re: Data Warehouse Database Link Config Query

    Posted Apr 15, 2015 05:59 AM

    Thank you so much for all of your assistance guys.

    In my NSA I now see my DHW Status as available and my PPMDBLINK appears to be working.



  • 22.  Re: Data Warehouse Database Link Config Query

    Posted Apr 15, 2015 06:15 AM

    May have spoken too soon!

     

    OK - so I was able to run CMN_DBLINK_SP 'PPMDBLINK','ClarityDbServer','NIKU','NIKU','password'

    against the ppm_dwh database whilst logged in as the ppm_dwh login user

     

    This completed successfully.

    However when I tried to verify using - SELECT count(1) FROM SRM_RESOURCES@PPMDBLINK

     

    I get the following: -

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'SRM_RESOURCES@PPMDBLINK'.

     

    This is what's confusing though - when I went to my NSA and populated the parameters as Nick suggested my status has actually changed to 'Available'.

    But, when I save I still get a connection type error - NSA-0132: Database link could not be created successfully. Please verify the Database and Data Warehouse connection details.

     

    So it would appear that my dwh and db are not communicating properly just yet.

     

     



  • 23.  Re: Data Warehouse Database Link Config Query

    Posted Apr 15, 2015 07:16 AM

    OK, some further progress.

     

    I created the DBLink OK, and now when I run the verification check I no longer get an error, "

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'SRM_RESOURCES@PPMDBLINK'."

     

    When I log into my NSA and look at the Data Warehouse Tab it says the status is 'Available'

     

    Again though, when I chose Save it tells me that - NSA-0132: Database link could not be created successfully. Please verify the Database and Data Warehouse connection details.

     

    I'm unsure as to whether the DBLink is set up correctly or not!



  • 24.  Re: Data Warehouse Database Link Config Query

    Posted Apr 15, 2015 09:59 AM

    How about: select count(1) from niku.SRM_RESOURCES@PPMDBLINK

     

    I haven't given this a try myself yet, but just suspecting since the dwh database user's default schema isn't going to be 'niku', that you may have to qualify it explicitly.



  • 25.  Re: Data Warehouse Database Link Config Query

    Broadcom Employee
    Posted Apr 15, 2015 10:41 AM

    Hi CMCN1982,

     

    It looks like now your link is set up properly, since the results are returned. It might be something else that causes the message. Please check the following permissions have been granted to ppm_dwh user:


    GRANT VIEW SERVER STATE to PPM_DWH
    GRANT ALTER ANY LOGIN TO PPM_DWH
    GRANT ALTER ANY LINKED SERVER TO PPM_DWH

     

    Then please retry and let me know if it works. If it does not, I see you have opened a Support case : we can set up a webex to resolve this further on a call.

     

     

    Kind Regards

     

    Nika Hadzhikidi
    CA Technologies
    Principal Support Engineer



  • 26.  Re: Data Warehouse Database Link Config Query

    Posted Apr 15, 2015 10:45 AM

    Thank you Nika.

     

    The ppm_dwh user definitely has those permissions applied. It's strange as I say because data is being returned, but when I choose Save in NSA it's telling me that the link could not be created successfully.

     

    Can we please set up a webex session? I feel that it is probably just some small issue that could be resolved quicker over a phone call. Thank you!