How to create fact table with measures derived from comparing two fact table rows

Hi,

I have come across unique requirement. I have a fact table say F1 that has 12 dimensions. say D1, D2, D3...D12. Modeling this is pretty simple. But the new requirement states that some measures that are derived by analytically comparing two rows from the Fact table F1. Which means this new fact table, lets call it F2, will have 24 dimensions. Two sets of the 12 dimensions each. 1 set referring to the first row of fact table F1 and second set referring to second row of the fact table F1 that are compared to arrive at the measures in this fact table F2.
This has lead to centepede fact table. Is there any better way of modeling this? I was thinking if I can insert a unique key in F1, then I can use it as a reference in the fact table F2 which effectively brings down the no. of dimensions in F2 to just 2. Is it an acceptable approach?

Hi, can you post an example of what you are describing. Cheers

Most SQLs these days support windowing functions that allow you to look at multiple rows in the same query, LEAD and LAG are examples. These may be sufficient to do what you need to do without creating a new fact.

Ok Let me explain with the actual scenario.
I am modeling asset management systems(BNFS domain).
I have a fact table for investment positions. These positions have around 10 -12 dimensions. In addition returns are calculated for each of the investment position.
Now there are performance metrics derived by comparing returns from two investment positions (that means two rows of the Returns Fact table). Hence, two positions have to be linked to these performance metrics. Two methods that come to my mind to achieve this are:

One method is to repeat all the 10-12 dimensions twice. First set referring to Returns from position 1 and second set referring to the returns from position 2. The other method is to have a surrogate key for the Returns fact table and capture the surrogate keys of the two returns being compared in the performance metric.

With first method I end with a centipede fact table with 20-24 dimensions. Implementing key for such a table in Oracle/MS SQL Server will become costly affair.
If I go with second method, its aganist Kimball principle to use surrogate keys in Fact table. By doing so am I not making the dimensions defunct?

Which two? Is the performance of position A ALWAYS compared to the performance of position B? Or would they also want to compare position A against positions C, D, F, H, W, and Z? Which pairings do you plan to put in this new fact table? Would they want to do comparisons you did not anticipate?... 99.99999% of the time they will.

First, to compare two positions, there needs to be dimensions in common. So, even if you did build this new fact table (which I do not recommend), you will have less than double the dimensions. Problem is, which dimensions are common? Are they comparing positions for the same time period? Does the time period span multiple rows? Maybe they want to compare the performance of the same position over different time periods. How would your new fact table handle that? What if they want to include a third position, or combine positions?

Second, a relationship between fact tables is always many to many (even to the same table), measures must be aggregated across common dimensions before they can be joined.

Third, adding a surrogate key to the fact table doesn't change anything. You already have sufficient keys.

Do it in a query. Join the fact table to itself. Create a view if necessary.

Sorry for the late response as I was away on Vaccation.
You have put the questions, which I was to pose to the functional experts. So I will be back with the answers.
Meanwhile, I suppose, they would like to compare any position with any position.

The calculations are pretty complex and not straight forward to perform through a query. Position aggregations are calculated taking into count the composition of the issues and accounts. Hence, aggregating on the fly is perhaps not possible.

You were suggesting to join fact table with itself towards the end. Did you mean aggregated facts? How do we do that?
And views. How do we maintain views for large amounts of data?

Wither two different fact tables or the same fact table, you always combine them in the same way... aggregate on common dimensions (or dimensional attributes) and join on those dimensions or union the two sets (your choice). Most BI tools will do this for you. If the nature of the joins is unknown, it is the only way to do it... views or prejoining facts (i.e. generating some aggregate) won't help.

Hi Neil,

LAG and LEAD have been very useful functions. Thanks for the info.
I am still awaiting the proper requirements from the functional team regarding which positions they would be comparing.

However, I have understood one point. The calculations for the analytical values arising out of comparing two positions involve quite complex business logic which is being done through java engines. If the same has to be done in database, then stored procedures would be required.

So in such a scenario how do you think should these be modeled?

Best Regards