Skip to end of metadata
Go to start of metadata

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

Compare with Current View Version History

« Previous Version 82 Next »

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

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.

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


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

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.

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

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/ 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).

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

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

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.

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. 

Example of code to add to the target context.xml file 
<!-- 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:

  1. axio-bi-operational → The Axiodis main database with all datas.
  2. axio-bi-reporting → The Axiodis datawarehouse database. Contains datas to build reportings.
  3. 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

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:

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

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

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:

maxHttpHeaderSize="65536"

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

JkMount /bi* axiodisWorker

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

Be careful to not duplicate existing datasources.

Be careful to update the target database for the datasources

<?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 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

3/ 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.


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.

Start the Tomcat server. You can now use the reporting tool !

  • No labels