企业绩效管理网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 827|回复: 11

Version / Scenario dimension best practice

[复制链接]

62

主题

411

帖子

557

积分

高级会员

Rank: 4

积分
557
QQ
发表于 2014-3-18 05:14:02 | 显示全部楼层 |阅读模式
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: ) - feel free to point me in the right direction!

Many thanks,
Zac

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复

使用道具 举报

66

主题

382

帖子

540

积分

高级会员

Rank: 4

积分
540
发表于 2014-3-18 06:29:36 | 显示全部楼层
Code: SKIPCHECK;

['Risk Exposure'] = N:

     IF ( !Regulator @= 'FCA' ,
          ['Drawn Balance'] + ['Undrawn Balance'] ,
          IF ( !Regulator @= 'Fed' ,
               ['Drawn Balance'] + 0.5 * ['Undrawn Balance'] ,
               ['Drawn Balance']
          )
     );

FEEDERS;

['Drawn Balance'] => ['Risk Exposure'];

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复 支持 反对

使用道具 举报

74

主题

392

帖子

562

积分

高级会员

Rank: 4

积分
562
QQ
发表于 2014-3-18 07:02:18 | 显示全部楼层
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.
回复 支持 反对

使用道具 举报

87

主题

397

帖子

595

积分

高级会员

Rank: 4

积分
595
QQ
发表于 2014-3-18 08:18:29 | 显示全部楼层
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
回复 支持 反对

使用道具 举报

76

主题

403

帖子

586

积分

高级会员

Rank: 4

积分
586
QQ
发表于 2014-3-18 08:27:37 | 显示全部楼层
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.
回复 支持 反对

使用道具 举报

77

主题

397

帖子

570

积分

高级会员

Rank: 4

积分
570
QQ
发表于 2014-3-18 08:31:31 | 显示全部楼层
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
回复 支持 反对

使用道具 举报

66

主题

378

帖子

540

积分

高级会员

Rank: 4

积分
540
QQ
发表于 2014-3-18 08:37:26 | 显示全部楼层
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?
回复 支持 反对

使用道具 举报

80

主题

402

帖子

587

积分

高级会员

Rank: 4

积分
587
QQ
发表于 2014-3-18 08:43:04 | 显示全部楼层
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.
回复 支持 反对

使用道具 举报

90

主题

419

帖子

614

积分

高级会员

Rank: 4

积分
614
QQ
发表于 2014-3-18 08:55:01 | 显示全部楼层
Can a given exposure fall under more than one regulator?
回复 支持 反对

使用道具 举报

78

主题

403

帖子

578

积分

高级会员

Rank: 4

积分
578
QQ
发表于 2014-3-18 09:04:13 | 显示全部楼层
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.
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|小黑屋|企业绩效管理网 ( 京ICP备14007298号   

GMT+8, 2020-7-5 16:18 , Processed in 0.148251 second(s), 12 queries , Memcache On.

Powered by Discuz! X3.1 Licensed

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表