Wrapping up Cross Join
Today Mark shares with us a perfect example of the real value of the CROSS join, or using the generic term Cartesian results, in an example defined by a dimension common in international finance reporting; Currency.
The value of any currency can be translated into the value of any other kind of currency through exchange rates. Since exchange rates vary over time, there is a TIME dimension that is also factored into the translation of one currency value to another. Let’s let Mark share his solution, which he documents nicely:
I saw your article on CROSS JOIN’s…yet another useful topic that you bring to light. In the financial world cross joins are essential for end of period reporting (quarter, year etc.). But in addition to any performance metrics being sort, the most common use is simply to generate an exchange rate matrix.
Example
We only store the exchange rates against a reference currency (in Switzerland the Swiss franc CHF). However we are legally required to present our reports with a matrix of all currency exchange rates at the end of the period. This matrix varies per report, as it only needs to show the currencies that are referenced in the report, which can vary from a few to more than 100, but obviously limited to the number of currencies that exist in the world.
We simply CROSS JOIN all pertinent currencies with a function that calculates the reference rate that is stored against the currency pair in question, which gives an easy table we can use to even calculate page splits in a final report containing 100 or more currencies, an example result of which is shown below.
| AUD | CHF | EUR | GBP | USD | |
|---|---|---|---|---|---|
| AUD | 1.00 | 0.95 | 0.78 | 0.66 | 1.04 |
| CHF | 1.05 | 1.00 | 0.82 | 0.70 | 1.07 |
| EUR | 1.27 | 1.22 | 1.00 | 0.86 | 1.32 |
| GBP | 1.48 | 1.42 | 1.16 | 1.00 | 1.54 |
| USD | 0.06 | .092 | .076 | 0.65 | 1.00 |
Since the number of possible currencies is limited, performance is never an issue, and the CROSS JOIN is the “only” automated solution for generating multi-page matrices.
Thanks for your continuously useful topic,
Thank you Mark for your excellent example of the true power of the CROSS join. Your example clearly demonstrates what I have been trying to communicate. Using CROSS join in the correct circumstances results in the ability to provide an essential matrix of data, regardless of the existence of facts meeting each intersection. In Mark’s case, there is always a fact.
User beware that a CROSS join has the potential to be harmful to your database. It works great on smaller sets of data. As the size of the data participating in the CROSS join increases, you have the potential to literally bring your database to its knees.
Would you like to try writing a CROSS JOIN? Here is a sample requirement:
I would like to have a report that produces a list of ages for individuals taken from list of people, counting the number of individuals of that age, with a separate count per month of the year.
Restrict the report to include counts individuals between the age of 20 to 60.
The report should contain a row for each age, and a column with the age presented. It should also have 12 additional columns, one for each month of the year. In each month column include the count of individuals matching that age/month intersection.
Next week we move on to another topic. Share your questions, solutions or thoughts by dropping me an email at btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Will Doing Tasks Beneath My Grade Hurt Me?
People feel the need to help-out when managers ask them to. They feel that they are being a team player or good-soldier, even if the task is beneath their rank or level. This is fine for a very short and contained duration. But if the majority of your assignments are this way — it is detrimental to your career. Your performance evaluations are based on your band level (i.e. Senior level). You are being rated against other Senior Level Engineers and what they are bringing to the table. If they are producing Senior Level Results and you are bringing junior or associate level results – you will be at the bottom of that totem pole.
Featured White Paper(s)
Ten Tips for Optimizing SQL Server Performance
read more)