Good day,
I've been looking at doing that, but am having problems converting
backwards.
The idea of a date dimension is to have one row for every possible date in
the time span of interest
For example, I'm tracking product histories, so I know that there will be no
activity before January 1, 1990 (a date well before manufacture of the first
product)
and I won't care about what happens well into the future retire (say around
year 2030 )
This yeilds (20+20)*365.25 =14,610 maximum potential rows. If all the
possibilities are pre-calculated, then if a user wants to express a roll up
of the facts by any grouping
select count(somthing_interesting) , dd.day_of_week
from fact_table ft
inner join date_dimension dd on ft.dateid = dd.dateid
group by dd,day_of_week
Then
a) the user doesn't have to worry about converting dates by some group
because it is all done for them.
b) we store the date of the event in our fact_table as an int
c) we don't have to run much of calculation of dates, just a join.
(I've been reading "The Data Warehoust Toolkit, Second Edition by Kimball
and Ross)
If I have to generate the date dimension on my own, I'm hoping to use
something like
create table date_dimension (
[Dateid] integer primary key,
[Real_Year] int ,
[Month_name] text,
[Day] int ,
[QuarterNumber] int,
[DayofWeek_name] text,
[dayofYear] int,
[epoch_day] int,
[julian_day] int
);
/*populate some an auto increment so that all days are covered even if I
have to write a loop doing*/
insert into date_dimension( epoch_day) select count(epoch_day) from
date_dimension;
/*then */
update date_dimension set julian_day = julianday('now') -
julianday('1990-01-01') + epoch_day;
/* then uh.... some query that updates the table containing the julian
date of every day from Jan 1, 1990 through 2030, and fill in the month
name, year, quarter in nice user friendly strings.*/
Is this approach better than generating a list of date strings for all
possible dates, throwing away the Feb 29s from non leap years, and then
parsing the string to get ye year, month, day, day of year, age from epoch,
month name, etc?
regards,
Adam
On Wed, Apr 21, 2010 at 3:59 PM, P Kishor <punk.k...@gmail.com> wrote:
> On Wed, Apr 21, 2010 at 2:37 PM, Adam DeVita <adev...@verifeye.com> wrote:
> > Yes.
> >
> > A Date dimension is a table that has all possible dates for your
> > data, thus making reporting on properties of the date easy. Something
> > like this
> >
> > CREATE TABLE Date_dimension (
> > DateID int NOT NULL , /*an int key to match up to date fields in fact
> > storage tables*/
> > [Date] datetime NOT NULL,
> > [Year] int NOT NULL,
> > [Month] int NOT NULL,
> > [Day] int NOT NULL,
> > [QuarterNumber] int NOT NULL,
> > [DayofWeek_name] text,
> > [Month_name] text,
> >
> > )
> >
>
>
> methinks you can calculate all of the above storing your dates as
> strings in a single column, and using the date time functions on that
> column. Check out the functions in the link I sent you.
>
> >
> >
> > On Wed, Apr 21, 2010 at 3:25 PM, Pavel Ivanov <paiva...@gmail.com>
> wrote:
> >
> >> > What is a "Date Dimension"?
> >>
> >> Probably OP meant this:
> >> http://en.wikipedia.org/wiki/Dimension_(data_warehouse)<http://en.wikipedia.org/wiki/Dimension_%28data_warehouse%29>
> <http://en.wikipedia.org/wiki/Dimension_%28data_warehouse%29>
> >> .
> >> But I don't have any answer to the question asked.
> >>
> >>
> >> Pavel
> >>
> >> On Wed, Apr 21, 2010 at 3:21 PM, P Kishor <punk.k...@gmail.com> wrote:
> >> > On Wed, Apr 21, 2010 at 1:50 PM, Adam DeVita <adev...@verifeye.com>
> >> wrote:
> >> >> Good day,
> >> >>
> >> >> Given the context I'm in, sqlite is going to be used for our data
> >> >> warehousing. (We generate about 2MB of raw data in a month, so we
> don't
> >> >> think we need a heavy DB engine.)
> >> >>
> >> >> Since most warehouses have one, which are very similar from
> application
> >> to
> >> >> application, I'm wondering if there is somewhere to download a
> >> pre-defined
> >> >> Date Dimension?
> >> >
> >> > What is a "Date Dimension"? For SQLite's date time functions, see
> >> > http://www.sqlite.org/lang_datefunc.html
> >> >
> >> >> I could write my own script, but re-invent and debug the
> >> >> wheel?
> >> >>
> >> >> regards,
> >> >> Adam
> >> >>
> >> >> --
> >> >> VerifEye Technologies Inc.
> >> >> 905-948-0015x245
> >> >> 7100 Warden Ave, Unit 3
> >> >> Markham ON, L3R 8B5
> >> >> Canada
> >> >> _______________________________________________
> >> >> sqlite-users mailing list
> >> >> sqlite-users@sqlite.org
> >> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >> >>
> >> >
> >> >
> >> >
> >> > --
> >> > Puneet Kishor http://www.punkish.org
> >> > Carbon Model http://carbonmodel.org
> >> > Charter Member, Open Source Geospatial Foundation
> http://www.osgeo.org
> >> > Science Commons Fellow,
> http://sciencecommons.org/about/whoweare/kishor
> >> > Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> >> >
> -----------------------------------------------------------------------
> >> > Assertions are politics; backing up assertions with evidence is
> science
> >> >
> =======================================================================
> >> > _______________________________________________
> >> > sqlite-users mailing list
> >> > sqlite-users@sqlite.org
> >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >> >
> >> _______________________________________________
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> >
> > --
> > VerifEye Technologies Inc.
> > 905-948-0015x245
> > 7100 Warden Ave, Unit 3
> > Markham ON, L3R 8B5
> > Canada
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> -----------------------------------------------------------------------
> Assertions are politics; backing up assertions with evidence is science
> =======================================================================
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
