0

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 ?

Justin
  • 393
  • 1
  • 7
  • 21
  • Can you give an example of desired results, and source data that makes it a problem? I can't picture why the different granularity is an issue, or how the granularity is different in the first place. – Tab Alleman Feb 16 '16 at 15:50
  • The thing is I want to create only one fact table instead of two fact table. So I want to combine Factpopulation and FactMettingCount togather in one table and do analysis. – Justin Feb 16 '16 at 17:29
  • Why would you want to do that? It sounds like poor design to me, unless there's some reason that isn't clear from your question so far. – Tab Alleman Feb 16 '16 at 18:16
  • What is FactPopulation? You need to work out an algorithm to push it down to month? Is it just the same yearly figure or is it it the yearly figure divided by 12 or is there some more sophisticated algorithm you could use. Do all dimensions connect to all facts or not? What tool are you using for analysis or are you just writing SQL queries? – Nick.Mc Feb 18 '16 at 00:25

2 Answers2

0

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.

Matt
  • 1,441
  • 1
  • 15
  • 29
0

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.

Ron Dunn
  • 2,971
  • 20
  • 27
  • Factpopulation is count of the population in different cities based on year. Where as FactMeeting count bases on every day and I want to combine those to for analysis per month and per year. – Justin Feb 17 '16 at 22:22