In our example, if you ask for SUM(Orders) by Product, Bime gets the Category for each Product and sums the Orders values for each category
NB : If you specify the same column on each connection and these columns store quantitative data, they will be merged. If they are qualitative data, they will be renamed by adding the connection name in the attribute name.
3-Analysis
a-Principles
1-Quick overview
Between the connection builder and the library is the heart of the analytical function of Bime: the Pivot Table illustrated in Figure 1.
Fig. 1. The Pivot Table
When a connection is loaded, all the data it contains is available to you on the pivot table. Loading data into the pivot table and creating a visualization is a 'query' - the result of interrogating your data. To do this, you simply drag and drop measures and attributes onto the layout. The table will tell you if you can't put it there!
At first, this will display data in the classical grid view, so you can see that you have included all the relevant data. This view can then be changed using the drop-down menu to change 'grid' into a different form (see section 3b for help with deciding what visualization would be best for a particular dataset).
As you change visualizations you can add, move, or remove measures or attributes at any time.
Using the 'options' box available with some visualizations (not in the grid view), you can 'weight' your data so that the representation includes a combination of measures (for example profit
and turnover) in same result by resizing or recoloring them (see
3a12).
You can fine-tune your analysis further still by creating calculated measures and attributes (see section 3c), allowing you to drag and drop the results of complex calculations as a single item.
To the right of the table are your 'post-processing' options, which include rendering your results as percentages, filtering to focus on the best and worst-performing areas, or setting floors or ceillings (see 3a6).
The 'totals' (see 3a5) and 'sort' (3a4) options are further tools to alter and improve the display of your results.
The 'Auto Query' button showing a green spot means that the visualization will automatically update itself as you drag and drop items onto the frame. This can be very helpful in showing immediate changes as new attributes or measures are factored in, but if you prefer to put all the data onto the frame and then see your visualization only when you are finished, click on the AutoQuery and it will show a red spot - a sort of Bime pause button - until you click it again to see your visualization finished (or, ready for more data to be added to it!).
The 'save' button shows you your options for saving a finished query.
You can remove individual measures or attributes from the frame to the dustbin, and the 'clean' button wipes the slate clean of all measures and attributes on the frame so you can start again!
2-Querying and the 4 main axes
The four main axes of analysis are
-
measure
-
column
-
row
-
filter
Measures are all quantifiable values - sales, costs, profit, unit price, quantities, etc. By default, the sum of the chosen measure will be shown, but this can be altered (see 3a3). In addition to the measures drawn from your data source, there is also a dynamic count added to all non-OLAP datasets, and you can add calculated measures as well (see 3c).
Attributes are used to form the rows and/or columns. You can also apply a filter, to concentrate on or exclude a particular region, or product line, or department.
For example, to look at profits over time:
You can also add a filter by dropping 'Sales Channel' onto the frame, and then choosing which channel you want to use to filter the whole dataset.
In this example, the data grid displays only data for the Priv.Owned E-shop.
3-Changing measure aggregation
The default when you drop a measure onto the frame is that it will be the sum of that measure. Once on the frame, however, a drop-down menu allows you to choose which aggregation you need.
The options available to you are:
SUM - the total of all the values of the chosen measure
AVG - the average of all the values of the chosen measure
COUNT - the number of values of the chosen measure (the number of lines on a spreadsheet, for example)
D_COUNT - the number of distinct values of the chosen measure
MAX - the maximum value of the chosen measure
MIN - the minimum value of the chosen measure
SUMX - the slightly tricky one - with a measure drawn from your original data source, SUMX acts just like SUM. However, when you are looking at a calculated measure (see 3c), SUMX makes sure that the right sum gets done. Using SUM on a calculated measure gives a global result - for example if your calculated measure is SUM(Order Quantity)*SUM(Unit Price), the 'simple' SUM would multiply the total number of units ordered by the total of all the unit prices = not very helpful. To see what actual turnover is, you need SUMX - this gives you the total based on line-by-line calculations, i.e. for each order, it calculates quantity * unit price = turnover, and then aggregates those line-by-line turnover figures to give you a result for total turnover.
4-Sorting
Once a visualization has been created, for example in a bar chart, the results will be displayed in the original order of the defining attribute, often alphabetical. The visualization can be sorted big-to-small or vice versa using the 'sort' button above the pivot table.
5-Totals and sub totals
Using default totals & sub totals
You can obtain standard sub and/or grand totals on rows and/or on columns simply by selecting the check boxes in the “Totals” toolbox illustrated in Figure 1. This can be done in any visualization, but a data grid is easiest for our example.
Fig. 5.1 – The Totals toolbox.
⇒ Use visual totals for non-additive aggregators on calculated measures
Totals cannot be computed correctly when the aggregator on a calculated measure is non-additive, such as AVG (average), COUNT or D_COUNT (distinct count). In this case, an information message is displayed and these totals are left blank in the result, as illustrated in Figure 2.
Fig. 2 – Totals are left blank (blue shapes) when aggregator is non-additive
If you want values for these non-additive totals, you can toggle the visual totals check box, computed as the sum of the displayed values, illustrated in Figure 3. However, keep in mind that these visual totals do not match the specified aggregator, e.g. are not averages in this case.
Fig. 3 – Visual totals (blue shapes) for a non-additive aggregator on a calculated measure.
⇒ Use visual totals for unifing values of calculated measures
Sometimes, the definition of a calculated measure (i.e. the associated calculated formula) can cause counterintuitive values for totals. For example, let West Shipping Cost or One be a calculated attribute defined as follows:
IF ([Region] = “West”) THEN SUM(Shipping Cost) ELSE 1 ENDIF
The semantic of this calculated measure is: if the value of the region attribute is equal to “West” then it returns the SUM(Shipping Cost) measure’s value, otherwise it returns 1. As illustrated in Figure 4, several computed values for totals are counterintuitive, such as the total of the line highlighted in blue. The expected value of this line would be 3 (i.e. the arithmetic sum of the line). However, since the value of the region attribute is not equal to “West”, the return value of the calculated measure is 1 for the total of the line.
Fig. 4 – A calculated measure’s definition can cause counterintuitive values for totals.
So, if you want to obtain the intuitive values for totals, you can toggle visual-totals, as illustrated in Figure 5.
Fig. 5 – Visual totals for intuitive values on a calculated measure.
6- Post processing: order of application
While Bime gives you the opportunity to create calculated measures that can be used as factors in new queries (see section
3c), you do not have to create your own formulas to perform calculations on your chosen measures. The post-processing menu on the right hand side of the pivot table provides you with a number of options to filter or change how the chosen measure is displayed.
These post-processing options can work simultaneously, so can be applied in combination - remember that switching from one to another will keep your initial filter, so if you want to go back to the beginning, you need to reset the filters to 'none'.
If you are using a combination of filters, to ensure that they are applied in the right order, click on the '123' button, which allows you to change the order of application by dragging and dropping them as you wish. The 'sort' function (see section 3a4) can also be applied to post-processed displays.
7-Post processing: Top and Bottom
The 'TOP' filter allows you to break down a long list to concentrate on the highest (or lowest) values. Just checking 'Top' with give you the maximum value, but you can choose to show any range at either the higher or lower end, or both. Thus, you can target your weakest areas and compare them with your strongest, to see what is going on, and develop new strategies to improve performance.
The filter will automatically choose the most obvious way to apply itself, but if you have a chart rather than a list, you can apply it as you wish, whether across columns or rows. The dropdown list also allows you to choose which of several measures that might be displayed on a chart you wish to filter, just select the measure you want.
If you want to be able to use the top or bottom of your data range and perform further queries on it, it might be helpful to create a filtered attribute, which would mean that you had that top or bottom range permanently available in the connection, without having to post-process (see section
3c6).
8-Post processing: Value filters
The 'eye' is the default for the post-processing menu. This draws the measures from the pivot table and allows you another option to focus on specific ranges within the measure range, but based on value rather than the number of results. With this, you can ignore 'one-offs' or unusually large orders, and focus on any range of your activities. Just drag the triangles on the appropriate measure line to the value you want to use as your start- or end-point, and 'zoom in' on the range that interests you.
9-Post processing: Part to Whole
The '%' filter allows automatic calculation of percentages without having to input a formula and create a calculated measure. Again, the filter will automatically choose the most obvious basis of application, but you can specify which axis you wish to convert.
In terms of order of application, the ability to combine filters can give you added functionality; you can look at your top 20 performing areas, and then apply part-to-whole to see the proportion of your business they make up.
10-Post processing: Running calculations
If you need to look at results on a cumulative basis, this is possible for date attributes by picking 'Running Total' from the dropdown menu on the tab for that attribute (see section 3a13). At its simplest, in the grid view, this changes your figures from those relevant to the stated period (left) to cumulative figures over the whole period shown (right), on a row-by-row basis, but it is available in all visualizations if required.
How to trigger Running Calculations
Without running calculation
With running calculation
Breaks are automatically managed when you drill down in the time dimension:
11-Access special capabilities of visualizations
Right-clicking on the field of your visualization brings up a menu of options to help you to add extra elements to it.
Add comment - available on every visualization, allowing you to add text to the display (see section 3a14)
Edit colors - available wherever colors are used! Re-order the given colors or choose your own by clicking on the color square and selecting from the range shown.
Show value - on line, bar, column, area and pie charts, you have the option to show your results purely visually, or with labels for values attached
Show zoomchart - on line, bar, column and area charts, you have the option to 'zoom in' on a particular area of the results (see section 3a17 for more details).
Clustered > stacked - on column and bar charts, where several attributes are shown, you have the option to show these separately (clustered) or as a single result (stacked). Just click on this to change from one visualization to the other, as shown below.
Segment > Curve - on line and area charts, you can opt for straight lines between data points (segment) or for the display to 'round off' into a curve.
Linear axis > log axis - on line and area charts, you have the option to change the usual linear axis to a log axis, which allows you to display two ranges of data far apart in numeric terms as close physically, so both can be examined on the same graphic. This is not meant for use when you need to compare the two ranges against each other, rather to see variations or trends in related but separate data. In the examples below showing cost components, the chart on the left uses a linear axis, and you can barely see the smaller value; the chart on the right uses the log axis, and in this both cost components can be seen easily, enabling one to see that the lower value has stayed very stable over time, while it is the higher that is subject to fluctuations. Thus, the values themselves represented by each color are not compared, but the trends represented can be.
No base at zero - in area and line charts, it is possible to alter the y-axis to start somewhere other than at zero. If chosen, the chart will automatically resize to start just below the lowest value, thus making variations more pronounced and enabling a more detailed look at a number of datasets that are all numerically close together. Note that this option can only apply if all values on the display are positive, and is not available if you have switched to a log axis view.
Standard > explode - an option for pie charts, which changes the traditional view to show white space between the segments.
Callout > inside with callout - a further option for pie charts when you have chosen to 'show value', so these are placed either outside or inside the pie.
A 'standard' pie with values as 'callouts' An 'exploded' pie with values shown 'inside'
Transparency 100 / 80 / 50 - on area and bubble charts, where different data ranges may overlap, you can alter the transparency of the display so that you can 'see through' to each range. The current setting for strength of color will be shown on the menu in square brackets, i.e. [100] - transparency is increased by reducing the strength of color. In the following examples of an area chart, you can see how two previous hidden data ranges emerge as transparency is increased.
The final option available is on heatmaps, where right-clicking allows you to hide the control menu.
12-Encoding in color, size and shape
If you have more than one measure to reflect in a single graphic, this can be difficult to achieve with traditional visualizations. Bime offers you three interesting visual ways to distinguish between different data sets on a chart, or to 'weight' your results to display two or three measures simultaneously. The available options for each type of chart are displayed automatically.
Drop the first measure onto the frame and a bar chart will display that in a single color. Your options here are to change the color, or the size, of the bars. Drop the second relevant measure onto one of these options and your chart is transformed to show both of the aspects you want to know about.
Changing the shape of data points is enabled on bubble-charts, so you can easily differentiate different datasets being shown on the same chart.
4 styles of color coding can now be applied on top of your data.
Default Style 1 is a gradient style that will show the value of the result on a sliding color scale across the dataset. The color coding below shows you how customer states perform against each other in terms of shipping cost.
To switch to a different color style, you just have to right click on the color legend of the chart.
Style 2 is not a gradient style. You specify a value above which the color will be red. Everything below this value will be green. In the example below, you see all the customer states that have shipping costs above $3000 in red.
Style 3 is a divergence gradient style. You see a strong red when the value goes far above the median and a strong green when the value goes far below the median of the result. Anything close to the median is shown by a sliding scale of grey.
Style 4 is a divergence non gradient style. All the states in grey are as close to the median as you want, and all the states in red and green are at the extremes of the dataset.
13-Time elements
Date attributes, which must be actual dates, are automatically formed into hierarchies by Year, Month and Day. You will start with 'Year', which you can then break down to the next step in the hierarchy simply by clicking on the '+' button on that tab, and you can 'close' a lower level and return to 'Year' by clicking on the '-' button on the one you want to close.
The drop-down menu from each date tab allows you to select specific years, months etc. to focus on a particular period.
In addition, you have ability to 'change time level' to show different periods from those automatically assigned, including half-years, quarters, weekdays, even hours, minutes and seconds!
The running total function is also accessed from here. This allows you to undertake calculations on a cumulative basis (see section 3a10).
14-Comments
Right-click on the active frame of your query to add a comment, which will be taken through when the query is saved to the dashboard. This gives some space for the creator to give some context to the query, point out an interesting variation, or pose a question to his or her viewers. While all viewers can pose questions about dashboards or queries using the messaging function in Bime (see section ___), only the creator of a query, or another user with access, can add text to the final presentation.
15-Trend lines
On column, bar, line, area and bubble charts, a 'Trend Line' button will appear above the frame, giving you a range of options to insert a reference line onto the chart.
You can set a 'constant' at whatever value you wish, for example to emphasize zero, or show a target.
A 'linear' trend line is a straight line showing the overall trend of the chart. If there is more than one line of data on the chart, there will be a separate trendline for each data set.
Polynomial trend lines are not straight but reflect variations over time, growing closer to a curve the more datapoints there are to deal with. 'n=2' will usually give a slight arc to the trend line, 'n=5' will fluctuate more to indicate the general pattern.
Additionally, on column, bar, area and line charts you have the option to add more reference lines based on other measures; these do not track the trend of the main measure shown, but allow you to keep an eye on those additional measures. Thus, if the main consideration is how unit price changes over time, this is the main measure to drop onto the frame as normal; but if you also want to bear order quantity in mind, add this to the 'trend' box to have a reference line added to your visualization.
Once you have added a reference line to your visualization, this stays 'live' as you switch between different visualizations, until you click 'remove' (for a trend line) or discard the additional measure from the trend box. Even if you click away to one of the visualizations that does not support such reference lines (such as treemap) and then back to one that does, your reference remains in place.
On all x / y-axis charts, gridlines will appear when you hover the cursor over the field of view, whether on the pivot table itself, or on the published dashboard. If you wish to show a permanent line, use the 'constant' trend as above.
16-Decompose
The cartesian and pie charts allow you to 'decompose' your visualization, both on queries and on dashboards. Hover over a data range and a pop-up appears, offering you this option. Click, and you can select to which attribute you want to dig down into.
For example, this pie chart shows results based on one attribute (here, 'Year(Ship Date)') - but you can focus in on one element of that (here '2009') by using decompose.
Having opted to decompose using 'Sales Channel', the chart changes to show results only by the chosen Year, which has been moved to be a filter.
You can continue to 'pivot' your data by altering the filter. Alternatively, decompose another segment of the pie and each new view adds that segment's attribute to 'filters', enabling it to focus in on that segment, and uses the new chosen attribute in 'columns'.
Thus, decompose allows you either to drill down into the detail, or 'flip' the visualization from being defined by one attribute to another.
17-Hierarchies
A hierarchy is a path though different attributes of a dimension. A typical example would be the dimension “product” with attributes that would be organized as Product Category > Sub Category > Product Name.
What is the point in such organization?
First, you can easily navigate through your data in a way that make sense from a business perspective. It is highly desirable to analyze product categories and then drill down on into the details of sub-categories. While this was possible before, it is now much easier and Bime does all the hard work for you.
Secondly, it helps to organize attributes in logical blocks. All your product-related attributes will be in one dimension, all your customer-related attributes in another, and so on. In the end you will have a naturally organized analysis canvas, allowing even those with minimal knowledge of the business or technical skill to a) understand the model, b) ask deep business questions, c) see what details are important, and d) drill down and explore those details.
Drag and Drop Product Category It created a new Dimension of Product It added a new attribute in the dimension. below Dimensions Category. Now Drag & Drop Sub category. Drag & Drop Product into the dimension.
You now have defined a dimension with 3 attributes ordered as: Product Category > Product Sub-Category > Product.
The last thing to do is to rename it:
This means that the level of analysis in your query can be changed up and down simply by clicking on the + or - signs on the attributes you have placed on the frame.
How does this change how I use Bime?
As with any feature, this would be useless if it were hard to use. Creating a hierarchy is done by simply dragging and dropping elements. The most popular new feature using this will be the “decompose” feature on the cartesian and pie charts. This feature allows you to click on any data point of a chart and ask Bime to decompose it by any attributes available (see section 3a16).
17-Zoom charts
Right-click on the data field of a cartesian chart and you get the option to show a 'zoom chart' - this allows you to focus on a particular range of the data, without applying a filter or post-processing. For example, having sorted results by value, to focus on the worst performing areas, just drag the 'zoom' over the range that you want to cover up; the chart automatically resizes to focus on the range that is left. If this is a visualization that you wish to keep, you can then right-click and opt to 'hide zoom chart', or you can remove the zoom to return to the original global visualization.
18-Row Selectors
On the cartesian charts, when an attribute is dropped onto rows, a little grey box pops up in the display frame. This allows you to use the same visualization to look either at the global results for that attribute, or focus in on one element of that. In the example below, the column chart is showing results by sales channel - click on a category to limit the visualization to the relevant figures, or back on the header 'sales channel' to return to the global view.
Dropping attributes in rows (here: "Sales Channel") gives a row selector in many of the Bime's charts.
Selecting a different "sales channel" in the row selector change the data provider of the chart. Please note that scales are maintained between elements in the selector for easy comparisons.
You can select multiple elements in the row selector either by maintaining shift + select on the different element or by clicking on the title of the row selector.
19-Custom display formats
Until now Measure display formats were Bime ones. We have given you the ability to apply the display format of your choice: including decimal point precision, choosing decimal and 'thousands' separators, currency indicators, prefixes and suffixes. Opt for 'custom' display format and make whatever changes you need.
20-Dynamic count
Sometimes, you just want to…count things. Every non-OLAP connection comes now with a new dynamic calculated measure called “Dynamic count” that allows you to count the number of rows in the orginal datasource that match your current query.
21-Display aggregator
You can use the "display aggregator" check-box if you want to hide the measure aggregator ( like "SUM, COUNT etc...).
With aggregator
Without aggregator
Note: In the monitor, we recommend that you do not mix widgets with an aggregator with those widgets without an aggregator.
b-visualizations
1-Grid
The grid view is the default starting point for your visualization. It is a simple numeric grid, organising the attributes and measures you wish to show, like a traditional pivot table. Thus, it supports looking up individual values precisely, and to highlight individual values, without giving a 'shape' to the numbers. You can of course stop here, if this meets your needs, or add visualization by using graphs instead.
Just drag and drop the headings you want for the rows and columns, and 'fill' the grid by adding the appropriate measure to the frame.
2-Multi
The 'multi chart' option uses automatic configuration to provide a visualization using several different charts in the same query, so these do not have to be prepared individually. This chart explosion allows for easy data comparison.
For example, if you want to compare results over time determined by an attribute, for example performance by region, or department, or product category, you could show the results for those attributes grouped together in a simple bar or column chart, or as lines on a line chart. With the multi chart function, you can also break the results out into separate charts for each period. For example, in the below example, switching the visualization to 'multi' automatically moves the measure across to 'columns', and shows a bar chart for each year of the period in question.
Drag the measure across to 'rows' and the chart will automatically re-form as a series of line graphs for each region.
Drag the attribute from 'rows' to 'columns', and the chart again re-forms, this time to show a series of column charts.
In each incarnation of the same data, you can see the principles behind how the multi chart is formed - more than one item in 'columns' and you will see your charts next to each other; more than one in 'rows', and they will be stacked on top of each other. More than one in both and the charts will spread out further! In each case, the items dropped onto 'columns' determine the x-axis (or x-axes) of the display and the 'rows' determine the y-axis/axes.
The real beauty of the multi chart option is when additional attributes are dropped onto the frame, as the chart explosion simplifies what could be a confusing mix of elements on a single chart. A single 'line' of charts as above can be exploded into separate charts for each primary attribute, showing results for the secondary attribute.
Note that with the added functionality of a multi-chart, the additional capabilities accessible through right-clicking on the field of display (see section 3a11) is limited to adding a comment and editing the colors used. The ability to 'weight' results using options for encoding in size and color (see section 3a12) remains, as do your options to apply a filter, sort the data (see 3a4), or post-process (see 3a6).
3-Column
How do the items in your data set stack up ? A column chart is a classic method for numerical comparisons. It emphasizes data at a specific point.
When to use it:
* To compare values
* When data is discrete
When not to use it:
* When there are a lot of columns (i.e. categories of data to be shown) it may be difficult to read the labels
* While this chart may be useful to look at results over time, for example annual results over a number of years, or monthly management accounts figures, where your data is tracking something that is continously changing, it may be best to use a line-graph, as trends can be easier to see in a linear format.
To use this visualization, drop the primary attribute (in this example, the year) onto 'columns' - there are two ways to add on a second attribute (in this example 'product category'): you can simply add it to 'column', but then all the columns are the same color and equally spaced, which may not be helpful. A much better approach is to add that secondary attribute to 'rows', so that you can either see the global results (as below), grouped by year but distinguished by color, or click on one category in the on-frame box (the row selector - see section 3a18) to see only the results for that category.
4-Bar
How do the items in your data set stack up ? A bar chart is a classic method for numerical comparisons. It emphasizes individual data at a given time.
When to use it:
* To compare values
* When data is discrete
When not to use it:
* When there are a lot of bars (i.e. categories of data to be shown) it may be difficult to read the labels
* While this chart may be useful to look at results over time, for example annual results over a number of years, or monthly management accounts figures, where your data is tracking something that is continously changing, it may be best to use a line-graph, as trends can be easier to see in a linear format.
Using this visualization is as easy as using a column chart - put the primary attribute (here, 'Product') on 'columns', and any secondary attribute (here, 'Sales Channel') onto rows, not forgetting to 'fill' the chart with the necessary measure (here, 'profits'), to show your results in bar format.
5-Line
Put the value you are measuring on the y-axis and draw lines to watch items change over time. A line graph is a classic method for visualizing continuous change, from one value to the next, throughout the entire series.
When to use it:
* To track rises and falls over time
* To reveal patterns in the data: trends, fluctuations, cycles, rates of change
* When the labels on the x-axis have a natural ordering (2005, 2006, 2007...)
When not to use it:
* If your data is categorized without a natural ordering, for example by country, or department, or activity, a bar or column chart works better.
To use this visualization is again very simple. Drop your x-axis attribute onto 'columns' - and note here that this shows results by month, an automatic hierarchy available to you on a date-based attribute, accessible by just clicking the '+' button next to 'Year' - your measure will form the y-axis, and then any attribute placed in 'rows' will add lines to the graph. Again, you can click on the row selector to look only at one Sales Channel, or on its header to show all three lines at once.
6-Area
An area chart tracks the changing values of items that add together to make a whole. It is a classic method for visualizing change in a set of items: the sum of the values is as important as the individual items.
-
To track rises and falls of a set of items over time
-
When the labels on the x-axis have a natural ordering only (for example, months or years, not results from different countries or departments)
-
Only for positive values, otherwise it suffers an 'occlusion' problem
-
Only for meaningful data series that add up: if the series cannot be added, it is best to use a line graph instead
-
If detailed comparisons are not of primary importance, otherwise it is best to use a line graph instead.
To use this visualization, you have several options. You can look only at measures, and how they change over time - in the first example below, it is clear that it is changing unit price, not shipping cost, that is leading to cost fluctuations for the business. Alternatively, you can look at measures with reference to another attribute by adding this to 'rows'.
7-Bullet
Have targets? Everyone has targets - and the 'bullet' visualization allows you to show performance against target in a very easy way. Drop your chosen measure onto 'value' and your chosen attribute onto 'rows'. If you wish, you can add in extra elements such as team or year to 'columns' to compare performance across your organisation, or over time.
Then, set your target - this can either be a number that you specify on the frame, or another measure. As with all the other visualizations, you can sort, or post-process, to focus on your best (or worst) performing areas, or, using the value filter, any range at all.
8-Gauge
A gauge is another fun way of visualising targets; again, drop your selected measure on 'value' and set your target. Here, by adding a date attribute to 'columns', we can see performance over time. The target has been set at 250,000, so the gauges show how well each activity has performed towards the target.
9-Pie
This classic and popular chart shows proportions: each component is a slice of the big pie. Each slice or area corresponds to a percentage of the whole. The pie visualization puts the numbers in percentage form automatically.
When to use it:
*To see the parts of a whole, not to communicate numbers exactly: (for which, consider a bar graph instead)
*To represent the + or - 50% of one item in a whole
*If you have positive values only
To use this visualization, your chosen attribute needs to be put in 'columns'. Again, you can add in more information using color-encoding or changing the size of your slices - or you can right-click to access options for the display, add value labels (which include 'part to whole' detail automatically), put them where you want them, explode your diagram, etc. Also, hover over each slice and you have the option to decompose your figures (see 3a16).
10-Radar
When color encoding or weighting by size is not sufficient to visualize a complex combination of factors, a radar view may be the answer. This shows results around three or four axes determined by the measures chosen. The axes are ordered anti-clockwise from the right-hand side, so order them as you wish. Drag the attribute you want to look at onto 'columns', and the radar will split that attribute down like a line-graph, only the lines circle a central point to give a multidimensional display. Here, you can see the 'shape' of your business sectors, comparing lots of different factors simultaneously.
11-Geospatial
a-Usage
Bime's geo visualization allows you to see where you are in the real world, quite literally, using Bime functionality on top of Google Maps. With this, you can visualize where your staff, clients, suppliers, or any other facet of your business is concentrated.
To use the geo visualization, you will need to log in to Google Maps. The attributes determining location are dropped onto the 'geographic' axis that is, as in most Bime visualization, in columns. The more detail you include here, the more accurate the visualization will be (remember that there are many towns and cities with the same name in different countries!). To ensure precision, just drop the different columns that represent your geographic points. For example begin with "countries" then add "cities", which will be much more precise than if you only drop "cities".
The great thing about the heatmap is that it is not simply a pretty picture - hover over the heatpoints and you can bring up the detailed data lying behind them by clicking on it. This is particularly helpful when different areas of focus are close together.
Also, when you click on an individual point, you can regeocode it:
Careful, as your regeocoded point can move outside the current Google Maps tile.
There are 2 rendering modes for geo visualization:
1) Heatmap that show "hotspots" of data. Heatmap is particularly useful when you have a lot of points as heatmap doesn't suffer from occlusion of points: Every point displayed participate to give you the big picture.
However you can only show one measure at a time with this rendering mode and it is not suitable if you have very few points.
2)
Graduated Circle shows each point as a circle. You can use up to 3 measures with this rendering: one in the size of the circle, one in the color of the circle and finally one in the text inside.
As ever, you can choose the visualization that is right for you, using the options at the foot of the heatmap. You can change the size, color and opacity of the data points. You can zoom in or out on the map, to focus on particular areas, and if you are not yet working worldwide, or wish simply to focus on a particular area on the dashboard you are creating, you can lock the view so that only the specific region you want will be displayed.
b-first time use
Bime will ask you to enter your Google Maps API key the first time you use the geo visualization.
12-Sparkline
A sparkline shows linear progression, but without stated values (other than the low-, high- and end-points). It therefore shows trends and variations in a direct and memorable way, condensing really intense data sets into something very simple to understand.
To use this visualization, the progression of the diagram is based on columns (here, a date attribute), the data to be tracked in the chosen measure, and more sparklines can be added by adding further attributes to 'rows'.
13-Bubble
The 'bubble chart' allows you to see at a glance how numeric variables relate to each other.
When to use it:
-
To see relationships among data points
-
To display several variables per data point: one variable across the x-axis, another one up the y-axis and the third one represented through the size of the point
A bubble-map is another way of showing results against two measures, and is a particularly good way of seeing many data points at once. Choose your axes (measures), put your chosen attribute(s) in 'columns', and then differentiate between the resulting data-points by adding other attributes to change the color, size or shape of the points as you wish.
If your data points are all clustered in one area of the chart, you can right-click and select 'no base at zero' - the chart will then resize to new axes that will include all the data points with the minimum of blank space, allowing the differences between them to be shown to their best effect.
Below is a bubble chart that uses all the encoding options: color, shape and size
14-Treemap
Treemaps also use area to show relative size of activity, with the ability to add on color encoding to represent more than one measure at once. These are meant to give a general snapshot of activity, and work best when there are large disparities in size or the color-encoded measure. However, hovering over each square on the map gives you a pop-up look at the underlying figures, so you can still tell the difference between areas apparently similar in size.
To use this visualization, you need your chosen attribute(s) to be in 'columns', your priority measure on 'surface', and any additional measure you wish to factor in on 'color'. Use more than one attribute, for example customer country and customer state, and you can choose either the classic view, with each area displaying both its attributes and ordered purely by its order in a 'global' list, or a hierarchical view. This orders the attributes first by the primary attribute, and then by the second, so your treemap is made up of many individual branches - you can then click on the '+' to enlarge each section to concentrate on, for example, a particular country.
c-Formulas engine
1-Quick overview
The Bime Formula Engine, also named BiFe, has been designed in order to allow you to deepen and enrich your analyzes through defining calculated members. Calculated members allow you to combine several attributes and measures of a datasource and thus extract more information from your data. For instance, you can create a calculated measure, 'Margin', by dividing the Revenue measure by the Cost measure.
The Bime Formula Engine is an additional tool available if the original attributes and measures of the datasource are not sufficiently detailed directly to express the information you want to show.
This chapter describes the main concepts of the Bime Formula Engine.
2-Create a calculated measure
In this section, we describe how to create a calculated measure in Bime. For this, consider a very simple financial datasource, with two measures of this being Revenues and Cost. These two measures are interesting, but they do not directly express the realized profits. Therefore, we will create a calculated measure named Profit, which will be defined as the simple numeric difference between the two previous measures Revenues and Cost.
First, start by clicking on the descending arrow on the right of the Measures field. Then click on "Create a calculated Measure", as illustrated in figure 1.
Fig. 1 - Click on Create a calculated Measure in order to start its creation.
Now, define your calculated Measure by setting the several fields of the Calculated Measure Definition's dialog box.
Fig. 2 - The Calculated Measure Definition's dialog box.
As illustrated in figure 2, we have first to name the calculated measure we want to create. In our trial example, we put Profit in the Name field. Note that the new name must be different from the existing measures and the attributes in the connection.
We have now to express the syntax of the calculated measure, i.e. how the calculated measure has to be computed. In our trial example, we set SUM(revenues) - SUM(cost) which expresses the numeric difference between Revenues and Cost.
Note that the definition's dialog box shows the many attributes, measures and the functions you can use in order to express more complex calculated measures. Please refer to Section 4 "Define a BiFe Formula" for more information about the definition of a calculated measure in Bime.
Once set, we check the syntax of the chosen formula by clicking on "Check Formula" to ensure that it will compute.
As illustrated in figure 3, we can now click on "save" and get back to the Pivot table.
Fig. 3 - Click on save in order to create the defined calculated measure.
Once created, the calculated measure Profit appears in the measures area. On the one hand, and as expected, our calculated measure matches exactly with the numeric difference between the Revenues measure and the Cost measure, as illustrated in figure 4. On the other hand, note that a calculated measure can itself be dragged-and-dropped in Bime, like the other measures, but independently of the original measure(s), as illustrated in figure 5.
Fig 4 - Our calculated measure Profit computes as expected the numeric difference between Revenues and Cost.
Fig 5 - Once created, a calculated measure can be dragged-and-dropped exactly like other measures.
3-Create a Calculated Attribute
As with calculated measures, a calculated attribute allows you to combine several attributes and measures from your connection in order to extract more information. For this, let us consider a connection that lists the top 500 fortunes in the world. To create a calculated attribute, click first on the descending arrow of the
Axis of Analysis field and then click on "
Create a Calculated Attribute", as illustrated in figure 3.1.
Fig 3.1 - Click on Create a Calculated Attribute in order to start its creation.
Let us imagine that you want to determine the most profitable countries. For this, you have to
define your calculated Measure by setting the several fields of the Calculated Measure Definition's dialog box.
As illustrated in figure 3.2, you first have to name the calculated attribute you want to create. In our trial example, we set "Profitable Country" in the Name field. Note that the new name must be different from the existing measures and attributes in the connection.
The second field of the definition's dialog box allows you to define if the calculated attribute will have to be systematically computed (i.e. depending of the values of attributes for each query), or if it has to be computed from one of the attributes of the connection (in this case, your calculated attribute will compute the first time it will appear in a query, and will be kept in memory as long as its definition remains the same). In our example, we want to be able to distinguish countries. Consequently, we select the Country attribute for the Computed from field.
We have now to express the syntax of our calculated attribute. In our example, let us imagine that we want distinguish two categories of countries, depending of the value of the generated profits: When a country has a profit greater than $50 000, we consider it to be a "Profitable Country", otherwise it is a "Non Profitable Country". The corresponding calculated formula is consequently:
IF (SUM(profits) > 50000) THEN "Profitable Country" ELSE "Non Profitable Country" ENDIF
as illustrated in figure 3.2 (see subsection 7.i for more details on conditional statements). Like for calculated measures, please note that the definition's dialog box shows the many attributes, measures and the functions you can use in order to express more complex calculated attributes. Please refer to Section 4 "Define a BiFe Formula" for more information about the definition of a calculated formula in Bime.
Once set, you click on "Check Formula" in order to validate the syntax of the formula.
You can now click on "save" and get back to the Pivot table.
Fig. 3.2 - The Calculated Attribute Definition's dialog box.
Once created, the calculated attribute Profitable Country appears in the Calculated Attributes area. As illustrated in figure 3.3, you can now drag-and-drop it on the surface, with, or independently of other attributes. In addition, note that you can reference each defined calculated attribute in other calculated measures and calculated attributes.
Fig 3.3 - Once created, a calculated attribute can be dragged-and-dropped exactly like other attributes.
4-Create a Global Variable & What If Analysis
In Bime, a global variable has an initial value, a maximum value and a minimum value, and can be referenced in all calculated attributes and calculated measures.
The great thing about a variable is that it comes with a slider control in the user interface that allows you to change the value of the variable on the fly and thus all dependent calculations. Then, all kinds of predictive/budgeting scenarios are possible, for example “what happens if I change my unit price by 20% and decrease my units sold by 10%”?
To describe how to create a global variable, let us consider a datasource that holds the activity of a delivery company, which delivers packages using different types of transport. As illustrated in figure 4.1, there are four types of transport, and two measures which indicate respectively the turnover and the amount of fuel consumed for each transport type.
Fig. 4.1 - A very simple delivery company connexion to illustrate What-If analysis.
A predictive scenario could be "What will happen if the fuel price goes up?". For this, you will start by defining a Global Variable that holds the fuel price. For this, click on the arrow on the right of the Axis of Analysis field and then click on "Create a Global Variable", as illustrated in figure 4.2.
Fig. 4.2 - Click on Create a Global Variable in order to start its creation.
Now, define your Global Variable by setting the fields of the dialog box. Click then on Save ; The Global Variable named "Fuel Price" is now defined and available for analysis.
Fig. 4.3 - The Calculated Measure Definition's dialog box.
For our predictive analysis, we will now define two different calculated measures (see section 3c2 for more details on defining Calculated Measures). The first one will hold the Shipping Cost, defined as the fuel consumption multiplied by the fuel price (i.e. our Global Variable). The second one will hold the Margin, defined as the difference between the turnover and the Shipping Cost.
Fig. 4.4.a - Definition of the Shipping Cost calculated measure.
|
Fig. 4.4.b - Definition of the Margin calculated measure.
|
As illustrated in figure 4.5, our calculated measures are now defined and available for the What-If analysis.
Fig. 4.5 - The Fuel Price Global Variable, the Shipping Cost & the Margin calculated measures for the What-If analysis.
As illustrated in figure 4.6, you can visualize the profit that the company is making today through dropping the Margin calculated measure on the surface of the Pivot Table. However, because this calculated measure uses another calculated measure (i.e. Shipping Cost) that references the Fuel Price Global Variable, you can use the slider on the right of the Pivot Table to modify its value. If you want to set your global variable precisely, you can click on the displayed value (under the slider), and manually set the wanted value (i.e. using your keyboard).
Fig. 4.6 - The slider you can use to modify the value of the Fuel Price.
If you modify the value of the Global Variable, you can see that if the Fuel Price goes beyond a certain threshold, the Margin becomes negative (see Figure 4.7) : 1.25 is threshold where you begin to lose money. As illustrated in figure 4.8, if you drop the Transport Type attribute on the surface, you can then learn that if the Fuel Price goes up, you would prefer to be using cars and small trucks to make deliveries.
Fig. 4.7 - Depending on the value of the Fuel Price Global Variable, your Margin is positive or negative.
|
Fig. 4.8 - If the Fuel Price goes up, prefer the use of cars and small trucks.
|
5-Create a Basket Attribute
Click on save. You now have a new attribute in the dimension explorer that you can drop on the layout along with your measure:
Click on one of black arrows in the dimensions explorer and choose “create a filtered attribute”.
Now let’s talk a bit about when you should use filtered attributes. You already have the ability to 'top' elements using the post processing filters:
This appendix describes the data types, the operators and the functions you can use in the Bime Formula Engine, when you define a calculated formula.
- Section h describes the boolean operators.
- Section i describes the conditional expressions,
This section describes all functions you can apply on strings.
CONTAINS(Text,Text_to_search)
Returns TRUE if the first specified parameter contains the second one as a substring
Examples:
CONTAINS([Ship Mode],"Truck") will return TRUE for each value of the 'Ship Mode' attribute which contains "Truck".
If [Ship Mode] = " Regular Truck " then CONTAINS([Ship Mode],"Truck") returns TRUE.
If [Ship Mode] = " Regular Mail " then CONTAINS([Ship Mode],"Truck") returns FALSE.
ENDSWITH(Text,Text_to_search)
Returns TRUE if the first specified parameter ends with the second one.
Examples:
ENDSWITH([US State],"nsas") will return TRUE for all US States included in the measure which end with "nsas".
If [US State] = "Arkansas" then ENDSWITH([US State],"nsas") returns TRUE.
If [US State] = "Virginia" then ENDSWITH([US State],"nsas") returns FALSE.
FIND(Text,Text_to_find,Number_start_index)
Returns the index, in the first specified text, of the first instance of the second specified text, from the specified startIndex. Returns -1 if the second specified text is not found.
Note that the index of the first character of a string is 0.
Examples:
FIND([Ship Mode],"Norm",0) will return the index of the first occurence of "Norm" in each value of the 'Ship Mode' attribute.
If [Ship Mode] = "Urgent" then FIND([Ship Mode],"Norm",0) returns -1.
If [Ship Mode] = "Normal" then FIND([Ship Mode],"ma",0) returns 4.
If [Ship Mode] = "Urgent" then FIND([Ship Mode],"ge",2) returns 0.
LEFTPART(Text,Number)
Returns the left-most number of characters in the specified text.
Examples:
LEFTPART([Department],6) will return the six first characters of each value of the 'Department' attribute.
If [Department] = "Marketing" then LEFTPART([Department],6) returns "Market".
If [Department] = "Sale" then LEFTPART([Department],6) returns "Sale".
LENGTH(Text)
Returns the length of the specified text.
Examples:
LENGTH([Product Category]) will return the length of each value of the 'Product Category' attribute.
If [Product Category] = "Book" then LENGTH([Product Category]) returns 4
LOWERCASE(Text)
Returns the lower case version of the specified text.
Examples:
LOWERCASE([Category]) will return the lower case of each value of the 'Category' attribute.
If [Category] = "Scientific Book" then UPPERCASE([Category]) returns "scientific book".
LTRIM(Text)
Returns a copy of the specified text in which all left-whitespaces have been removed.
Examples:
LTRIM([Ship Mode]) will return a copy of each value of the 'Ship Mode' attribute, in which all left-whitespaces will be removed.
If [Ship Mode] = " Regular Truck " then LTRIM([Ship Mode]) returns "Regular Truck "
REPLACE(Text,Text_to_replace,Text_to_replace_with)
Returns a copy of the first specified text in which all instances of the second specified text have been replaced with the third one.
Examples:
REPLACE([US State],"s","SS") will replace, for each value of the 'US State' attribute, all occurences of "s" with "SS".
If [US State] = "Missouri" then REPLACE([US State],"s","SS") returns "MiSSSSouri"
RIGHTPART(Text,Number)
Returns the right-most number of characters in the specified text.
Examples:
RIGHTPART([Department],6) will return the six last characters of each value of the 'Department' attribute.
If [Department] = "Marketing" then RIGHTPART([Department],6) returns "keting".
If [Department] = "Sale" then RIGHTPART([Department],6) returns "Sale".
RTRIM(Text)
Returns a copy of the specified text in which all right-whitespaces have been removed.
Examples:
RTRIM([Ship Mode]) will return a copy of each value of the 'Ship Mode' attribute, in which all right-whitespaces will be removed.
If [Ship Mode] = " Regular Truck " then RTRIM([Ship Mode]) returns " Regular Truck"
STARTSWITH(Text,Text_to_search)
Returns TRUE if the first specified text starts with the second one.
Examples:
STARTSWITH([US State],"Miss") returns TRUE for all US States which start with "Miss".
If [US State] = "Mississip" then STARTSWITH([US State],"Miss") returns TRUE.
If [US State] = "Texas" then STARTSWITH([US State],"Miss") returns FALSE.
SUBSTRING(Text,Number_begin_index,Number_end_index)
Returns a text consisting of the character specified by startIndex (the first specified integer) and all characters up to endIndex - 1 (the second specified integer).
Example:
SUBSTRING([Dept],2,10) will return, for each value the 'Dept' attribute, the seven characters which are between indexes 2 and 10.
If [Dept] = "Web Marketing" then SUBSTRING([Dept],2,10) returns "b Market".
TRIM(Text)
Returns a copy of the specified text in which all left and right whitespaces have been removed.
Examples:
TRIM([Ship Mode]) will return a copy of each value of the 'Ship Mode' attribute, in which all left and right whitespaces will be removed.
If [Ship Mode] = " Regular Truck " then TRIM([Ship Mode]) returns "Regular Truck"
UPPERCASE(Text)
Returns the upper case version of the specified text.
Examples:
UPPERCASE([Category]) will return the upper case of each value of the 'Category' attribute.
If [Category] = "Science" then UPPERCASE([Category]) returns "SCIENCE"
This section describes the several functions you can use to handle dates.
DATE(Text)
Returns a new date that holds the specified text.
The specified text can be in a variety of formats, but must at least include the month, the date and the year.
The following instances indicates some of valid formats:
"Tue Feb 9 00:00:00 GMT-0800 2010",
"Tue Feb 9 2010 12:00:00 AM",
"Tue Feb 9 2010",
"07/24/1980",
"1980-07-24T06:23:00+0200".
DATE(year, month, day, hour, minute, second)
Returns a new date that holds the specified parameters.
All parameters must be numbers: 'year', 'month' & 'day' are needed, whereas the others are optional.
DATE_EQUAL(date, date)
Returns TRUE if the two specified dates are equal.
DATE_NOT_EQUAL(date, date)
Returns TRUE if the two specified dates are different.
DATE_LESS(date, date)
Returns TRUE if the first specified date is longer ago than the second specified date.
DATE_LESS_OR_EQUAL(date, date)
Returns TRUE if the first specified date is longer ago or equal to the second specified date.
DATE_GREATER(date, date)
Returns TRUE if the first specified date is more recent than the second specified date.
DATE_GREATER_OR_EQUAL(date, date)
Returns TRUE if the first specified date is more recent or equal to the second specified date.
DATE_ADD(date, date_part, increment)
Returns the given date, such that the specified part of the date is updated by the specified increment.
The date_part parameter could have the following values: "year", "half_year", "quarter", "month", "day", "day_of_year", "hour", "minute" or "second".
The specified increment must be an integer value.
DATE_DIFF(date, date, date_part)
Returns the difference between the two specified dates, calculated on the specified date_part.
The date_part parameter could have the following values: "year", "half_year", "quarter", "month", "day", "day_of_year", "hour", "minute" or "second".
CURRENT_YEAR()
Returns the current year.
Example:
CURRENT_YEAR() returns 2009 if the current date is the 23th October 2009.
CURRENT_HALFYEAR()
Returns the current semester as text (i.e. "H1" or "H2").
Example:
CURRENT_HALFYEAR() returns "H1" if the current date is the 22th March 2010.
CURRENT_QUARTER()
Returns the current quarter as text (i.e. "Q1", "Q2", "Q3" or "Q4").
Example:
CURRENT_QUARTER() returns "Q3" if the current date is the 14th July 2010.
CURRENT_MONTH()
Returns the current month as text (i.e. January, February, and so on).
Example:
CURRENT_MONTH() returns "July" if the current date is the 24th July 2010.
CURRENT_MONTH_NUMERIC()
Returns the current month as number (i.e. 1 for January, 2 for February, and so on).
Example:
CURRENT_MONTH_NUMERIC() returns 8 if the current date is the 31th August 2010.
CURRENT_DAY()
Returns the current day as number.
Example:
CURRENT_DAY() returns 6 if current date is the 6th January 2010.;
CURRENT_WEEKDAY()
Returns the current weekday as text (i.e. Monday, Tuesday, and so on).
Example:
CURRENT_WEEKDAY() returns "Sunday" if the current date is the 4th April 2010.
CURRENT_WEEKDAY_NUMERIC()
Returns the current weekday as number: 0 for Sunday, 1 for Monday, and so on.
Example:
CURRENT_WEEKDAY_NUMERIC() returns 0 if the current date is the 7th November 2010.
CURRENT_HOUR()
Returns the current hour as number.
CURRENT_MINUTE()
Returns the current minute as number.
CURRENT_SECOND()
Returns the current second as number.
YEAR(date)
Returns the year of the specified date.
MONTH(date)
Returns the month of the specified date as string (i.e. January, February, and so on).
MONTH_NUMERIC(date)
Returns the month of the specified date as Number (i.e. 1 for January, 2 for February, and so on).
DAY(date)
Returns the day of the specified date.
WEEKDAY(date)
Returns the day of the specified date as string (i.e. Monday, Tuesday, and so on).
WEEKDAY_NUMERIC(date)
Returns the day of the specified date as number: 0 for Sunday, 1 for Monday, and so on.
HOURS(date)
Returns the hour of the specified date.
MINUTES(date)
Returns the minute of the specified date.
SECONDS(date)
Returns the second of the specified date.
g-Comparison functions
This section describes the several functions you can use to compare elements.
= (equal)
Allows you to test if two elements are equal (i.e. if the two elements have the same value).
Please note that the compared elements of the function must have the same type (e.g. number or text).
Examples:
2.56 = 2.56 will return TRUE,
SUM(Shipping Cost) = 1568.78 will return TRUE if the value of the Shipping Cost measure is equal to 1568.78, else it will return FALSE,
[Region] = "South" will return TRUE only if the current value of the Region attribute is "South", else it will return FALSE,
[Delivery Address] = [Billing Address] will return TRUE only if the values of the Delivery Address and the Billing Address attributes are equal.
!= (not equal)
Allows you to test if two elements are different (i.e. if the two elements have different values).
Please note that the compared elements of the function must have the same type.
Examples:
78.67 != -55.02 will return TRUE,
SUM(Order Quantity) != 0 will return TRUE if the value of the Order Quantity measure is different from 0,
[Delivery State] != [Production State] will return TRUE if the values of the Delivery State and the Production State attributes are different.
< (less than)
Allows you to test if a numeric element is strictly less than another one.
Examples:
0 < 1000 will return TRUE,
SUM(Shipping Cost) < 856.89 will return TRUE only if the value of the Shipping Cost measure is strictly less than 856.89, else it will return FALSE.
<= (less than or equal to)
Allows you to test if a numeric element is less or equal another one.
Example:
SUM(Unit Price) <= 100 will return TRUE if the value of the Unit Price measure is less or equal to 100, else it will return FALSE.
>= (greater than or equal to)
Allows you to test if a numeric element is greater or equal to another one.
Example:
SUM(Shipping Cost) >= 150 will return TRUE if the value of the Shipping Cost measure is greater or equal to 150.
> (greater than)
Allows you to test if a numeric element is strictly greater than another one.
Example:
SUM(Profit) > 25000 will return TRUE if the value of the Profit measure is strictly greater than 25000.
h-Boolean operators
This section describes the boolean operators you can use to define a calculated member.
AND
This refers to the logical conjunction. A boolean, a comparison or a boolean expression must appear on either side of the AND operator. If these two expressions are TRUE, then the AND operator will return TRUE. Otherwise, the AND operator returns FALSE.
Examples:
([First Name] = "John") AND ([Last Name] = "Smith") will return TRUE if the value of the First Name attribute is equal to "John" and if the value of the Last Name attribute is equal to "Smith",
([Product Category] = "Technology") AND (SUM(Order Quantity) > 500) will return TRUE if the value of the Product Category attribute is "Technology" and if the value of the Order Quantity measure is greater than 500.
OR
This refers to the logical disjunction. A boolean, a comparison or a boolean expression must appear on either side of the OR operator. At least one of these two expressions must be TRUE in order for the OR operator to return TRUE.
Examples:
([Region] = "South") OR ([Region] = "North") will return TRUE if the value of the Region attribute is equal to "South" or "North".
([Ship Mode] = "Regular Truck") OR ([Delivery Priority] != "High") will return TRUE if the value of the Ship Mode attribute is equal to "Regular Truck" or if the value of the Delivery Priority attribute is different from "High".
(SUM(Order Quantity) < 10) OR ([Product Category] = "Luxury articles") will return TRUE if the value of the Order Quantity measure is less than 10 or if the value of the Product Category attribute is "Luxury articles".
NOT
This refers to the logical negation. The NOT operator returns FALSE when the specified argument is TRUE, and returns TRUE when the specified argument is FALSE.
Example:
NOT(CONTAINS([Ship Mode], "Air")) will return TRUE if the value of the Ship Mode attribute does not contain "Air".
i-Conditional Expressions
This section describes the conditional expressions you can use to define a calculated member.
IF THEN ELSE ENDIF and IF THEN ENDIF conditional expressions
These allow you to specify a logical test to evaluate.
You can define a conditional expression in one of two ways:
IF (boolean condition) THEN
consequent
ELSE
alternative
ENDIF
or
IF (boolean condition) THEN
consequent
ENDIF
A conditional expression is interpreted as follows: If the specified boolean condition is true, the return value is computed through executing the statement defined the 'THEN' block. Otherwise, the return value depends on the structure of the conditional expresion. If the conditional expression contains an 'ELSE' block, then the return value is computed through executing the statement defined in the 'ELSE' block. If there is no 'ELSE' block, NULL is returned.
Example:
IF (SUM(Profits) > 10000) THEN SUM(Profits)*2 ELSE SUM(Profits)*1.5 ENDIF specifies to return SUM(Profits)*2 if the current value of the SUM(Profits) measure is greater than 10000, and to return SUM(Profits)*1.5 in other cases.
IF ([Region] = "South") THEN SUM(Profits) ENDIF allows to return the value of the SUM(Profits) measure only when the Region attribute is equal to "South".
SWITCH
The SWITCH conditional expression provides a convenient alternative to the IF-THEN-ELSE conditional expression when dealing with a multi-way branch.
The syntax of the SWITCH expression follows:
SWITCH testedElement {
CASE value1: returnValue if testedElement = value1
CASE value2: returnValue if testedElement = value2
...
DEFAULT : defaultReturnValue
}
The evaluation of SWITCH conditional expression works as follows: 'CASE' values are checked in turn for the one that matches the value of 'testedElement'. If a matching label is found, execution returns the corresponding 'returnValue'. If none matches, and the optional DEFAULT label exists, execution returns 'defaultReturnValue'. If there is no default return and no values match, then the NULL value is returned.
The tested element could be an attribute, a measure, a calculated attribute, a calculated measure or the result of a function. The 'case' and 'returns' values could be text, number or date.
Example:
SWITCH [Country] {
CASE "England" : "Fish & Chips"
CASE "France" : "Cheese & Wine"
CASE "Italy" : "Pasta & Pizza"
CASE "Mexico" : "Tortillas, Guacamole & Enchiladas"
DEFAULT : "Speciality not filled"
}
This SWITCH conditional espression returns "Pasta & Pizza" when the current value of the Country attribute is "Italy", and returns "Speciality not filled" when the current value of the Country attribute is "Australia".
A dashboard is the product that results from the connections built and queries made. The dashboard is where queries, potentially from numerous data sources, are displayed. They are not, however, static displays, but still contain analytical functionality in the post-processing menu on the right of the dashboard (see section d) or through the use of data filter prompts.
