Octoboad Data Warehouse access

Octoboad Data Warehouse access

How to access PPC data in the Octoboard data warehouse

Any selected data source - Custom, Standard or Multi-Channel - can be exported and continuously updated in the Octoboard data warehouse. You can create your data warehouse database and a user in the export configuration panel. To access the configuration go to the Left Menu > PPC Data Analytics > Destinations > Data Warehouse.

Octoboard data warehouse configuration panel

Data warehouse is a dedicated MySQL database hosted by Octoboard. You can use it to point to a 3rd party Business Intelligence platform such as Tableau and others to view the data in your internal system.

Click the EDIT button to create a new user with read-only access privileges to the data warehouse tables shown below:

Creating new data warehouse user
A separate database and user are created for every organisation (client) in your Octoboard account. Users have read-only table access.

You can always modify your user by visiting this panel. If user configuration is changed, all existing connections to the data warehouse tables will be removed.

You can use any data viewer to access the tables using the above parameters. In this case, we are using DBeaver. The following image shows populated Connection details in DBeaver:

Mysql connection details in data viewer

Note the Server Host, Database name and login details copied from the Octoboard configuration panel. Make sure you enable the Secure Sockets Layer (SSL) and use the Test Connection button to verify if the setting work.

Ssl configuration for mysql connection

Data warehouse tables in MySQL database

Once you have created the new data warehouse instance and a user in Octoboard, you can access the data. The following image shows key tables and views:

Tables in mysql data warehouse

These are the main tables of every database:

  • Raw Stream Tables - contain unmodified data retrieved from the PPC cloud platform (Google, Facebook Ads, LinkedIn Ads and others)
  • Standard Stream Tables - contains standard streams data (data processed by the Octoboard ETL and rules logic)
  • Custom Stream Table - same as above but for custom streams
  • Dictionary - a special table (one for every data warehouse database) that contains the additional information for every data source and stream in the data warehouse.
  • Data Views - views created by Octoboard that contain table joins (Dictionary and Standard / Custom Tables) for ease of use in external BI tools.

Octoboard will export every created stream (Standard, Custom or Combined) to a separate table. The views are also created automatically by Octoboard and present table joins that combine stream properties and historical data captured by individual streams.

As you enable or disable individual streams in Octoboard, the data warehouse will be automatically updated: stream tables, and views will be added or removed.

Data views “join” Dictionary and Streams using octoDataSourceId and offer additional information about every exported data stream in Data Warehouse tables such as loginName, accountName, accountCurrency and others. The following image shows dictionary table fields. The dictionary table is updated every time a new source or stream is added or deleted.

Dictionary table data fields

Now that you have enabled the Data Warehouse, you can use systems such as Tableau or Splunk to view and query the collected data. Simply point your BI tool to the Octoboard Data Warehouse connection.

In addition to the Data Warehouse feature covered in this Knowledge Base topic, you can use the following data destinations to access Octoboard data: