In this method, we try to add or create fields which can match the granularity of the fact tables. We add fields in the fact table which has the least granularity of the data. The reason why we do it in the fact table which has the least level of data is because by using the least level of data you can easily arrive at the upper level of data, namely upper hierarchy. However, it is not possible to get the least level of data from upper hierarchy.

For example, you can get the product category from product field, but you cannot do vice versa, because if you do that, the data sanctity may get lost and you may create duplicate records in the fact table. New fields in the fact table can be added by joining the dimension table, which can get you the higher level of hierarchy or you can derive that field from existing field in the fact table.

If you refer to the earlier example of the sales and budget table, you can see that the budget table is at higher granularity, namely at product category and month level, whereas the sales table is at lower granularity, that is at product and salesdate level. So to match the granularity of tables, we will add new fields, like product category and month in sales table, which has the lowest granularity.

Let us see how to code this method. Following is the script for the same:

As you can see in the preceding script, we have created two fields, that is the Product Category and Month fields, in the Sales table to match the granularity of the Budget table. After making this change, the data model looks as follows:

>

After matching the granularity, we can see that it has created synthetic tables in the data model. Nothing to worry about there, because we know how to solve this using the method learnt in the previous section, that is by concatenating tables or by creating link table.