How to install SpagoBI

Create Datawarehouse main database

1/ Launch SQL Server Management Studio.
2/ Click on "New query".
3/ Copy and paste the script below in SQL Server Management Studio and replace DATAWAREHOUSE_NAME by the desired name for your datawarehouse database. Copy the following text into the opened tab: Expand source declare @dbName nvarchar(MAX); set @dbName = 'DATAWAREHOUSE_NAME' --Replace DATAWAREHOUSE_NAME by the desired name for your datawarehouse database execute ('CREATE DATABASE ' + @dbName); execute ('use ' + @dbName + '; ' + 'create table dbo.IC_D_ROUTE ( ID varchar(255) not null, CODE varchar(255), EXTERNAL_ID varchar(255), ROUTE_DATE date, DOMAIN_CODE varchar(255), LOGISTICS_STATUS varchar(255), TRUCK_CODE varchar(255), DRIVER_CODE varchar(255), TRAILER_CODE varchar(255), primary key(ID) ); create table dbo.IC_M_ROUTE ( ID_ROUTE varchar(255) not null, TOTAL_DISTANCE_PREDICTED numeric(19,6), TOTAL_DISTANCE numeric(19,6), NOMINAL_DIFF_TOTAL_DISTANCE numeric(19,6), RELATIVE_DIFF_TOTAL_DISTANCE numeric(19,6), TOTAL_TIME_PREDICTED numeric(19,6), TOTAL_TIME numeric(19,6), NOMINAL_DIFF_TOTAL_TIME numeric(19,6), RELATIVE_DIFF_TOTAL_TIME numeric(19,6), UV_DELIVERED_PREDICTED numeric(19,6), UV_DELIVERED numeric(19,6), NOMINAL_DIFF_UV_DELIVERED numeric(19,6), RELATIVE_DIFF_UV_DELIVERED numeric(19,6), UP_DELIVERED_PREDICTED numeric(19,6), UP_DELIVERED numeric(19,6), NOMINAL_DIFF_UP_DELIVERED numeric(19,6), RELATIVE_DIFF_UP_DELIVERED numeric(19,6), UQ_DELIVERED_PREDICTED numeric(19,6), UQ_DELIVERED numeric(19,6), NOMINAL_DIFF_UQ_DELIVERED numeric(19,6), RELATIVE_DIFF_UQ_DELIVERED numeric(19,6), UM_DELIVERED_PREDICTED numeric(19,6), UM_DELIVERED numeric(19,6), NOMINAL_DIFF_UM_DELIVERED numeric(19,6), RELATIVE_DIFF_UM_DELIVERED numeric(19,6), UV_COLLECTED_PREDICTED numeric(19,6), UV_COLLECTED numeric(19,6), NOMINAL_DIFF_UV_COLLECTED numeric(19,6), RELATIVE_DIFF_UV_COLLECTED numeric(19,6), UP_COLLECTED_PREDICTED numeric(19,6), UP_COLLECTED numeric(19,6), NOMINAL_DIFF_UP_COLLECTED numeric(19,6), RELATIVE_DIFF_UP_COLLECTED numeric(19,6), UQ_COLLECTED_PREDICTED numeric(19,6), UQ_COLLECTED numeric(19,6), NOMINAL_DIFF_UQ_COLLECTED numeric(19,6), RELATIVE_DIFF_UQ_COLLECTED numeric(19,6), UM_COLLECTED_PREDICTED numeric(19,6), UM_COLLECTED numeric(19,6), NOMINAL_DIFF_UM_COLLECTED numeric(19,6), RELATIVE_DIFF_UM_COLLECTED numeric(19,6), START_DATE_PREDICTED datetime, START_DATE datetime, NOMINAL_DIFF_START_DATE int, END_DATE_PREDICTED datetime, END_DATE datetime, NOMINAL_DIFF_END_DATE int, FILLRATE_UV numeric(19,6), FILLRATE_UV_PREDICTED numeric(19,6), NOMINAL_DIFF_FILLRATE_UV numeric(19,6), RELATIVE_DIFF_FILLRATE_UV numeric(19,6), FILLRATE_UP numeric(19,6), FILLRATE_UP_PREDICTED numeric(19,6), NOMINAL_DIFF_FILLRATE_UP numeric(19,6), RELATIVE_DIFF_FILLRATE_UP numeric(19,6), #SITES_DELIVERED int, #SITES_DELIVERED_PREDICTED int, NOMINAL_DIFF_#SITES_DELIVERED int, RELATIVE_DIFF_#SITES_DELIVERED numeric(19,6), #SITES_COLLECTED int, #SITES_COLLECTED_PREDICTED int, NOMINAL_DIFF_#SITES_COLLECTED int, RELATIVE_DIFF_#SITES_COLLECTED numeric(19,6), #OPERATIONS_DELIVERED int, #OPERATIONS_DELIVERED_PREDICTED int, NOMINAL_DIFF_#OPERATIONS_DELIVERED int, RELATIVE_DIFF_#OPERATIONS_DELIVERED numeric(19,6), #OPERATIONS_COLLECTED int, #OPERATIONS_COLLECTED_PREDICTED int, NOMINAL_DIFF_#OPERATIONS_COLLECTED int, RELATIVE_DIFF_#OPERATIONS_COLLECTED numeric(19,6), TRANSPORTATION_COST_PREDICTED numeric(19,6), TRANSPORTATION_COST numeric(19,6), NOMINAL_DIFF_TRANSPORTATION_COST numeric(19,6), RELATIVE_DIFF_TRANSPORTATION_COST numeric(19,6), primary key(ID_ROUTE) ); create table dbo.IC_D_SEQUENCE ( ID_ROUTE varchar(255) not null, ACTIVITY_CODE varchar(255) not null, SITE_CODE varchar(255) not null, PASSAGE_INDEX int not null, ROUTE_ORDER int, STARTING_SITE_CODE varchar(255), primary key(ID_ROUTE, ACTIVITY_CODE, SITE_CODE, PASSAGE_INDEX) ); create table dbo.IC_D_OPERATION ( ID varchar(255) not null, DIVISION_INDEX int, ID_ROUTE varchar(255), CLIENT_SITE_CODE varchar(255), DEPOSIT_SITE_CODE varchar(255), PRODUCT_CODE varchar(255), PACKAGING_CODE varchar(255), CLIENT_SEQ_ACTIVITY_CODE varchar(255), CLIENT_SEQ_SITE_CODE varchar(255), CLIENT_SEQ_PASSAGE_INDEX int, DEPOSIT_SEQ_ACTIVITY_CODE varchar(255), DEPOSIT_SEQ_SITE_CODE varchar(255), DEPOSIT_SEQ_PASSAGE_INDEX int, primary key(ID) ); create table dbo.IC_M_SEQUENCE ( ID_ROUTE varchar(255) not null, ACTIVITY_CODE varchar(255) not null, SITE_CODE varchar(255) not null, PASSAGE_INDEX int not null, TOTAL_TIME_PREDICTED int, TOTAL_TIME int, NOMINAL_DIFF_TOTAL_TIME int, RELATIVE_DIFF_TOTAL_TIME numeric(19,6), UV_PREDICTED numeric(19,6), UV numeric(19,6), NOMINAL_DIFF_UV numeric(19,6), RELATIVE_DIFF_UV numeric(19,6), UP_PREDICTED numeric(19,6), UP numeric(19,6), NOMINAL_DIFF_UP numeric(19,6), RELATIVE_DIFF_UP numeric(19,6), UQ_PREDICTED numeric(19,6), UQ numeric(19,6), NOMINAL_DIFF_UQ numeric(19,6), RELATIVE_DIFF_UQ numeric(19,6), UM_PREDICTED numeric(19,6), UM numeric(19,6), NOMINAL_DIFF_UM numeric(19,6), RELATIVE_DIFF_UM numeric(19,6), START_DATE_PREDICTED datetime, START_DATE datetime, NOMINAL_DIFF_START_DATE int, END_DATE_PREDICTED datetime, END_DATE datetime, NOMINAL_DIFF_END_DATE int, primary key(ID_ROUTE, ACTIVITY_CODE, SITE_CODE, PASSAGE_INDEX) ); create table dbo.IC_M_OPERATION ( ID_OPERATION varchar(255) not null, DIVISION_INDEX int, UV_PREDICTED numeric(19,6), UV_INITIAL numeric(19,6), UV_REALISED numeric(19,6), NOMINAL_DIFF_UV numeric(19,6), RELATIVE_DIFF_UV numeric(19,6), UP_PREDICTED numeric(19,6), UP_INITIAL numeric(19,6), UP_REALISED numeric(19,6), NOMINAL_DIFF_UP numeric(19,6), RELATIVE_DIFF_UP numeric(19,6), UQ_PREDICTED numeric(19,6), UQ_INITIAL numeric(19,6), UQ_REALISED numeric(19,6), NOMINAL_DIFF_UQ numeric(19,6), RELATIVE_DIFF_UQ numeric(19,6), UM_PREDICTED numeric(19,6), UM_INITIAL numeric(19,6), UM_REALISED numeric(19,6), NOMINAL_DIFF_UM numeric(19,6), RELATIVE_DIFF_UM numeric(19,6), primary key(ID_OPERATION) ); create table dbo.IC_D_LOGISTIC_AREA ( ID varchar(255) not null, LABEL varchar(255), primary key(ID) ); create table dbo.IC_D_SITE ( ID varchar(255) not null, LABEL varchar(255), primary key(ID) ); create table dbo.IC_D_PRODUCT ( ID varchar(255) not null, LABEL varchar(255), primary key(ID) ); create table dbo.IC_D_PROVIDER ( ID varchar(255) not null, LABEL varchar(255), primary key(ID) ); create table dbo.IC_D_TRUCK ( ID varchar(255) not null, LABEL varchar(255), primary key(ID) ); create table dbo.IC_D_DRIVER ( ID varchar(255) not null, LABEL varchar(255), primary key(ID) ); create table dbo.IC_D_TRAILER ( ID varchar(255) not null, LABEL varchar(255), primary key(ID) ); CREATE LOGIN [' + @dbName + '] WITH PASSWORD=N''' + @dbName + ''', DEFAULT_DATABASE=[' + @dbName + '], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF ALTER LOGIN [' + @dbName + '] ENABLE ALTER SERVER ROLE [bulkadmin] ADD MEMBER [' + @dbName + '] ALTER SERVER ROLE [sysadmin] ADD MEMBER [' + @dbName + '] CREATE USER ' + @dbName + ' FROM LOGIN ' + @dbName + ' ALTER USER ' + @dbName + ' WITH DEFAULT_SCHEMA=[dbo] EXEC sp_addrolemember ''db_owner'', ''' + @dbName + ''' USE [master] GRANT CONNECT ANY DATABASE, AUTHENTICATE SERVER, CONNECT SQL, CONNECT ANY DATABASE TO [' + @dbName + '] WITH GRANT OPTION AS sa RECONFIGURE ; USE [' + @dbName + '] ALTER ROLE [db_owner] ADD MEMBER [' + @dbName + '] RECONFIGURE ; ');
4/ Click on "Execute". You should have a confirmation message "Command(s) completed successfully".

Create SpagoBI 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.

Copy the following text into the opened tab:

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 ;  

')