Difference between revisions of "Data Sources and Tables"
Gadiyedwab (talk | contribs) |
Gadiyedwab (talk | contribs) |
||
Line 26: | Line 26: | ||
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. | 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. | ||
+ | |||
+ | To manually define a reference field, use this dialog: | ||
+ | |||
+ | [[File:reference_field.png]] | ||
+ | |||
+ | * '''Label''' - This is the display name of the current field. No need to change that. | ||
+ | * '''Type''' - Set this to "reference". The next two field will be revealed. | ||
+ | * '''Target Table''' - the name of the table that this reference field point to. | ||
+ | * '''Target Field''' - the name of the field in the target table that this reference 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. | ||
==Manually Defining a Reference Field== | ==Manually Defining a Reference Field== |
Revision as of 21:01, 19 December 2013
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.
To manually define a reference field, use this dialog:
- Label - This is the display name of the current field. No need to change that.
- Type - Set this to "reference". The next two field will be revealed.
- Target Table - the name of the table that this reference field point to.
- Target Field - the name of the field in the target table that this reference 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.
Manually Defining a Reference Field
Let's do this by an example. Suppose we have an "employee" table with a "department_id" field that has the employee's department ID, and a "department" table with a "department_id" field that uniquely identifies a department.
Both tables must be in the same data source.
- using the menu, click Explore -> Data Sources and Tables and then click on the data source that includes the employee table
- right-click or touch on any cell in the row of the "employee" table to open the context menu
- from the context menu, click "Fields"
- in the fields list for table employee, open the context menu for the "department_id" field
- from the context menu, click "Edit..."
- change the Type to reference
- set the target table to "department"
- set the target field to "department_id"
- click OK
The next time you select fields from the employee table you'll be able to also select fields from the department table.