Calculations can be performed in two areas of the Omniscope application:
- in the workflow, using the Field Organiser block
- on the individual report level, using the Report data sources functionality
From the ‘best practice’ point of view, adding calculated fields in the preparation phase, in your ETL, makes the calculation part of the data transformation routine, enabling all new records to undergo the same treatment. The data is now available to be visualised in one or multiple reports, stemming out of the transformation process. It is centrally managed and easy to access or modify if needed. Inside the Field organiser block the new formula fields, just like any modifications, are highlighted yellow, so anyone checking/editing the formulas won't have any trouble finding them.
In the Evo application it is possible to create multiple dashboards in the same workflow, allowing the user to manage a single ETL process, while publishing different reports. These reports might contain completely unrelated datasets, therefore the analyst may have to perform calculations on the target report level. Other reasons to move calculations to the Report data sources include:
- ‘Live query’ data input mode - when Omniscope is connected to a large database and is ‘streaming the data’, rather loading it and doing calculations in the workflow.
- Variables – incredibly useful tool to allow the end user to control the dashboard by interacting with numerical/date/text variable, which is built into the formula. Use cases: ‘what if’ scenario, where the user wants to recalculate the charts instantly by interacting with the variables such as exchange rate or market price, and watch how different levels would affect the outcome (see demo). Another use case is situation where the dataset contains multiple category fields that the user may wish to compare, which would result in multiple dashboard tabs, alternatively multiple KPIs that might be good candidates for the chart measure. To avoid multiplication of identical charts that differ only in the choice of split or measure field, variables could be used to give control to the end user, so they can pick the comparison fields on a single tab, while still having the access to all the combinations e.g. analyse the sales by [Country] or [Company] or [Product] and use for the numeric measure [Value $] or [Units] or [Weight]. All this could be performed on a single page - see demo.
- Dynamic queries driven by the end user. If a query is added in the Report data sources area, before the Aggregation or Formula blocks (see image below), consumer of the report will be able to control the outcome of the calculations, by allowing all or only some of the data to pass through the calculation step.
- Performance optimisation – when dealing with large datasets (regardless of the origin). Report data sources formula builder interface contains smaller number of functions, compared to the Field Organiser - these are optimised for the big data use and complemented by the functions in the Aggregation block.
Report data sources offers powerful advanced functionality by combining the Formula, Aggregation, Query and Join blocks to create a central calculation and querying resource in a report for multiple visualisations. Results can be multiple new data sources and multiple new fields, in each data source. Unlike the fields created in the ETL phase, these new sources/fields are only accessible inside the given report.
More about this functionality in our next article.