Design of fact table(s) and dimensions tables for data warehouse

To quote Ralph Kimball, "declaring the grain" is:

saying exactly what a fact table record represents

You need to choose which entity from you list above represents the grain. You could choose any of them, however claim_line_items looks the best candidate to me as this is at the lowest level (it is the only table with no children).

If you make claims your grain, then it won't be possible to report on what the individual line items are. This may be what the business wants to track (and they don't care about line items). However, it'll be very difficult to change the grain to line items at some point in the future if they change their mind. Whereas you can make the grain line items and provide aggregations which show summaries at the claim level.

For a more detailed discussion about this (again by Kimball) go here.

This is probably a bad idea, and definitely not standard good practice.

In a typical fact/dimension schema for a data warehouse, based on Ralph Kimball's methodology, the fact tables should always store data at the most granular level possible. Your example fact table is storing data at two different levels of granularity. This is bad, if only because it's repeating the same fact twice. Fact tables are intended to get very deep (many rows) and duplication by design will not scale well. I'm going to answer your question, but also show how you can avoid that duplication.

Another principle of fact table design is that they should be as narrow as possible (few columns), and the columns there are should be as compact as possible (least storage space). This minimises the storage impact of tables with so many rows.

So storing strings in the fact table should be avoided: instead, store an integer foreign key to a dimension table, that holds the larger string values. Each string is thus only stored once (in the shallow, few rows, dimension table), with the smaller int being repeated many times in the deep fact table.

Once you decide that, it follows that actually, you only need one foreign key dimension ID column to represent both your City and State columns - because if you know the city, you inherently know the state too, each city is only in one state. These descriptors (and Country) should be columns in the Location (or Geography, or Territory, or whatever) dimension. The metrics you refer to also belong in columns in the dimension table. The dimension has one primary key per City.

Fact table

LocationId    Sales
1             100
2             100

Location dimension table

LocationId    City        State        Country        SalesPeopleInCity    StateExpenses
1             Miami       Florida      USA            
2             Tampa       Florida      USA

Your example fact table now needs only the first two rows - duplication problem gone! If you want to find total sales for Florida, you join the fact table to the dimension table, restrict on the State column, and group up to find the sum of sales.

(Aside: with a good indexing strategy on a decent database platform, that probably won't take long at all, even if your fact table gets really big (millions/billions of rows). If lots of people need to find lots of different totals lots of times...? That's why pre-aggregated OLAP cubing technologies exist.)

It's much more acceptable for the dimension table to contain blanks (or nulls) in some columns if need be, and also to contain repeated values. The rationale for this is that the dimension table never has that many rows relative to the fact table: storage is not a problem. Because this is not a transactional system database, consistency is also not a problem.

So your metrics that apply only to State level can be repeated for every applicable City row in the dimension table. You don't need any blanks at all in this simple example. By repeating the values on every applicable row, you can always get the State-level value instantly no matter which City is being analysed.

Location dimension table

LocationId    City        State        Country        SalesPeopleInCity    StateExpenses
1             Miami       Florida      USA            6                    $2500
2             Tampa       Florida      USA            2                    $750

This is all assuming you wish to use a simple "star schema" data model. An alternative way to model this would be using a "snowflake schema", treating the location dimension as a hierarchy and sorting data about each level of the hierarchy (City, State, Country) in a different table. Metrics would then go in the appropriate level table, or perhaps in some generic attribute (key/value) table.

There are pros and cons to each approach and it depends mainly on how you will use the data.