Data Sources and Tables
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.
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.
