1. Introduction
1.1 Purpose of the document
The aim of document is to explain how the Galilee module works, including the schematics of the data warehouse and the main table elements.
1.2 General principles
Galilée est le module « Business Intelligence » de 
Project Monitor. Il comporte un dispositif d’alimentation d’un entrepôt de données.
L’entrepôt de données va contenir des données exportées depuis la base de production sur une fréquence à établir (en général, rafraîchissement chaque jour ouvré). Son schéma est organisé pour facilement être exploitable et interrogeable sans avoir besoin de connaissances approfondies sur le modèle Project Monitor.
Synchronization takes place in two stages:
- Delete all database tables Galilee
- Création des tables et l’insertion des données de Project Monitor vers la base de données Galilée
Les données de l’entrepôt couvrent l’essentiel des données de Project Monitor.
2. Logical architecture
This diagram represents the general logical architecture of the Business Intelligence (BI) offering integrated into the Galilee solution (datamart):
3. The tables of Galilee
3.1 Synchronization
Depuis Project Monitor, l’administrateur peut choisir, dans la carte 
Galilee, la liste des attributs (indépendamment des thèmes sélectionnés) et des thèmes à descendre dans Galilée (datamart).
How it works :
- Cliquer sur le menu principal >
Administration > Paramétrages avancés>Configuration technique - Click on the map Galilee :
Dans la section Paramétrage Galilée vous avez le choix entre :
- Attributes: lists the attributes to be synchronized in Galilee.
- Themes: lists the themes to be synchronized in Galilee.
- Law
- Planning
- Budget
- Resource
- risk
- indicator
- task
- exchange
La synchronisation peut s’effectuer manuellement depuis l’IHM de Project Monitor (voir ci-dessus, en cliquant sur le bouton « exécuter ») ou automatiquement via un paramétrage depuis le fichier de configuration de Project Monitor (voir Dossier d’Exploitation).
3.2 Tables
Le schéma de la base de données Galilée (datamart) est architecturé pour facilement être exploitable et interrogeable sans avoir besoin de connaissances approfondies sur le modèle Project Monitor. Vous trouverez ci-dessous une vue générale de la base de données Galilée :
3.2.1 The theme budget
The tables containing the information on budget are as follows:
Description of each table:
Table | Description |
Budget | List of type measures budget |
envelope | Envelope descriptive and reporting data (e.g. status, amount, wording, etc.) |
DoneBudget | Budget reporting data (e.g. amount target, 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, links between father and son projects...) |
AttachmentEnvelope | Data from structure link from envelopes to hierarchies |
period | Period description |
Remarks :
The notion of drift corresponds to "Project - target ".
3.2.2 theme demandbudget
The tables that concentrate the "request-budget" information are :
Description of each table:
Tables | Descriptions |
Budget | List of type measures budget |
FactBudgetRequest | Values of budget requests in the projects, associated with the assumptions made |
BudgetRequest | monitoring versions and status of budget project requests |
Phase | Phase data (phases and milestones) |
Project | Descriptive project data (e.g. name, project manager, links between father and son projects...) |
AttachmentEnvelope | Data from structure link from envelopes to hierarchies |
3.2.3 The theme structure
The tables that concentrate information on hierarchies are :
Description of each table:
Tables | Descriptions |
Dimension 1 à 30 | Description des hiérarchies. IMPORTANT : Galilée est limité à 30 hiérarchies ainsi qu’à 10 niveaux au sein de chaque hiérarchie |
AttachmentEnvelope | Data from structure link from envelopes to hierarchies |
AttachmentProject | Project data structure link |
RattachementRessource | Data from structure link resources |
Here is the synchronization rule:
Hierarchies are synchronized if and only if in the structure file the "optional" or "mandatory" options are active on one of the fields of the structure file.
If one of the 2 fields is active, structure will be synchronized.
The order of entry in the "dimension" tables is as follows:
Order number (smallest to largest), then name (alphabetical order)
Pour avoir des informations complémentaires sur ces tables, veuillez vous référer au chapitre 
Mémo Galilée (Datamart) - 4.4.2 Les hiérarchies projet
3.2.4 The theme project
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, links between father and son projects...) |
AttachmentProject | Project data structure link |
Period | Period description |
3.2.5 The theme resource
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 | Data from structure link resources |
Resource | List and characteristics of resources |
Phases | Phase data (phases and milestones) |
Project | Descriptive project data (e.g. name, project manager, links between father and son projects...) |
Period | Period description |
Instantane | Logbook to record resource evolution |
3.2.6 The theme indicator
The tables that concentrate the information from theme indicator are :
Description of each table:
Tables | Descriptions |
FactIndicator | Indicator reporting data (e.g. reference, completed, planned, ...) |
indicator | Indicator descriptive data |
Phases | Phase data (phases and milestones) |
Project | Descriptive project data (e.g. name, project manager, links between father and son projects...) |
Period | Period description |
3.2.7 The theme risk
The tables that concentrate the information on risk 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, links between father and son projects...) |
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 | Données descriptives et de reporting des tâches (ex : libellés, assigné, statut…) |
Project | Descriptive project data (e.g. name, project manager, links between father and son projects...) |
3.2.9 The 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, ...) |
Project | Descriptive project data (e.g. name, project manager, links between father and son projects...) |
3.2.10 The theme attribute
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. value ofattribute, ...) |
FaitAttributPhase | Phase attribute reporting data (e.g. value ofattribute, ...) |
FaitAttributTache | Task attribute reporting data (e.g. value ofattribute, ...) |
Period | Period description |
form | Form descriptions |
AttributeForm | structure link relationship from a 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 message fields.
3.2.11 The right 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, links between father and son projects...) |
FaitRole | Role reporting data (role project, role general, hierarchical roles...) |
Dimensions | Description of hierarchies. IMPORTANT: Galilee is limited to 20 hierarchies and 10 levels within each hierarchy. structure |
For further information on these tables, please refer to the Security management chapter.
3.2.12 Technical theme
The tables containing the technical information are :
Description of each table:
Tables | Descriptions |
Libelle | List of all labels |
Update | Description of Galilee |
ScriptsBdD | Description of database script pass elements |
4. Fact tables
4.1 Description
The fact tables link the various themes seen above and contain the values of these themes.
Here's a list of all the de facto tables:
- FactBudgetRequest
- FaitBudget,
- ResourceFact,
- PlatformResourceFact,
- FaitAttributProjet
- FaitDureeProjet,
- FaitAttributPhase
- FactAttributeExchange
- DoneAttributeTask,
- FactIndicator
- FaitRole
In each fact table, we find :
- the corresponding object id
- the id of theattribute
- the id of the period if it is a periodic attribute
- 3 columns by value :
- valueString
- NumericValue
- datevalue
The "String value" contains string, Boolean, rich and list values.
The period table can be accessed to query fact tables corresponding to measurements.
This table is structured on three levels:
- Level 1: month
- Level 2: quarterly
- Level 3: the year
- codeLevel: retrieves the "month", "quarter", "year" and "fiscal year" levels of each period with :
- NIV_MOIS: corresponds to the period month level
- NIV_TRIMESTRE: corresponds to the period quarter level.
- NIV_EXERCICE: corresponds to the period exercise level (if exercises exist on the UP).
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 of interactions with the "resource" fact table:
Resources work like budgets. They also have the notion of "profile".
Inactive resource data is available at 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 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, i.e. 10. 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 parent project of a child project (idProjet).
Since version 6.3.3, project links have been integrated into Galilee in the "DependencePlanning" 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 "estPhase" field has been added to the " Phase " table. The value is true if it is a phase, false if it is a milestone.
4.6 Risks
Below is the view overview of interactions with the " risk " fact table:
4.7 Attributes
Here is the view overview of interactions with the "attributes" fact table:
4.8 Indicators
Below is the view overview of interactions with the " indicator " fact table:
Remarks :
Average advancement corresponds to (target - 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:
- Module Budget / Budget management / Consultation of a budget
- Module milestone / Milestone management / Consultation of a milestone
- Module risk / Risk management / Consulting a risk
- Module indicator / Indicator management / Consultation of a 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 parameterization can be deduced from the Roles tables of Galilee.
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 in 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. Informations qui ne sont pas déversées dans Galilée
Security is implemented by the "Right" table
Certaines informations disponibles au sein de Project Monitor ne sont pas déversées dans le Datamart Galilée.
Il s’agit d’informations qui sont soit nécessaires au fonctionnement de Project Monitor, soit métier mais qui ne présentent pas d’intérêt pour un usage décisionnel de l’outil de la part des clients
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_Module_Period | 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-support purposes is :
Table Project Monitor | |
Invoice | Billing list |
Quote | Quotation list |
VersionBudget | Version of a budget |
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. Annexes
7.1 Mindmap Galilée
All schematics were created using XMIND software (free version).
7.2 MCD Galilée
The complete MCD of the Galilee database is available in Microsoft Access format.
7.3 Configuration sous 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