ismail talhaoui

Jan 17, 2021

6 min read

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

Photo by Carlos Muza on Unsplash

0- Introduction

I- Project Scope

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

II- Functional needs

  • 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
  • Secure port crossings with better knowledge of the information of goods
    transiting through Moroccan ports.

III- Project Planning and management


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

VI- Functional and Technical Study

  • 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

VII- Architecture

  • In this project, we worked with Kimball approach .
  • As Oracle Data Integrator support we Use ELT architecture :


  • Definition :
  • Number of Arrival Notice
Number of AN
  • Data Mart :