Data Schema & Field Types



In datapine, you can always adjust the data schema of your connected databases and flat files to fit it to your analytical needs. You may rename the tables and columns of your database or even change the field types if necessary. This is important as the field types determine whether you can apply some of the sorting and limit functions to it. Here is an overview of the data schema in datapine and the different field types.



Data Schema


All connected data schemas can be accessed in the left panel of the Analyzer. Their fields and tables can be used to create charts and graphs. You may limit and edit the table names and columns displayed in the database connection sections Import and Label Editor.


Explanation of data schema in Analyzer





1. This is the data source name that you have given your database in the database setup as Internal Name. Click here to learn where you can change the data source name displayed to other users in your organization.



2. These are the tables in your data schema. In the database connection setup you may choose to exclude full tables that might not be necessary for your analyses in datapine. In the Label Editor you can also change the name of the tables displayed in the data schema.



3. The columns of your database tables will be displayed as grey fields in your data schema. During the database connection setup, you may include or exclude individual fields, rename them and also change their field type.







Field Types


The field type of your column determines whether you can run specific aggregations or formatting on this data later. We generally differentiate between four field types in datapine. In your data schema, these fields will be shown with different icons to the left of the field’s name.


a) Date Fields


This type of field may contain classical date and time values or timestamps. In the Chart Creator, you may apply different date aggregations and formats to these fields.


b) Boolean Fields


This type of field usually contains only two values, intended to represent the true and wrong values of a system logic. They work great as a filter value for your charts.


c) Text Fields


These field types contain any form of strings that you wish to use for your analysis. They usually are the variables you put into the X-Axis of your charts in datapine or you can use them as Filters on your dashboards.


d) Number Fields


Number fields may contain whole numbers or decimals. They can be aggregated using different aggregation types.



Change Field Types


For example, if you have a field containing decimals that is recognized as whole numbers you can change the field type of this column in the Connect section. Go to the data sources overview under Connect and access your data source with a click onto the edit function on the right side of the data source widget.



Change the field type in a database


You can also change the field types of database fields in the Label Fields section of the database setup process. There you will have a preview of all tables and fields in your database and the corresponding field types. Here is quick tutorial on how to change them in datapine.


  1. In the database connection screen, access the Label Editor with a click on Label fields on the navigation bar.
  2. From the database table structure on the left select the table that contains the field whose data type you wish to change.
  3. On the right, you will now see the list of columns in this table. Click onto the data type dropdown next to the column labels and select the new field type.
  4. Make sure to save any changes.


How to update the field type in a database




Change the field type in a CSV file


The field types of CSV files can be adjusted in the CSV Connection Screen. In the data preview each column will have the data type shown below the column name. Here is how to change the data type in a csv file in datapine.


  1. In the Connect CSV screen, go to the column for which you wish to change the column type. Click onto the Field Type dropdown displayed below the column name.
  2. Then select the preferred field type from the drop down.
  3. Save your changes.

How to update a field type in a csv