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

...

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.

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

...

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" 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"/>
<!--This is where the BI extract, trasform, load and store the big data-->
<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 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.

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 ?

#{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).

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

No Format
maxHttpHeaderSize="65536"

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

No Format
JkMount /bi* axiodisWorker

...

Create Datasources and add MIA streams to populate the Datawarehouse database

Prerequisites:

...

User MIA exist to read/write on 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 update the target database for the 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>

Datasource called Axiodis exist to access to the Axiodis database.

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

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

36/ 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 86	New Version Current
Changes made by	Axel PHILIPPON-LEBREC (Unlicensed)	Axel PHILIPPON-LEBREC (Unlicensed)
Saved on	Jul 19, 2018	Jul 19, 2018

Versions Compared

Key

Create Datasources and add MIA streams to populate the Datawarehouse database

Prerequisites:

Page Comparison

Versions Compared

Key

Create Datasources and add MIA streams to populate the Datawarehouse database

Prerequisites: