On Wed, Apr 21, 2010 at 3:36 PM, Adam DeVita <adev...@verifeye.com> wrote:
> 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.
>
Yea, in my view, if you are going to do a lot of math on parts of the
date, you are probably better of storing those parts separately
anyway.
Your db size is trivial though, so you should have no problem either way.
> (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
>
--
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
