Can I use blank dimensions in a fact table?

For instance, total sales in Florida are 200.

Sales    City    State
100      Miami
100      Tampa
200              Florida

Reason why is the total for the city may have metrics, like number of salespeople only available in city records. Total for state may have metrics, like expenses, only available in state, and no real way to break it down to cities.

  • How is your geography dimension structured?
    – Tom V
    May 13 '16 at 5:34
  • City | State | Country
    – stampede76
    May 13 '16 at 11:45

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.

  • What if --> 1) StateExpenses is only available total for the entire state, not each city. 2) It changes every week 3) Different data sources disagree? I think I'm facing multiple problems here, and I'm going to break this down in an update to my question. Ultimately, I think I'm going to have to join multiple fact tables by date.
    – stampede76
    May 13 '16 at 22:20
  • Responses in order: 1) That's just a matter of how you interpret/use the StateExpenses column; it's denormalised, doesn't have to be a property of a city. 2) Sounds like Expenses might actually be another fact table then, not a dimension 3) Totally unrelated problem, worthy of a separate question altogether perhaps. May 13 '16 at 22:24
  • If you think you need to join multiple fact tables, you almost certainly have the wrong schema design. May 13 '16 at 22:26
  • Got it now, thank you. Splitting up the dimensions based on granularity helped. Same with one fact one table. About the joins, separate data sources made me think I could drill down on data, but the data just wasn't there.
    – stampede76
    May 14 '16 at 16:58
  • No problem. One fact table can have multiple facts, so long as the primary key of those facts is the same. Eg modelling grocery store sales, one date and product (primary key) might have 3 numeric facts: number of units sold, total sales value for that product that day, and total mass (pounds or kilograms) of that product sold. But since your sales fact is keyed on city, while your expenses fact is keyed on state, they'd need different fact tables. May 15 '16 at 8:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.