Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
thenearfuture
Starting Member
35 Posts |
 Posted - 2008-02-28 : 10:18:59
|
| At my office, we've been slowly working on putting together a data warehouse.We're a financial services company and one of the services that we offer is debt collection. As far as reports go, our clients are interested in knowing how much money we collect over time. In particular, they want to know how many payments we've gotten 5, 10, and 15 months (and so on) after we receive a case. (Obviously, the 5-month payments are also included in the 10 and 15-month calculations). When I wrote this report using our transactional database, I was completely new to SQL and the ever-resourceful Patron Saint took pity on me, so you can see a good description of the details at [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78510[/url]Now that I'm no longer a total newbie at SQL, and having been through a relatively extensive seminar on data warehousing, I've been entrusted with researching certain aspects of data warehouse development (rest easy, though, folks - the real DWH work is not being done by the very inexperienced me, but by an actual professional :) ).My question: how would you model this kind of "relative time" in a data warehouse? How would you display the 5-month, 10-month, and 15-month payments in a DWH? I can't really imagine that the kinds of joins necessary to do this in a transactional database would be desirable in a data warehouse. We have the following:1.) FACT_Payment: A fact table showing each each payment to the most detailed granularity. One attribute of this table is the payment date. Another attribute is a foreign key to the case dimension described below. 2.) DIM_Case: A dimension table showing information on each case, including the case start date. DIM_Case3.) DIM_Date: A date dimension table. (For added clarification: The FACT_Payment payment date has to be 5, 10, 15 months etc... after the DIM_Case start date.) Any ideas, comments, experience with something like this?Thank you. |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-02-28 : 11:13:06
|
quote:
Originally posted by thenearfuture
Now that I'm no longer a total newbie at SQL, and having been through a relatively extensive seminar on data warehousing,...
Let me guess....Kimbal?e4 d5 xd5 Nf6 |
 |
|
|
thenearfuture
Starting Member
35 Posts |
Posted - 2008-02-28 : 11:27:11
|
quote:
Originally posted by blindman
quote:
Originally posted by thenearfuture
Now that I'm no longer a total newbie at SQL, and having been through a relatively extensive seminar on data warehousing,...
Let me guess....Kimbal?
Kimball Group? Naw, it was through Intelliminds. A consultant set up camp in our conference room for about a week and put us through the paces. It was a very good foundation, and I'm trying to build upon it as much as I can. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-02-28 : 12:32:48
|
| The references to FACT tables and DIMENSIONs led me to assume that this is some derivative of Kimball's cookie-cutter one-design-fits-all approach, which is really more suited to datamarts than datawarehouses.Regardless, what makes you think that your joins and queries would not be desirable in a data warehouse? In DW, query execution time is less important than in a transactional database.e4 d5 xd5 Nf6 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-02-28 : 14:00:59
|
The issue of relative dates is something that I addressed in this date table:Date Table Function F_TABLE_DATEhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519If particular, the following columns can be used for this because they represent a sequential series of numbers for that date attribute:[DATE_ID] [int] not null [YEAR] [smallint] not null ,[QUARTER_SEQ_NO] [int] not null ,[MONTH_SEQ_NO] [int] not null ,[WEEK_STARTING_SUN_SEQ_NO] [int] not null ,[WEEK_STARTING_MON_SEQ_NO] [int] not null ,[WEEK_STARTING_TUE_SEQ_NO] [int] not null ,[WEEK_STARTING_WED_SEQ_NO] [int] not null ,[WEEK_STARTING_THU_SEQ_NO] [int] not null ,[WEEK_STARTING_FRI_SEQ_NO] [int] not null ,[WEEK_STARTING_SAT_SEQ_NO] [int] not null ,[JULIAN_DATE] [int] not null ,[MODIFIED_JULIAN_DATE] [int] not null , For example, if you are interested in the prior five months and current month and you have a date table named T_DIMENSION_DATE that was loaded with the F_TABLE_DATE function on the link above:select a.*, [RELATIVE_MONTH] = a.[MONTH_SEQ_NO]-b.[MONTH_SEQ_NO]from T_DIMENSION_DATE a join ( -- Select MONTH_SEQ_NO for today select top 1 bb.[MONTH_SEQ_NO] from T_DIMENSION_DATE bb where bb.[DATE] = dateadd(dd,datediff(dd,0,getdate()),0) ) b on -- Select 5 prior months and current month a.[MONTH_SEQ_NO] between b.[MONTH_SEQ_NO]-5 and b.[MONTH_SEQ_NO]order by a.[DATE] It is relativly easy to write table valued functions to encapulate this logic to make this sort of query even easier.Edit:Added relative month column that will have values of -5 through 0. This is very useful for crosstab type reportsCODO ERGO SUM |
|
|
|
thenearfuture
Starting Member
35 Posts |
Posted - 2008-03-03 : 07:05:49
|
quote:
Originally posted by blindman
Regardless, what makes you think that your joins and queries would not be desirable in a data warehouse? In DW, query execution time is less important than in a transactional database.
We're mainly using our DWH to create reports and I've gotten the impression that one of the advantages of a DWH (and please correct me on this) is that the information is organized so as to be more easily accessible than in a transactional database. In my head, this means that queries that would be relatively complicated in a transactional database should be a little easier in a DWH. I realize that my experience and knowledge is very limited, and I would appreciate hearing more about your views on this, if you have the inclination and time. |
|
|
|
thenearfuture
Starting Member
35 Posts |
Posted - 2008-03-03 : 07:11:08
|
| Michael Valentine Jones - Thank you! I had actually populated our date dimension with your function already, as it was light years ahead of the one our consultant had made - and it just keeps getting better and better. I think I'll spend a lot more time in your Script Library posts to learn how to use these tools better. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-03-03 : 10:07:50
|
quote:
Originally posted by thenearfuture
Michael Valentine Jones - Thank you! I had actually populated our date dimension with your function already, as it was light years ahead of the one our consultant had made - and it just keeps getting better and better. I think I'll spend a lot more time in your Script Library posts to learn how to use these tools better.
I went back and added a relative month column to my original example. This makes it a lot easier for reporting because the query remains the same, and reruns for prior day reports is very easy. As I said before, this is good logic to put into a table valued function to make it even easier to use for reporting.CODO ERGO SUM |
|
|
|
|
|
|
|
|
Relative time in a data warehouse