Optimize queries utilizing dataset parameters in Amazon QuickSight

Amazon QuickSight powers data-driven organizations with unified enterprise intelligence (BI) at hyperscale. With QuickSight, all customers can meet various analytic wants from the identical supply of reality by way of fashionable interactive dashboards, paginated stories, embedded analytics and pure language queries.

Now we have launched dataset parameters, a brand new sort of parameter in QuickSight that may provide help to create interactive experiences in your dashboards. On this put up, we dive deeper into what dataset parameters are, clarify the important thing variations between dataset and evaluation parameters, and talk about completely different use circumstances for dataset parameters alongside their advantages.

Introduction to dataset parameters

Earlier than going deep into dataset parameters, let’s first talk about QuickSight evaluation parameters. QuickSight evaluation parameters are named variables that may switch a price to be used by an motion or an object. Parameters assist customers create interactive experiences of their dashboards. You may tie parameters with different options within the QuickSight evaluation. For instance, a dashboard person can reference a parameter worth in a number of locations, utilizing controls, filters, and actions, and in addition inside calculated fields, narratives, and dynamic titles. Then the visuals within the dashboard react to the person’s number of parameter worth. Parameters can even assist join one dashboard to a different, permitting a dashboard person to drill down into knowledge that’s in a distinct evaluation.

Dataset parameters, however, are outlined on the dataset stage. With dataset parameters, authors can optimize the expertise and cargo time of dashboards which can be related reside to exterior SQL-based sources. When readers work together with their knowledge, the choice and actions they make in controls, filters, and visuals might be propagated to the info sources by way of reside, customized, parameterized SQL queries. By mapping a number of dataset parameters to evaluation parameters, customers can create all kinds of experiences utilizing controls, person actions, parameterized URLs, and calculated fields, in addition to dynamic visuals’ titles and insights.

Within the following instance, dataset house owners related by way of direct question to a desk containing knowledge about taxi rides in New York. They’ll add a WHERE clause of their customized SQL to filter the dataset based mostly on the end-user’s enter of a particular pickup date that can be later offered by the dashboard readers. Within the SQL question, the rows are filtered by the date within the dataset parameter <<$pPickupDate>> if it matches the date within the pickupdate column. This fashion, the dataset dimension might be considerably smaller for customers which can be solely concerned with knowledge for a particular taxi experience date. See the next code:

SELECT *
FROM nytaxidata
WHERE pickupdate = <<$pPickupDate>>

To permit customers to offer a number of values within the parameter, you’ll be able to create a multi-value parameter (for instance, pPickupDates), and insert the parameter into an IN phrase as follows:

SELECT *
FROM nytaxidata
WHERE pickupdate in (<<$pPickupDates>>)

Use circumstances for dataset parameters

On this part, we talk about widespread use circumstances utilizing dataset parameters and their advantages.

Optimized customized SQL in direct queries

With dataset parameters, you not must trade-off between the flexibleness of utilizing customized SQL logic and the efficiency of an optimized SQL question. Parameterized datasets might be filtered to a comparatively smaller consequence set when loaded. Authors and readers can profit from the quicker load of analyses and dashboards for the primary time utilizing default values, in addition to for later queries when knowledge is sliced and diced utilizing filter controls on the dashboard. Additionally, knowledge house owners profit from their datasets placing much less load on backend database assets, making it extra scalable and performant to serve larger person concurrency.

The efficiency features can be evident while you work with direct question datasets which have advanced customized SQL, akin to nested queries that must filter the info within the inside sections of the question.

Generic datasets reusable throughout analyses

Dataset parameters can allow datasets to be largely reused throughout numerous analyses, thereby decreasing the trouble for the info house owners to arrange and preserve the datasets. Whether or not you’ve a SPICE dataset or direct question dataset, with dataset parameters, you’ll be able to port calculated subject referencing parameters from the evaluation to the dataset. Authors can now reuse calculated fields referencing parameters created by dataset house owners in a dataset, fairly than recreate these fields throughout a number of evaluation.

With the choice to port parameter-dependent calculated fields from the evaluation to the underlying datasets, dataset parameters may help you create the identical calculated fields within the dataset and reuse them throughout a number of analyses. That is vital for governance use circumstances as effectively: dataset house owners can transfer the parameter-dependent calculated fields from the evaluation to guard the enterprise logic, guaranteeing that their calculated fields can’t be modified by analyses’ authors.

Less complicated dataset upkeep with repeatable variables

When you’ve a dataset that refers to a static worth (placeholder) in a number of locations in customized SQL and calculated fields, now you can create a dataset parameter and reuse it in a number of locations. This may assist in higher code maintainability. (Notice that inserting parameters in customized SQL is barely obtainable in direct question.)

Answer overview

On this situation, we create a customized SQL direct question dataset to look at unoptimized SQL queries which can be generated with out dataset parameters, and show how your present customized SQL queries run for those who don’t use dataset parameters. Then we modify the customized SQL, add the dataset parameter, and present the optimized question generated for a similar dataset if we use dataset parameters.

On this instance, we use an Amazon RDS for PostgreSQL database. Nonetheless, this function will work with any SQL-based knowledge supply in QuickSight.

Question your knowledge with evaluation parameters

To arrange your knowledge supply, dataset, and evaluation, full the next steps. If you happen to’re utilizing actual knowledge, you’ll be able to skip to the following part.

1. Create a QuickSight knowledge supply.

The next screenshot exhibits pattern connection particulars.

2. Create a brand new direct question customized SQL dataset.

We’re utilizing pattern knowledge from NYC OpenData for New York taxi rides with a subset of roughly 1 million data. The information is loaded in an RDS for PostgreSQL database desk referred to as nytaxidata.

3. Create a pattern evaluation utilizing the dataset you simply created. Select the desk visible and add just a few columns from the Fields checklist.

4. Reload the evaluation and observe the question generated on the PostgreSQL database.

You’ll discover it masses the total dataset (choose * from nytaxidata) as referenced within the screenshot under from RDS Efficiency Perception.

5. Add an evaluation parameter-based filter management to the QuickSight evaluation. Change the worth of this filter management (evaluation parameter on this case).

The inside question over the dataset nonetheless makes use of customized SQL with out utilizing the filter within the WHERE clause. This filter management parameter remains to be a part of the WHERE clause of the outer question, so the customized SQL fetches the entire consequence set as a part of the inside question. This is probably not the case for those who use database tables as a dataset fairly than a customized SQL question as a dataset. With a dataset based mostly straight on tables, parameter values are handed to the database within the WHERE clause.

So how will we overcome the problem of having the ability to embody the parameter within the WHERE clause in customized SQL datasets? With dataset parameters!

Optimize your question with dataset parameters

Let’s have a look at just a few eventualities the place we are able to use dataset parameters to ship extra optimized queries to the database.

1. Create a dataset parameter (for instance, pDSfareamount) and add it to the WHERE clause with an equality predicate within the customized SQL.Observe if there’s any change within the SQL question that was handed to the database.

This time, you will notice optimized SQL generated utilizing the default parameter worth within the WHERE clause of the inside question (choose * from nytaxidata the place fare_amount=0). This leads to higher question efficiency for direct question datasets.

Map dataset parameters with evaluation parameters

Dataset parameters might be mapped to evaluation parameters and user-selected values can move to the dataset parameters from the interactions on the dashboard at run time.

You should use a single evaluation parameter and map it to a number of dataset parameters. The mother or father evaluation parameter can now be linked with a filter management or an motion, and may help you filter a number of datasets based mostly on customized SQL.

On this part, we map a dataset parameter with an evaluation parameter and bind it with a filter management at runtime.

1. First, we create an evaluation parameter and map it to a dataset parameter (we use the dataset parameter we created earlier).

2. Now the evaluation parameter (for this instance, pAfareamount) is created. You may create the management object Fare Quantity to dynamically change the dataset parameter worth from the evaluation or dashboard utilizing a parameter management. You may bind pAfareamount with a QuickSight filter to move values to the dataset parameter dynamically. While you’re altering values in a parameter management, you’ll find optimized SQL on the backend database with the WHERE predicate in inside question generated.

Further examples utilizing dataset parameters

Up to now, now we have used dataset parameters with an equality predicate.Let’s have a look at just a few extra eventualities utilizing dataset parameters.

1. The next screenshot demonstrates utilizing a dataset parameter with a variety predicate of customized SQL.

2. The next instance illustrates utilizing two dataset parameters with a between operator.

3. The next instance exhibits utilizing a dataset parameter inside a calculation.

4. We are able to additionally use a dataset parameter with a scalar user-defined perform (UDF). Within the following instance, now we have a scalar perform is_holiday(pickupdate), which takes a pickupdate as a parameter and returns a flag of 0 or 1 based mostly on whether or not pickupdate is a public vacation.

5. Moreover, we are able to use a dataset parameter to derive a calculated subject. Within the following instance, we have to calculate the surcharge_amount dynamically based mostly on a price specified at runtime and the variety of passengers. We use a dataset parameter together with a case assertion to calculate the specified surcharge_amount.

6. The ultimate instance illustrates the right way to transfer calculations utilizing parameters within the evaluation to the dataset for reusability.

Dataset parameter limitations

The next are the recognized limitations (as of this writing) that you could be encounter when working with dataset parameters in QuickSight:

• Dataset parameters can’t be inserted into customized SQL of datasets saved in SPICE.
• Dynamic defaults can solely be configured on the evaluation web page of the evaluation that’s utilizing the dataset. You may’t configure a dynamic default on the dataset stage.
• The Choose all choice isn’t supported on multi-value controls of research parameters which can be mapped to dataset parameters (however there’s a workaround that you may observe).
• Cascading controls aren’t supported for dataset parameters.
• Dataset parameters can solely be utilized by dataset filters when the dataset is utilizing a direct question.
• When dashboard readers schedule emailed stories, chosen controls don’t propagate to the dataset parameters which can be included within the report that’s connected to the e-mail. As an alternative, the default values of the parameters are used.

Discuss with Utilizing dataset parameters in Amazon QuickSight for extra data.

Conclusion

On this put up, we confirmed you the right way to create QuickSight dataset parameters and map them to evaluation parameters. Dataset parameters assist enhance your QuickSight dashboard efficiency for direct question customized SQL datasets by producing optimized SQL queries. We additionally confirmed just a few examples of the right way to use dataset parameters in SQL vary predicates, calculated fields, scalar UDFs, and case statements.

Dataset parameters allow dataset house owners to centrally create and govern parameter-dependent calculated fields on the dataset stage. Such calculated fields might be reused throughout a number of analyses, and can’t be tampered with by evaluation authors.

We hope you’ll find dataset parameters in QuickSight helpful. Now we have already seen how the function is creatively utilized in a variety of use circumstances. We advocate that you just evaluation your present direct question customized SQL datasets in your QuickSight deployment to search for candidates for optimization, or make the most of the opposite advantages of dataset parameters. For instance, BI groups can profit from dataset parameters by reusing the identical dataset with completely different values within the parameter to research completely different slices of the identical knowledge, akin to completely different areas, merchandise, or prospects by business segments.

Are you contemplating migrating legacy stories to QuickSight? Dataset parameters may help enterprise BI builders cut back the migration effort of legacy stories that have already got parameterized SQL queries within the legacy queries. These SQL queries might be handed alongside their parameters to QuickSight datasets by way of automations with the assistance of QuickSight APIs (and some changes to the queries if the parameters are marked otherwise).

For extra data on dataset parameters, confer with Utilizing dataset parameters in Amazon QuickSight.

Concerning the authors

Anwar Ali is a Specialist Options Architect for Amazon QuickSight. Anwar has over 18 years of expertise implementing enterprise enterprise intelligence (BI), knowledge analytics and database options . He makes a speciality of integration of BI options with enterprise functions, serving to prospects in BI structure design patterns and greatest practices.

Salim Khan is a Specialist Options Architect for Amazon QuickSight. Salim has over 16 years of expertise implementing enterprise enterprise intelligence (BI) options. Previous to AWS, Salim labored as a BI advisor catering to business verticals like Automotive, Healthcare, Leisure, Shopper, Publishing and Monetary Companies. He has delivered enterprise intelligence, knowledge warehousing, knowledge integration and grasp knowledge administration options throughout enterprises.

Gil Raviv is a Principal Product Supervisor for Amazon QuickSight, AWS’ cloud-native, totally managed SaaS BI service. As a thought-leader in BI, Gil accelerated the expansion of worldwide BI practices at AWS and Avanade, and has guided Fortune 1000 enterprises of their Knowledge & AI journey. As a passionate evangelist, writer and blogger of low-code/no-code knowledge prep and analytic instruments, Gil was awarded 5 occasions as a Microsoft MVP (Most Invaluable Skilled).