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.
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
- In the Query Builder, click the Query Blender icon () on the right panel.
- 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.
- 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
- Select a field for each table, then click Add condition. For more information on join conditions, see Creating join conditions.
- After you add a condition, you can select a join type. There are three different join
types (see Selecting a join type).
- When you are finished click Okay.
- BIME will check to confirm the join is correct, then redirect you back to the Query Blender.
- 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.
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.