After you import your data, you can begin creating reports, or queries. BIME uses the term queries to describe any reports or graphs you create. Once you click Save in the Data source Builder, you will automatically be redirected to the Query Builder. The Query Builder is BIME's interactive tool for building and customizing queries.
Queries can provide answers to inquiries or detail information about certain areas of interest. For example, queries can answer questions such as what products produce the highest profit, the trends of sales among each quarter, or how results compare to an overall average.
The first step in creating a query is adding elements. Recall from the previous lesson that BIME assigns elements as metrics or attributes. Metrics represent all quantitative results, while attributes slice your results by non-quantitative values such as dates or locations.
There are five places to add metrics and attributes in the Query Builder. Metrics can be added to Metrics, and attributes can be added to Columns, Rows, Explosions, and Filters. Your results are sliced differently depending on where you place your attributes (see Adding attributes).
Every report must contain at least one metric. You can add metrics in different sizes, colors, on a dual axis, to a trend line, or include them in datatips (see Adding metrics). BIME automatically calculates the summation of your results (SUM), but you can also view the average, minimum, maximum, and several others (see Changing the metric aggregator).
All attributes are composed of values. You can think of attributes as the categories of your data and values as the sub-categories. For example the countries England, Austria, and Germany would be values of your Country attribute. Depending on where you place your attribute, the values will be displayed different.
The four attribute locations will display your values as follows:
- Explosions break your results into individual charts for each value.
- Filters exclude results based on the values you select.
- Rows enable you to navigate through the results for each value.
- Columns slice your results normally.
Try it yourself: Create a new query
If you have already added a connection, you can use the steps below to create a new query. For example, the query below will analyze what year earned the highest profit.
- In Metrics, click the + button.
- Select the metric you would like to add to your query from the drop-down list. Your results will not be displayed unless you select a metric. This example selects Profit.
- In one of the attribute locations, click the + button, then select an attribute from the drop-down list. The example below places the Year (Ship Date) attribute on Columns to create a simple report.
- Your results will now be displayed in BIME's Auto visualization. For information on customizing your query and selecting a new chart type, see Customizing queries.
- Click the Save button () to save your query. Select Save as new in the Library to remain in the Query Builder. For information on other saving options, see Finishing your query.
After you add elements, you can begin to customize your query. Customizing your query enables you to represent your data in a way that is most impactful to you and your viewers.
In the Query Builder, you can find the available query options on the right panel. This section only discusses the customization menus. You can find information on the calculation menus in Adding calculations and the Query Blender in Creating joins with the Query Blender.
There are three global customization menus available. Some connection types might have additional query menus available.
The three menus are:
- Visualization type menu: In the Visualization type menu, you can select a new chart type to represent your data. When you first add data, BIME automatically selects the most natural chart type for your results; however, you might want to represent your data in a different way. You can use the Visualization type menu to select from 25 different chart types. For information on the different chart types, see Visualization types reference.
Data structure menu (): In the Data structure menu, you can rename elements, organize and create folders, and set attribute security permissions. The Data structure menu is useful if you want to continue to edit your data elements after they are imported. For information on restricting attributes to specific users, see Setting attribute permissions. If you are using a geolocation chart, you can set your values geographic locations (see Geocoding values).
Chart configuration menu (): The Chart configuration menu contains the majority of customization options for creating unique queries. Different chart types contain different chart customization options. For all charts, you can edit color, text size, legend, datatips, the Visualization Selector, and interactivity options. You can also edit your result type (numeric, currency, percentage, duration) in Display format.
Try it yourself: Customize your query
- Click the Auto chart type icon () to open the Visualization type menu.
- Select a new chart type. The example below uses a line chart. The Visualization type icon should now reflect your selected chart. Click the icon again to close the menu.
- Click the Chart configuration icon ().
- Select the Colors option.
- Underneath Automatic color, click the color of your current metric or Apply predefined palette. The predefined palettes contain BIME's suggested color groups.
- Select your new color, then click Select.
- Click the Chart configuration icon again to close the menu.
- Your query will be updated with your new color.
- Rename your query by typing a new name into the Query Name text box at the top of the Query Builder.
- Click the Save button () to save your query. If you have already saved your query, you can select Update Query.
If you are looking for a more advanced analysis, you can perform custom calculations on your elements and query results. These calculations can help you analyze running totals, percentage differences, forecasts, and overall benchmarks.
There are two calculation menus available:
- Result manipulation menu: In the Result manipulation menu (), you can perform calculations on processed results. All result manipulations you add will appear on the Filters bar. Result manipulations do not require you to type any formulas, but you can only perform calculations on the elements currently added to your query. For more information on the different result manipulations, Result manipulation reference.
- Calculation engine menu: In the Calculation engine menu (), you can create custom calculated metrics and attributes to add to your query or to use in other calculations. To create calculated elements, you must manually type formulas in BIME's calculation language (see Formula writing rules). For more information on Calculation engine menu options, see Calculation engine reference.
Try it yourself: Create a calculation
If you would like to create a basic calculation, you can follow the steps below to calculate the totals of your results. Before you add this calculation, please add both metrics and attributes to your frame.
- Click the Result manipulation icon () .
- Select the Totals option.
- Select the location for your totals. Your totals' location should correspond with your attributes' location. The example below contains an attribute on Columns, so totals are calculated on Columns as well.
- Select the location for any subtotals you want to add.
- Underneath Advanced options, you can select to display only the totals for the sum of your results, calculate all subtotals for every value, or inverse the totals position. Inversing your totals position will place your totals column or row as the first result in your report.
- Click Apply.
- A new totals column or row will appear, and the totals filter will be added to your Filters bar.
Finishing your query
Before you can move on to creating your first dashboard, you must save your query. BIME will not save your query automatically. The save button is located at the top of the Query Builder ().
- Save as new to dashboard adds your query to a new or existing dashboard. If you select this option, you will be redirected to the Dashboard Builder. For information on creating a dashboard, see Creating dashboards.
- Save as new in the Library saves your query to the Queries library (). The Queries library stores all saved queries, even if saved to your dashboard. You can share and organize queries in the Queries library (see Sharing queries from the Queries library and Organizing dashboards and queries into folders).
- Export provides a CSV, image, PDF, or Excel file of your query. Exporting your query will not save it to the Queries library.
- Update query appears if you are editing a pre-existing query. You can use Update query to save your changes in all locations.