Data Sources and Tables
Contents
Overview
Data for analysis is organized in tables grouped under data sources. See Preparing to Explore for more information about configuring data sources and uploading data.
The menu option "Data" -> "Browse data sources and tables" displays a list of your data sources. Click on a data source name to get a list of tables for that data source. From there you can click on a table name to see a list of views for that table and to create a new view. You can also use the context menu to access many table options such as displaying its fields.
Deleting a Table
If the table belongs to an external data source, then deleting the table will only remove it from Explore Analytics and will not affect the table itself on the remote data source. On the other hand, if the table is stored in Explore Analytics, for example if you uploaded a file, then deleting the table will also delete all the data from Explore Analytics.
If you delete a table, all views that are defined on this table will also be permanently deleted. Also, if there are other tables with reference fields that point to this table, then deleting this table may break views created on those other tables and you probably should not delete this table.
If you decide to delete this table, use the "Delete table" option of the context menu shown in the picture in the previous section.
Changing a Table Label
Changing the label simple changes the display name of the table anywhere tables are listed in Explore Analytics. You can change the name to make it more descriptive to make it easier for users to select the table. It's also an opportunity to localize the table label to your language.
Refresh Table Configuration
Refreshing a table is available for tables of external data sources. It is useful for discovering new table fields that were added since the data source was created in Explore Analytics.
Listing Table Fields
Select "Fields" from the context menu shown in the overview above. It shows a list of all the fields of the table. You can edit table fields as explained in the next section by clicking on a field and selecting "Edit this field" from the context menu.
Editing Table Fields
The following attributes of a field are automatically set, however depending on your role and permission, you may be able to edit them. When editing fields, you are editing the field definition for all users who may access this table. Therefore, the changes you make will affect all these users and any view that is based on this table.
- Name - the name of the field in the database. When you upload a CSV File, this is the field name from the file. The name cannot be edited
- Label - the display name of this field. This is the name shown when selecting fields and it is the title shown for fields in views. You can edit the label to shorten it or to make it more descriptive. You can also use the label to translate the name to your language.
- Type - the type of this field: datetime, decimal, integer, text, duration, choice, or reference. When you change the type, you are not changing the data, but only the way that Explore Analytics interprets the data in this field. Your choice of types will be restricted. For example, you may interpret a number as text, but not as a date.
- Target Table - if the type is reference, it means that this field hold values that can be used to uniquely identify records in another table. The Target Table is the name of the other table.
- Target Field - if the type is reference, it means that this field hold values that can be used to uniquely identify records in another table. This field can be matched against the "Target Field" of the other table. The "Target Field" is often referred to as the primary key of the other table.
- Use As Label - at most one field of this table can be designated to be used for display when another table has a reference field that points to this table. To understand this, imaging an "employee" table with a "department" field. The "department" field hold the department ID and is a reference to the "ID" field in the "department" table. Now, if the "department" table designate the "name" field as "Use As Label", then instead of displaying the department ID when the user selects the "department" field, we could display the department name. Currently, you cannot change the assignment of "Use As Label". It's used automatically with certain data sources.
- Hierarchy - if this field is part of a drill-down hierarchy, then the name of the hierarchy is shown here.
- Drill Down - if this field is part of a drill-down hierarchy, then the it shows the name of the field that's next (down) in the hierarchy.
- Drill Up - if this field is part of a drill-down hierarchy, then the it shows the name of the previous field (up) in the hierarchy.
Reference Fields
Reference Fields are automatically defined when you add a data source that is a database such as Oracle or MySQL and the database has these references already defined (they are called Referential Integrity Constraints). In that case, Explore Analytics will automatically defined fields that have a (single field) referential constraint as reference field.
A reference field has values that uniquely identify rows in another table by matching a field in that table. These are called the Target Table and Target Field respectively. You can manually define reference field.
Manually Defining a Reference Field
Let's do this by example. The location field is a reference to the "Locations" table and will match the "Location Id" field in that table:
Both tables must be in the same data source.
To bring up the following dialog, click on the the location field (in the field list) select "Edit this field" from the context menu.
- Label - This is the display name of the current field. No need to change that.
- Type - Set this to "reference". The next two fields will be revealed.
- Target Table - the name of the table that this field point to.
- Target Field - the name of the field in the target table that this field will match.
It it crucial that the target field uniquely identifies records in the target table. If that assumption does not hold, bad things happen (for example, double-counting data). If the target table is in the Explore data source, in other words, if it's a table that is stored in Explore Analytics, a unique index will be created and an error will be reported if the target field has any duplicate values.
The next time you select fields from this table you'll be able to also select fields from the Locations table by expanding the list under the location field.
