|
发表于 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. |
|