tag:blogger.com,1999:blog-8884584404576003487.post6764751622545825998..comments2024-02-29T09:43:12.251-05:00Comments on ORACLENERD: How To Populate Your TIME Dimensionoraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.comBlogger13125tag:blogger.com,1999:blog-8884584404576003487.post-39060815127104155472012-01-10T10:11:25.118-05:002012-01-10T10:11:25.118-05:00@nilay9999
I don't have it available, but I m...@nilay9999<br /><br />I don't have it available, but I might be able to find it.<br /><br />How is your fiscal year defined? July - June?oraclenerdhttps://www.blogger.com/profile/12412013306950057961noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-74818053559085121142012-01-10T04:44:24.959-05:002012-01-10T04:44:24.959-05:00Hi,
I am currently having big trouble trying to cr...Hi,<br />I am currently having big trouble trying to create a Time dimension table With fiscal year support (not starting in January). You seem to have removed just that!<br /><br />I know this is an old post, but do you by any chance have the script or the link to one, that I can refer to?<br /><br />Thanks in advance!nilay9999https://www.blogger.com/profile/08025452328660959434noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-38617695911843974962012-01-10T04:42:30.149-05:002012-01-10T04:42:30.149-05:00Hi, I know this is an old post, but you mentioned ...Hi, I know this is an old post, but you mentioned you Removed the complicated fiscal calculations. I infact need just those.<br /><br />Do you by any chance still have the script that figured fiscal years into the calculation? Been looking for one everywhere!nilay9999https://www.blogger.com/profile/08025452328660959434noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-82826393225899141872011-09-14T14:53:52.016-04:002011-09-14T14:53:52.016-04:00@anonymous
No oversight.
Should be fairly simpl...@anonymous<br /><br />No oversight. <br /><br />Should be fairly simple to add in there though.<br /><br />chetoraclenerdhttps://www.blogger.com/profile/12412013306950057961noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-28849970673415333912011-09-12T10:39:10.079-04:002011-09-12T10:39:10.079-04:00Great stuff,
just one comment, I don't see &qu...Great stuff,<br />just one comment, I don't see "SEMESTER", was that just an oversight?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-43072648665319964032010-09-11T13:26:39.590-04:002010-09-11T13:26:39.590-04:00@Chet: great stuff... nice starting point for anyo...@Chet: great stuff... nice starting point for anyone that needs to do this.<br /><br />@Jiri: <br /><br />You said: "I would recommend to add all date periods (not just days) but also MONTHS, QUARTERS, YEARS, ... and corresponding PERIOD_TYPE_CD which would be DAY / MONTH / QUARTER / YEAR ... this way your period table really covers all periods and can be used in all snapshot fact tables even e.g. monthly snapshot tables (for example PERIOD_KEY = 111 would correspond to 201004 - whole month)."<br /><br />I couldn't disagree more. The number one rule in dimensional modeling is to never mix grains in the same table. If you really need a "shrunken dimension"... then create a new dimension table called, called MONTH_DIM for instance, that is not at the grain of an individual day, but instead at the grain of a month. This is also easy to do with a view over the daily dimension table. That's also why I think the date dimension table should always be called DATE_DIM. A dimension table name should always specify the grain of the table.<br /><br />If you go with your PERIOD_TYPE approach... every single join of a fact table to the PERIOD table would require a filter on PERIOD table. Why do that? It just begs for a user to make a mistake and overallocate. Keep it simple... use another table or view.<br /><br />However... if you use Oracle, why go through the process of building aggregate tables at all? The database will do this for you with fairly little effort. Search for "oracle query rewrite" and see where that takes you.Stewart Brysonhttps://www.blogger.com/profile/16904522901298746643noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-76514240278287816182010-04-04T22:24:03.830-04:002010-04-04T22:24:03.830-04:00Interesting article.
If I can add my two cents ba...Interesting article.<br /><br />If I can add my two cents based on our 80TB data warehouse and one single period table ...<br /><br />1. I would recommend to add all date periods (not just days) but also MONTHS, QUARTERS, YEARS, ... and corresponding PERIOD_TYPE_CD which would be DAY / MONTH / QUARTER / YEAR ... this way your period table really covers all periods and can be used in all snapshot fact tables even e.g. monthly snapshot tables (for example PERIOD_KEY = 111 would correspond to 201004 - whole month).<br /><br />2. in general, I always felt that PERIOD table is nice to have for anything I can predict (e.g. is next day Monday? type questions), it is critical for non-predictable information e.g. Federal Holidays, Retail Calendar, "is my servicer sending data today", ... that's where you get real interest from user community and where people stop asking why you replaced all dates with key.<br /><br />3. small comment about name (I know I am too picky), I would not call it TIME dimension but PERIOD or something like that. It really tracks days, not time (hours, minutes, seconds, ...)Unknownhttps://www.blogger.com/profile/04728658238512679074noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-48379725079098222262009-12-19T18:52:42.214-05:002009-12-19T18:52:42.214-05:00Thank you very much for making it clear.
In estim...Thank you very much for making it clear.<br /><br />In estimation of another field days_in_cal_year +1 is needed I think.<br /><br />( TO_DATE( '12/31/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )<br />- TRUNC( sd + rn, 'YEAR' ) ) days_in_cal_year : It gives 364 or 365 days for a day. I used it like this:<br /><br />( TO_DATE( '12/31/' || TO_CHAR( sd + rn, 'YYYY' ), 'MM/DD/YYYY' )<br />- TRUNC( sd + rn, 'YEAR' ) + 1 ) days_in_cal_year<br /><br />and got the desired results.<br /><br />Have a good day or night depending on where you are :)Saimhttp://msaimkuru.blogspot.comnoreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-20646965296633944932009-12-19T18:27:09.693-05:002009-12-19T18:27:09.693-05:00@Saim
That would depend on your requirements of c...@Saim<br /><br />That would depend on your requirements of course...I used Oracle's default which is day 1 of the week is Sunday and day 7 is Saturday, it's fairly trivial to adjust according to your needs.<br /><br />chetoraclenerdhttps://www.blogger.com/profile/12412013306950057961noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-67991916365703507842009-12-19T17:43:57.084-05:002009-12-19T17:43:57.084-05:00Hi
I must say that It is a good source first of a...Hi<br /><br />I must say that It is a good source first of all.<br /><br />I think for estimating week_ending_date SUNDAY must be used instead of Saturday.Saimhttp://msaimkuru.blogspot.comnoreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-25044942452631228632009-11-10T05:42:54.387-05:002009-11-10T05:42:54.387-05:00Thank you. That's very helpful.Thank you. That's very helpful.Unknownhttps://www.blogger.com/profile/12465574680505486861noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-54487667789240406402009-03-13T09:23:00.000-04:002009-03-13T09:23:00.000-04:00Good point Chris.I was "rushing" through it and ga...Good point Chris.<BR/><BR/>I was "rushing" through it and gave it a brief thought, but didn't really pursue it...oraclenerdhttps://www.blogger.com/profile/12412013306950057961noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-9084437995596178812009-03-13T04:03:00.000-04:002009-03-13T04:03:00.000-04:00Hi!Just a quick observation: As you are always cal...Hi!<BR/><BR/>Just a quick observation: As you are always calculating sd+rn it might be convenient to put that in the driving query.Chrishttps://www.blogger.com/profile/00349422981782291908noreply@blogger.com