Blending Data

How to:

Blending data is the process of gathering data from various sources and combining the useful data in to a customized, functioning data set. For example, you may have some of the basic fields available in the current data source. You can use the Blend option to add relevant data fields from a different data source to the current data source in order to create a unique data set.

The Blend functionality allows you to explicitly select the data fields that you want to include. More specifically, you can join multi-fact data structures and combine pertinent external data into your current data source, creating a blended data resource. This can be from local or other system resources.

You may want to blend data if you are reporting from two different fact tables that share a common dimension, such as a product dimension. The Blend option allows a new external fact table, for example an uploaded Excel worksheet, to be added to a multi-segment master as a parent of an existing child segment.

For an example of this, you can view the sample retail database, wf_retail. The wf_retail Master File contains a fact table, wf_retail_sales, and a dimension table, wf_retail_store_sales. The wf_retail_sales segment is defined as the parent of the wf_retail_store_sales segment. You may want to add a second fact table to the report. The second fact table in this example can be an Excel spreadsheet that you upload to WebFOCUS App Studio for use with a report or chart. The data in the spreadsheet file that you upload will be joined to the wf_retail database using a common dimension field.

The following are some general rules that apply to the Blend option:

Procedure: How to Blend Data Using the Join Tab

This procedure describes how to blend data from two existing data sources. It uses the wf_retail Master File and the product_category_sales spreadsheet.

  1. Create a new procedure or open an existing procedure.
  2. In the Procedure View panel, right-click the procedure folder or the component that you want to create the Join after, point to New, and click Join.
  3. Select the wf_retail data source from the Select a Data Source dialog box and click OK.

    The Join canvas opens and displays the fields in the Master File.

  4. Select the Join tab on the ribbon and in the Join group, click Add.

    You can also right-click in the Join canvas and select Add File to add the second part of the join.

  5. Select the product_category_sales spreadsheet from the Select a Data Source dialog box and click OK.

    The new file is added to the canvas. Common fields in both files are linked by a Join connector line, as shown in the following image.

    Sample of two files joined for a blend.
  6. To blend the data in the join, select the linked field in each file list, and click the Join connector line.
  7. In the Join tab on the ribbon, click the Blend option.
  8. Select Inner (default) or Full Outer as the Blend type.
  9. Click Run.

    A dialog box opens, displaying the component code, and either an error message or text stating that no error exists.

Example: Blending Data Using WebFOCUS Syntax

You can blend data using the following JOIN AS_ROOT command, which you can type into a procedure using the Text Editor. In this example, the Projected Excel worksheet is added as a parent of the wf_retail Product dimension. The report output contains field values from the wf_retail_sales fact table and the Projected Excel worksheet for each product category. 

JOIN AS_ROOT PRODUCT_CATEGORY IN WF_RETAIL TO UNIQUE PRODUCT_CATEGORY IN PROJECTED
END
TABLE FILE wf_retail
SUM REVENUE_US
PROJECTED_COG
PROJECTED_SALE_UNITS
BY PRODUCT_CATEGORY
END

Note: The AS_ROOT command is only supported with JOIN syntax as shown above and is not supported for conditional JOIN syntax.

The output of the above syntax is shown below: