Can a 'fact grain' attribute be null?

[nash]

I understand that we build a special value (something "N/A") in each dimension to cater for certain scenarios (and to enforce FK). It makes sense for optional (non-key, non fact-grain) attributes. But is it acceptable for the grain attributes too?

I have a fact table that stores volumes (counts only). It has 5 attributes that define its grain. Now we are adding a new data source, which counts exactly the same volumes for the same concept, but only 4 out of 5 fact grain attributes are applicable to its count fact.
Is ramming this into existing fact table ok (and always assign "N/A" to 5th attribute of the grain)? Or
this calls for a new fact table with 4 attributes being the grain?

[BoxesAndLines]

I don't see any issue. I normally put a surrogate key on my fact tables anyways. The grain is enforced with an alternate key which can have nullable columns.

[nash]

The grain is enforced with an alternate key which can have nullable columns.

Thank for your reply BoxesAndLines and I have the same view too, but I must play a bit of a devil's advocate here

By having 'nullables' in the alternate key, are we mixing grain?
Aren't we saying, in my above example, some facts in the fact table are asserted at the intersection of 5 dimensions while other at 4, and so on?

Pls comment...thanks

[BoxesAndLines]

Are the facts at the same grain or a different grain? I.E. If there are multiple facts when you add the final dimension then you have a mixed grain and that is a new table.

[ngalemmo]

The grain is enforced with an alternate key which can have nullable columns.

Thank for your reply BoxesAndLines and I have the same view too, but I must play a bit of a devil's advocate here

By having 'nullables' in the alternate key, are we mixing grain?
Aren't we saying, in my above example, some facts in the fact table are asserted at the intersection of 5 dimensions while other at 4, and so on?

Pls comment...thanks

The dimensions of a fact table do not necessarily define the level of detail of the facts. It depends on how you define grain. Grain is either conceptual (defined by the dimensions of the fact) or it is defined by the event represented by the facts (true level of detail), which may be a subset of the dimensions associated with the fact. For example, in retail someone may be purchasing something as a gift and, as part of that transaction information is collected on the recipient. As part of the sales fact table, there may be a 'giftee' dimension. While the giftee dimension adds context, it doesn't change the level of detail of the facts. One can reasonably argue that the addition of a giftee dimension does not change the grain of the fact.

If the missing dimension truly affects what the measures represent (in this case, an aggregate of those measures with all dimensions), then you are mixing grains and should use another fact table. However, if the missing dimension does not alter the level of detail of the measures, then having a 'not applicable' dimension row works fine.

[nash]

Grain is either conceptual (defined by the dimensions of the fact) or it is defined by the event represented by the facts (true level of detail), which may be a subset of the dimensions associated with the fact. For example, in retail someone may be purchasing something as a gift and, as part of that transaction information is collected on the recipient. As part of the sales fact table, there may be a 'giftee' dimension. While the giftee dimension adds context, it doesn't change the level of detail of the facts. One can reasonably argue that the addition of a giftee dimension does not change the grain of the fact.

I think if I understood your point correctly, you are talking about the minimal grain (the true key). Right? If so then I get that, and it's not an issue at all. As long as true grain is not broken, you could add any number of dimensions.

I will illustrate my problem with an example from retail order process. Say we are interested in monthly order amount and order volume (i.e. #orders) by order type and customer. Our Grain will be Month_Id, Order_Type, Cust_Id with #Orders and $Orders facts that are true to that grain. So far so good.

Let's say if there is another new ordering system that does not provide customer details (may be it's not relevant to their ordering process at all). From this new ordering system we need the same data (without customer info). Now what should we do? Should we:
a. use our existing order fact to store this data. And for customer dimension, we always default to N/A? Or
b. create a new order fact which does not have the customer dimension at all (because it does not make sense for these orders).

What would be your preferrence?

I am highly interested in your views. This example is representative of the real issue I am facing, which is quite large (in terms of number of dimension, number of source systems and data volumes).

Thanks for your time.

[ngalemmo]

The situation you describe is very common in retail. You would usually reference an 'unknown' customer (option A).

[Sponsored content]