1. Introduction

1.1 Purpose of the document

The purpose of this document is to explain how the Galilee module works, including the data warehouse schema and the main table elements.

1.2 General principles

Galilee is the Business Intelligence module of Project Monitor. It includes a data warehouse feeder.

The data warehouse will contain data exported from the production database at a frequency to be determined (generally refreshed every working day). Its schema is organized so that it can be easily exploited and queried without the need for in-depth knowledge of the data model. Project Monitor.

Synchronization takes place in two stages:

1. Delete all Galilee database tables
2. Create tables and insert data from Project Monitor to the Galilee database

Warehouse data covers the bulk of Project Monitor.

2. Logical architecture

This diagram shows the general logical architecture of the Business Intelligence (BI) offer integrated into the Galilee solution (datamart):

3. Galilee tables

3.1 Synchronization

From Project Monitorthe administrator can select, in the map GalileeThe list of attributes (independent of the themes selected) and themes to be descended in Galilee (datamart).

How it works :

• Click on the Main menu > Administration > Advanced settings > Technical configuration
• Click on the map Galilee :

In the Galilee settings you can choose between :

• Attributes : Lists the attributes to be synchronized in Galilee.
• Themes : lists the topics to be synchronized in Galilee.
• Law
• Planning
• Budget
• Resource
• risk
• indicator
• task
• exchange

Synchronization can be performed manually from the Project Monitor (see above, by clicking on the "run" button) or automatically via a setting in the Project Monitor (see Operating File).

3.2 Tables

The Galilee database schema (datamart) is designed to be easily exploitable and searchable without the need for in-depth knowledge of the model. Project Monitor. Below is a general view the Galilee database:

3.2.1 Thebudget theme

The tables containing the budget information are as follows:

Description of each table:

Table	Description
Budget	List of budget measures
envelope	Envelope descriptive and reporting data (e.g. status, amount, label, etc.)
DoneBudget	Budget reporting data (e.g. target amount, planned, ...)
PieceFinanciere	Descriptive data for financial documents (e.g. commitment, mandate, etc.)
Phase	Phase data (phases and milestones)
Project	Descriptive project data (e.g. name, project manager, father/son project links, etc.)
AttachmentEnvelope	structure link data structure link from envelopes to hierarchies
period	Period description

💡

The notion of drift corresponds to "Project - target ".

3.2.2 The demand-budget theme

The tables that concentrate the "request-budget" information are :

Description of each table:

Tables	Descriptions
Budget	List of budget measures
FactBudgetRequest	Values of budget requests in projects, associated with assumptions made
BudgetRequest	version and status monitoring of project budget requests
Phase	Phase data (phases and milestones)
Project	Descriptive project data (e.g. name, project manager, father/son project links, etc.)
AttachmentEnvelope	structure link data structure link from envelopes to hierarchies

3.2.3 Thestructure theme

The tables that concentrate information on hierarchies are :

Description of each table:

Tables	Descriptions
Dimensions 1 to 30	Description of hierarchies. IMPORTANT: Galilee is limited to 30 hierarchies and 10 levels within each structure.
AttachmentEnvelope	structure link data structure link from envelopes to hierarchies
AttachmentProject	Project structure link data
RattachementRessource	structure link data structure link resources

Here is the synchronization rule:

Hierarchies are synchronized if and only if the "optional" or "mandatory" options are active in one of the structure master fields.

If one of the 2 fields is active, the structure will be synchronized.

The order of entry in the "dimension" tables is as follows:

Order number (smallest to largest), then wording (in alphabetical order)

For further information on these tables, please refer to chapter Memo Galilee (Datamart) - 4.4.2 Project hierarchies

3.2.4 The project theme

The tables that concentrate project information are :

Description of each table:

Tables	Descriptions
FaitDureeProjet	Pre-calculated data over project duration
Project	Descriptive project data (e.g. name, project manager, father/son project links, etc.)
AttachmentProject	Project structure link data
Period	Period description

3.2.5 The resource theme

The tables that concentrate resource information are :

Description of each table:

Tables	Descriptions
ResourceFact	Resource reporting data (e.g. time spent, planned, ...)
ResourceFactDay	Detailed daily resource reporting data
FaitRessourcePlatform	Descriptive resource data
Profile	Profile list
RattachementRessource	structure link data structure link resources
Resource	List and characteristics of resources
Phases	Phase data (phases and milestones)
Project	Descriptive project data (e.g. name, project manager, father/son project links, etc.)
Period	Period description
Instantane	Logbook to record resource evolution

3.2.6 Theindicator theme

The tables that concentrate the theme indicator information are :

Description of each table:

Tables	Descriptions
FactIndicator	Indicator reporting data (e.g. reference, completed, planned, etc.)
indicator	Indicator descriptive data
Phases	Phase data (phases and milestones)
Project	Descriptive project data (e.g. name, project manager, father/son project links, etc.)
Period	Period description

3.2.7 Therisk theme

The tables that concentrate the risk information are :

Description of each table:

Tables	Descriptions
CategoryRisk	List and characteristics of risks
FactRisk	Risk reporting data (e.g. probability, severity, etc.)
Phases	Phase data (phases and milestones)
Project	Descriptive project data (e.g. name, project manager, father/son project links, etc.)
Period	Period description
risk	List of Risks

3.2.8 The theme task

The table containing the task information is :

Description of each table:

Tables	Descriptions
Stain	Descriptive and reporting data for tasks (e.g. labels, assigned, status, etc.)
Project	Descriptive project data (e.g. name, project manager, father/son project links, etc.)

3.2.9 theme exchange

The table containing the exchange information is :

Description of each table:

Tables	Descriptions
Exchange	Descriptive data on exchanges (e.g. labels, message, type, etc.)
Project	Descriptive project data (e.g. name, project manager, father/son project links, etc.)

3.2.10 Theattribute theme

The table containing the attribute information is :

Description of each table:

Tables	Descriptions
attribute	Attribute descriptive data (e.g. code, labels, type, etc.)
FaitAttributProjet	Project attribute reporting data (e.g.attribute value,attribute creation date)
FactAttributeExchange	Trade attribute reporting data (e.g.attribute value, ...)
FaitAttributPhase	Phase attribute reporting data (e.g.attribute value, ...)
FaitAttributTache	Task attribute reporting data (e.g.attribute value, ...)
Period	Period description
form	Form descriptions
AttributeForm	structure link from an attribute to a form

Remarks :

• For historical attributes, we save the historical values in these tables.
• For rich attributes, HTML fields can be synchronized in Galilee in a non-HTML translated form (display with tags), notably to simplify field content and remove images from spot message fields.

3.2.11 The right-hand theme

The tables which concentrate the law information are :

Description of each table:

Tables	Descriptions
Law	Data containing the information needed to establish consultation rights per user, as well as the first and last name of each user.
User	User descriptive data (e.g. surname, first name, language, e-mail address, etc.)
Project	Descriptive project data (e.g. name, project manager, father/son project links, etc.)
FaitRole	Role reporting datarole projectrole , general role , hierarchical roles, etc.)
Dimensions	Description of hierarchies. IMPORTANT: Galilee is limited to 20 hierarchies and 10 levels within each structure.

For further information on these tables, please refer to the Safety management chapter .

3.2.12 The technical theme

The tables containing the technical information are :

Description of each table:

Tables	Descriptions
Libelle	List of all labels
Update	Description of Galilee update elements
ScriptsBdD	Description of database script pass elements

4. Fact tables

4.1 Description

4.2 Budgets

Here are the general views of interactions with the "FaitBudget" and " Budget " fact tables:

4.3 Resources

Here is the general view interactions with the "resource" fact table:

Resources work like budgets. They also have the notion of "profile".

Data on inactive resources are included in Galilee, so as not to truncate analyses. An "active" column has been added to the Resource table, to indicate whether the resource is active or not.

4.4 Projects

4.4.1 general view

The "Project" table groups all UP projects linked to the data warehouse.

4.4.2 Project hierarchies

This table is also linked to a number of generic tables (30 in the schema: Dimension1, ...Dimension30). If there are more than 30 hierarchies, then the datamart retrieves the first 30 hierarchies according to their orders and labels.

For a structure to be synchronized in Galilee, at least one structure link must be activated (optional or mandatory); otherwise it will not be synchronized.

However, each generic dimension has a fixed number of levels (10), so a dimension with more than 10 levels of depth cannot be imported into Galilee .

Since version 6.2, the link between father and son projects has been integrated into Galilee. This information is contained in the "idProjetPere" column of this table. This column provides the identifier of the father project of a child project (idProjet).

Since version 6.3.3, project links have been integrated into Galilee in the "DependencyPlanning" table.

4.5 The phases

Since version 5.3, milestones are technically phases.

The "milestones" tables have therefore disappeared, their data having been integrated into the "phases" tables.

To distinguish phases from milestones, an "isPhase" field has been added to the " Phase " table. The value is true if it's a phase, false if it's a milestone.

4.6 Risks

Here is the general view interactions with the " risk " fact table:

4.7 Attributes

Here is the general view interactions with the "attributes" fact table:

4.8 Indicators

Here is the general view interactions with the " indicator " fact table:

Remarks :

Average progress corresponds totarget - completed) /target - reference).

5. Safety management

Security is implemented by the "Right" table:

It is fed with data from the production diagram using the following rights:

• Budget module / Budget management / Consulting a budget
• milestone module / Milestone management / Consulting a milestone
• risk module / Risk management / Consulting a risk
• indicator module / Indicator management / Viewing an indicator

Each query to the data warehouse can filter projects according to the logged-in user.

Example: filter on projects with budget consultation rights

project.idProject in (select idpropjet from Droit where login = %loginUtilisateurConnecté% and consultationBudget = 1)

Queries created in reports will have to manage security manually. In the case of a ROLAP tool, if it integrates a notion of security, its parameters can be deduced from Galilee's Roles tables.

The "FaitRole" table shows the role of each user:

• If the user's role is of type PROJECT, then the code "TYPE_PROJET" is entered in the "codeTypeRole" column.
• If the user's role is of type HIERARCHIE, then the "TYPE_HIERARCHIE" code is entered in the "codeTypeRole" column.
• The "idVn" column is filled with the id of the structure value to which it is attached.
• If the user's role is of type GENERAL, then the code "TYPE_GENERAL" is entered in the "codeTypeRole" column.

6. Information not uploaded to Galilee

Security is implemented by the "Right" table 

Some of the information available in Project Monitor are not uploaded to the Galilee Datamart.

This is information that is either necessary for the operation of the Project Monitoreither business-specific, but which are of no interest to customers for decision-support purposes

The list of technical entities not included in Galilee is :

Table Project Monitor
SFRIM	Technical Table
Favourite	Technical Table
Favourite list	Technical Table
Document	Technical Table
Platform	Technical Table
List	Technical Table
List value	Technical Table
view	Technical Table
Calendar	Technical Table
CalendarYear	Technical Table
RegleCalendar	Technical Table
library	Technical Table
Instant perimeter	Technical Table
ConfigAccueilBloc	Technical Table
WebHook	Technical Table
Representation	Technical Table
ThemeFilActualite	Technical Table
Datatalk	Technical Table
Constraint	Technical Table
PlatformLanguage	Technical Table
subscription	Technical Table
Workflow	Technical Table
AddOn	Technical Table
Interval	Technical Table
status	Technical Table
ActivityStatus	Technical Table
Rates	Technical Table
Slideshow template	Technical Table

The list of entities not included in Galilee Métier that are not of interest for decision-making is :

Table Project Monitor
Invoice	Billing list
Quote	Quotation list
VersionBudget	budget version
portfolio	List of portfolios
RevuePortefeuille	List of Portfolio Reviews
CommentExchange	List of comments in exchanges
CommentSpot	List of comments contained in Tasks
CommentsEnterActivity	List of comments contained in the activity entry

7. Appendices

7.1 MCD Galilee

7.2 Configuration in Oracle

In the event of a data import error with the following code ORA-1000: Too many open cursors.

Check the following keys in the [CodeUP].galilee.properties file:

persistence._defaut_.hibernate.dbcp.poolPreparedStatements=false
persistence._default_.hibernate.dbcp.maxOpenPreparedStatements=100

7.3 Changes in Version 7.7, effective upon the transition to Galilee

Fait" tables: deletion of "codeGenre" and "libelleGenre" columns

The "codeGenre" and "libelleGenre" columns have been removed from the "FaitBudget", "FaitDemandeBudget", "FaitIndicateur", "FaitRessource" and "FaitRisque" tables.

These columns are present in the Budget, indicator, Resource and risk tables and can be retrieved as follows:

• BudgetFact and BudgetRequestFact => join with Budget table via column idBudget
• FactIndicator => join with indicator table via idIndicator column
• FaitRessource => join with the Resource table via the idRessource column
• FaitRisque => join with table risk via column idRisque

Deleting views

The following views have been removed

• FaitAttributProjet
• FaitAttributPhase
• FactAttributeExchange
• FaitAttributTache
• FaitAttributPieceFinanciere
• FactAttributeScenario
• FaitAttributProjetPer
• FaitAttributPhasePer
• FaitAttributEchangePer
• FaitAttributTachePer
• FaitAttributPiecePer
• FactAttributeScenarioPer

Table renaming

The FaitAttributXXXParDate tables have been renamed FaitAttributXXX, including the "beginDate" and "endDate" columns, but without the idPeriode column. The complete list is shown below:

• FaitAttributProjetParDate => FaitAttributProjet
• FactAttributePhaseParDate => FactAttributePhase
• FactAttributeExchangeParDate => FactAttributeExchange
• FactAttributeTaskParDate => FactAttributeTask
• FaitAttributPieceFinanciereParDate => FaitAttributPieceFinanciere
• FactAttributeScenarioParDate => FactAttributeScenario

Integrating change

The join between the FaitAttributXXX and Periode tables will now be possible with the FaitAttributXXX.beginDate and Periode.dateDebut columns by selecting Periode.codeLevel = NIV_MOIS

It will still be possible to retrieve the lastattribute value by searching the FaitAttributXXX tables with FaitAttributXXX.endDate set to null.

To illustrate these changes, here are 2 queries (pure SQL):

Retrieve periodic project attribute values for the active period

• Before :

SELECT pr.libelleProjet,
       a.libelleAttribut,
       fap.valueString
FROM FaitAttributProjet fap
JOIN Project pr ON pr.idProjet = fap.idProjet
JOIN attribute a ON a.idAttribut = fap.idAttribut
WHERE fap.idPeriode = (
     SELECT p.idPeriode
     FROM Periode p
     WHERE p.codeLevel = 'NIV_MOIS'
       AND p.periodeActive = 1
     )
ORDER BY pr.libelleProjet, a.libelleAttribut;

• After :

SELECT pr.libelleProjet,
       a.libelleAttribut,
       fap.valueString
FROM FaitAttributProjet fap
JOIN Project pr ON pr.idProjet = fap.idProjet
JOIN attribute a ON a.idAttribut = fap.IdAttribut
WHERE fap.endDate IS NULL AND fap.beginDate IS NOT NULL
ORDER BY pr.libelleProjet, a.libelleAttribut;

Retrieve periodic project attribute values by period

- Before:
SELECT pr.libelleProjet,
       a.libelleAttribut,
       p.libelleMois,
       p.libelleAnnee,
       fap.string.value
FROM FaitAttributProjet fap
JOIN Period p ON p.idPeriode = fap.idPeriode
JOIN Project pr ON pr.idProjet = fap.idProjet
JOIN attribute a ON a.idAttribut = fap.idAttribut
WHERE p.codeLevel = 'NIV_MOIS'
ORDER BY pr.libelleProjet, a.libelleAttribut, p.dateDebut;

• After :

Changes in version 7.6

Tableenvelope": deletion of the columns "idPhase", "montantTotalMesure" and "montantPeriodeMesure".

The "idPhase", "montantTotalMesure" and "montantPeriodeMesure" columns have been removed from theenvelope" table, as the data can be found in the "FaitBudget" table.

Fait" tables: deletion of "idGenre" column

The "idGenre" column has been removed from the "FaitBudget", "FaitDemandeBudget", "FaitIndicateur", "FaitRessource", "FaitRessourceJour" and "FaitRisque" tables.

Table "TypePieceFinanciere": deletion of column "estEmbryon".

The "estEmbryon" column has been removed from the "TypePieceFinanciere" table.

Obsolete columns that can be removed in a future version (remember to update your scripts)

GenCode and GenLabel columns in Fact tables

New "ActivityStatusFact" table

The "FaitActiviteStatut" table has been added; it corresponds to the status of activity entries for a resource and a monthly period ; if the entry is validated, the validation date is indicated.

FaitRessourceJour" table: "comment" column added

The "comment" column has been added to the "FaitRessourceJour" table; it corresponds to an optional comment on a phase for a given day of a resource's timesheet .

Note: a comment on a day without a charge will not be shown in "FaitRessourceJour".

Treatment modification

• Rich-format data such as task messages are now retrieved with the full HTML content of the data, rather than just the text.
• Previously, if the value of a list-type attribute with a default value was entered and then set to null in an object (e.g. a project form , a financial document, etc.). The default value was visible in Galilee tables. From now on, the null value will be present.

Modifying data types

Some data types have been modified, for the entire Galilee database

• numeric => bit (for Boolean numerics only)
• numeric => bigint (for numeric containing large numbers only)
• numeric => int (for numeric containing standard numbers)
• text => nvarchar(max) (data can now be null)
• datetime => datetime2 (when date with time)
• datetime => date (when date without time)

Modifying tables

Some unnecessary tables have been removed

• FaitDureeProjet
• ScriptsBdD

Add columns to certain tables

• Form attribute: id
• Budget : codeGenre, libelleGenre
• Dimension[1-30] : id
• Law: id
• envelope : id
• FaitBudget : id
• FaitBudgetProjetScenario : id
• ScenarioConstraintFact: id
• BudgetRequestFact: id
• FactIndicator: id
• FaitRessource : id
• FaitRessourceJour : id
• FaitRessourcePlateforme : id
• FaitRessourceProjetScenario : id
• RiskFact: id
• FaitRole : id
• indicator : codeGenre, libelleGenre
• ProjectLinks : id
• PieceFinanciere : id
• ProjectScenario : id
• AttachmentAttribute: id
• EnvelopeAttachment: id
• RattachementProjet : id
• ResourceAttachment : id
• Resource: codeGenre, libelleGenre
• risk : codeGenre, libelleGenre

Changing column names in certain tables

• envelope : amountTotalMeasure becomes targetEnvelope
• envelope : MeasureAmountPeriod becomes EnvelopePlanner

Deleting columns in certain tables

• Exchange
• echangePere
• exchangeResponse
• idEchangePere
• DoneBudget
• idGenre
• FactBudgetRequest
• idGenre
• FactIndicator
• idGenre
• ResourceFact
• idGenre
• ResourceFactDay
• idGenre
• envelope
• idPhase

Changing the data type for certain tables

• FaitRessourcePlatform
• costDailyResource: can no longer be null, set to zero by default
• capaciteRessource: can no longer be null, set to zero by default
• DoneBudget
• cumuleBudget: can no longer be null, set to zero by default
• deriveBudget: can no longer be null, set to zero by default
• previsionnel1Budget: can no longer be null, set to zero by default
• previsionnel2Budget: can no longer be null, set to zero by default
• previsionnel3Budget: can no longer be null, set to zero by default
• reestimeBudget: can no longer be null, set to zero by default
• resteAFaireBudget: can no longer be null, set to zero by default
• ResourceFact
• deriveRessource: can no longer be null, set to zero by default
• planifieRessource: can no longer be null, set to zero by default
• previsionnelReparti: can no longer be null, set to zero by default
• previsionnelRestantCumule: can no longer be null, set to zero by default
• reestimeCalculeRessource: can no longer be null, set to zero by default
• reestimeRessource: can no longer be null, set to zero by default
• resteAFaireGlobal: can no longer be null, set to zero by default
• resteAFaireRessource: can no longer be null, set to zero by default
• FaitRessourcePlatform
• capaciteRessource: can no longer be null, set to zero by default
• costDailyResource: can no longer be null, set to zero by default
• FactRisk
• maximumRiskCriticism: can no longer be null, set to zero by default
• numberCriticalRisks: can no longer be null, set to zero by default
• sumRiskCriticism: can no longer be null, set to zero by default
• sumCriticiteRisqueMaxPoss: can no longer be null, set to zero by default
• FactRisk
• maximumRiskCriticism: can no longer be null, set to zero by default
• numberCriticalRisks: can no longer be null, set to zero by default
• sumRiskCriticism: can no longer be null, set to zero by default
• sumCriticiteRisqueMaxPoss: can no longer be null, set to zero by default
• Phase
• codeStatutPhase : nvarchar(500) => nvarchar(100)
• weightingStatusPhase: can no longer be null, defaults to zero
• phaseTerminee: can no longer be null (Boolean)
• User
• languageUser : nvarchar(500) => varchar(100)