Creating joins with the Query Blender

Follow

The Query Blender is a tool for joining data from existing connections in your library. All joins created in the Query Blender are pushed to the database to maximize performance. It is good for handling high-level joins on data from different places or attributes with a low number of values.

Note: If you want to join tables from the same relational database, the Query Blender is not the best tool.

Creating joins in Query Blender

All connections that you want to blend must have a join key, or common data. This can be an original element or a calculated attribute. The Query Blender will look for an exact match on the join key.

To create joins in the Query Blender

  1. In the Query Builder, click the Query Blender icon () on the right panel.
  2. Select one or more connections from the Select a data source drop-down list. You can view the attributes in a connection by clicking the arrow underneath the connection name.
  3. Click and drag from one half-circle on the sides of one table box to a half-circle on the sides of another box. You will automatically be redirected to the Join definition window.

  4. Select a field for each table, then click Add condition. For more information on join conditions, see Creating join conditions.
  5. After you add a condition, you can select a join type. There are three different join types (see Selecting a join type).

  6. When you are finished click Okay.
  7. BIME will check to confirm the join is correct, then redirect you back to the Query Blender.
  8. Click Save when you are finished.

Building blended queries

After your join is established, you can access the metrics and attributes from all your joined connections and create simple cross-calculations. Cross-calculations are created in the same way as standard calculated metrics.

When you add a new metric or attribute to the frame you can switch connections by clicking on the arrows at the top of the menu, or you can select a connection by clicking on the current connection to open a drop-down list.

Note: Only attributes included in the join should be added. If an attribute is not included in the join, BIME does not know where to put the results, so they will be shown as undefined. This will only affect the result from the connection where the attribute comes from.

Technical considerations

If there are multiple attributes in the join, the number of potential combinations of elements will increase. For example, if you join two sets of full-year data around the attributes Date and Category, and there are three values in Category, the number of join elements could be as high as 1095 (365 dates x 3 categories).

As as general rule, if the number of join elements exceeds 20,000, please use another approach such as Managed Storage or simplifying the join. For example, you can simplify the above join by changing the attribute Date to Month. This would limit the number of join elements to 36.

Have more questions? Submit a request

3 Comments

  • 0

    When you add several conditions to the query blender, a drop-down menu appears on the right side between every condition. It is only displaying "and". Is it supposed to display "or" as well ? If yes, I couldn't find the configuration to make "or" appear.

  • 0

    Hi Thibault, 

    That is correct, only the "and" option is available for the Query blender. However, If the connections you are trying to join are tables within the same database, you can use the "Or" operator in the SQL Designer. 

    Have a good day, 

    Jessica

  • 0

    We are using Managed Storage.  Rather than Query Blender between two Salesforce tables we have imported, what is the recommended action?

Please sign in to leave a comment.
Powered by Zendesk