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"])
)

Where 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:

1. Rates to convert equity currency to USD
2. Rates to convert USD to the referenced currency

Knowing this we can proceed with the implementation in two ways:

1. 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.
2. 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 cube.schema:

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 Rates.

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 create_parameter_hierarchy_from_members function:

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 create_parameter_hierarchy_from_column:

cube.create_parameter_hierarchy_from_column("Reference currency", fx_tbl["Currency"])

Where fx_tbl is the Atoti table for Fx_rates.

This is a better solution because:

1. We will definitely have a corresponding FX rate for each currency.
2. 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_members function, 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:

Conclusion

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.