Facts that only exist in PERIODS of time

Facts that only exist in PERIODS of time

Post by Wouter Schoonve » Wed, 05 Jun 2002 21:50:45



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

Post by Nick Bowlin » Tue, 11 Jun 2002 02:36:07


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
addressed by the employee..."

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


 
 
 

1. Fact table size 0! Time.Calander, Time.Fiscal

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
DateAdd(year, 1, "dbo"."tbl_time"."the_date") End )

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
DateAdd(year, 1, "dbo"."tbl_time"."th

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.

I'm in Dire Straits! Please help!

regards
Dennis

2. dbase IV

3. Find Missing Records: For Each Period, make sure a Group of Other Fields exists

4. HELP: setting the header column name in a tableframe

5. DTS can't read from network drive?

6. how to program for period-to-period comparisons ?

7. DATABASE FOR PC

8. Time periods in a star schema?

9. Precalculated values and time periods

10. Time Dimension for Fiscal Calendar with 13 periods

11. Parallel Periods and Relative Times ????

12. Aggregations accross time periods