Infocapture Database Structure Explained and How to use BI Business Intelligence (BI Tool)

Overview

A simple guide to help on-prem clients to integrate InfoCapture with their existing BI (Business Intelligence) software.

InfoCapture provides an easy and quick way to create e­form, capture information and design business process for your organisation.  Many enterprises may have already implemented BI (Business Intelligence) tool in­house such as Microsoft Power BI, or Tableau

Many of this tool provides connection directly to SQL database allowing data to be manipulated in real­time.  This guide is to help understand how InfoCapture data is stored and structured and how to create ​SQL view ​for your BI tools. 

Please note: this is only possible for on-premise deployment and you have direct access to the SQL database.

 

SQL View

InfoCapture tables are designed to allow your users to create form and design business process without coding knowledge. This may not provide direct view required for BI tools, in this case, ​the SQL view​ needs to be created. 

SQL View: a view is a virtual table based on the result­set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. 

 

Resource

How to create SQL View depends on the type of database

MSSQL 

MySQL

 

Understanding InfoCapture Tables

InfoCapture Project

We are using an example InfoCapture Project called "Claromentis Asset Records" below

 

InfoCapture Database Tables

 

Data relational diagram between InfoCapture Project (hd_project) InfoCapture Tickets (hd_issue) and InfoCapture Field Data (fb_field_data)

 

hd_project

This is the main InfoCapture project table contain information about project name, properties and other settings.

id​= project ID
name =​ project name

 

 

hd_issues

All tickets/issues are stored in this table

id​= global unique id of each ticket
project_id =​ id of the project
id_in_project =​unique issue id within the project
form_data_id​= reference to the form data

 

fb_field_data

individual form data is stored in this table

fb_field_data ​has three columns ­

  • v​alue 
  • value_int 
  • value_text​

Only one of them actually contains data, depending on the field type (which is known for each form, but also can be found in table f​b_field​ by matching f​b_field.​f​orm_id ​with f​b_form_data.​form_id

and fb_field_data.f​ield_sym_name​ with f​b_field.s​ym_name).

hd_issue​ and f​b_field_data​ both have a column f​orm_data_id,​which is not visible in the front­end but identifies a group of fields in that issue (it's a reference to f​b_form_data,​but it's not relevant for the reporting). 

 

 

Created on 10 September 2018 by Michael Christian. Last modified on 11 March 2020

Was this helpful?  

0 Likes
Share