Versions Compared

Version Old Version 83 New Version 84
Changes made by

Axel PHILIPPON-LEBREC

Axel PHILIPPON-LEBREC

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Info

 Note: This tutorial has been made with SQL databases. Please adapt your actions if you use a different database type.

Table of Contents
outlinetrue

Create Datawarehouse main database

Prerequisites:

...


Info

 Note: This tutorial has been made with SQL databases. Please adapt your actions if you use a different database type.

Table of Contents
outlinetrue

Create Datawarehouse main database

Prerequisites:

  • Axiodis is already installed.
  • Datawarehouse and Cache databases are not already created.

1/ Launch SQL Server Management Studio.



2/ Click on "New query".

3/ Copy and past the script "01 Datawarehouse main database creation" in SQL Server Management Studio and replace DATAWAREHOUSE_NAME (don't replace the punctuation marks) by the desired name for your datawarehouse database.

Info

Please visit the /wiki/spaces/APT/pages/574226516 to download the AxiodisBI creation scripts.


4/ Click on "Execute". You should have a confirmation message "Command(s) completed successfully".

...

declare @dbName nvarchar(MAX); set @dbName = 'SPAGOBI_DATABASE_NAME' --Replace DATAWAREHOUSE_NAME by the desired name for your datawarehouse database /** Create the Database **/ execute ( ' CREATE DATABASE [' + @dbName + '] ') /** Compatibility SQLServer 2008 100 **/ /** Compatibility SQLServer 2012 110 **/ /** Compatibility SQLServer 2014 120 **/ /** Compatibility SQLServer 2016 130 **/ /** Compatibility SQLServer 2017 140 **/ execute ( ' ALTER DATABASE [' + @dbName + '] SET COMPATIBILITY_LEVEL = 110 ALTER DATABASE [' + @dbName + '] SET ALLOW_SNAPSHOT_ISOLATION ON ') /****** Create the Login [' + @dbName + '] ******/ execute ( ' CREATE LOGIN [' + @dbName + '] WITH PASSWORD=N''' + @dbName + ''', DEFAULT_DATABASE=[' + @dbName + '], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF ALTER LOGIN [' + @dbName + '] ENABLE ') /** Add the required roles for the created login **/ execute ( ' ALTER SERVER ROLE [bulkadmin] ADD MEMBER [' + @dbName + '] ALTER SERVER ROLE [sysadmin] ADD MEMBER [' + @dbName + '] ') execute ( ' CREATE USER ' + @dbName + ' FROM LOGIN ' + @dbName + ' ALTER USER ' + @dbName + ' WITH DEFAULT_SCHEMA=[dbo] ') /** Give the created login the ownership and connect rights on the created database **/ execute ( ' EXEC sp_addrolemember ''db_owner'', ''' + @dbName + ''' GRANT CONNECT ANY DATABASE, AUTHENTICATE SERVER, CONNECT SQL, CONNECT ANY DATABASE TO [' + @dbName + '] WITH GRANT OPTION AS sa RECONFIGURE ; ') /** Give the created database a user linked to the created login and set it as the owner **/ execute ( ' USE [' + @dbName + '] CREATE USER [' + @dbName + '] FOR LOGIN [' + @dbName + '] ALTER USER [' + @dbName + '] WITH DEFAULT_SCHEMA=[dbo] ALTER ROLE [db_owner] ADD MEMBER [' + @dbName + '] RECONFIGURE ; ')

"02 SpagoBI cache database creation" in SQL Server Management Studio and replace SPAGOBI_DATABASE_NAME (don't replace the punctuation marks) by the desired name for your datawarehouse database.

1/ Always in SQL Server Management Studio, click again on "New query" to open a new request tab.

2/ Copy and paste the script below in SQL Server Management Studio and replace SPAGOBI_DATABASE_NAME by the desired name for your datawarehouse database.

Code Block
titleCopy the following text into the opened tab:
linenumberstrue
collapsetrue
Info

Please visit the /wiki/spaces/APT/pages/574226516 to download the AxiodisBI creation scripts.


3/ Click on "Execute". You should have a confirmation message "Command(s) completed successfully".

...