Building Business Intelligence system of National Single Window for foreign trade — Morocco

Photo by Carlos Muza on Unsplash

0- Introduction

The world we live in is becoming more and more complex. Information technologies are generating a big mass of data like never before.
The problem is no longer to acquire this mass but to exploit it. To do this, we must collect quality information, standardize it, classify it, aggregate it, analyze it, exploit it, and make the right decision in the right moments.
For this purpose, it is necessary to set up a Business Intelligence system.

I- Project Scope

In the context of international business competition and sustained growth in Moroccan traffic port, this project was launched.

As part of the gradual implementation of the Moroccan Single Window of proceedings of the external trade, the entry into force of the de-commercialized circuit of subscription of the import documents will be effective from September 26, 2014.
Simplification of the import process will allow after stabilization of the circuit to reduce the import title process by eliminating the deposition and the physical circulation of documents relating thereto throughout the circuit :

• Subscription, pre-domiciliation, and bank domiciliation of the title of
  importation.
• Financial regulations and customs clearing and clearance of title of
  importation.

II- Functional needs

The main objectives of this project revolve around the following 4 Axes :

• Facilitate the formalities simplify and automate the procedures of the trade port level by acting on the information chain.
• Optimize the length of stay of ships and the transit time of goods, leading
  to better use of port infrastructure.
• Reduce the cost of logistics by optimally scheduling calls before their
  arrival.
• Secure port crossings with better knowledge of the information of goods
  transiting through Moroccan ports.

III- Project Planning and management

Like any Business intelligence project it is often much recommended to establish a good start project management.

For decision-making projects a universal initiative has been established by
Ralph Kimball and who bears his name.
The life cycle of the project proposed by Ralph is presented in the figure below. The contents of data warehouse will be determined according to the needs of the users, and according to the availability of the given on the system.

RALPH KIMBALL BI PROJECT MANAGEMENT APPROACH

IV- Tools and technologies

• Oracle Database 12c : The Oracle Database offers a wide range of options and features in the areas of Availability, Scalability, Analytics, Performance, Security, Management, Developers, and Integration. These aim to enhance and complement existing database functionality to meet customer-specific requirements. All Database Options are only available for Enterprise Edition and offered for an extra cost. An exception to these two rules is Oracle Real Application Clusters option, which comes included with Oracle Database 12c Standard Edition 2 at no additional cost.
• SQL Developer : SQL Developer is an Oracle IDE that enhances productivity and simplifies database development and administration tasks.
• ODI 12c: Oracle Data Integrator provides a new declarative design approach to defining data transformation and integration processes, resulting in faster and simpler development and maintenance. Based on a unique ELT architecture (Extract -Load Transform), Oracle Data Integrator guarantees the highest level of performance possible for the execution of data transformation and validation processes.
• OBIEE 12c: Oracle BI Enterprise Edition is a powerful tool for analyzing and presenting data it provides a full range of business intelligence capabilities that allow us to:
  - Collect up-to-date data from our organization
  - Present the data in easy-to-understand formats (such as tables and graphs)
  - Deliver data in a timely fashion to the employees in our organization
• PL-SQL: is a combination of SQL along with the procedural features of programming languages.

V- My Role

As Business Intelligence I took three modules from the project :

1- Arrival Notice (AN)

The Arrival Notice is used to warn the port authorities, Customs and Stowage of the entry of a ship with notice following what is required by law in Morocco.

2- Import Title (IT)

The Import Title is used to warn different authorities: banks, Ministry of Foreign Trade, exchange office, technical services.
Administration of Customs and Indirect Taxes, it is characterized by two types import license, import commitment.

3- Request Post Allocation (RPA)

The RPA is used to make effective the application for the assignment of a ship’s post to the port authorities and stowage within the period provided for by law in Morocco.

in this article, I will present the first module and in the next I will present the rest.

VI- Functional and Technical Study

=> the Arrival Notice pass through Five overlaps:

• The Consignee of the ship: responsible for creating the Arrival Notice and sending it
• The Consignor of goods: specified in the Arrival Notice, it will act once the manifest sent by the ship’s consignee has obtained an authorization number from the Administration of Customs and Indirect Taxes.
• National Ports Agency : will receive the Arrival Notice and will have to confirm or reject it
• Maintenance Operator or Terminal : terminal that will receive the Arrival Notice to confirm or reject it
• Customs Agency of Morocco : will receive the Arrival Notice to confirm or reject it

=> STATE DIAGRAM OF AN

State Diagram of AN

VII- Architecture

• In this project, we worked with Kimball approach .

As Kimball said in 1997, “the data warehouse is nothing more than the union of all data marts.”

• As Oracle Data Integrator support we Use ELT architecture :

VII- AN KPIs

• Definition :

A KPI or Key Performance Indicator is a measurement that evaluates the performance of a business activity.

I will limit this module to one KPI

• Number of Arrival Notice

Number of AN

• Data Mart :

By definition a data mart is a subset of a data warehouse oriented to a specific business line. Data marts contain repositories of summarized data collected for analysis on a specific section or unit within an organization, for example, the sales department

in this Example the arrival Notice Data Mart has eight Dimensions and one fact Table with five measures .
The dimension tables consist of a technical key, a business key, and the attributes.
The technical key has been put in place so that future changes in the source database do not cause inconsistencies in the Data Mart.
They also make it possible to improve the response times (because the index on integer values is faster than on character strings).
We have therefore created an auto-incremented sequence for each dimension to replace the natural keys with technical keys.

Thank you for your Attention and your time If you are interested in Business Intelligence or Java or Scrum or making websites and Application or Decision System, make sure to check out my portfolio and to contact Me !

Thanks to All teams Who contributed to this work