View Full Version : How to connect a time period with the time di

02-09-2003, 06:22 PM
I have a fact table with start- and end- date of the given periods. I created the time dimension with years and months. Question: Is there any possibility to connect them in the schema, so I can see the number of facts for each month/year laying between start- and end- date?
SQL doing similar thing would look like this:
select view_time.month_txt,
(select count(id) from fact where view_time.month_date > fact.start_date and view_time.month_date < fact.end_date) as cnt
from view_time, fact
group by view_time.month_txt, view_time.month_date, fact.end_date

02-09-2003, 09:03 PM
Easy part of the problem first: to see the number of facts, create a measure using the COUNT function, based upon any mandatory column. Or create a measure using SUM based upon the constant '1'.
Now the hard part. The multidimensional approach requires that every row in the fact table belongs to precisely one (leaf-level) member of each dimension, so your schema needs to be adjusted to fit into that model.
If the granularity of time periods is low, you can split each of your fact records into a record for each time period. Yours seems to have a granularity of a month, so that would be ok.
A concrete example is employees (empno, hire_date, fire_date, salary). You would split each employee into a record for each month, which you could conveniently call paycheck(empno, date, amount). This fits neatly into a time dimension.
If the granularity is a shorter period, you could use the approach like that to measure warehouse inventory: create, and base your cube upon, a fact table which contains a subtotal for each time period. One peculiar thing about this kind of fact table is that the measure doesn't roll up by summing: for example, the inventory on hand at the end of Q1 is not the sum of the inventory on hand at the end of January, February, March, but is just the inventory on hand at the end of March.
Hope the above gave you some ideas. Since I'm not the world's greatest expert on dimensional modeling, I suggest you check out a book such as Ralph Kimball's "Data Warehouse Toolkit".

02-18-2003, 12:06 AM
Thanks a lot Julian!
I found out that it's impossible aggregate over 'living' facts i.e facts that exist in periods of dimensions like totals, tenure etc.
What you can do is to pick one or more reference points in such a dimension (like reference date) and measure the difference, sum, etc.
As soon as you try to measure over an open period (of time) you will see weird numbers. And what's more interesting - no controller wants to see headcounts over such a period of time.
It was my fault not to understand the basics of cubes, now I have to find out how to put it all into mdx, so we can count over reference points only.
Wish you luck,

02-18-2003, 11:14 PM
Another discussion just started regarding semi-additive measures. You may be interested in this: