A simple example of converting your analytics into any referenced currency
Today we are going to look at an exciting new function in atoti v0.7.1 called lookup! What’s a lookup? We look up words we don’t know in a dictionary, and we look up news on the internet, etc. So, what do we look up in a multidimensional cube?
Answer: data in a reference table!
Imagine you have the exchange rates from 1 USD to some other currencies. How would you design your cube to be able to convert your pricing to any of these currencies?
Continue reading to understand how this works, starting from the most basic cube design.
Basic currency conversion in a multidimensional cube
Let’s take a basic use case where we have three equities in different currencies:
When we have the corresponding daily FX rates, the most intuitive solution is to link the two datasets by Currency as shown below:
Once we have that, we can easily convert the equity’s price to USD as follows:
m["Price (USD)"] = tt.agg.sum( m["Daily price"] / m["USD rate"], scope=tt.OriginScope(l["Currency"]) )
tt is the alias we’ve set for atoti library,
m for atoti cube’s measures and
l for the atoti cube’s levels.
In the above snippet,
m["Daily price"] is the daily price obtained by expanding the
Price array in the Equities table. Similarly,
m["USD rate"] is the daily exchange rate obtained from the
Rates array from the Fx_rates table. It gives the rate of 1 USD to the respective currencies. Check out the notebook for more details.
The relationship between the Equity and Fx_rates table only allows us to convert the Price to USD as we see above. We are unable to convert to other currencies available in the Fx_ rates table.
Implementing conversion to referenced currency
Before we go further, let’s spend a bit of time understanding our FX conversion. The FX rate that we have is for 1 USD to another currency. This means that from our equity currency to USD, we simply divide
Price by the FX rate:
Price (USD) = Price / FX rate
For us to convert the equity price to another currency, we can leverage
Price (USD) above:
Reference price = Price (USD) * FX rate
This implies that we needed two rates:
- Rates to convert equity currency to USD
- Rates to convert USD to the referenced currency
Knowing this we can proceed with the implementation in two ways:
- Keep the joint between Equity and Fx_rates table and perform lookup only for the second rates mentioned above. We have derived
Price (USD)in the previous section.
- Remove the joint between Equity and Fx_rates table and perform lookup for both rates.
In the atoti notebook example, the second method is adopted.
Note: When the Fx_rates table is not joined to the Equity table, it no longer gets displayed when we execute
However, we will be able to see it using
session.tables.schema. It is displayed as a standalone table (i.e. there’s no connection to the other tables in the cube).
Difference between joining and not joining referenced table
The main difference between the two implementations proposed above can be seen in the code snippets above. It impacts only the way we obtain the rates for conversion to USD.
When the Fx_rates table is joined to the cube, a relationship with the base table is already established through the currency join. Therefore, we can simply use the
single_value function to obtain the
Without the join, we need to define the condition(s) to get the corresponding
Rates from the standalone Fx_rates table. In this case, using the lookup function, we retrieve the
Rates only if the Currency in the Fx_rates table matches the value in our currency level.
Note, we are not limited to level conditions for the lookup. We can also use measure output to perform lookup depending on the use case.
Creating referenced currency for lookup
For both implementations proposed, we have to perform a lookup for the referenced currency. A problem arises here: Where do we get the value for referenced currency?
The equity table only has the currency for itself. So it’s not what we wanted.
One option is for us to define a list of currencies we want using the
cube.create_parameter_hierarchy_from_members( "Reference currency", ["EUR", "JPY", "GBP", "CHF", "CAD", "AUD", "CNY"] )
This creates an analysis hierarchy containing the currencies in the list as its members. There are no underlying facts for these members. We will see later how we associate these members with the cube.
Alternatively, a better solution is to create the analysis hierarchy using
cube.create_parameter_hierarchy_from_column("Reference currency", fx_tbl["Currency"])
fx_tbl is the atoti table for Fx_rates.
This is a better solution because:
- We will definitely have a corresponding FX rate for each currency.
- The members of the hierarchy are dynamic — all values under the Fx_rates’ currency column will become a member of the hierarchy. This includes data that is inserted into Fx_rates table later on. In the previous
create_parameter_hierarchy_from_membersfunction, the member list is fixed during creation.
Associating reference currency to cube via lookup
Our main objective is to convert the daily price into the selected
Reference currency (1), giving us the
Reference price (3). With this in mind, we will need the
Reference rate (2) that we can apply to
Price (USD) for the conversion.
We will use the
Reference currency (3) to perform the lookup against the Fx_rates table above:
m["Lookup reference rate"] = tt.lookup( fx_tbl["Rates"], fx_tbl["Currency"] == l["Reference currency"] ) # Lookup reference rate is an array. Therefore we used `Date Index` to get the daily rate from the array. m["Reference rate"] = tt.where( l["Reference currency"] == "USD", 1, m["Lookup reference rate"][m["Date Index"]] ) m["Reference rate"].formatter = "DOUBLE[#,##0.0000]"
Once we have the conversion rate, we can apply it to
Price (USD) to get the corresponding value of the selected reference currency:
m["Reference price"] = m["Price (USD)"] * m["Reference rate"] m["Reference price"].formatter = "DOUBLE[#,##0.0000]"
Once we have the measures created, the rest is a matter of representation. We can have the interactive dashboard as we saw in the GIF at the beginning of the article, or we can simply display all the prices for all currencies in a pivot table:
As you have seen by now, there are many ways to achieve the same objective. To summarize quickly, we have demonstrated:
- Creation of an analysis hierarchy, which allows us to select the currency to convert to other than the currencies in the base table.
- Lookup on an atoti table conditionally using the cube’s level against the table columns. The table can be a standalone table or joined to the cube.
The possibilities don’t just end here. Give it a try! Reach out to us on GitHub discussion if you need help.