3 Added example tables and OLAP reference
source | link

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.

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 probably a bad idea.  

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.

Your example fact table now needs only the first two rows. 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.

It's much more acceptable for the dimension table to contain blanks (or nulls) in some columns, 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.

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.

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
2 clarify deduplication of fact table ; added 135 characters in body
source | link

This is probably a bad idea.

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.

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 two 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.

Your example fact table now needs only the first two rows. 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.

It's much more acceptable for the dimension table to contain blanks (or nulls) in some columns, 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.

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.

This is probably a bad idea.

In a typical fact/dimension schema, 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.

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 should be two columns in the Location (or Geography, or Territory, or whatever) dimension. The metrics you refer to also belong in columns in the dimension table.

It's much more acceptable for the dimension table to contain blanks (or nulls) in some columns, 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.

So your metrics that apply only to State level can be repeated for every applicable row in the dimension table.

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 in a different table. There are pros and cons to each approach and it depends mainly on how you will use the data.

This is probably a bad idea.

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.

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.

Your example fact table now needs only the first two rows. 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.

It's much more acceptable for the dimension table to contain blanks (or nulls) in some columns, 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.

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.

1
source | link

This is probably a bad idea.

In a typical fact/dimension schema, 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.

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 should be two columns in the Location (or Geography, or Territory, or whatever) dimension. The metrics you refer to also belong in columns in the dimension table.

It's much more acceptable for the dimension table to contain blanks (or nulls) in some columns, 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.

So your metrics that apply only to State level can be repeated for every applicable row in the dimension table.

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 in a different table. There are pros and cons to each approach and it depends mainly on how you will use the data.