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

...

1/ Launch SQL Server Management Studio.

2/ Click on "New query".

3/ Copy and paste past the script below in "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.

Code Block

title	Copy the following text into the opened tab:
linenumbers	true
collapse	true

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),
PROVIDER_CODE varchar(255),
INVOLVED_PROVIDER_CODE varchar(255),
VALORIZED_PROVIDER_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),
RATIO_UVKM_PREDICTED numeric(19,6),
RATIO_UVKM numeric(19,6),
NOMINAL_DIFF_RATIO_UVKM numeric(19,6),
RELATIVE_DIFF_RATIO_UVKM numeric(19,6),
RATIO_UPKM_PREDICTED numeric(19,6),
RATIO_UPKM numeric(19,6),
NOMINAL_DIFF_RATIO_UPKM numeric(19,6),
RELATIVE_DIFF_RATIO_UPKM numeric(19,6),
RATIO_KMUV_PREDICTED numeric(19,6),
RATIO_KMUV numeric(19,6),
NOMINAL_DIFF_RATIO_KMUV numeric(19,6),
RELATIVE_DIFF_RATIO_KMUV numeric(19,6),
RATIO_KMUP_PREDICTED numeric(19,6),
RATIO_KMUP numeric(19,6),
NOMINAL_DIFF_RATIO_KMUP numeric(19,6),
RELATIVE_DIFF_RATIO_KMUP numeric(19,6), 
RATIO_COSTUP numeric(19,6),
RATIO_COSTUP_PREDICTED numeric(19,6),
NOMINAL_DIFF_RATIO_COSTUP numeric(19,6),
RELATIVE_DIFF_RATIO_COSTUP numeric(19,6), 
RATIO_COSTUV numeric(19,6),
RATIO_COSTUV_PREDICTED numeric(19,6),
NOMINAL_DIFF_RATIO_COSTUV numeric(19,6),
RELATIVE_DIFF_RATIO_COSTUV numeric(19,6), 
RATIO_COSTKM numeric(19,6),
RATIO_COSTKM_PREDICTED numeric(19,6),
NOMINAL_DIFF_RATIO_COSTKM numeric(19,6),
RELATIVE_DIFF_RATIO_COSTKM 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 not null,
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, DIVISION_INDEX)
);


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 not null,
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, DIVISION_INDEX)
);

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 TABLE dbo.IC_D_ROUTE_INVOLVED_PROVIDERS (
ID_ROUTE VARCHAR(255) NOT NULL,
ID_PROVIDER VARCHAR(255) NOT NULL,
PRIMARY KEY(ID_ROUTE, ID_PROVIDER)
);

CREATE TABLE dbo.IC_D_ROUTE_VALORIZED_PROVIDERS (
ID_ROUTE VARCHAR(255) NOT NULL,
ID_PROVIDER VARCHAR(255) NOT NULL,
PRIMARY KEY(ID_ROUTE, ID_PROVIDER)
);

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

Image Removed

Create Reporting cache database

...

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

title	Copy the following text into the opened tab:
linenumbers	true
collapse	true

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 ;  

')

...

Image Removed

Reporting tool installation

Prerequisites:

Download last version of Axiodis BI: V /wiki/spaces/APT/pages/574226516
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.

...

Image Removed

...

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

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

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

...

Image Removed

...

5/ Choose the installation type you want (1/ installation from scratch, 2/ installation over an existing Axiodis with a Tomcat server, 3/ update of an existing reporting installation).

...

Image Removed

...

Image Removed

...

Reporting tool setup

If your version is 6.9.0.0 or later, please skip this step.

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

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

Warning

There is two different files called "context.xml":

C:\Program Files\Optilogistic\Axiodis6\...\tomcat\conf\bi\context.xml → Temporary file with setup variables for the reporting tool.

C:\Program Files\Optilogistic\Axiodis6\...\tomcat\conf\context.xml → The target file. Setup configuration will be in it.

<Environment name="bi_resource_path" type="java.lang.String" value="${catalina.home}/../bi"/> <Environment name="bi_sso_class" type="java.lang.String" value="it.eng.spagobi.services.common.FakeSsoService"/>  <Environment name="bi_service_url" type="java.lang.String" value="http://#{HOSTNAME}:8080/bi"/> <Environment name="bi_host_url" type="java.lang.String" value="http://#{HOSTNAME}:8080"/>  <Environment name="jdbc/bi_dialect" type="java.lang.String" value="org.hibernate.dialect.HSQLDialect"/> <Environment name="jdbc/bi_cache_dialect" type="java.lang.String" value="org.hibernate.dialect.SQLServerDialect"/>   <Resource name="jdbc/axio-bi" auth="Container" type="javax.sql.DataSource" driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:file:${catalina.base}/database/axiodisbi" username="sa" password="" maxTotal="64" maxIdle="8" maxWaitMillis="-1" validationQuery="select 1 from INFORMATION_SCHEMA.SYSTEM_USERS"/>  <Resource name="jdbc/axio-bi-cache" auth="Container" type="javax.sql.DataSource" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" url="jdbc:sqlserver://#{CACHE_HOSTNAME}:#{CACHE_HOSTPORT};databaseName=#{CACHE_DB}" username="#{CACHE_DB_USERNAME}" password="#{CACHE_DB_PASSWD}" maxTotal="64" maxIdle="8" maxWaitMillis="-1"/> <!--This is where the BI reads the real time data coming unaggregaged from the axiodis

Code Block

language	xml
title	Example of code to add to the target context.xml file
linenumbers	true
collapse	true

Info

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

Image Added

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

Image Added

Create Reporting cache 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 "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.

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

Image Added

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

Image Added

Reporting tool installation

Prerequisites:

Download last version of Axiodis BI: V /wiki/spaces/APT/pages/574226516
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.

Image Added

2/ Click on the "Next" button until the target directory screen.

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

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

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

Image Added

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.

Image Added

5/ Choose the installation type you want (1/ installation from scratch, 2/ installation over an existing Axiodis with a Tomcat server, 3/ update of an existing reporting installation).

Image Added

6/ Click on the install button.

Image Added

7/ At the end of the installation, click on the "Next" button, do not take care about the message for now.

Image Added

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

Image Added

Reporting tool setup

If your version is 6.9.0.0 or later, please skip this step.

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

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

Warning

There is two different files called "context.xml":

C:\Program Files\Optilogistic\Axiodis6\...\tomcat\conf\bi\context.xml → Temporary file with setup variables for the reporting tool.

C:\Program Files\Optilogistic\Axiodis6\...\tomcat\conf\context.xml → The target file. Setup configuration will be in it.

Code Block

language	xml
title	Example of code to add to the target context.xml file
linenumbers	true
collapse	true

<!-- BI main datasource -->
<Environment name="bi_resource_path" type="java.lang.String" value="${catalina.home}/../bi"/>
<Environment name="bi_sso_class" type="java.lang.String" value="it.eng.spagobi.services.common.FakeSsoService"/>
<!--<Environment name="bi_sso_class" type="java.lang.String" value="it.eng.spagobi.services.cas.CasSsoService3"/>-->
<Environment name="bi_service_url" type="java.lang.String" value="http://#{HOSTNAME}:8080/bi"/>
<Environment name="bi_host_url" type="java.lang.String" value="http://#{HOSTNAME}:8080"/>
<!--# Define the global hibernate dialect to be used for the main JNDI Datasource -->
<Environment name="jdbc/bi_dialect" type="java.lang.String" value="org.hibernate.dialect.HSQLDialect"/>
<Environment name="jdbc/bi_cache_dialect" type="java.lang.String" value="org.hibernate.dialect.SQLServerDialect"/>
<!--TODO: Currently the JBPM, cache and quartz are REQUIRED to be on SqlServer Database!-->

<!--This is the main schema portable database-->
<Resource name="jdbc/axio-bi

-operational

" auth="Container"
type="javax.sql.DataSource" driverClassName="

com

org.

microsoft.sqlserver.jdbc.SQLServerDriver

hsqldb.jdbcDriver"
url="jdbc:

sqlserver://#{AXIODIS_HOSTNAME}:#{AXIODIS_HOSTPORT};databaseName=#{AXIODIS_DB}

hsqldb:file:${catalina.base}/database/axiodisbi"
username="

#{AXIODIS_DB_USERNAME}

sa" password="

#{AXIODIS_DB_PASSWD}

" maxTotal="64" maxIdle="8"
maxWaitMillis="-1" validationQuery="select 1 from INFORMATION_SCHEMA.SYSTEM_USERS"/>
<!--This is where the BI

reads the MIA flux aggregaged data from

extract, trasform, load and store the

Infocenter

big

database

data-->
<Resource name="jdbc/axio-bi-

reporting

cache" auth="Container"
type="javax.sql.DataSource" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://#{

INFOCENTER

CACHE_HOSTNAME}:#{

INFOCENTER

CACHE_HOSTPORT};databaseName=#{

INFOCENTER

CACHE_DB}"
username="#{

INFOCENTER

CACHE_DB_USERNAME}" password="#{

INFOCENTER

CACHE_DB_PASSWD}

" maxTotal="64" maxIdle="8" maxWaitMillis="-1"/> <Resource name="wm/BiWorkManager" auth="Container" type="commonj.work.WorkManager" factory="de.myfoo.commonj.work.FooWorkManagerFactory" maxThreads

" maxTotal="64" maxIdle="8"

/>

If your version is 6.9.0.0 or later, please skip this step.

2/ Paste them just after the tag <Context containerSciFilter=""> in the target file.

Image Removed

3/ Setup the database accesses in the target context.xml file. You have to replace some variables in the file (look at the list on the right).

You have three database accesses to setup:

axio-bi-operational → The Axiodis main database with all datas.
axio-bi-reporting → The Axiodis datawarehouse database. Contains datas to build reportings.
axio-bi-cache → The Reporting tool database, used to stock reports and settings from the reporting tool.

List of variables to replace:

#{HOSTNAME} → Axiodis server hostname

Ex: gcrec02.optilogistic.dom

#{CACHE_HOSTNAME} → Cache database hostname

Ex: gcsql01

#{CACHE_HOSTPORT} → Cache database hostport number

Ex: 1533

Info

title	How to find your hostport number ?

Image Removed

#{CACHE_DB} → Cache database name

Ex: REC_V6_MINOR_BI1

#{CACHE_DB_USERNAME} → Cache database username

Ex: REC_V6_MINOR_BI1

#{CACHE_DB_PASSWD} → Cache database password

#{AXIODIS_HOSTNAME} → Axiodis main database hostname

Ex: gcsql01

#{AXIODIS_HOSTPORT} → Axiodis main database hostport number

#{AXIODIS_DB} → Axiodis main database name

Ex: REC_V6_MINOR_AX1

#{AXIODIS_DB_USERNAME} → Axiodis main database username

Ex: REC_V6_MINOR_AX1

#{AXIODIS_DB_PASSWD} → Axiodis main database password

#{INFOCENTER_HOSTNAME} → Axiodis datawarehouse database hostname

Ex: gcsql01

#{INFOCENTER_HOSTPORT} → Axiodis datawarehouse database hosport number

#{INFOCENTER_DB} → Axiodis datawarehouse name

Ex: REC_V6_MINOR_IC1

#{INFOCENTER_DB_USERNAME} → Axiodis datawarehouse database username

#{INFOCENTER_DB_PASSWD} → Axiodis datawarehouse database password

4/ In C:\Program Files\Optilogistic\Axiodis6\...\tomcat\conf\logging.properties add the following line if missing:

No Format
org.apache.jasper.servlet.TldScanner.level = SEVERE

5/ In C:\Program Files\Optilogistic\Axiodis6\...\conf\axio.remote.properties add the following line:

No Format
axio.reporting.app.remote.url=http\://#{HOSTNAME}:8080/bi

Replace #{HOSTNAME} by the Axiodis server hostname (see step 3).

Image Removed

6/ In C:\Program Files\Optilogistic\Axiodis6\...\tomcat\conf\server.xml add the following line:

No Format
maxHttpHeaderSize="65536"

Image Removed

7/ In C:\Program Files\Optilogistic\Axiodis6\...\apache\conf\httpd.conf add the following line:

No Format
JkMount /bi* axiodisWorker

Image Removed

Add MIA streams to populate the Datawarehouse database

Prerequisites:

User MIA to read/write the Axiodis database.

1/ Add the following _DataSources.xml content in the existing one in:

C:\Program Files\Optilogistic\Axiodis6\...\data\mia\flux

Info
Be careful to not duplicate existing datasources.

Code Block

linenumbers	true
collapse	true

<?xml version="1.0" encoding="UTF-8"?>
<dataSources>
  <dataSource code="Axiodis" type="Axiodis">
    <dateModification>10/08/2016 09:48:32</dateModification>
    <modifieur><![CDATA[nbarreau]]></modifieur>
    <libelle><![CDATA[Connecteur Axiodis 6]]></libelle>
    <description><![CDATA[Accès au connecteur API de la suite AXIODIS 6]]></description>
    <login><![CDATA[MIA]]></login>
    <password><![CDATA[F0BFXmENlh5xe673ojkdUA==]]></password>
    <locale><![CDATA[fr]]></locale>
    <timezone><![CDATA[Europe/Paris]]></timezone>
  </dataSource>
  <dataSource code="Infocentre" type="DataBase">
    <dateModification>24/10/2017 12:52:40</dateModification>
    <modifieur><![CDATA[lgirard]]></modifieur>
    <libelle><![CDATA[Base infocentre Axiodis 6]]></libelle>
    <description><![CDATA[Base de données infocentre d'Axiodis 6.]]></description>
    <type><![CDATA[SQLServer]]></type>
    <url><![CDATA[jdbc:jtds:sqlserver://GCSQL01:1533]]></url>
    <username><![CDATA[REC_V6_STABLE_IC1]]></username>
    <password><![CDATA[Pmi1X6Svp9/6LdSlat+Sp2FvdBchDDay]]></password>
    <databaseName><![CDATA[REC_V6_STABLE_IC1]]></databaseName>
    <schema><![CDATA[dbo]]></schema>
    <timezone><![CDATA[Europe/Paris]]></timezone>
  </dataSource>
  <dataSource code="AxiodisDB" type="DataBase">
    <dateModification>25/04/2018 10:10:14</dateModification>
    <modifieur><![CDATA[system]]></modifieur>
    <libelle></libelle>
    <description></description>
    <type><![CDATA[SQLServer]]></type>
    <url><![CDATA[jdbc:jtds:sqlserver://GCSQL01:1533]]></url>
    <username><![CDATA[REC_V6_STABLE_AX1]]></username>
    <password><![CDATA[Pmi1X6Svp98AfE+ZFONPkGFvdBchDDay]]></password>
    <databaseName><![CDATA[REC_V6_STABLE_AX1]]></databaseName>
    <schema><![CDATA[dbo]]></schema>
    <timezone><![CDATA[Europe/Paris]]></timezone>
  </dataSource>
</dataSources>

2/ Add the following

 maxWaitMillis="-1"/>
<!--This is where the BI reads the real time data coming unaggregaged from the axiodis database-->
<Resource name="jdbc/axio-bi-operational" auth="Container"
type="javax.sql.DataSource" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://#{AXIODIS_HOSTNAME}:#{AXIODIS_HOSTPORT};databaseName=#{AXIODIS_DB}"
username="#{AXIODIS_DB_USERNAME}" password="#{AXIODIS_DB_PASSWD}" maxTotal="64" maxIdle="8" maxWaitMillis="-1"/>
<!--This is where the BI reads the MIA flux aggregaged data from the Infocenter database-->
<Resource name="jdbc/axio-bi-reporting" auth="Container"
type="javax.sql.DataSource" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://#{INFOCENTER_HOSTNAME}:#{INFOCENTER_HOSTPORT};databaseName=#{INFOCENTER_DB}"
username="#{INFOCENTER_DB_USERNAME}" password="#{INFOCENTER_DB_PASSWD}" maxTotal="64" maxIdle="8" maxWaitMillis="-1"/>

<Resource name="wm/BiWorkManager" auth="Container"
type="commonj.work.WorkManager" factory="de.myfoo.commonj.work.FooWorkManagerFactory" maxThreads="8"/>

If your version is 6.9.0.0 or later, please skip this step.

2/ Paste them just after the tag <Context containerSciFilter=""> in the target file.

Image Added

3/ Setup the database accesses in the target context.xml file. You have to replace some variables in the file (look at the list on the right).

You have three database accesses to setup:

axio-bi-operational → The Axiodis main database with all datas.
axio-bi-reporting → The Axiodis datawarehouse database. Contains datas to build reportings.
axio-bi-cache → The Reporting tool database, used to stock reports and settings from the reporting tool.

List of variables to replace:

#{HOSTNAME} → Axiodis server hostname

Ex: gcrec02.optilogistic.dom

#{CACHE_HOSTNAME} → Cache database hostname

Ex: gcsql01

#{CACHE_HOSTPORT} → Cache database hostport number

Ex: 1533

Info

title	How to find your hostport number ?

Image Added

#{CACHE_DB} → Cache database name

Ex: REC_V6_MINOR_BI1

#{CACHE_DB_USERNAME} → Cache database username

Ex: REC_V6_MINOR_BI1

#{CACHE_DB_PASSWD} → Cache database password

#{AXIODIS_HOSTNAME} → Axiodis main database hostname

Ex: gcsql01

#{AXIODIS_HOSTPORT} → Axiodis main database hostport number

#{AXIODIS_DB} → Axiodis main database name

Ex: REC_V6_MINOR_AX1

#{AXIODIS_DB_USERNAME} → Axiodis main database username

Ex: REC_V6_MINOR_AX1

#{AXIODIS_DB_PASSWD} → Axiodis main database password

#{INFOCENTER_HOSTNAME} → Axiodis datawarehouse database hostname

Ex: gcsql01

#{INFOCENTER_HOSTPORT} → Axiodis datawarehouse database hosport number

#{INFOCENTER_DB} → Axiodis datawarehouse name

Ex: REC_V6_MINOR_IC1

#{INFOCENTER_DB_USERNAME} → Axiodis datawarehouse database username

#{INFOCENTER_DB_PASSWD} → Axiodis datawarehouse database password

4/ In C:\Program Files\Optilogistic\Axiodis6\...\tomcat\conf\logging.properties add the following line if missing:

No Format
org.apache.jasper.servlet.TldScanner.level = SEVERE

5/ In C:\Program Files\Optilogistic\Axiodis6\...\conf\axio.remote.properties add the following line:

No Format
axio.reporting.app.remote.url=http\://#{HOSTNAME}:8080/bi

Replace #{HOSTNAME} by the Axiodis server hostname (see step 3).

Image Added

6/ In C:\Program Files\Optilogistic\Axiodis6\...\tomcat\conf\server.xml add the following line:

No Format
maxHttpHeaderSize="65536"

Image Added

7/ In C:\Program Files\Optilogistic\Axiodis6\...\apache\conf\httpd.conf add the following line:

No Format
JkMount /bi* axiodisWorker

Image Added

Create Datasources and add MIA streams to populate the Datawarehouse database

Prerequisites:

User MIA exist to read/write on the Axiodis database.
Datasource called Axiodis exist to access to the Axiodis database.

3

1/ Go to Admnistration → Axiodis interfacing → Module MIA → Datasources.

2/ Click on the "Add" button to create a new Datasource.

Image Added

3/ Set a datasource called AxiodisDB with the settings below. This datasource will connect to the Axiodis database. Use the settings to connect to the Axiodis main database.

URL: Set with your Axiodis database access URL and port. Ex: GCSQL01:1533

Type: Choose your database type. SQL Server in our example.

Password: Set with the password to access to the Axiodis database.

Timezone: Set the timezone of your country.

Schema: Set with "dbo".

DatabaseName: Set with your Axiodis database name. Ex: REC_V6_STABLE_IC1

Username: Set with the username to access to the Axiodis database. Ex: REC_V6_STABLE_IC1

Image Added

4/ Set a datasource called Infocentre with the settings below. This datasource will connect to the datawarehouse database. Use the same settings as above to create datawarehouse database.

URL: Set with your Axiodis database access URL and port. Ex: GCSQL01:1533

Type: Choose your database type. SQL Server in our example.

Password: Set with the password to access to the Axiodis database.

Timezone: Set the timezone of your country.

Schema: Set with "dbo".

DatabaseName: Set with your Axiodis database name. Ex: REC_V6_STABLE_IC1

Username: Set with the username to access to the Axiodis database. Ex: REC_V6_STABLE_IC1

Image Added

5/ Add the AxiodisBI streams in the MIA streams directory:

C:\Program Files\Optilogistic\Axiodis6\...\data\mia\flux

IC-STD-01.xml → Export standard data for Axiodis datawharehouse

IC-STD-02.xml → Build event routes data for current day-1

IC-STD-03.xml → Export routes data for Axiodis datawharehouse

Info
Please visit the /wiki/spaces/APT/pages/574226516 to download the AxiodisBI streams.

6/ In Axiodis, check settings and change them in needed.

Initial settings:

IC-STD-01.xml → Launch once per day at 12:30 am.

IC-STD-02.xml → Launch once per day at 12:15 am.

IC-STD-03.xml → Launch every 2 hours.

Info
You can launch those streams manually (one by one and each time wait for the end of the execution) to populate datawarehouse database the first time.

...

Version	Old Version 80	New Version Current
Changes made by	Axel PHILIPPON-LEBREC (Unlicensed)	Axel PHILIPPON-LEBREC (Unlicensed)
Saved on	Jul 11, 2018	Jul 19, 2018

Versions Compared

Key

Create Reporting cache database

Reporting tool installation

Prerequisites:

Reporting tool setup

Create Reporting cache database

Reporting tool installation

Prerequisites:

Reporting tool setup

Add MIA streams to populate the Datawarehouse database

Prerequisites:

Create Datasources and add MIA streams to populate the Datawarehouse database

Prerequisites:

Page Comparison

Versions Compared

Key

Create Reporting cache database

Reporting tool installation

Prerequisites:

Reporting tool setup

Create Reporting cache database

Reporting tool installation

Prerequisites:

Reporting tool setup

Add MIA streams to populate the Datawarehouse database

Prerequisites:

Create Datasources and add MIA streams to populate the Datawarehouse database

Prerequisites: