Facts that only exist in PERIODS of time

Facts that only exist in PERIODS of time

Hi, a difficult one:
We have a datawarehouse with facts in a fact table that `exist' only
in a time period, defined by using begin and end dates. Example data
could be:

FACT TABLE
Key | Name | Begindate | EndDate | LengthInMonths
-----------------------------------------------
1,  'Pete', '20010101', '20010630', '6'
2,  'Mary', '20010322', '20010622', '3'
3,  'Jane', '20010413', '20020413', '12'

Question now could be: Who was present in February? A: Pete = 1 in
total

or Q: How many persons were present from 5th of April till 20th of
May? A: Pete, Mary, Jane  = 3 in total

I do have a self made time dimension (table) with records per day. Can
I use/link this somehow to answer these question? Problem is we have
variable periods in both facts and question. The fact table has about
300.000 records, so using it as a dimension somehow maybe an option?
It is NOT an option to have multiple records for each day a person has
in his period.

Would using MDX in the cube be a solution?

Any suggestions would be more then welcome!

Wouter

Facts that only exist in PERIODS of time

Woulter,

Kimball proposed a solution for this type of problem in his "The Data
Warehouse Lifecycle Toolkit". You can get a summary at
http://www.dbmsmag.co/9802d05.html but beware in the web article there is an
error in the proposed SQL in the paragraph starting "The second query is

Hope this helps

Nick Bowling

Quote:> Hi, a difficult one:
> We have a datawarehouse with facts in a fact table that `exist' only
> in a time period, defined by using begin and end dates. Example data
> could be:

> FACT TABLE
> Key | Name | Begindate | EndDate | LengthInMonths
> -----------------------------------------------
> 1,  'Pete', '20010101', '20010630', '6'
> 2,  'Mary', '20010322', '20010622', '3'
> 3,  'Jane', '20010413', '20020413', '12'

> Question now could be: Who was present in February? A: Pete = 1 in
> total

> or Q: How many persons were present from 5th of April till 20th of
> May? A: Pete, Mary, Jane  = 3 in total

> I do have a self made time dimension (table) with records per day. Can
> I use/link this somehow to answer these question? Problem is we have
> variable periods in both facts and question. The fact table has about
> 300.000 records, so using it as a dimension somehow maybe an option?
> It is NOT an option to have multiple records for each day a person has
> in his period.

> Would using MDX in the cube be a solution?

> Any suggestions would be more then welcome!

> Wouter

Hi!

I had a requirement to represent time using both the Calendar Year (1st Jan)
and Fiscal year (1st April).

This, from all that I have read, can be done by creating 2 dimension. I have
2 dimensions "Time.Calendar" and Time.Fiscal, which have 1 Jan and 1 April
as the respective starting dates. When I process the Time.Fiscal dimension
for the first time, there is no problem, everything works fine. But if I
subsequently do a full process (of the dimension), I get various errors.
1. Cannot save inside a DSO transaction.
2. Sometimes the Member Key/ Name calculation gets truncated and I get an
invalid definition error. i.e.

DatePart(year,Case when (DatePart(month, "dbo"."tbl_time"."the_date") <
4 or (DatePart(month, "dbo"."tbl_time"."the_date") = 4 and DatePart(day,
"dbo"."tbl_time"."the_date") < 1)) then "dbo"."tbl_time"."the_date" Else

becomes

DatePart(year,Case when (DatePart(month, "dbo"."tbl_time"."the_date") <
4 or (DatePart(month, "dbo"."tbl_time"."the_date") = 4 and DatePart(day,
"dbo"."tbl_time"."the_date") < 1)) then "dbo"."tbl_time"."the_date" Else

the last 12 characters are deleted.

If, I am to perform a full process on  a cube using the Time.Fiscal
dimension, I get an error stating that Fact Table size cannot be zero. This
error does not go away even if I remove the dimension or redesign the
aggregations.