Working with datasets in an Amazon QuickSight Q topic

Applies to: Enterprise Edition

Intended audience: Amazon QuickSight administrators and authors

When you create a topic, you can add additional datasets to it or import datasets from existing dashboards. At any time, you can edit metadata for a dataset and set a data refresh schedule. You can also add new fields to a dataset in a topic by creating calculated fields, filters, or named entities.

Adding datasets to a topic

At any time, you can add datasets to a topic. Use the following procedure to learn how.

To add datasets to a topic

1. Open the topic that you want to add one or more datasets to.

2. On the Summary page, under Datasets, choose Add datasets.

   

3. On the Add datasets page that opens, choose the dataset or datasets that you want to add, and then choose Add datasets.

   The dataset is added to the topic and Q begins indexing the dataset's unique string values. You can edit the field configurations right away. For more information about the Q index, see Refreshing Amazon QuickSight Q topic indexes. For more information about editing field configurations for Q, see Making Amazon QuickSight Q topics natural-language-friendly.

Adding datasets with row-level security (RLS) to a topic

You can add datasets that contain row-level security (RLS) to Q topics. All fields in a topic respect the RLS rules applied to your dataset. For example, if a user asks, "show me sales by region," the data that Q returns is based on the user's access to the underlying data. So, if they're only allowed to see the East region, only data for the East region appears in the Q answer.

RLS rules are applied to automatic suggestions when users are asking questions. As users enter questions, only the values that they have access to are suggested to them. If a user enters a question about a dimensional value that they don't have access to, they do not get an answer for that value. For example, suppose that the same user is entering the question, "show me sales in the West region." In this case, they do not get a suggestion or an answer for it, even if they ask, because they don't have RLS access to that region.

By default, QuickSight Q allows users to ask questions regarding fields based on the user's permissions in RLS. Continue to use this option if your field contains sensitive data that you want to restrict access to. If your fields don't contain sensitive information and you want all users to see the information in Q suggestions, then you can choose to allow questions for all values in the field.

To allow questions for all fields

1. From the QuickSight start page, choose Datasets.

2. On the Datasets page, choose the dataset that you added RLS to, and then choose Edit dataset.

   For more information about adding RLS to a dataset, see Using row-level security (RLS) in Amazon QuickSight.

3. On the data preparation page, choose the field menu (the three dots) for a field that you want to allow for Q, and then choose Row level security for Q.

   

4. On the Row level security for QuickSight Q page that opens, choose Allow users to ask questions regarding all values on this field.

   

5. Choose Apply.

6. When finished editing the dataset, choose Save & publish in the blue toolbar at upper right.

7. Add the dataset to your Q topic. For more information, see the previous section, Adding datasets to a topic.

If you currently allow users to ask questions regarding all values, but want to implement the dataset's RLS rules to protect sensitive information, then repeat steps 1–4 and choose Allow users to ask questions regarding this field based on their permissions. When you are done, refresh the dataset in your topic. For more information, see Refreshing datasets in a topic.

Refreshing datasets in a topic

When you add a dataset to a topic, you can specify how often you want that dataset to refresh. When you refresh datasets in a topic, Q refreshes the index for that topic with any new and updated information.

Q doesn't replicate your datasets when you add them to a topic. Q creates an index of unique string values and doesn't index metrics. For example, measures stored as integers are not indexed by Q. Questions asked always fetch the latest sales metrics based on data in your dataset.

For more information about refreshing the topic index, see Refreshing Amazon QuickSight Q topic indexes

You can set a refresh schedule for a dataset in a topic, or refresh the dataset manually. You can also see when the data was last refreshed.

To set a refresh schedule for a topic dataset

1. Open the topic that you want to change.

2. On the Summary page, under Datasets, expand the dataset that you want to set a refresh schedule for.

3. Choose Add schedule, and then do one of the following in the Add refresh schedule page that opens.

   

   • If the dataset is a SPICE dataset, select Refresh topic when dataset is imported into SPICE.

     Currently, hourly refresh SPICE datasets aren't supported in Q. SPICE datasets that are set to refresh every hour are automatically converted to a daily refresh. For more information about setting refresh schedules for SPICE datasets, see Refreshing SPICE data.

   • If the dataset is a direct query dataset, do the following:

     1. For Timezone, choose a time zone.

     2. For Repeats, choose how often you want the refresh to happen. You can choose to refresh the dataset daily, weekly, or monthly.

     3. For Refresh time, enter the time that you want the refresh to start.

     4. For Start first refresh on, choose a date that you want start refreshing the dataset on.

4. Choose Save.

To manually refresh a dataset

1. On the topic Summary page, under Datasets, choose the dataset that you want to refresh.

2. Choose Refresh now.

   

To view refresh history for a dataset

1. On the topic Summary page, under Datasets, choose the dataset that you want to see refresh history for.

2. Choose View history.

   

   The Update history page opens with a list of the times the dataset was refreshed.

Removing datasets from a topic

You can remove datasets from a topic. Removing datasets from a topic doesn't delete them from QuickSight.

Use the following procedure to remove a dataset from a topic.

To remove a dataset from a topic

1. Open the topic that you want to change.

2. On the Summary page, under Datasets, choose the dataset menu (the three dots) at right, and then choose Remove from topic.

   

3. On the Are you sure you want to delete? page that opens, choose Delete to remove the dataset from the topic. Choose Cancel if you don't want to remove the dataset from the topic.

Adding calculated fields to a topic dataset

You can create new fields in a topic by creating calculated fields. Calculated fields are fields that use a combination of one or two fields from a dataset with a supported function to create new data.

For example, if your dataset contains columns for sales and expenses, you can combine them in a calculated field with a simple function to create a profit column. The function might look like the following: sum({Sales}) - sum({Expenses}).

To add a calculated field to a topic

1. Open the topic that you want to change.

2. In the topic, choose the Data tab.

3. For Actions, choose Add calculated field.

   

4. In the calculations editor that opens, do the following:

   1. Give the calculated field a friendly name.

   2. For Datasets at right, choose a dataset that you want to use for the calculated field.

   3. Enter a calculation in the calculation editor at left.

      You can see a list of fields in the dataset in the Fields pane at right. You can also see a list of supported functions in the Functions pane at right.

      For more information about the functions and operators you can use to create calculations in QuickSight, see the Calculated field function and operator reference for Amazon QuickSight .

5. When finished, choose Save.

   The calculated field is added to the list of fields in the topic. You can add a description to it and configure metadata for it to make it more natural language friendly.

Adding filters to a topic dataset

Sometimes your business users (readers) might ask questions that contain terms that map to multiple cells of values in the data. For example, let's say one of your readers asks Q, "Show me weekly sales trend in the west." West in this instance refers to both the Northwest and Southwest values in the Region field, and requires the data to be filtered to generate an answer. You can add filters to a topic to support requests like these.

To add a filter to a topic

1. Open the topic that you want to add a filter to.

2. In the topic, choose the Data tab.

3. For Actions, choose Add filter.

   

4. In the Filter configuration page that opens, do the following:

   1. For Name, enter a friendly name for the filter.

   2. For Dataset, choose a dataset that you want to apply the filter to.

   3. For Field, choose the field that you want to filter.

      Depending on the type of field you choose, you're offered different filtering options.

      • If you chose a text field (for example, Region), do the following:

        1. For Filter type, choose the type of filter that you want.

           For more information about filter text fields, see Adding text filters.

        2. For Rule, choose a rule.

        3. For Value, enter one or more values.

      • If you chose a date field (for example, Date), do the following:

        1. For Filter type, choose the type of filter that you want, and then enter the date or dates that you want to apply the filter to.

           For more information about filtering dates, see Adding date filters.

      • If you chose a numeric field (for example, Compensation), do the following:

        1. For Aggregation, choose how you want to aggregate the filtered values.

        2. For Rule, choose a rule for the filter, and then enter a value for that rule.

        For more information about filtering numeric fields, see Adding numeric filters.

   4. (Optional) To specify when the filter is applied, choose Apply the filter anytime the dataset is used, and then choose one of the following:

      • Apply always – When you choose this option, the filter is applied whenever any column from the dataset you specified is linked to a question.

      • Apply always, unless a question results in an explicit filter from the dataset – When you choose this option, the filter is applied whenever any column from the dataset you specified is linked to a question. However, if the question mentions an explicit filter on the same field, the filter isn't applied.

   5. When finished, choose Save.

      The filter is added to the list of fields in the topic. You can edit the description for it or adjust when the filter is applied.

Adding named entities to a topic dataset

When asking questions about your topic, your readers might refer to multiple columns of data without stating each column explicitly. For example, they might ask for the address of a transaction. What they actually mean is that they want the branch name, state, and city of where the transaction was made. To support requests like this, you can create a named entity.

A named entity is a collection of fields that display together in an answer. For example, using the transaction address example, you can create a named entity called Address. You can then add the Branch Name, State, and City columns to it, which already exist in the dataset. When someone asks a question about address, the answer displays the branch, state, and city where a transaction took place.

To add a named entity to a topic

1. Open the topic that you want to change.

2. In the topic, choose the Data tab.

3. For Actions, choose Add named entity.

   

4. In the Named entity page that opens, do the following:

   1. For Dataset, choose a dataset.

   2. For Name, enter a friendly name for the named entity.

   3. For Description, enter a description of the named entity.

   4. (Optional) For Synonyms, add any alternate names that you think your readers might use to refer to the named entity or the data it contains.

   5. Choose Add field, and then choose a field from the list.

      Choose Add field again to add another field.

      The ordering of the fields listed here are the order they appear in answers. To move a field, choose the six dots at left of the field name and drag and drop the field to the order that you want.

   6. When finished, choose Save.

   The named entity is added to the list of fields in the topic. You can add edit the description for it and add synonyms to it to make it more natural language friendly.