Mixed Grain Fact Tables
Granularity is determined by the number of foreign keys in the fact table. For instance, the grain of your Statement of Comprehensive Income (SOCI) depth could be by Country, Date or Legal Entity. That is to say, each transaction is recorded by each of the above. However, frequently items in the financial statements are explainable at one grain whilst others are not. This article covers:
Outlining the problem of mixed grain
Solution 1: Activity Based Costing (ABC)
Solution 2: Dummy Surrogate and Foreign Keys
Solution 3: Splitting into Different Fact Tables
The Problem of Mixed Grain
Within the SOCI, Revenue could be explainable by customer. Therefore, a foreign key for customer is introduced into the fact table. This, however, introduces a problem. Namely, whilst Revenue can be explained by customer, other items in the fact table cannot. For instance, Depreciation in the SOCI is not explained by customer. This creates a multiple grain fact table which, as explained by Kimball and Ross, is not an applicable approach:
One of the core tenants of dimensional modelling is that all the measurement rows in a fact table must be of the same grain
The Data Warehouse Toolkit 3rd edition
To overcome this problem, there are a range of solutions.
Solution 1: Activity Based Costing (ABC)
ABC is a well known method within the accounting world in which even though ledgers are not recorded at a given granularity, they are allocated out to that grain. For instance, even though depreciation is not recorded at a customer grain, it could be allocated at this granularity. This could be done using a range of different methodologies. i.e., allocated depreciation based on customer costs, customer revenue or some other allocation such as expected used of the assets being depreciated.
Solution 2: Dummy Surrogate and Foreign Keys
When a item from the SOCI is not applicable at a given level of granularity, it can be assigned a dummy Foreign Keys. This dummy Foreign Key then relates to a Surrogate Key the attributes of which are listed as Non Applicable. For instance, the "Customer Key" can remain within the SOCI, but for the depreciation line it can be assigned to a Customer the name of which is "NA". Effectively this creates a customer called "NA" which receives the full allocation of depreciation. When slicing and dicing by all other customers the depreciation line disappears
Solution 3: Splitting into Different Fact Tables
The third solution involves creating different fact tables at different grains. For instance, Revenue belongs to one fact table which is stored at the grain of Customer, whilst the depreciation is assigned at a different grain which does not contain a foreign key for customer. The problem with this approach is that it creates a more complex model with a new fact table for each grain. It also doesn't overcome the problem of slicing and dicing by a dimension which is not applicable. For instance, slicing and dicing depreciation by Customer this will return a result showing the full value for each customer. This may be the desired behaviour, but it can easily lead to misunderstanding.
Conclusion
Mixed Grain in financial accounts is highly likely. It maybe that the financial system has already dealt with these issues, for instance assigning depreciation to a NA customer. However, you need to pay close attention to the requirements; it maybe the very reason you're being asked to produce in Power BI is to overcome these limitations, I.e. Show me for each existing customer Profit Before Interest and tax (PBIT). In these cases you'll need to employ an appropriate to methodology to overcome the financial systems limitations.
Bình luận