Home > GIDE Poweroom > Introduction to Poweroom

Introduction to Poweroom

Poweroom is GIDE’s answer to looking into, understanding, and processing extra-large volumes of data such as invoices, transaction records, customer relationship events, contract profiles, production measures or any other extra-large set of numbers that a company records every tick, second, day, week or month.

Tags

Let's imagine a car, a house, an ATM or a bank loan. Each of these objects can have several properties. A car may have a certain colour or weight; a bank loan may have an interest rate, remaining principal amount or maturity date. Any of these properties may change its value during the lifetime of the object, but some of them may not (keeping the same value the whole time). Each property can have only a single value assigned at one moment in time (a car can't be blue and red at the same time, similarly a bank loan may have only single maturity date at one time, but these can be changed at some point during its lifetime). Such object properties within Poweroom's terminology are generally called Tags:

  • Tag types - The individual properties (e.g. "colour", "weight", "maturity date").
  • Tag values - Values assigned to properties (e.g. "red", "2500kg", "31-Dec-2008", etc.). Tag values have date (including time) assigned. This date defines the time point from which the value of the Tag is valid.

Let's suppose we have bought a green car. Tag type "colour" will be assigned a Tag value "green" valid as of the date of purchase. After few years we decide to repaint the car a different colour. A new Tag value "blue" will be appended to the Tag type "colour" (having now two different Tag values), however the date denoting “valid from” will be set to e.g. today. This way we will have a complete track of all changes of a certain Tag type.

Value

For example, we may track kilometres driven or consumption for a car. In the same way we can keep a record of the amount of money withdrawn or the total number of withdrawals for an ATM. The common feature of all these values is that they summarize specific amounts for certain periods of time.
Not surprisingly, these values are referred to as Values in Poweroom. Again, more precisely, the types of "entities" we are recording are referred to Value type (e.g. "amount of money withdrawn", "kilometres driven", "payments paid") and the numbers themselves are called Values (e.g. "1580" (Eur), "645" (km), etc.).

Stage of a Value

Imagine that we are tracking some Value type (or Type of Value), e.g. "kilometres driven" for a car. But if we say that this Value type has a Value of e.g. "645" (km) - what time period are we referring to? If we say that Value type "amount of money withdrawn" of a certain ATM has a Value of "1580" (Eur) - again, what time period are we talking about?
This property is called a Stage in Poweroom. Each Value must have (in addition to its Value type) a Stage assigned. By defining Stage of a value we say, what are the time periods we will record the values for. For cars, we will define a Value type "kilometres driven". Additionally, we will assign Stage "month" to this Value type. Doing so we say that we will track kilometres driven by any car on a monthly basis. Users may decide, what Stage to assign to any Value type. However, every Value type can have only a single Stage assigned.
When deciding on Stages, you should ask several questions that may help in selecting the most suitable Stage for a certain Value type:
What granularity of values do I have available in my current source of data?
Yes, it is possible to track "kilometres driven" hourly, but do we really have such a fine-grained source of data?
What calculation/reports do I expect to be generated?
I could assign "Year" as a Stage for "kilometres driven" Value type, however I've lost all the details by doing so. I'll have a single value for every year (stating "kilometres driven during the course of year XXXX", but is this enough for my needs? Don't I additionally need a breakdown based on months? Or even days? Having a single value for a whole year makes creating such fine-grained outputs simply impossible.

Horizontal Aggregation

Let's assume that we will track the (Value type) "amount of money withdrawn" from an ATM on a daily basis (Stage = "day" in this case). But what if I need to report monthly summaries? Moreover, what if I need an "average amount of daily withdrawals" reported on a weekly basis? This is something that happens fully automatically in Poweroom. For this feature there are two important facts:

  • Aggregation of values resulting in other Value stages (e.g. summarizing daily values producing a monthly value) is called horizontal aggregation.
  • Horizontal aggregation is handled by Poweroom automatically, if possible. By this we mean that not all aggregations may be calculated. For example, if we have monthly values, we can easily aggregate into quarterly or annual summaries. However, it's simply not possible to calculate weekly or daily breakdown from a single monthly value.

Distinguish between Tags and Values

To decide correctly, whether a certain number should be treated as Tag or Value, the following characteristics of the number should be considered.

  • Tag - the number expressing a certain value, valid "as of" a certain date (at that moment). Aggregating two or more numbers of this type seems unreasonable or irrational
  • Value - the number expressing an amount of something accumulated "for" a certain period of time. Aggregate several numbers into a single one (e.g. calculate their summary, mean average, maximum value, etc.). E.g. taking all numbers within a certain year and summarizing them to get an annual figure.

Data Type

Poweroom supports several types of information that can be stored as Tag value or Value. The user (when designing the data storage) may decide what type of data (information) will be stored in a particular Tag value or Value:

  • Double – Stores a rational number that may have a fractional part.
  • Integer – Stores whole numbers but not fractions. If a fraction is passed, it will be automatically rounded to the nearest integer (whole number).
  • Date / Time – Stores dates with maximum precision limited to seconds.
  • String – Stores strings consisting of characters. Poweroom stores strings encoded in UTF-8 allowing users to use special characters from nearly all languages.
  • Boolean – Stores a logical type that can be either "true" or "false".
  • Enum – Stores drop-down list of user defined options.

Values are usually horizontally aggregated into higher Stages using standard mathematical operations. Therefore most of Values will have data type set to either "double" or "integer".
Tag values can't be aggregated or otherwise calculated, they can be used for much more varied purposes, e.g.:

  • Filtering you can create a segment with cars having "red" colour only. Or you may want to calculate only loans having a maturity date of more than 10 years time.
  • Conditional decision making you may charge a fee of 5% for loans having started more than 5 years ago and a fee of 10% for all other loans. Or it is possible to calculate a lower interest rate for insured loans and a higher rate for un-insured loans.
  • Split into groups you can even let Poweroom to automatically divide e.g. all ATM's into groups based on city they are installed in. Or you can split loans into two groups - insured and un-insured loans.

Item

Tags or Values are always related to an object in Poweroom called an Item. Each Item usually has multiple Tag types and Value types assigned. Not all Items necessarily have to have the same collection of Tags and Values. What Tag types and Value types are assigned to a certain Item depends solely on the data available and the user's choice.

Item Code

An Item is uniquely identified by a Code, which is any string that is assigned only to this Item. If a code is not provided during data import (or manual entry), Poweroom generates a random code for it automatically. There are some limitations that apply to a Code:

  • Must have string data type. You cannot identify Items by e.g. numbers, dates or boolean values.
  • Must not be used for any other purpose than identifying Items.

Note: If Item name is selected as a code of the item, filter by Item Name will not be allowed. Its recommended to create a separate Tag type called "Name" having Item's name stored as its Tag value therefor the name of each Item will be stored redundantly (once as Code and once as Tag).

Datasystem

To apply certain processing (calculation, filtering, aggregation, etc.) on a certain set of Items, these Items have to be of the same type. You can, for example, filter a set of cars, calculate certain indicators for a set of loans or split ATM's into groups based on their location. Please note that all the Items are of the same type. What's important to remember is that Items of the same type are always stored together in a single "database" (technically we are referring to a simple file) which is called a Datasystem. Imagine the Datasystem as an area where Items of the same type are stored.

Note 1: Never mix Items of several types in a single Datasystem. This usually results in an unexpected behaviour.
Note 2: Under certain circumstances you may concurrently refer multiple Datasystems in your calculations.

Calculating an Item

Imagine a car as an Item, having a Tag "fuel type" and also having Values "kilometres driven" and "fuel consumed" (both recorded monthly). "Calculating" this car means performing any mathematical calculations using its Tags and Values. We could calculate for example:

  • Monthly cost of fuel (based on fuel type and consumption)
  • Average cost per kilometre (based on previously calculated cost and kilometres driven)
  • Both these amounts calculated on monthly and annual basis and many others

It's possible to multiply (fuel consumed by fuels price), divide (total cost by kilometres driven), sum (monthly data to get annual data) or use any of pre-defined mathematical formulas provided by Poweroom to "calculate" your Items. You may perform different calculations depending on a certain Tag (use different price based on fuel's type). You can use even different formulas for insured and not insured loans (providing that you have a Tag "is insured" with boolean values "Yes" or "No" on your loan Items).
There's one major limitation in the calculation of Items that must be taken into consideration every time a user is designing any calculation. The calculation of individual Items must be independent. In other words, calculation of item A cannot be dependent on calculation of Item B (remember, we are talking about Items of the same type stored in the same Datasystem). For example, if we have cars as Items, all cars have to be independent to all other cars. It must be (mathematically and logically) possible to calculate any of the cars regardless of results of other cars.

Note: Please note that this restriction applies to Items of the same type only. It is possible to have scenarios involving a dependency on Items of different types. For example, let's say Datasystem "A" storing Items representing loans and another Datasystem "B" storing Items representing branch offices. As branches are different from loans, it's reasonable to calculate branches’ performance ratios based on the number (or portfolio value) of loans they have. In this case, the calculation of a single branch item depends on the prior calculation of loan Items, which is allowed.

Vertical aggregation

One of Poweroom's purposes is to perform calculation over vast amounts of Items. The process of "combining" calculation results of individual Items into a single result is called Vertical aggregation.
Let's take an example of a Datasystem storing cars as Items.
What is my total cost of fuel for all cars (or for a group of cars I'm currently interested in)? Moreover, what's the average cost per kilometre driven by all (or several) cars? This is where Vertical aggregation comes in handy. You simply define calculations you are interested in (e.g. monthly cost of fuel and average cost per km driven), select what Items (cars) should be calculated (e.g. define filtering criteria) and finally specify, how each amount should be Vertically aggregated. I would choose "SUM" for total cost of fuel and "AVG" for average cost per kilometre. Poweroom will automatically calculate all selected cars and vertically aggregate them. The final result is actually two numbers:

  • the total cost of fuel and
  • average cost per km.

Note: This aggregation is called vertical, because you can imagine the process of calculating Items as putting the results on a stack (on top of each other) and consequently aggregating them into a single result.

Calculation model

To create a calculation (that will be executed on all desired Items and consequently vertically aggregated) one has to create a so called Calculation model. For better visualization, you may imagine the calculation model like a spreadsheet (consisting of rows and columns), however featuring incomparably more possibilities for the user. The fundamental element of each Calculation model is a row, in Poweroom's terminology called Account. Accounts typically define the type of result we would like to see (e.g. amount of money withdrawn, cost of fuel, value of loan portfolio, etc.).

Note: A column within a certain account is usually called "Cell".

A single account may contain many columns calculating different results. The results may vary (for example) by date (e.g. one column will calculate amount of money withdrawn in Jan-2010, the second column will calculate amount of money withdrawn in Feb-2010 and a third column will calculate amount of money withdrawn during the whole year 2011 from an ATM). The results (columns) in a single account may also vary by calculation formula used - e.g. a column will calculate a loan's average repayment amount, while another one will return future value of the loan. All these possibilities depend on two most important facts:

Calculation formula There are a variety of pre-defined mathematical formulas to be selected or you can create your own formula. Multiple formulas can be selected for a single account.
Formula assignment Each column of an account may use a different formula. It's up to the user, which formula will be assigned to a particular column. Poweroom's engine will then automatically link a formula to each column for calculation.

Datasources

Each Calculation model may have one Datasource assigned which it can read data from. So, for example, to calculate performance of our branch offices, we would create a Calculation model and select a Datasystem storing Branch offices as its data source. Then we would create some accounts, columns and assign formulas calculating the desired indicators. But what if in order to calculate a certain Branch office performance indicator, a total value of loans (for that branch) is required? We can't select another Datasystem (storing Loans in this case) as a data source (as we already have our primary data source selected - Branch offices). There's a small exception for this purpose. Although you can't select another Datasystem as an additional data source for your Calculation model, you still can reference accounts of any other existing Calculation models. So to calculate an indicator where value of loans is required, you can simply link a Calculation model, which calculates the desired numbers. Poweroom will then automatically calculate values of loans for the Branch offices first and then calculate the requested performance indicators.

Datasystem relations

Datasystem relations informs the Poweroom, how Items in two different Datasystems are related to each other. In our example, we had Datasystem storing Loans and Datasystem storing Branch offices. If we ask the Calculation model to calculate Branch offices using Loans, we have to define, how Branch offices and Loans are related to each other (simply said, which Loan belongs to which Branch office). In the current version of Poweroom, only a relationship based on Tag equality is supported. We need to have a Tag in both Datasystems, that have the same Tag value for related Items. E.g. Each Loan Item would have a Tag called "Branch code" where the code of the corresponding Branch office would be stored. And similarly, each Branch office would have a "Branch code" Tag. Now it's easy - to calculate the performance indicator for Branch "XY", the Poweroom's calculation engine knows that it needs to calculate the value of all loans having the same Tag value equal to "XY" first.

Formulas and Parameters

Each Formula used in Poweroom needs certain Parameters to be calculated. The following list contains the available types of these parameters:

  • Constant Is a value that never changes. Please note that constants don't have to be just numbers. See Data types for a complete list of data types supported by Poweroom.
  • Cell of the same Calculation model For calculating a particular value you can use any other existing value from the same Calculation model.
  • Array of cells from the same Calculation model Is an advanced version of ‘Cell of the same Calculation model’. Instead of selecting a single cell you may choose an array of cells. But you don't have to be very specific. It's enough to say that you request (for example) an average value of last 3 months from Account XY. Poweroom will automatically identify the meaning of "last 3 months" (based on the date of the column you are currently calculating), calculate the average and provide the result as Parameter for your formula. It is important to mention, that columns absolutely don't have to be ordered by date. Poweroom will always determine "the last 3 months" correctly for you.
  • Tag value from Datasystem For each Item calculated, the value of its corresponding Tag will be read from Datasystem. The user only needs to specify Tag type and Date of validity for a tag.
  • Value from Datasystem For each Item calculated, the specified Value will be read from Datasystem. The user only need set appropriate attributes for the value columns. Stage conversions will be calculated automatically. Let's say you are specifying a parameter for a formula located in column having year 2010 and Stage "year" (the column calculates annual values for year 2010). However, your Datasystem stores Values in Stage "month". Poweroom's calculation engine will handle this automatically. The user only needs request the Horizontal aggregation (see appropriate paragraph) and the annual Value will be calculated fully automatically.
  • Global constant (Model Parameters) Is similar to a standard constant, however is defined within the Calculation model's scope and may be re-used in any formula of the model. If someone changes the value of a global constant, the new value will be automatically used in all its occurrences.
  • Column date When this type of Parameter is used, it will automatically be replaced by the date of the column the formula is defined in. Certainly, this value will always have date data type.

The process of calculating

  1. On the basis of user defined Column Types (Tag or Value) of the columns from the importing file for items in Datasystem, Poweroom automatically creates a list of available Item Tags and Item Values.
  2. The user when creating a model defines formulas and the method of vertical aggregation for the accounts and how it will be calculated (Time period or Custom Columns).
  3. The user when creating a Case defines the view of the accounts and columns. Then in the active case defines a Storage Map.
  4. For selected a Data study in an active case, all values in the appropriate accounts and columns will be automatically calculated on the basis of data in the related Datasource. All calculations including vertical and horizontal aggregation will be done in the background.
  5. Activating other data studies, changing the value in Model Parameters or activating/deactivating filters will initiate an instant calculation (including aggregations) for the appropriate accounts displayed in the Case.