AnsweredAssumed Answered

Is dwh_setup_information.txt uptodate

Question asked by urmas on Oct 11, 2016
Latest reply on Oct 11, 2016 by urmas

The dwh_setup_information.txt file that is created in

..\checkinstall\check-logs

 

Is


    -- Follow the below commands to create the Database schema and user with appropriate rights required for the Datawarehouse feature.

    -- You might require to have DBA/administrator privileges to create the following.

 
    USE master

    CREATE DATABASE clarity_dwh

    ALTER DATABASE clarity_dwh SET ARITHABORT ON

    ALTER DATABASE clarity_dwh SET ANSI_NULLS ON

    ALTER DATABASE clarity_dwh SET QUOTED_IDENTIFIER ON

    exec ('ALTER LOGIN ppm_dwh WITH CHECK_POLICY=OFF')

 

    -- SQL Server 2008

    IF (SELECT (CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1) AS INTEGER))) = 10

 

        BEGIN

        exec sp_dbcmptlevel 'clarity_dwh', @new_cmptlevel=100

        END

 

    -- SQL Server 2012

    IF (SELECT (CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1) AS INTEGER))) = 11

 

        BEGIN

        exec sp_dbcmptlevel 'clarity_dwh', @new_cmptlevel=110

        END

 

    IF NOT EXISTS (SELECT 'yes' FROM syslogins WHERE LOWER(name) = LOWER('ppm_dwh') )

        BEGIN

          exec sp_addlogin 'ppm_dwh','niku','clarity_dwh','us_english'

        END

 

    -- Set isolation level to 'read committed snapshot' for SQL Server 2005

    -- Also make sure that the user can access the system views (syslockinfo, sysindexes...)

 

    begin

      exec ('alter database clarity_dwh set read_committed_snapshot on')

      exec ('grant VIEW SERVER STATE to ppm_dwh')

      exec ('ALTER LOGIN ppm_dwh WITH CHECK_POLICY=OFF')

      exec ('grant ALTER ANY LOGIN to ppm_dwh')

      exec ('grant ALTER ANY LINKED SERVER to ppm_dwh')

    end

 

    USE clarity_dwh

    -- Schema owner is referenced in code when executing functions in MSSQL,

    -- so we intentionally hard-code it here to niku. Login name can differ.

 

    exec sp_grantdbaccess 'ppm_dwh','niku'

    exec sp_defaultdb 'ppm_dwh', 'clarity_dwh'

    exec sp_addrolemember 'db_owner', 'niku'


    --  Setup the access to the MSDB for the user

 

    use msdb

    IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'ppm_dwh')

    BEGIN

      create user ppm_dwh for login ppm_dwh

      exec sp_addrolemember 'SQLAgentUserRole', 'ppm_dwh'

    END

   
    -- Follow the below steps to Import the Microsoft SQL Server Database Backup Image for PPM_DWH.

    -- 1. The database image is available in '<root>\database' folder.

    -- 2. Copy the file database image into a temporary location on the database server.

    -- 3. Use the Restore Database tool in Microsoft SQL Server Management Studio to restore the base image.

    --        To database: clarity_dwh

    --        From device: c:\<temporary location you saved the database image to>\dwh_mssql_base.db

    -- 4. Verify the import, and remove the temporary folder and its contents.

    -- 5. Associate the imported database with your organization's SQL server security user by running the following pl/sql as the sa user:

 

            USE clarity_dwh

           ALTER USER ppm_dwh WITH LOGIN=ppm_dwh

    -- 6. To grant the VIEW SERVER STATE to the ppm_dwh user, use the following command:

            GRANT VIEW SERVER STATE to ppm_dwh

 

As far as I can see in the dwh_mssql_base.db that comes with 15.1 contains a database named ppm_dwh.

According to Release notes 15.1 only supports

Microsoft SQL Server 2012 Enterprise Edition

Microsoft SQL Server 2014 Enterprise Edition

2005 and 2008 references in the file are not valid.

 

Is this a product or documentation issue?

            

Outcomes