Is my understanding of Fact table granularity correct?

Myself and another DBA at our company are tasked with reviewing a database design that a vendor has developed for us. The vendor has said they use Kimball as the basis for their design. (NOTE: I am not looking for arguments of Kimball vs Inmon, etc.) They have designed a mart with multiple facts and dimensions.

Now in all fairness, our company has never designed a single mart. We have always had the consultants do it. And we have never been sent to classes or anything. So our knowledge of warehousing/marts/dimensional modelling, etc. is based on what little experience we have, what we can find on the Internet, and self reading (we have Inmon's and Kimball's books and are trying to make our way through them).

Now that the stage is set for my level of knowledge, we come to the design challenge.

There is a Fact table called "Claim Loss Statistics" (this is for insurance). And they are trying to capture both the payments for claims (rolled up to a monthly level), and then money in the reserves (kind of like a bank account for claims). They wish to see the monthly amounts for payments (no biggie). But they wish to see the account current balance of the reserves.

I'll give a pictorial example.

Say we set up $1000 USD in reserves for a claim. This gets set aside (so in some respects it functions kind of like a bank account).

In October 2014, we don't pay out anything yet. So the business wants to see the payments and the reserve balance at the end of October.

-----------------------------------------------
-  MONTH_YEAR  -  PAYMENTS -  RESERVE_BALANCE -
-----------------------------------------------
-      102014  -      0.00 -          1000.00 -
-----------------------------------------------

Then November comes along. We make out payments of $100, $150, and $75 dollars. They wish to see those amounts aggregated and the reserve at the balance as follows:

```
-----------------------------------------------

• MONTH_YEAR - PAYMENTS - RESERVE_BALANCE -

-

You are correct: "different grains must not be mixed in the same fact table".

But reserve balance at the end of the month and sum of payments at the end of the month are at the same grain. It just one of facts is semi-additive. Type of fact (additive or not) does not define table's grain.

From what you describing, I see your grain as "monthly claim snapshot", which makes your fact table "Periodic Snapshot Fact table".

In this article Kimball have an example of additive and semi-additive facts in the same fact table.

Here is example of periodic snapshot with semi-additive facts from The Data Warehouse Toolkit (page 116):

Best practice is to have transactional fact table that will reflect every change in reserve (payments and adjustments) on the lowest atomic level.
When you deal with claims, often the atomic level is not claim but sub-claim (your insurance company may have its own term for it).
Generally each sub-claim will represent different party to the claim and payments / reserves for each party.
For example, there may be no payments to the insured, but payments to not-insured by your company injured person and payments to the hospital and attorney.

Depending on performance of your BI tool you may use transactional fact table directly to get monthly payments and balances. Or you may update periodic snapshot fact table from transactional daily or at the end of the month.

Ability of handling semi-additive facts will depend on what BI layer you are using.
Some tools able to deal easily with semi-additive facts and some not.

Kimball's main book (The Data Warehouse Toolkit) has full chapter (16) on insurance.

StackExchange Database Administrators Q#77402, answer score: 7