V2.44 ERwi[ 2(( char(18)X%KeyType%TableName %EntityName()Note ID Note Text 2CURRENT_TIMESTAMPCURRENT_TIMESTAMP CURRENT_USER CURRENT_USERDF_dim_day_date_type ('Normal')DF_dim_day_is_US_civil_holiday('N')distant_futurecast('2599-12-31' as datetime)Fill With SpacesspacesNow getdate()NULLNULL SESSION_USER SESSION_USERSet To Empty Stringempty Set To Nullnull Set To Zero0USERUSER Template Name" Template Code Template TypeTemplate CSX Id"PARENT UPDATE RESTRICT /* ERwin Builtin %Datetime */ /* %Parent %VerbPhrase %Child ON PARENT UPDATE RESTRICT */ if /* %%ParentPK(" or",update) */ %ParentPK(" or",update) begin if exists ( select * from deleted,%Child where /* %%JoinFKPK(%Child,deleted," = "," and") */ %JoinFKPK(%Child,deleted," = "," and") ) begin select @errno = 30005, @errmsg = 'Cannot UPDATE %Parent because %Child exists.' goto error end end 'PARENT UPDATE CASCADE /* ERwin Builtin %Datetime */ /* %Parent %VerbPhrase %Child ON PARENT UPDATE CASCADE */ if /* %%ParentPK(" or",update) */ %ParentPK(" or",update) begin if @numrows = 1 begin select %JoinPKPK(@ins,inserted," = ",", ") from inserted update %Child set /* %%JoinFKPK(%Child,@ins," = ",",") */ %JoinFKPK(%Child,@ins," = ",",") from %Child,inserted,deleted where /* %%JoinFKPK(%Child,deleted," = "," and") */ %JoinFKPK(%Child,deleted," = "," and") end else begin select @errno = 30006, @errmsg = 'Cannot cascade %Parent UPDATE because more than one row has been affected.' goto error end end 'PARENT UPDATE SET NULL /* ERwin Builtin %Datetime */ /* %Parent %VerbPhrase %Child ON PARENT UPDATE SET NULL */ if /* %%ParentPK(" or",update) */ %ParentPK(" or",update) begin update %Child set /* %%SetFK(%Child,NULL) */ %SetFK(%Child,NULL) from %Child,deleted where /* %%JoinFKPK(%Child,deleted," = "," and") */ %JoinFKPK(%Child,deleted," = "," and") end 'PARENT DELETE RESTRICT /* ERwin Builtin %Datetime */ /* %Parent %VerbPhrase %Child ON PARENT DELETE RESTRICT */ if exists ( select * from deleted,%Child where /* %%JoinFKPK(%Child,deleted," = "," and") */ %JoinFKPK(%Child,deleted," = "," and") ) begin select @errno = 30001, @errmsg = 'Cannot DELETE %Parent because %Child exists.' goto error end 'PARENT DELETE CASCADE /* ERwin Builtin %Datetime */ /* %Parent %VerbPhrase %Child ON PARENT DELETE CASCADE */ delete %Child from %Child,deleted where /* %%JoinFKPK(%Child,deleted," = "," and") */ %JoinFKPK(%Child,deleted," = "," and") 'PARENT DELETE SET NULLQ /* ERwin Builtin %Datetime */ /* %Parent %VerbPhrase %Child ON PARENT DELETE SET NULL */ update %Child set /* %%SetFK(%Child,NULL) */ %SetFK(%Child,NULL) from %Child,deleted where /* %%JoinFKPK(%Child,deleted," = "," and") */ %JoinFKPK(%Child,deleted," = "," and") 'CHILD INSERT RESTRICT /* ERwin Builtin %Datetime */ /* %Parent %VerbPhrase %Child ON CHILD INSERT RESTRICT */ if /* %%ChildFK(" or",update) */ %ChildFK(" or",update) begin select @nullcnt = 0 select @validcnt = count(*) from inserted,%Parent where /* %%JoinFKPK(inserted,%Parent) */ %JoinFKPK(inserted,%Parent) /* %%NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted where"," and") */ %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted where"," and") if @validcnt + @nullcnt != @numrows begin select @errno = 30002, @errmsg = 'Cannot INSERT %Child because %Parent does not exist.' goto error end end 'CHILD INSERT CASCADEH /* ERwin Builtin %Datetime */ /* %Parent %VerbPhrase %Child ON CHILD INSERT CASCADE */ if /* %%ChildFK(" or",update) */ %ChildFK(" or",update) begin insert %Parent (%ParentPK(",",)) select %ChildFK(",",) from inserted where /* %%NotnullFK(inserted," is not null and") */ %NotnullFK(inserted," is not null and") not exists ( select * from %Parent where /* %%JoinFKPK(inserted,%Parent," = "," and") */ %JoinFKPK(inserted,%Parent," = "," and") ) end 'CHILD INSERT SETNULLn /* ERwin Builtin %Datetime */ /* %Parent %VerbPhrase %Child ON CHILD INSERT SET NULL */ if /* %%ChildFK(" or",update) */ %ChildFK(" or",update) begin update %Child set /* %%SetFK(%Child,NULL) */ %SetFK(%Child,NULL) from %Child,inserted where /* %%JoinPKPK(%Child,inserted," = "," and") */ %JoinPKPK(%Child,inserted," = "," and") and not exists ( select * from %Parent where /* %%JoinFKPK(inserted,%Parent," = "," and") */ %JoinFKPK(inserted,%Parent," = "," and") ) end 'CHILD UPDATE RESTRICT /* ERwin Builtin %Datetime */ /* %Parent %VerbPhrase %Child ON CHILD UPDATE RESTRICT */ if /* %%ChildFK(" or",update) */ %ChildFK(" or",update) begin select @nullcnt = 0 select @validcnt = count(*) from inserted,%Parent where /* %%JoinFKPK(inserted,%Parent) */ %JoinFKPK(inserted,%Parent) /* %%NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted where"," and") */ %NotnullFK(inserted," is null","select @nullcnt = count(*) from inserted where"," and") if @validcnt + @nullcnt != @numrows begin select @errno = 30007, @errmsg = 'Cannot UPDATE %Child because %Parent does not exist.' goto error end end 'CHILD UPDATE CASCADEH /* ERwin Builtin %Datetime */ /* %Parent %VerbPhrase %Child ON CHILD UPDATE CASCADE */ if /* %%ChildFK(" or",update) */ %ChildFK(" or",update) begin insert %Parent (%ParentPK(",",)) select %ChildFK(",",) from inserted where /* %%NotnullFK(inserted," is not null and") */ %NotnullFK(inserted," is not null and") not exists ( select * from %Parent where /* %%JoinFKPK(inserted,%Parent," = "," and") */ %JoinFKPK(inserted,%Parent," = "," and") ) end 'CHILD UPDATE SETNULLo /* ERwin Builtin %Datetime */ /* %Parent %VerbPhrase %Child ON CHILD UPDATE SET NULL */ if /* %%ChildFK(" or",update) */ %ChildFK(" or",update) begin update %Child set /* %%SetFK(%Child,NULL) */ %SetFK(%Child,NULL) from %Child,inserted where /* %%JoinPKPK(%Child,inserted," = "," and") */ %JoinPKPK(%Child,inserted," = "," and") and not exists ( select * from %Parent where /* %%JoinFKPK(inserted,%Parent," = "," and") */ %JoinFKPK(inserted,%Parent," = "," and") ) end 'CHILD DELETE RESTRICTc /* ERwin Builtin %Datetime */ /* %Parent %VerbPhrase %Child ON CHILD DELETE RESTRICT */ if exists (select * from deleted,%Parent where /* %%JoinFKPK(deleted,%Parent," = "," and") */ %JoinFKPK(deleted,%Parent," = "," and") and not exists ( select * from %Child where /* %%JoinFKPK(%Child,%Parent," = "," and") */ %JoinFKPK(%Child,%Parent," = "," and") ) ) begin select @errno = 30010, @errmsg = 'Cannot DELETE last %Child because %Parent exists.' goto error end 'CHILD DELETE CASCADE /* ERwin Builtin %Datetime */ /* %Parent %VerbPhrase %Child ON CHILD DELETE CASCADE */ delete %Parent from %Parent,deleted where /* %%JoinFKPK(deleted,%Parent," = "," and") */ %JoinFKPK(deleted,%Parent," = "," and") and not exists ( select * from %Child where /* %%JoinFKPK(%Child,%Parent," = "," and") */ %JoinFKPK(%Child,%Parent," = "," and") ) !'PARENT INSERT RESTRICT /* ERwin Builtin %Datetime */ /* %Parent %VerbPhrase %Child ON PARENT INSERT RESTRICT */ if exists (select * from inserted where not exists ( select * from %Child where /* %%JoinFKPK(%Child,inserted," = "," and") */ %JoinFKPK(%Child,inserted," = "," and") ) ) begin select @errno = 30011, @errmsg = 'Cannot INSERT %Parent because %Child does not.' goto error end $' UPDATE HEADERcreate trigger t%1Action_%27TableName on %TableName for %Action as /* ERwin Builtin %Datetime */ /* %Action trigger on %TableName */ begin declare @numrows int, @nullcnt int, @validcnt int, %PKDecl(,@ins)%decl(bComma,0)%ForEachAtt() {%if(%AttIsPk) {%=(bComma,1)} }%if (%==(%:bComma,1)) {,} @errno int, @errmsg varchar(255) select @numrows = @@rowcount(' UPDATE FOOTERt /* ERwin Builtin %Datetime */ return error: raiserror @errno @errmsg rollback transaction end go )' DELETE HEADERcreate trigger t%1Action_%27TableName on %TableName for %Action as /* ERwin Builtin %Datetime */ /* %Action trigger on %TableName */ begin declare @errno int, @errmsg varchar(255)*' DELETE FOOTERx /* ERwin Builtin %Datetime */ return error: raiserror @errno @errmsg rollback transaction end go +' INSERT HEADER:create trigger t%1Action_%27TableName on %TableName for %Action as /* ERwin Builtin %Datetime */ /* %Action trigger on %TableName */ begin declare @numrows int, @nullcnt int, @validcnt int, @errno int, @errmsg varchar(255) select @numrows = @@rowcount,' INSERT FOOTERt /* ERwin Builtin %Datetime */ return error: raiserror @errno @errmsg rollback transaction end go -'CUSTOM TRIGGER HEADERFcreate trigger %TriggerName on %TableName for %Actions(",") as .'CUSTOM TRIGGER FOOTERCUSTOM TRIGGER FOOTERgo /'CUSTOM TRIGGER DEFAULT BODYcreate trigger %TriggerName on %TableName for %Actions(",") as /* ERwin Builtin %Datetime */ /* %Actions(",") trigger on %TableName */ /* default body for %TriggerName */ begin declare @numrows int, @nullcnt int, @validcnt int, %PKDecl(,@ins)%decl(bComma,0)%ForEachAtt() {%if(%AttIsPk) {%=(bComma,1)} }%if (%==(%:bComma,1)) {,} @errno int, @errmsg varchar(255) select @numrows = @@rowcount %ForEachChildRel() { %RelTemplate } %ForEachParentRel() { %RelTemplate } return error: raiserror @errno @errmsg rollback transaction end go 0'PARENT UPDATE SET DEFAULT/* ERwin Builtin %Datetime */ /* %Parent %VerbPhrase %Child ON PARENT UPDATE SET DEFAULT */ /* Application specific. Left out intentionally. */'PARENT DELETE SET DEFAULT/* ERwin Builtin %Datetime */ /* %Parent %VerbPhrase %Child ON PARENT DELETE SET DEFAULT */ /* Application specific. Left out intentionally. */'PARENT INSERT SET DEFAULT/* ERwin Builtin %Datetime */ /* %Parent %VerbPhrase %Child ON PARENT INSERT SET DEFAULT */ /* Application specific. Left out intentionally. */''CHILD INSERT SET DEFAULT/* ERwin Builtin %Datetime */ /* %Parent %VerbPhrase %Child ON CHILD INSERT SET DEFAULT */ /* Application specific. Left out intentionally. */'CHILD UPDATE SET DEFAULT/* ERwin Builtin %Datetime */ /* %Parent %VerbPhrase %Child ON CHILD UPDATE SET DEFAULT */ /* Application specific. Left out intentionally. */'CHILD DELETE SET DEFAULT/* ERwin Builtin %Datetime */ /* %Parent %VerbPhrase %Child ON CHILD DELETE SET DEFAULT */ /* Application specific. Left out intentionally. */#'CHILD DELETE SET NULL/* ERwin Builtin %Datetime */ /* %Parent %VerbPhrase %Child ON CHILD DELETE SET NULL */ /* Application specific. Left out intentionally. */"'PARENT INSERT SET NULL/* ERwin Builtin %Datetime */ /* %Parent %VerbPhrase %Child ON PARENT INSERT SET NULL */ /* Application specific. Left out intentionally. */&'PARENT INSERT CASCADE/* ERwin Builtin %Datetime */ /* %Parent %VerbPhrase %Child ON PARENT INSERT CASCADE */ /* Application specific. Left out intentionally. */%' Template Name"Template Handle Purpose Text"PurposeTemplate Handle""CHILD DELETE CASCADE!'!'CHILD DELETE RESTRICT ' 'CHILD DELETE SET DEFAULT#'#'CHILD DELETE SET NULL"'"'CHILD INSERT CASCADE''CHILD INSERT RESTRICT''CHILD INSERT SET DEFAULT''CHILD INSERT SET NULL''CHILD UPDATE CASCADE''CHILD UPDATE RESTRICT''CHILD UPDATE SET DEFAULT''CHILD UPDATE SET NULL''CUSTOM TRIGGER DEFAULT BODY0'0'CUSTOM TRIGGER FOOTER/'/'CUSTOM TRIGGER HEADER.'.'PARENT DELETE CASCADE''PARENT DELETE RESTRICT''PARENT DELETE SET DEFAULT''PARENT DELETE SET NULL''PARENT INSERT CASCADE%'%'PARENT INSERT RESTRICT$'$'PARENT INSERT SET DEFAULT''''PARENT INSERT SET NULL&'&'PARENT UPDATE CASCADE''PARENT UPDATE RESTRICT''PARENT UPDATE SET DEFAULT''PARENT UPDATE SET NULL''RAISE EXCEPTION PROC1'TRIGGER DELETE FOOTER+'+'TRIGGER DELETE HEADER*'*'TRIGGER INSERT FOOTER-'-'TRIGGER INSERT HEADER,','TRIGGER UPDATE FOOTER)')'TRIGGER UPDATE HEADER('('#@ #@ @ @ ''''''''''''''''Default Entity Icon ( ADefault Entity Small Iconh(@Default Icon((Default String Iconh(@ Default Number Iconh(@Default Datetime Icon(Default Blob Iconh(@(((((char(18)%AttName %AttName:%AttName%AttDef %DomainDef %DomainName %AttDomain%AttDefny! !Blobbinary !Datetimedatetime !Numberint !String varchar(20)   UdpKeyList Key ID Key Name Key Flags Key Def Key TypeKey Default Key Seq Key List Key MMId7 DiagImpType6 DiagDbName5 DiagLogin8 DiagSrvrName  UdpKeyList Key ID Key Name Key Flags Key Def Key TypeKey Default Key Seq Key List Key MMId  UdpKeyList Key ID Key Name Key Flags Key Def Key TypeKey Default Key Seq Key List Key MMId(((C:\Don\Dev\dim_day.SQL 7k 2C:\Don\Dev\dim_day.SQL "Arial "Arial      !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWYZ[\]^_`abcdefghijklmnopqrstuvwyz{|}~     DBMS DPO_CLASSDPO_IDDPO_NAME DPO_LAST_NAMEA DPO_EPP_UDS DPO_CSX_IDDPO_UDPSGK48<dim_holiday_3NFdim_holiday_3NF( holiday_key holiday_keyM holiday_name holiday_nameO yearyear_numdatetimeP holiday_dateR effective_dateeffective_datedatetimeS expiration_dateexpiration_datedatetimeT is_current is_currentchar(1)Udddddd   ddPKYES PKASCPROP  [48<*YA view may be constructed containing left outer joins to pivot the data into rows with a new "holiday_name" attribute being coded into the view, but preferably represented as a materialized view or just handled in the ETL process to produce something a data structure as shown in dim_holiday_3NF that will be easier to join to the dim_day table.   dd  N /48<*"is_current" should be a "computed" column cast to be char(1); the computation should be based on expiration_date = cast('2599-12-31' as datetime) or some equally distant date beyond the maximum date domain in the dataset;   dd   lI3-48<dim_holidays dim_holiday( holiday_key holiday_keyint. yearyear_numdatetime0 new_years_day new_years_daydatetime2 mlk_daymlk_daydatetime3 ground_hog_dayground_hog_daydatetime4 lincolns_birthdaylincolns_birthdaydatetime5 valentines_dayvalentines_daydatetime6 washingtons_birthdaywashingtons_birthdaydatetime7 presidents_daypresidents_daydatetime8 st_patricks_dayst_patricks_daydatetime9 april_fools_dayapril_fools_daydatetime: good_friday good_fridaydatetime; eastereasterdatetime< mothers_day mothers_daydatetime= memorial_day memorial_daydatetime> fathers_day fathers_daydatetime? us_independence_dayus_independence_daydatetime@ parents_day parents_daydatetimeA labor_day labor_daydatetimeB grandparents_daygrandparents_daydatetimeC columbus_day columbus_daydatetimeE halloween halloweendatetimeF veterans_day veterans_daydatetimeG thanksgiving_daythanksgiving_daydatetimeH christmas_day christmas_daydatetimeI effective_dateeffective_datedatetimeJ expiration_dateexpiration_datedatetimeK is_current is_currentchar(1)Lddddddddd d d d d dddddddddddddd   ddPKYES PKASCPROP  y48<*Don McMunn's FREEWARE Date Dimension Kit Normalized Time Hierarcy tables Contains all days between Jan 1, 1900 and Dec 31, 2499(   dd   48<*Don McMunn's FREEWARE Date Dimension Kit Denormalized Day-level table Table name: dim_day Contains all days between Jan 1, 1900 and Dec 31, 2499(   dd   (48<dim_dayProvides highly denomalized date-related information suited for dimensional reporting; copyright c. 2007 IPC Designs, LLC; Author: Don McMunn;dim_day(intfiscal_year_end_date_key fiscal_year_end_date_key datetimefiscal_year_end_date fiscal_year_end_dateintfiscal_year_begin_date_key fiscal_year_begin_date_keydatetimefiscal_year_begin_date fiscal_year_begin_dateintfiscal_year_num fiscal_year_numintfiscal_quarter_end_date_key fiscal_quarter_end_date_keydatetimefiscal_quarter_end_date fiscal_quarter_end_dateintfiscal_quarter_begin_date_key fiscal_quarter_begin_date_keydatetimefiscal_quarter_begin_date fiscal_quarter_begin_dateintfiscal_quarter_num_overall fiscal_quarter_num_overallintfiscal_quarter_num_of_year fiscal_quarter_num_of_yearintfiscal_month_end_date_key fiscal_month_end_date_keydatetimefiscal_month_end_date fiscal_month_end_dateintfiscal_month_begin_date_key fiscal_month_begin_date_keydatetimefiscal_month_begin_date fiscal_month_begin_dateintfiscal_month_num_overall fiscal_month_num_overallintfiscal_month_num_of_year fiscal_month_num_of_yeardatetimefiscal_week_end_date fiscal_week_end_dateintfiscal_week_end_date_key fiscal_week_end_date_keydatetimefiscal_week_begin_date fiscal_week_begin_date intfiscal_week_begin_date_key fiscal_week_begin_date_key!intfiscal_week_num_overall fiscal_week_num_overall"intfiscal_week_num_of_year fiscal_week_num_of_year# varchar(9) DDMONYYYY DDMONYYYY$ varchar(7)DDMONYY DDMONYY% varchar(8)YYYYMMDD YYYYMMDD& varchar(6)YYYYMM YYYYMM'intyear_end_date_key year_end_date_key(datetime year_end_date year_end_date)intyear_begin_date_key year_begin_date_key*datetimeyear_begin_date year_begin_date+intyear_num year_num,intquarter_end_date_key quarter_end_date_key-datetimequarter_end_date quarter_end_date.intquarter_begin_date_key quarter_begin_date_key/datetimequarter_begin_date quarter_begin_date0intquarter_num_overall quarter_num_overall1intquarter_num_of_year quarter_num_of_year2intmonth_end_date_key month_end_date_key3datetimemonth_end_date month_end_date4intmonth_begin_date_key month_begin_date_key5datetimemonth_begin_date month_begin_date6 varchar(3)month_name_abbreviation month_name_abbreviation7 varchar(10) month_name month_name8intmonth_num_overall month_num_overall9intmonth_num_of_year month_num_of_year:intweek_num_overall week_num_overall;intweek_num_of_year week_num_of_year<intweek_num_of_quarter week_num_of_quarter=intweek_num_of_month week_num_of_month>intweek_of_quarter_end_date_key week_of_quarter_end_date_key?datetimeweek_of_quarter_end_date week_of_quarter_end_date@intweek_of_quarter_begin_date_key week_of_quarter_begin_date_keyAdatetimeweek_of_quarter_begin_date week_of_quarter_begin_dateBintweek_of_month_end_date_key week_of_month_end_date_keyCdatetimeweek_of_month_end_date week_of_month_end_dateDintweek_of_month_begin_date_key week_of_month_begin_date_keyEdatetimeweek_of_month_begin_date week_of_month_begin_dateFintweek_of_year_end_date_key week_of_year_end_date_keyGdatetimeweek_of_year_end_date week_of_year_end_dateHintweek_of_year_begin_date_key week_of_year_begin_date_keyIdatetimeweek_of_year_begin_date week_of_year_begin_dateJintsame_weekday_year_ago_date_key same_weekday_year_ago_date_keyKdatetimesame_weekday_year_ago_date same_weekday_year_ago_dateLintprev_day_date_key prev_day_date_keyMdatetime prev_day_date prev_day_dateNchar(1)is_last_day_of_fiscal_year is_last_day_of_fiscal_yearOchar(1)is_last_day_of_fiscal_quarter is_last_day_of_fiscal_quarterPchar(1)is_last_day_of_fiscal_month is_last_day_of_fiscal_monthQchar(1)is_last_day_of_year is_last_day_of_yearRchar(1)is_last_day_of_quarter is_last_day_of_quarterSchar(1)is_last_day_of_month is_last_day_of_monthTchar(1)is_last_day_of_week is_last_day_of_weekUchar(1)is_US_civil_holiday is_US_civil_holidayVchar(1) is_weekday is_weekdayW decimal(18,0)julian_day_num_absolute julian_day_num_absoluteXintjulian_day_num_of_year julian_day_num_of_yearY varchar(3)day_of_week_abbreviation day_of_week_abbreviationZ varchar(10)day_of_week_name day_of_week_name[intday_num_absolute day_num_absolute\intday_num_of_year day_num_of_year]intday_num_of_quarter day_num_of_quarter^intday_num_of_month day_num_of_month_intday_num_of_week day_num_of_week`datetime full_date full_datea varchar(20) date_type date_typebintdate_key date_keycWdate_keyVVd date_typeUUd full_dateTTdday_num_of_weekSSdday_num_of_monthRRdday_num_of_quarterQQdday_num_of_yearPPdday_num_absoluteOOdday_of_week_nameNNd day_of_week_abbreviationMMd julian_day_num_of_yearLLd julian_day_num_absoluteKKd is_weekdayJJd is_US_civil_holidayIIdis_last_day_of_weekHHdis_last_day_of_monthGGdis_last_day_of_quarterFFdis_last_day_of_yearEEdis_last_day_of_fiscal_monthDDdis_last_day_of_fiscal_quarterCCdis_last_day_of_fiscal_yearBBd prev_day_dateAAdprev_day_date_key@@dsame_weekday_year_ago_date??dsame_weekday_year_ago_date_key>>dweek_of_year_begin_date==dweek_of_year_begin_date_key<<dweek_of_year_end_date;;dweek_of_year_end_date_key::dweek_of_month_begin_date99dweek_of_month_begin_date_key88dweek_of_month_end_date77d week_of_month_end_date_key66d!!week_of_quarter_begin_date55d""week_of_quarter_begin_date_key44d##week_of_quarter_end_date33d$$week_of_quarter_end_date_key22d%%week_num_of_month11d&&week_num_of_quarter00d''week_num_of_year//d((week_num_overall..d))month_num_of_year--d**month_num_overall,,d++ month_name++d,,month_name_abbreviation**d--month_begin_date))d..month_begin_date_key((d//month_end_date''d00month_end_date_key&&d11quarter_num_of_year%%d22quarter_num_overall$$d33quarter_begin_date##d44quarter_begin_date_key""d55quarter_end_date!!d66quarter_end_date_key  d77year_numd88year_begin_dated99year_begin_date_keyd:: year_end_dated;;year_end_date_keyd<<YYYYMMd==YYYYMMDDd>>DDMONYYd?? DDMONYYYYd@@fiscal_week_num_of_yeardAAfiscal_week_num_overalldBBfiscal_week_begin_date_keydCCfiscal_week_begin_datedDDfiscal_week_end_date_keydEEfiscal_week_end_datedFFfiscal_month_num_of_yeardGGfiscal_month_num_overalldHHfiscal_month_begin_datedIIfiscal_month_begin_date_key  dJJfiscal_month_end_date  dKKfiscal_month_end_date_key  dLLfiscal_quarter_num_of_year  dMMfiscal_quarter_num_overall  dNNfiscal_quarter_begin_datedOOfiscal_quarter_begin_date_keydPPfiscal_quarter_end_datedQQfiscal_quarter_end_date_keydRRfiscal_year_numdSSfiscal_year_begin_datedTTfiscal_year_begin_date_keydUUfiscal_year_end_datedVVfiscal_year_end_date_key   ddPK PK_dim_dayYESASC PKASCPROP  dim_day l!h548<dim_dateContains only the day attributes of the time hierarchy; and only enough referential (foreign key) information to relate the day to the more summarized levels of the time hierarchy: week of month, week of year and fiscal week of year; This represents the bottom of the time hierarchy, and the common point of connection between the two psuedo-parallel time hierarchies: fiscal and calendar;dim_date( intdate_key date_key A surrogate, primary key; Uniquely identifies a calendar day; may be used as a referential tie to fact tables or other transactional data kept at the day level; varchar(20) date_type date_typedatetime full_date full_dateintday_num_of_week day_num_of_weekintday_num_of_month day_num_of_monthintday_num_of_quarter day_num_of_quarterintday_num_of_year day_num_of_yearintday_num_absolute day_num_absolute varchar(10)day_of_week_name day_of_week_name varchar(3)day_of_week_abbreviation day_of_week_abbreviationintjulian_day_num_of_year julian_day_num_of_year decimal(18,0)julian_day_num_absolute julian_day_num_absolutechar(1) is_weekday is_weekdaychar(1)is_US_civil_holiday is_US_civil_holidaychar(1)is_last_day_of_week is_last_day_of_weekchar(1)is_last_day_of_month is_last_day_of_monthchar(1)is_last_day_of_quarter is_last_day_of_quarterchar(1)is_last_day_of_year is_last_day_of_yearchar(1)is_last_day_of_fiscal_month is_last_day_of_fiscal_monthchar(1)is_last_day_of_fiscal_quarter is_last_day_of_fiscal_quarterchar(1)is_last_day_of_fiscal_year is_last_day_of_fiscal_yeardatetime prev_day_date prev_day_dateintprev_day_date_key prev_day_date_keydatetimesame_weekday_year_ago_date same_weekday_year_ago_dateintsame_weekday_year_ago_date_key same_weekday_year_ago_date_key varchar(8)YYYYMMDD YYYYMMDD varchar(7)DDMONYY DDMONYY varchar(9) DDMONYYYY DDMONYYYY week_of_year_keyweek_of_year_keyint' week_of_month_keyweek_of_month_keyint( fiscal_week_of_year_keyfiscal_week_of_year_keyint*YZd[d\d]d^d_d`dadbd cd dd ed fd gdhdidjdkdldmdndodpdqddddd.12d.11d.14   ddPKYES IF11NO IF12NO IF14NO IF11ASCPROP IF12ASCPROP IF14ASCPROP PKASCPROP   J@%48<dim_weekContains only the week of calendar year attributes of the time hierarchy; and only enough referential (foreign key) information to relate the week of year to the next higher summarized level of the time hierarchy: year;dim_week_of_year(datetimeweek_of_year_begin_date week_of_year_begin_dateintweek_of_year_begin_date_key week_of_year_begin_date_keydatetimeweek_of_year_end_date week_of_year_end_dateintweek_of_year_end_date_key week_of_year_end_date_key week_of_year_keyweek_of_year_keyint A surrogate, primary key; Uniquely identifies a non-overlapping, calendar week of year time period; used as a referential tie to day of calendar year (dim_date); may be used as a referential tie to fact tables or other transactional data kept at the calendar week of year level;intweek_num_of_year week_num_of_year year_keyyear_keyint$dddddwd.8   ddPKYES IF8NO IF8ASCPROP PKASCPROP   .48<dim_monthContains only the month of calendar year portion of the time hierarchy; and only enough referential (foreign key) information to relate the calendar month to the next higher summarized level of the time hierarchy: quarter; dim_month( intmonth_num_of_year month_num_of_yearintmonth_num_overall month_num_overall varchar(10) month_name month_name varchar(3)month_name_abbreviation month_name_abbreviationdatetimemonth_begin_date month_begin_dateintmonth_begin_date_key month_begin_date_keydatetimemonth_end_date month_end_dateintmonth_end_date_key month_end_date_key month_num_of_year_keymonth_num_of_year_keyint A surrogate, primary key; Uniquely identifies a non-overlapping, calendar month of year time period from all others; may be used as a referential tie to fact tables or other transactional data kept at the calendar month of year level; varchar(6)YYYYMM YYYYMM quarter_key quarter_keyint-ddddddddd d.16    ddPKYES IF16NO IF16 ASCPROP PKASCPROP   QS48<dim_quarterContains only the quarter of calendar year attributes of the time hierarchy; and only enough referential (foreign key) information to relate the quarter of the year to the next higher summarized level of the time hierarchy: year; dim_quarter(intquarter_num_of_year quarter_num_of_yearintquarter_num_overall quarter_num_overalldatetimequarter_begin_date quarter_begin_dateintquarter_begin_date_key quarter_begin_date_keydatetimequarter_end_date quarter_end_dateintquarter_end_date_key quarter_end_date_key year_keyyear_keyint quarter_key quarter_keyint A surrogate, primary key; Uniquely identifies a non-overlapping, calendar quarter time period from all others; used as a referential tie to month of calendar year (dim_month) and week of quarter (dim_week_of_quarter);ydzd{d|d}d~dwd.6   ddPKYES IF6NO IF6ASCPROP PKASCPROP   mO48<dim_yearContains only the calendar year attributes of the time hierarchy; and represents the most highly summarized level of the time hierarchy: year;dim_year(intyear_num year_numdatetimeyear_begin_date year_begin_dateintyear_begin_date_key year_begin_date_keydatetime year_end_date year_end_dateintyear_end_date_key year_end_date_key year_keyyear_keyint ,A surrogate, primary key; Uniquely identifies a non-overlapping, calendar year from all others; used as a referential tie to quarter of calendar year (dim_quarter) and week of year (dim_week_of_year); may be used as a referential tie to fact tables or other transactional data kept at the year level;wrdsdtdudvd   ddPKYES PKASCPROP   b{L48<dim_week_of_quarterContains only the week of calendar quarter attributes of the time hierarchy; and only enough referential (foreign key) information to relate the week of quarter to the next higher summarized level of the time hierarchy: quarter;dim_week_of_quarter(datetimeweek_of_quarter_begin_date week_of_quarter_begin_dateintweek_of_quarter_begin_date_key week_of_quarter_begin_date_keydatetimeweek_of_quarter_end_date week_of_quarter_end_dateintweek_of_quarter_end_date_key week_of_quarter_end_date_key week_of_quarter_keyweek_of_quarter_keyint A surrogate, primary key; Uniquely identifies a non-overlapping, calendar week of quarter time period from all others; may be used as a referential tie to fact tables or other transactional data kept at the calendar week of quarter level;intweek_num_of_quarter week_num_of_quarter quarter_key quarter_keyint"dddddd.5   ddPKYES IF5NO IF5ASCPROP PKASCPROP   48<dim_week_of_monthContains only the week of calendar month portion of the time hierarchy; and only enough referential (foreign key) information to relate the week of month to the next higher summarized level of the time hierarchy: month;dim_week_of_month(datetimeweek_of_month_begin_date week_of_month_begin_dateintweek_of_month_begin_date_key week_of_month_begin_date_keydatetimeweek_of_month_end_date week_of_month_end_dateintweek_of_month_end_date_key week_of_month_end_date_key week_of_month_keyweek_of_month_keyint A surrogate, primary key; Uniquely identifies a non-overlapping, calendar week of month time period from all others; may be used as a referential tie to fact tables or other transactional data kept at the calendar week of month level;intweek_num_of_month week_num_of_month month_num_of_year_keymonth_num_of_year_keyint#dddddd.7   ddPKYES IF7NO IF7ASCPROP PKASCPROP   M48<dim_fiscal_week_of_yearContains only the fiscal week attributes of the time hierarchy; and only enough referential (foreign key) information to relate the fiscal week to the next higher summarized level of the time hierarchy: fiscal month;dim_fiscal_week_of_year(intfiscal_week_num_of_year fiscal_week_num_of_yearintfiscal_week_num_overall fiscal_week_num_overallintfiscal_week_begin_date_key fiscal_week_begin_date_keydatetimefiscal_week_begin_date fiscal_week_begin_dateintfiscal_week_end_date_key fiscal_week_end_date_keydatetimefiscal_week_end_date fiscal_week_end_date fiscal_week_of_year_keyfiscal_week_of_year_keyint A surrogate, primary key; Uniquely identifies a non-overlapping, fiscal week time period from all others; may be used as a referential tie to fact tables or other transactional data kept at the fiscal week level; fiscal_month_of_year_keyfiscal_month_of_year_keyint+d.15dddddd   ddPKYES IF15NO IF15ASCPROP PKASCPROP   In48<dim_fiscal_month_of_yearContains only the fiscal month attributes of the time hierarchy; and only enough referential (foreign key) information to relate the fiscal month to the next higher summarized level of the time hierarchy: fiscal quarter;dim_fiscal_month_of_year( intfiscal_month_num_of_year fiscal_month_num_of_year intfiscal_month_num_overall fiscal_month_num_overall datetimefiscal_month_begin_date fiscal_month_begin_date intfiscal_month_begin_date_key fiscal_month_begin_date_key datetimefiscal_month_end_date fiscal_month_end_date intfiscal_month_end_date_key fiscal_month_end_date_key fiscal_month_of_year_keyfiscal_month_of_year_keyint A surrogate, primary key; Uniquely identifies a non-overlapping, fiscal month time period from all others; may be used as a referential tie to fact tables or other transactional data kept at the fiscal month level; fiscal_quarter_of_year_keyfiscal_quarter_of_year_keyint,ddddddd.4   ddPKYES IF4NO IF4ASCPROP PKASCPROP   |W48<dim_fiscal_quarter_of_yearContains only the fiscal quarter attributes of the time hierarchy; and only enough referential (foreign key) information to relate the fiscal quarter to the next higher summarized level of the fiscal time hierarchy: fiscal year;dim_fiscal_quarter_of_year(intfiscal_quarter_num_of_year fiscal_quarter_num_of_yearintfiscal_quarter_num_overall fiscal_quarter_num_overalldatetimefiscal_quarter_begin_date fiscal_quarter_begin_dateintfiscal_quarter_begin_date_key fiscal_quarter_begin_date_keydatetimefiscal_quarter_end_date fiscal_quarter_end_dateintfiscal_quarter_end_date_key fiscal_quarter_end_date_key fiscal_quarter_of_year_keyfiscal_quarter_of_year_keyint A surrogate, primary key; Uniquely identifies a non-overlapping, fiscal quarter time period from all others; may be used as a referential tie to fact tables or other transactional data kept at the fiscal quarter level; fiscal_year_keyfiscal_year_keyintddddddd.1   ddPKYES IF1NO IF1ASCPROP PKASCPROP  s48<dim_fiscal_yearContains only the fiscal year attributes of the time hierarchy; and represents the highest summarized level of the fiscal time hierarchy: fiscal year;dim_fiscal_year(intfiscal_year_num fiscal_year_numdatetimefiscal_year_begin_date fiscal_year_begin_dateintfiscal_year_begin_date_key fiscal_year_begin_date_keydatetimefiscal_year_end_date fiscal_year_end_dateintfiscal_year_end_date_key fiscal_year_end_date_key fiscal_year_keyfiscal_year_keyint A surrogate, primary key; Uniquely identifies a non-overlapping, fiscal year time period from all others; may be used as a referential tie to fact tables or other transactional data kept at the fiscal year level;ddddd   ddPKYES PKASCPROP   d ''''R/1 Purpose Text"PurposeTemplate Handle"CHILD INSERT SET NULL'CHILD UPDATE SET NULL'PARENT DELETE SET NULL'PARENT UPDATE SET NULL'd |'''' R/4 Purpose Text"PurposeTemplate Handle"CHILD INSERT SET NULL'CHILD UPDATE SET NULL'PARENT DELETE SET NULL'PARENT UPDATE SET NULL'd {''''R/5 Purpose Text"PurposeTemplate Handle"CHILD INSERT SET NULL'CHILD UPDATE SET NULL'PARENT DELETE SET NULL'PARENT UPDATE SET NULL'dO'''' R/6 Purpose Text"PurposeTemplate Handle"CHILD INSERT SET NULL'CHILD UPDATE SET NULL'PARENT DELETE SET NULL'PARENT UPDATE SET NULL'd L''''LLR/7 Purpose Text"PurposeTemplate Handle"CHILD INSERT SET NULL'CHILD UPDATE SET NULL'PARENT DELETE SET NULL'PARENT UPDATE SET NULL'd@''''R/8 Purpose Text"PurposeTemplate Handle"CHILD INSERT SET NULL'CHILD UPDATE SET NULL'PARENT DELETE SET NULL'PARENT UPDATE SET NULL' d%dE!''''EdEd R/11 Purpose Text"PurposeTemplate Handle"CHILD INSERT SET NULL'CHILD UPDATE SET NULL'PARENT DELETE SET NULL'PARENT UPDATE SET NULL' d ;h;'''';; R/12 Purpose Text"PurposeTemplate Handle"CHILD INSERT SET NULL'CHILD UPDATE SET NULL'PARENT DELETE SET NULL'PARENT UPDATE SET NULL'dm vMh%''''v%v% R/14 Purpose Text"PurposeTemplate Handle"CHILD INSERT SET NULL'CHILD UPDATE SET NULL'PARENT DELETE SET NULL'PARENT UPDATE SET NULL'd I0''''00R/15 Purpose Text"PurposeTemplate Handle"CHILD INSERT SET NULL'CHILD UPDATE SET NULL'PARENT DELETE SET NULL'PARENT UPDATE SET NULL'diQ.'''' R/16 Purpose Text"PurposeTemplate Handle"CHILD INSERT SET NULL'CHILD UPDATE SET NULL'PARENT DELETE SET NULL'PARENT UPDATE SET NULL'U
splay% / %SubjectArea%| | ,|` (QTime% , FF ( s |W In M  b{LmOQS.J@%l!h5 ;;;h;  %dEdEdE! @LLLO { | I000mvMv%v%h% iQ. yN /lI3-GK[Display1`| | P|` Q|[| ||a f PFF%File% -- %Display% / %SubjectArea%%CurrentPage% / %TotalPages% -- %Time% , %Date%<%winspoolAcrobat PDFWriterLPT1:Acrobat PDFWriter$ oK``$@Default Schema Generation     !"#$%&'()*+,-./0123456789:;<=>?0mmDefault Option Set      !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~