How to solve if there are two different granularity in data mart ?

Question

We are creating data mart from data source that we are given. I have created following tables in data mart.

FactPopulation (Fact table which has population data per year)
FactMeeting count (Fact table which has event info occur almost everyday 2005-)
DimMeeting Topic (Name of Meetings)
DimGeography (Place)
DimDate (Date Dimension)

So if I want to do analysis Population and meeting county have different granularity. I want to do analysis by year and by month. Is it any way to combine both fact table into one or any suggestion ?

Answer 1

Once the data is rolled up to a certain granularity the easiest way to go deeper than the grain of say the FactMeeting grain would be to look back at the source and produce another rollup at the grain you want. Consider creating a FactMeetingByCounty table which is rolled so the count metric is by county. Then produce the combined data you are after as a single table.

The alternative may be to produce the table you are after utilizing this "new grain logic" and the logic used in the creation of the FactPopulation fact.

Lots of options here.

Answer 2

What is FactPopulation? From the name, I am guessing it represents the population of a place over time.

Is that place the same as DimGeography? If so, make population an attribute of this dimension, and make it a Type-2 slowly changing dimension to account for the change in population over time.

EDIT AFTER COMMENT 1:

It would help if there was some example data, or more complete column names in the facts and dimensions you suggested, but my interpretation is that a model like this would solve your problem:

dim_event (id,name,...)
dim_geography (id,country,city,...,population,from_date,to_date)
dim_date (id,cal_date,cal_year,cal_month,...)
fact_meeting (event_id,geography_id,date_id,...,attendee_count)

Are you familiar with slowly changing dimensions? A type-2 dimension will handle the change in population over time.

You haven't given examples of the business queries that you are trying to satisfy, but if you post some in the questions I will update this answer with some SQL to show you how they are satisfied by this model.