Generate a new data with forecasts in an ad hoc analysis environment

Developing an Ad Hoc Analysis Environment

Any time a bureaucrat (i.e., a custodian of a system) stands between you and something you need or want, your challenge is to help that bureaucrat discover a means, harmonious with the systems, to meet your need.

– GORDON MACKENZIE

Ad hoc analytics is the discipline of analyzing data on an as-needed or requested basis. Historically challenging, ad hoc analytics on big data sets versus relational databases adds a new layer of complexity due to increased data volumes, faster data velocity, greater data variety and more sophisticated data models.

The Tableau core design encourages discovery. In connecting to your data post, you learned that Tableau connects to a wide variety of data sources, and this is further extended through data blending from external sources. Building your first visualization post introduced the Show Me button; trend lines; reference lines; and how filters, sets, grouping, and hierarchies can be used to present information meaningfully — for facts and dimensions that are included in views.

Interested in mastering Tableau? Enrol now for a FREE demo on TableauTraining in Bangalore.

Webster’s Dictionary defines ad hoc as follows: Concerned with a particular end or purpose, formed or used for specific or immediate problems or needs, fashioned from whatever is immediately available.
In this post you’ll explore three ways through which Tableau facilitates ad hoc analysis:

• Generating new data with forecasts
• Designing flexible views using parameters
• Changing or creating designs in Tableau Server

Desktop users can create forecasts when viewing time series data, and also get feedback from Tableau on the quality and type of forecasts Tableau generates. Desktop report designers can also build flexibility into views and dashboards using variables called parameters. Parameters allow information consumers to alter views within limits defined by the designer. Most significantly, even staff with Tableau Server licenses can build completely new views or alter existing designs without the need for Tableau Desktop.

Related Page: How To Build Your First Advanced Dashboard In Tableau?

Generating New Data with Forecasts

Tableau Desktop users can create forecasts with a couple of mouse clicks. The resulting figures can be exported, revised, and possibly added to your datasource — providing a fast and easy way to model the future based on past history.

Checkout Tableau Interview Questions

How Tableau Creates Forecasts

Forecasting requires a view that uses at least one date dimension and one measure. For example:

• The field you want to forecast is on the Rows shelf and a continuous date field is on the Columns shelf.
• The field you want to forecast is on the Columns shelf and a continuous date field is on the Rows shelf.
• The field you want to forecast on either the Rows or Columns shelf, and discrete dates are on either the Rows or Columns shelf. At least one of the included date levels must be Year.
• The field you want to forecast is on the Marks card, and a continuous date or discrete date set is in Rows, Columns or Marks.

You can forecast quantitative time-series data using exponential smoothing models in Tableau Desktop. With exponential smoothing, recent observations are given relatively more weight than older observations. These models capture the evolving trend or seasonality of your data and extrapolate them into the future. Forecasting is fully automatic, yet configurable. Many forecast results can become fields in your visualizations.
When a forecast is showing, future values of the measure are shown next to the actual values.
Tableau generates forecasted values by using time-series data that is included in your worksheet. Figure 6.1 shows a time series chart that includes forecasted values.

Figure 6.1: Time series with forecast

Forecasted values are presented in a lighter colour than the actual values. The forecast values in figure 6.1  can be added by right-clicking within the worksheet and selecting the forecast/show forecast menu option. You can also generate the forecast from the analysis menu/forecast option.

Figure 6.2: The forecast option menu

Learn Tableau Server Tutorial

Forecasting options

Tableau can forecast data in a variety of ways and will automatically select what it feels is the best method. If you don’t want to accept the default, edit the forecast model by right-clicking on the worksheet and selecting the forecast model by right-clicking on the worksheet and selecting the forecast options menu. Tableau provides the following forecast trend models:

• Trend and season
• Trend only
• Season-only
• Neither trend nor season

Depending on the amount and granularity of the historical data, each option will generate different results. Trend and the season will generate the most volatile forecast data. The forecast options menu includes several other variables that can be adjusted. You can see the forecast options menu in Figure 6.2.

At the top of Figure 6.2 you can see that by default, Tableau will generate a 12-month forecast, but it is possible to forecast a specific number of periods into the future. The number of periods that Tableau forecasts are dependent on the date range in your view and the data aggregation level presented. The Ignore Last setting allows you to omit incomplete historical data so that it won’t skew the forecast results. Checking the Fill In Missing Values With Zeroes box will prevent null values from corrupting the forecast.

Review and Present Forecast Quality Metrics

You can examine the quality of the forecast that Tableau generates by right-clicking within the workspace and selecting the forecast/ describe forecast option. Figure 6.3 shows the summary tab.
The summary tab provides details regarding the precision and quality of the forecasted values. You can choose to express the forecast precision as a number or percentage ranges. Forecast quality is described as poor, OK, or better. Clicking on the Models tab exposes more detailed quality metrics that you see in Figure 6.4.

Figure 6.3: Describe forecast summary

Figure 6.4: Describe forecast model

Tableau’s forecasting model weighs recent history more heavily. The statistical models for the different quality metrics presented in figure 6.4 are defined in detail in the tableau desktop manual. The smoothing coefficients for alpha (level smoothing), beta (trend smoothing) and gamma (seasonal smoothing) refer to the amount of smoothing  applied. Values closer to one are smoothed less than the lower values. If the values are very close to zero a lot of smoothing was performed.

[Related Page: Quality Metrics In A Hospital In Tableau]

Adding quality metrics to tooltips in visualizations

By dragging and dropping the forecasted measure from the measures shelf in the data window to the marks card detail window, it allows you to modify the information contained in the tooltip to include quality and precision metrics. Figure 6.5 shows the placement.

Once sales are dropped into the detail button (figure 6.5), you can modify the value presented by right-clicking on the pill in the marks card and making a selection as you see in figure 6.6.

Figure 6.5: Adding sales to detail button

Figure 6.6: Enabling quality metrics

In figure 6.6 you see the selection options for adding quality and precision metrics so that they become available for tooltips in the chart, each individual selection desired in the view will require another sales measure to be dropped on the details button. In this example, two metrics were added-quality and precision. Figure 6.7 shows the resulting tooltip.

Figure 6.7: Tooltip with quality and precision metrics

                          
This tooltip is exposed to users when they hover their mouse over the mark. Figure 6.7 shows that a quality of sales forecast metric and a precision of sales forecast metric has been added to the tooltip. The quality metric range is from zero to 100 (higher number means better quality). The precision metric is expressed as a value range and provides the 95 percent prediction interval for the forecast – a measure of the potential volatility of the forecast value. In this example, the value range refers to a sales dollar range of 49,611.

Exporting forecasts

Exporting tableau-generated forecasts can be a time-saver for developing more nuanced forecasts. One way to accomplish this might be to duplicate the original view in figure 6.1 as a crosstab, then export the view using the menu option for worksheet/export/crosstab to excel. Figure 6.8 shows the resulting spreadsheet values.

Figure 6.8: Exported forecast in a spreadsheet

Alternatively, it is also possible to go to the menu option worksheet/export/data to access database, using either method allows you to adjust the forecast values more specifically and perhaps add those altered figures into your main database in their own field tableau’s forecasting model. It is intended to replace sophisticated statistical forecasting tools. It provides an easy-to-use way to create forecasts along with quality and precision metrics to access the quality and precision of the resulting estimates.

Creating forecasts in the views that you publish to tableau server and share with others is one way to stimulate an ad hoc analysis environment for users that only have access to the view via Tableau Server. In the next section, you’ll learn how to create parameter controls that enable Tableau Server users to change measures and dimensions in views or dashboards.