Step by Step to create and restore the DATA Warehouse under MS SQL

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

This technical document is to show you step by step of the creation of the user of Data Warehouse "ppm_dwh" and restore procedure of the database with the same name on the MS SQL server.

 

Environment

Release: 451-101-15.1-Clarity-Creator User License
Component:

Resolution

1. Run the "SQL Server Management Studio" from your MS SQL server

2. From your SQL Server Management Studio use your credentials "sa" to enter

                

<Please see attached file for image>

 

3. Under the Security, you will have to create new Login user called "ppm_dwh", please verify that you change to the authentication to "SQL Server authentication".

              

<Please see attached file for image>

 

4. To Create and Restore the Data Warehouse you will need to Right-click on the Databases and select "Restore Database..."

5 In the Destination Database field, type the following: niku

6 Under Source, locate the source y click the Browse button and choosing  the file that came in the media DVD:\InstallMedia\clarityv14X\Database and Select mssql_base.db file.

                 

<Please see attached file for image>

 

7 Wait until the restore of the database, appears OK.

8. Under a New Query please enter and run:

    USE Master

    ALTER DATABASE ppm_dwh SET ARITHABORT ON

    ALTER DATABASE ppm_dwh SET ANSI_NULLS ON

    ALTER DATABASE ppm_dwh SET QUOTED_IDENTIFIER ON

    ALTER DATABASE ppm_dwh SET READ_COMMITTED_SNAPSHOT ON

    GRANT VIEW SERVER STATE TO ppm_dwh

    SP_DEFAULTDB 'ppm_dwh', 'ppm_dwh'

    USE ppm_dwh

    ALTER USER ppm_dwh WITH LOGIN=ppm_dwh

    EXEC SP_DBCMPTLEVEL niku, 110             (using MSSQL SERVER 2008: 100 or MSSQL SERVER 2012: 110)

    SP_CONFIGURE 'REMOTE QUERY TIMEOUT', 0

    RECONFIGURE WITH OVERRIDE

    use Master    

    GRANT ALTER ANY LOGIN TO ppm_dwh

    GRANT ALTER ANY LINKED SERVER TO ppm_dwh

    use Master 

    ppm_dwh.cmn_dblink_sp 'ppmdblink','MSSQL_SEVER_NAME','niku','niku','niku'

 

 Note: "MSSQL_SEVER_NAME" is the MS SQL server name, so you will have to change it the server name you have!!!

 

9. Under the Security - Login with the "ppm_dwh" user, in the properties change the Default database:

                

<Please see attached file for image>

 

OK, procedure done.