Data Warehousing

The Data Warehousing feature of ChilliConnect gives you access to a sandboxed Amazon Redshift Instance that contains all of the Analytics Data collected from your game since launch. You can either connect to the Redshift Instance directly to run your own PostgreSQL queries or connect to the Redshift Instance via a 3rd Party BI tool.
Data is automatically populated into the Warehouse each day by ChilliConnect.
In order to enable Warehousing for your Application, just get in contact with us via e-mail.

Connecting to your Data Warehouse

The Redshift Connection Details are available on the ChilliConnect dashboard under the Metrics > Warehouse section:


To connect to your Redshift Instance use SQL Workbench/J , and supply the the details from the dashboard as follows:


If using the SQL Workbench/J client to connect, the Endpoint will need to be prefixed with "jdbc:redshift://", so more like "jdbc:redshift://".
The port and database are already appended to the connection endpoint.
You will not be provided with a password to go with the User initially; when first visiting this connection details page, click the "Reset" button to be given a password to copy. It's important to note that this password is only shown once, so if you forget and have to generate a new one, all previous saved connections will need to be updated as well.
The Allowed IPs section you can see is a detail that you can supply to us, and we will restrict access to the Instance unless the connection request comes from one of the IPs listed.

Once you have configured your client with the correct connection details click "Test" to make sure all the details are correct and you should be good to go.

Database Structure

The warehouse is broken up into a Schema per Game, so if you have multiple Games with warehousing enabled you will also have multiple schemas in the data warehouse.
The connection details page will contain the relevant schema to connect to per game.


The purpose of each table is as follows:

Table Description DIST Key SORT Key(s)
Events Every time your Game makes a call to AddEvent or AddEvents that Event Data ends up here sessiontoken date + eventtypeid
EventTypes Event Types as defined in the dashboard, for use in joining with the Events table to narrow results down to specific Types eventtypeid index
EventTypeParams Also defined in the dashboard under the EventTypes parent; used to narrow down results to specific Types eventtypeid applicationid
Installs The UserID and Date of every fresh game install/new user is recorded here. userid datecreated
KPIs All the KPIs that are available to the Metrics Charts are also stored here on a Row-per-Day basis. date date
Purchases All IAPs and calls that go through an IAP redemption endpoint (e.g. RedeemAmazonIAP) or through the AddIAPEvent endpoint will be inserted here. sessiontoken date
Sessions Sessions are created when a Player logs in to the Game (also when the RefreshSession endpoint is hit when the session is over a certain lifespan). The details of all the sessions are recorded here, essentially becoming a way to track how many people use the game and for how long. sessiontoken datestart
SessionSegments In order to better categorise players, they can have Segments assigned to them upon login, such as what ABTest they are a part of, or country they currently reside in. This table records the Segments that applied to the player during a certain session, allowing you to see the sessions of all player who logged in from USA etc. sessiontoken sessiontoken + type + value

Optimising Data Warehouse Queries