Skip to end of metadata
Go to start of metadata

You are viewing an old version of this content. View the current version.

Compare with Current View Version History

« Previous Version 15 Next »

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: 
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 ;  

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

SpagoBI installation

Prerequisites:

  • The installation file "Axiodis BI OpenSource Suite" is already on the installation server.
  • Axiodis Tomcat server is stopped.

1/ Double click on the "Axiodis BI OpenSource.exe" file to start the installation.

2/ Click on the "Next" button.



3/ A screen will ask you a directory to install SpagoBI. Choose the Tomcat directory of Axiodis:

C:\Program FIles\Optilogistic\Axiodis\...\tomcat

Be careful to the directory, check it to be sure that SpagoBI will be install in the good one and not in a sub-folder.

4/ A pop-up will tell you that the folder already exist. Check the directory and click on "Yes" to confirm if it's ok.
5/ Click on the install button.
6/ At the end of the installation, click on the "Next" button, do not take care about the message for now.

7/ Check "Open the configuration file" if you want to open the settings file directly. Click on "Finish" to close the installer.

SpagoBI setup

1/ Open the file context.xml in C:\Program Files\Optilogistic\Axiodis6\...\tomcat\conf\bi

and copy in C:\Program Files\Optilogistic\Axiodis6\...\tomcat\conf\ all missing lines and only them.


2/ Paste them just after the tag  <Context containerSciFilter="">



  • No labels