TM1 Forum

Hi,

I'm designing an application to pull in banking risk data using TI from an star schema Oracle database, and subsequently build rules on top of it. The main target cube has around 10 dimensions, all contained in the data except for a 'Version' or 'Scenario'-style dimension. The 'version' dimension we want in this case is 'Regulator', and the rules will vary by regulator, though the incoming fact data does not vary by regulator.

For example, we may have two regulators: "FCA" and "Fed" (UK and US regulators). Different regulatory risk rules have slightly different formulae, eg under regulator FCA, we might have (Risk Exposure = Drawn Balance + Undrawn Balance) whereas under regulator Fed, we might have (Risk Exposure = Drawn Balance + 0.5 * Undrawn Balance), and then perhaps some catch-all rule such as (Risk Exposure = Drawn Balance) for all others.

One way to do this in a non-dimensional system would of course be to have multiple Measures:

['FCA Risk Exposure'] = N: ['Drawn Balance'] + ['Undrawn Balance'];
['Fed Risk Exposure'] = N: ['Drawn Balance'] + 0.5 * ['Undrawn Balance'];
['Other Risk Exposure'] = N: ['Drawn Balance'];

But this is not flexible and does not make use of the Regulator dimension therefore we cannot slice/dice the data. Ideally I think in TM1 we would do something like this:

['Risk Exposure', 'FCA'] = N: ['Drawn Balance'] + ['Undrawn Balance'];
['Risk Exposure', 'Fed'] = N: ['Drawn Balance'] + 0.5 * ['Undrawn Balance'];
['Risk Exposure'] = N: ['Drawn Balance'];

However, the incoming data does not hold Regulator - it has only one record across all regulators. Finally, my question: What is the best practice here in TM1?

I'm guessing: include the dimension Regulator in the target cube, and load the fact data into a cube multiple times with TI: once for every regulator. Then rules can vary by Regulator with different results also being stored in the rules, which can also be sliced/diced by Regulator.

The main drawback I can see with this approach would be that if Regulator is not explicitly set on a report (ie if the parent 'All Regulators is included rather than a specific regulator), the data will be aggregated automatically and will be double-counted. Should we get around this by telling TM1 not to aggregate over the Regulator dimension?

Apologies for what may be a basic question for this kind of analysis. I'm fairly new to TM1 so just getting used to best practice for some of these techniques. I've searched the forums but couldn't find a thread quite suitable (this came close but didn't have a satisfactory conclusion: http://www.tm1forum.com/viewtopic.php?p=15218) - feel free to point me in the right direction!

Many thanks,
Zac

What you are tying to do is very simple when the structure of the calculation stays the same for each regulator, only the ratios or factors change. When the structure of the formula is different by regulator, there really is no best practice. You do whatever you have to do to make it as easy to maintain as possible, while organizing the data in such a way as to facilitate the desired slicing and dicing. Your safest bet will probably be to structure your rules as Matt has shown even though it's going to be a bugger to maintain.

Actually, after rereading the question, it's probably better to do it in TI since "regulator" is probably a fixed piece of data (rather than something you'd manipulate in TM1 in real-time). I'm also confused about how the data is coming to TM1. Is there one load file with all regulators in it? If so, you're going to have to figure out a programmatic way to identify which is which (possibly using an attribute or lookup cube, if necessary). If you'll have multiple data sources, you can just customize each process to do the correct calculation.

I'm not following your comment about double-counting. If you display n-level and c-level data in a single report, you've going to have double-counting, more or less by definition. You can always eliminate the "All Regulators" consolidation or force it to zero with a rule if you have an unusual situation.

Matt

Thanks Matt and Tomok for your replies.

mattgoff wrote: I'm also confused about how the data is coming to TM1. Is there one load file with all regulators in it?

I think perhaps I should clarify what I mean better by "the incoming fact data does not vary by regulator".

Each fact record (an 'exposure' which is basically something like a loan) is not associated with a regulator in the data - there is no Regulator field or dimension. From the point of view of the bank, the regulator does not change what the balance is and at this stage it is not associated. There is only one record per exposure. There is also no need to store it multiple times by regulator since this data is identical.

However, when we come to applying rules, the same exposure might need a risk amount calculated in multiple ways. We might need to report this amount to one or many different regulators, each using a different rule. If we decide we need to do this for all exposures, we just need to know a list of Regulators to calculate for, which will be set in an unlinked table which would be used to build the Regulator dimension (but - not linked to the fact data).

(If we decide we need to report a subset of exposures to a particular Regulator, this is a little more complex but simply requires a mapping table to identify which exposures report to which regulators. Still the fact data would only contain one record per exposure and Regulator would not be a field.)

Ideally we would like to be able to use Regulator as a dimension to slice/dice the data so I can compare the Risk Exposure amounts (and other measures that build on this) side by side in a report. This means that using a single Measure that varies by Regulator is preferable to multiple Measures.

tomok wrote:Your safest bet will probably be to structure your rules as Matt has shown even though it's going to be a bugger to maintain.

I appreciate the sample code you supplied Matt, for applying the rule differently by regulator, but since there is only one exposure record in the data, the question is more about a best practice (or a good technique) for how to load a cube with an 'analysis' or 'scenario' dimension like this that isn't contained in the source data. I can see a few different options for us:
a) Do not include Regulator as a dimension in the cube, and build separate Measures for each Regulator (I'm trying to avoid this).
b) Include Regulator as a dimension in the cube; load the cube multiple times, so if (for example) there are two Regulators as before, we load all the fact data once for FCA and all the fact data for Fed etc. We then have two sets of 'base' data across the Regulator dimension which are initially identical over which we can then apply different rules, as per your code.
c) Include Regulator as a dimension in the cube; load the data once into an 'Undefined' Regulator category. Then use this area of the cube as a set of base Measures to do further calculations. Then, however, for our example your code would need to be altered to reference 'Drawn Balance' and 'Undrawn Balance' for Regulator 'Undefined' (I guess using a DB function).

Are there any other options?

tomok wrote:What you are tying to do is very simple when the structure of the calculation stays the same for each regulator, only the ratios or factors change.

I see this actually, and we can probably do that with most, if not all, of our rules. So that in our example for instance, we would use 'Drawn Balance' + 'Undrawn Balance' * 'Undrawn Balance Factor', and use a lookup cube to get the 'Undrawn Balance Factor' which varies by Regulator. This is a great suggestion which we'll use in the design, but I think still doesn't on its own answer how we load the data into the main cube and vary results by Regulator, as above.

mattgoff wrote:I'm not following your comment about double-counting. If you display n-level and c-level data in a single report, you've going to have double-counting, more or less by definition. You can always eliminate the "All Regulators" consolidation or force it to zero with a rule if you have an unusual situation.

What I mean here is, if we load the data multiple times (as per option b above), then data which would normally be aggregable to the top level of all dimensions will now double-count. For example, 'Drawn Balance' can usually be aggregated over any normal dimension to get a total drawn balance (eg Drawn Balance over All Industries, All Regions, All currencies etc makes sense). However, if we have the data multiple times (once per regulator for each exposure) then aggregating over the Regulators dimension will double-count. I guess your answer is that we can eliminate the 'All Regulators' consolidation -> would it be better to not have a consolidation element at all (ie do not have 'All Regulators') or to include this and set the weight of child elements to 0? (still getting the hang of the best way to do this kind of thing in TM1). Essentially we need to 'force' the user to choose which Regulator the rules need to be applied for - they can't be applied for all at once!

Hope this clears things up a bit more.

Your advice is much appreciated!

Thanks,
Zac

p.s. Sorry for the long post.

Hi,

I am not sure I fully grasp all the issues here...

I would think though that one data load is enough then use rules to reference.

If you want the Regulators as a dimension, then you could add a dummy element to load into, then rules reference this for the calculations.

['Risk Exposure'] = N:
IF( !Regulator@<>'Dummy',
IF ( !Regulator @= 'FCA' ,
['Drawn Balance','Dummy'] + ['Undrawn Balance','Dummy'] ,
IF ( !Regulator @= 'Fed' ,
['Drawn Balance','Dummy'] + 0.5 * ['Undrawn Balance','Dummy'] ,
['Drawn Balance','Dummy']
)
)
CONTINUE
);

FEEDERS;

If you want to show the 'Drawn Balance' and 'Undrawn Balance' by each Regulator then you could write a rule to show that.
As I say I am not sure I really got what the issues are but thought this may help based on what I read.

Regards
JD

Thanks JD, so I guess this is like my option c - thanks for the example syntax. My concern had been that 'base measures' such as Drawn Balance would not then be defined for each regulator but I guess it would be pretty trivial to define these explicitly as well.

I've noticed that the syntax being recommended here is consistently if, then, else rather than using the area section of the rules. Is there any particular reason for this? I'd read that using the area to define when rules are performed will have efficiency (and perhaps readability?) gains for large cubes, since it prevents the rules being evaluated in areas that are irrelevant. Any thoughts on this?

You don't have to use the IF THEN ELSE structure, you can just specify each regulator on the left hand side of the rule. This is probably how I would do it though I doubt it would make a bug difference, performance-wise.

Can a given exposure fall under more than one regulator?

Matt, yes an exposure could fall under more than one regulator. For a large multinational bank an exposure needs to be reported against several different regulators using different rules.

zachinton wrote:Matt, yes an exposure could fall under more than one regulator. For a large multinational bank an exposure needs to be reported against several different regulators using different rules.

In that case and if you need the ability to slice along regulator, I'd probably build a second "Risk Exposure" cube. You can always designate a "primary" regulator per exposure and pull that into your original cube. Or you could automatically pull in an average or max value, depending on your conservativeness. If you don't need to slice along regulator, I'd just have multiple "risk exposure" measures.

Matt

If the calculation logic is as straight forward as your examples (maybe not if you are calculating risk) you don't even need a Regulator dimension as all your logic can be done in the consolidation structure of your measure dimension.

C element "FCA Risk Exposure" = Drawn Balance' + 'Undrawn Balance'
C Element "Fed Risk Exposure" = Drawn Balance' + (0.5 weight).'Undrawn Balance'

If your logic is always simple weighted sums then this would be a better solution. Consolidations beat rules every time and less dimensions are better (assuming you still meet requirements).

Even if your logic is more complex and you can't get to the result using a consolidation I would still consider dropping the regulator dimension and having the different flavours of risk in your measure dim.
FCA Risk Exposure = N: Drawn Balance' + 'Undrawn Balance'
Fed Risk Exposure = N: Drawn Balance' + 0.5* 'Undrawn Balance'

Not sure if the regulator dim came from you or the previous posters but you only really want a regulator dim if nearly all your measures are by regulator.

(basically what Matt said but using many more words...)

If you don't need to slice along regulator, I'd just have multiple "risk exposure" measures.

Cheers,