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 a Table

You can edit the label and description for a table. This will help your users decide when to use the table. To edit a table, using the list of table shown above click (or touch) the row for the table to bring up the context menu and select "Edit table".

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.

Restricting Access

By default, users who have the "explorer" role (regular users) can list all the data sources and tables. You can however restrict access to data sources and tables by assigning one or more roles to the table or data source. If you do that, then only users who have at least one of the specified roles would be allowed to see and access the table or data source.

Editing Roles for a Table or Data Source

To assign roles to a table or data source, follow these steps:

1. Using the "Browse data sources and tables" option on the "Data" menu, bring up the list
2. Find the data source or table in the list and click on that line to display the context menu
3. From the context menu, select "Edit data source roles" or "Edit table roles"
4. The Edit Read Roles dialog is displayed

Select roles from the drop down list. Remember that users will need just one of these roles, so selecting more roles can potentially increase the number of users who can see the data source or table.

To remove roles from the list simply uncheck them. If you clear out Roles completely, then anyone with the "explorer" role can see the table or data source.

To create a new role, simply type its name in Roles. Type a comma followed by the name of the new role. The double quotes around the name are optional. If you specify a new role, then no user currently has that role and therefore no user (including yourself) will be allowed to see this table or data source, so remember to grant this new role to yourself and other users who should have this new role.

⇦ Managing Favorites

⇧ Table of Contents

⇨ Drill Down