CODEWITHSUNDEEP
olapbusiness-intelligencepentahostar-schema
elkarel
elkarel

Reputation: 753

sum and distinct-count measures (star schema design koan)

I am quite a beginner in Data Warehouse Design. I have red some theory, but recently met a practical problem with a design of a OLAP cube. I use star schema.
Lets say I have 2 dimension tables and 1 fact table:

Dimension Gazetteer:
dimension_id
country_name
province_name
district_name

Dimension Device:
dimension_id
device_category
device_subcategory

Fact table:
gazetteer_id
device_dimension_id
hazard_id (measure column)
area_m2 (measure column)

A "business object" (which is a mine field actually) can have multiple devices, is located in a single location (Gazetteer) and ocuppies X square meters.
So in order to know which device categories there are, I created a fact per each device in hazard like this:

+--------------+---------------------+-----------------------+-----------+
| gazetteer_id | device_dimension_id | hazard_id             | area_m2   |
+--------------+---------------------+-----------------------+-----------+
| 123          | 321                 | 0a0a-502c-11aa1331e98 | 6000      |
+--------------+---------------------+-----------------------+-----------+
| 123          | 654                 | 0a0a-502c-11aa1331e98 | 6000      |
+--------------+---------------------+-----------------------+-----------+
| 123          | 987                 | 0a0a-502c-11aa1331e98 | 6000      |
+--------------+---------------------+-----------------------+-----------+

I defined a measure "number of hazards" as distinct-count of hazard_id.
I also defined a "total area occupied" measure as a sum of area_m2.
Now I can use the dimension gazetteer and device and know how many hazards there are with given dimension members.
But the problem is the area_m2: because it is defined as a sum, it gives a value n-times higher than the actual area, where n is th number of devices of the hazard object. For example, with the data above would give 18000m2.
How would you solve this problem?

I am using the Pentaho stack.

Thanks in advance

Upvotes: 0

Views: 1270

Answers (1)

Jamie
Jamie

Reputation: 816

[moved from comment]

If a hazard-id is a minefield, and you're looking at mines-by-region(gazetter) & size-of-minefields-by-gazetteer, maybe you could make a Hazard dimension, which holds the area of the Hazard; or possibly make a Null-device entry in the DeviceDimension table, and only the Null-device entry gets the area_m2 set, the real devices get area_m2=0.

If you need to answer queries like: total area of minefields containing device 321, the second approach isn't going to easily answer these questions, which suggests that making a Hazard dimension might be a better approach.

I would also consider adding a device-count fact, which could have the num devices of each type per hazard.

Upvotes: 1

Related Questions