Post Open Exchange Rates reports to Microsoft Teams

Open Exchange Rates gives a rate for each currency and date, and a message posted from it lists rates. The sums your finance team needs come from joining those rates to orders and invoices, which neither the source nor the channel does. A Teams channel is read by people outside the team that built the report, so every number needs to explain itself. The join belongs in a data warehouse first.

• Open Exchange Rates lists rates by currency, not converted revenue
• A Teams channel reaches colleagues who never saw the source, so definitions must travel with the figures
• The data analyst links the sources by SQL keys and controls what the message says

In your data warehouse the data model joins every rate to orders or invoices by date and currency, so all revenue is in one currency. Pick an industry to see the shape of its model.

• Each order gets the rate of its own day
• Invoices join by currency and date
• Measures such as settled_revenue are defined once

Add two or three lines beneath the table in the same message. Pick a send condition on the report, and open the Triggers tab to set the day and time of the run.

• The lines rest on currency and rate from the table above them
• Figures come from a fresh run of the Data Mart before each message
• Send always, only when empty or only when not empty: chosen per report