This post is the fourth installment in our series exploring the Oracle AI foosball demo, which was recently showcased at Oracle CloudWorld. The demo offered attendees an interactive experience, allowing them to play foosball on our table while real-time AI-generated analytics provided insights into their gameplay. For part one of this series, see Creating an AI foosball experience: Integrating OCI technologies.

This post explains the process we undertook to transform a complex, schemaless data structure optimized for operational data capture and storage, into a streamlined analytics layer capable of providing insights that the players could understand and relate to the foosball game they just played. Each event in a game is stored in a long skinny table, comprising the event type and a character large object (CLOB) column containing the JSON representation for each event.

For example, the ball position event (event type 1) appeared as the following string:

{"log":[{"x":7.4,"y":42.22,"frame_number":281},{"x":7.2,"y":41.89,
"frame_number":282},{"x":7.07,"y":41.56,"frame_number":283}]}

A goal was event type 5, and its JSON appeared as the following string:

{"gamePlayerId":860}

Our goal was to extract meaningful insights from in-game events and provide real-time analytics using Oracle Analytics Cloud (OAC). Although the flexibility of the underlying data structure was beneficial for accommodating various game types at scale, it presented substantial challenges when building an efficient analytics layer. In addition to the challenges posed by the schemaless design, we also had to perform numerous joins to trace the gamePlayerId and gameInstanceId parameters back to their corresponding teams to identify who scored each goal. This step was essential because we had no goals table to query.

This issue is common in analytics projects, where data is often not structured with analysis in mind. The real task lies in transforming this raw, unstructured data into accurate and meaningful insights that business users can easily understand—in our case, the players.

To work around these challenges, we built our analytics layer to create multiple analytics views using the JSON_TABLE SQL function as shown in the following code block:

CREATE OR REPLACE FORCE EDITIONABLE VIEW "EXDEMO"."GOALS_V" ("PLAYER",
"PLAYERDISPLAYNAME", "GAMEDATATIMESTAMP", "GAMEINSTANCEID") DEFAULT COLLATION
"USING_NLS_COMP" AS
  select PLAYER, GP.PLAYERDISPLAYNAME, CT.GAMEDATATIMESTAMP, CT.GAMEINSTANCEID from
  EXDEMO.GAMEDATAUPLOADINTERNAL CT, EXDEMO.GAMEPLAYER GP,
        JSON_TABLE (CT.GAMEDATA, '$'  
                        COLUMNS (
                            PLAYER INTEGER PATH '$.gamePlayerId'
                        )
        )
    where CT.GAMEEVENTTYPEID = 5 and PLAYER= GP.GAMEPLAYERID
    order by CT.GAMEDATATIMESTAMP
;

 

We created a total of eight views to form the basis of our first-generation analytics layer. Next, we turned to OAC for dataset modeling, as shown in Figure 1, to create the necessary joined views for our game analytics and augmented insights.

the Oracle Analytics Cloud data model
Figure 1: The Oracle Analytics Cloud data model

We opted for OAC over other options, including performing the analytics directly in our Oracle 23ai database, or in a custom APEX app, because not everyone on the team was comfortable with SQL Developer, writing SQL queries or coding. Ultimately, we developed two key OAC datasets: A real-time dashboard showing the statistics for the active match underway and a view of the entire foosball league stats covering all completed games. We also incorporated a straightforward table containing the game ID and a link to our AI avatar summary videos, enabling a direct link in the dashboard to watch the MP4 video associated with the game instance ID.

Continue reading about the Oracle AI Foosball Frenzy demo in part 5.

Get more information about Oracle’s: