Case Study Introduction

The International Federation of Professional Soccer Clubs (IFPSC) is a non-profit organization uniting professional soccer clubs across the globe. It has a decentralized structure with a global HQ and administration in Switzerland and national member federations in various countries across all continents. Professional soccer clubs can join the national member federation in their geographical region.

IFPSC has recently launched a new initiative called Player Transfer Fair-play (PTFP). The main goal of this initiative is to support and professionalize youth player education programs in all member clubs, by granting them education fees that are based on the transfer sum of professional players.

Professional Soccer Club

• Can register youth players from age 12
• Receives funds in order to organize youth player education
• Reports all incoming and outgoing transfers of professional players

National Member Federation

• Distributes youth education program funds to member clubs
• Monitors national transfers between member clubs
• Calculates education fee based on transfer sum
• Collects education fee from acquiring member club
• Registers all international transfers of professional players

IFPSC

• Receives registered youth players from all National Member Federations
• Monitors international transfers of professional players on behalf of National Member Federation
• Collects and distributes youth education program funds to National Member Federations

IFPSC would like to implement an appropriate BI ecosystem in order to monitor and report on the PTFP initiative. The required BI architecture and design will be modeled with the help of EA4BI.

Enterprise Architecture viewpoints

Ross & Weill Core Diagram

The Ross & Weill Core Diagram is a simple one-page picture that provides a high-level view of the process, data, and technologies constituting the desired foundation for execution. It helps Management, Business and IT to understand the required enterprise architecture for the organization. The early identification of processes and data provides a good starting point the definition of a BI architecture.

This sample diagram identifies 3 core processes (Register Youth Player, Register International Player Transfer, Grant Education Fee) that are being supported by shared technology (PTFP Web API). This technology makes use of 4 shared data entities (International Player Transfer, Youth Player, International Player Education Fee, Professional Player).
The PTFP Web API can be used by all National Member Federations. The latter is represented as a shared customer on the diagram.

Analytical Master Data Management (MDM)

The primary purpose of Analytical Master Data Management is to improve analytics, reports, and business intelligence in general. An analytical master data capability, adequately managed by Business and IT, results in the identification and the provisioning of a single point of reference for the critical data, required by an organization in order to support decision making by Management. As such, it elaborates and details the core data present on the Ross & Weill Core Diagram, and represents an important foundational building block of the BI architecture.

This sample diagram identifies the data sources of the core data present on the previous PTFP Ross & Weill Core Diagram (Youth Players, International Player Transfers, Education Fees).

The ETL architectural building block makes use of 2 generic ETL Activities (Standardize, Join) in order to standardize the data in Youth Players and to link International Player Transfers with the originating club where the Professional Players received their youth education. The latter results in the calculation of the appropriate education fee and identifies the beneficiary club.

The MDM Hub architectural building block receives the analytical master data that is produced by ETL, although the details of this process are not shown on this diagram. In turn, the MDM Hub supports the BI artefact Reporting, which is used to produce an International Player Transfer Trends report.

Solution Architecture viewpoints

Five-Layered BI Architecture

The Five-Layered BI Architecture is a reference architecture for BI. As it is methodology-neutral, conceptual by nature, and defined at the level of solution architecture, it can serve as a good starting point for the elaboration of the BI solution space and the resulting BI architecture, without having to make an early “dogmatic” choice between the major BI methodologies represented by the other viewpoints in this architectural layer.

The five layers are: Data Source, ETL, Data Warehouse, End User and Metadata Layer.

This sample diagram elaborates the BI solution space for the PTFP initiative.

• Data Source: 3 internal data sources are identified (Financial Management System, PTFP, CRM)
  
  
• ETL: A Daily Extraction activity retrieves data from the aforementioned data sources. A cleansing activity upgrades the quality of Youth Players. The transformation activity Settlement monitors the daily clearing between IFPSC and the National Member Federations. A Daily Load activity uploads the resulting data towards the PTFP Data Warehouse
  
  
• Data Warehouse: The PTFP Data Warehouse is the enterprise data warehouse (EDW) for the PTFP initiative. The data is not directly accessed by the end users. For this purpose, 2 subject matter dimensional data marts are created (PTFP Transfers & Fees and Finance)
  
  
• End User: End users make use of SAP’s Business Objects Cloud (BOC) platform for their reports (Finance, Players & Fees), dashboard (National Member Federations) and scorecard (Settlement Forecasting)
  
  
• Metadata Layer: this utility layer manages the metadata that is required by the other layers: Data Source (PTFP, CRM, FMS), ETL (PTFP, CRM, FMS), Data Warehouse (PTFP) and End User (PTFP – Finance / Players & Fees)

Data Vault 2.0 Architecture

The Data Vault 2.0 Architecture is the solution architecture component of the Data Vault 2.0 methodology. The following sample diagram elaborates the BI solution space for the PTFP initiative, when it would be developed based on this methodology.

A Corporate ESB is used as the primary integration technology between the various operational systems (FMS, CRM) and databases (PTFP) of IFPSC. Via the ESB, the messages from these data sources can be intercepted and uploaded to the PTFP Staging area. For FMS and CRM, this is rather straightforward. But for the PTFP, a complex business rules activity (Player Education Fee) is used in order to calculate of the appropriate education fee and identify the beneficiary club.

Inside the staging area, 2 hard business rules activities are used in order to handle the daily clearing between IFPSC and the National Member Federations (Settlement) and to improve the quality of the Professional Players (Player matching). For the latter and also for other analytical master data, PTFP Staging interacts with the PTFP MDM Hub.

The resulting data is uploaded to the PTFP Data Vault and PTFP Business Vault. The latter feeds the multidimensional cube PTFP Transfers & Fees, which in turn adds the required context to the data and allows the delivery of information to the end-user layer.

Inmon Corporate Information Factory (CIF)

The Inmon Corporate Information Factory (CIF) is the solution architecture component of the Inmon Data Warehouse 2.0 methodology. The following sample diagram elaborates the BI solution space for the PTFP initiative, when it would be developed based on this methodology.

A Corporate ESB is used as the primary integration technology between the various operational systems (FMS, CRM) and databases (PTFP) of IFPSC. Via the ESB, the messages from these data sources can be intercepted and uploaded to the PTFP Staging area. Inside the staging area, a Change Data Capture (CDC) component keeps track of PTFP related data.

The ETL component contains an ETL transformation activity in order to handle the daily clearing between IFPSC and the National Member Federations (Settlement) and a ETL cleansing activity in order to improve the quality of the youth players (Youth Players).

The resulting data is uploaded to the PTFP enterprise data warehouse. The latter feeds 2 normalized data marts (PTFP Transfers & Fees and Finance) and an explorational warehouse (Finance).

These components support the Decision Support System (DSS) Applications area, allowing the delivery of information to the end-user by means of reports (Players & Fees, Finance), dashboards (National Member Federations) and scorecards (Settlement Forecasting).

Kimball Data Warehouse Architecture

The Kimball Data Warehouse Architecture is the solution architecture component of the of the Kimball Data Warehouse Toolkit methodology. The following sample diagram elaborates the BI solution space for the PTFP initiative, when it would be developed based on this methodology.

Data is retrieved from the various operational systems (FMS, CRM) and databases (PTFP) of IFPSC and uploaded to the PTFP Staging area. A cleansing activity upgrades the quality of Youth Players. The transformation activity Settlement monitors the daily clearing between IFPSC and the National Member Federations.

The results are uploaded to the dimensional data marts Finance and PTFP Transfers & Fees. The former is directly accessed by the reporting artefacts Finance and Players & Fees. The latter feeds the multidimensional cube PTFP Transfers & Fees, which in turn supports the dashboards (National Member Federations) and scorecards (Settlement Forecasting).

Technical Architecture viewpoints

Technical Reference / Solution Architecture (TRA/TSA)

The Technical Reference Architecture (TRA) allows an organization to define a platform and technology dependent technical BI reference architecture, as such providing an overarching guidance to all BI projects.

The Technical Solution Architecture (TSA) elaborates the platform and technology dependent technical BI architecture for a given project scope. It can be based on the aforementioned TRA or created ad hoc.

This sample diagram outlines a TSA based on Microsoft technology for the PTFP initiative.

MS Power BI Desktop is used at client-side in order to support the dashboards (National Member Federation) and scorecards (Settlement Forecasting) and to support the required interactivity with end-users.

The resulting OLAP requests are sent to the multidimensional cube PTFP Transfers & Fees, which runs on SQL Server Analysis Services 2016. The latter has a near real-time data interface with the dimensional data mart PTFP Transfers & Fees, which is located in a relational database that is hosted by a SQL Server 2016 instance.

The dimensional data mart PTFP Transfers & Fees also has a direct data interface with the reports (Finance and Players & Fees) that run on SQL Server Reporting Services 2016.

The client-side technical components (MS Power BI Desktop and PBIX files) are deployed on Intel x64 hardware capable of running the Windows 10 64-bits operating system.

The server-side technical components (SSAS 2016, SQL 2016, SSRS 2016) and their related artefacts (multidimensional cube, database, reports) are deployed on a Hyper-V virtual machine.

An F8 corporate firewall actively monitors and secures the communication between client PCs and server.

Technical Design viewpoints

Dimensional Modeling

Dimensional Modeling is a database design technique for modeling data warehouses and data marts, and part of the Kimball Data Warehouse Toolkit. It allows for the creation of technical designs based on this methodology.

This sample diagram provides an example star schema for the PTFP initiative.

The player transfer facts in FactPlayerTransfer can be filtered from various perspectives with the help of the dimensions Date, Professional Player, Professional Club, and National Member Federation.

Data Vault 2.0 Modeling

Data Vault 2.0 Modeling is a database design technique for modeling data vaults, and part of the Data Vault 2.0 methodology. It allows for the creation of technical designs based on this methodology.

This sample diagram provides an example data model for the PTFP initiative.

The player transfer transactions between the hubs Professional Player and Club are captured via the link Player Transfer. The satellites Player Info, Transfer Details and Club Info provide storage for the related data. A Same-as-link (SAL) Duplicate Player allows for deduplication of professional players.

ETL Modeling

ETL Modeling allows depicting the Extract Transform Load (ETL) processes that enable the data flows in the BI ecosystem of an organization. The purpose of this viewpoint is not to describe all ETL flows, but to select those that expose high complexity and/or have a major impact on the BI landscape.

This sample diagram provides an example ETL flow for the PTFP initiative.

Player transfer information is delivered by the National Member Federation via the web API and cleansed by the Player Transfer Registration activity. The result is merged in the Youth Player History activity with the information that is present in the CRM system.

If the player is found, then the originating club is retrieved and granted an appropriate youth education fee. If the player is not found, an escrow flag is added and an escrow is calculated that has to be given for a limited timeframe by the acquiring club. The results are stored in the PTFP Staging area.