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

Document ID : KB000009694
Last Modified Date : 14/02/2018
Show Technical Document Details
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.

 

Instructions:

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

                Create Clarity Database Step 2.jpg

 

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".

              Create DWH Database and user Step 5.jpg

 

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.

                 Create DWH Database and user Step 4.jpg

 

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:

                Create DWH Database and user Step 8.jpg

 

OK, procedure done.