dimension(day ranges)

View previous topic View next topic Go down

dimension(day ranges)

Post  kball15 on Mon Nov 30, 2015 1:08 pm

Please I have a Fact table that records a call for each customer, there is a requirement to know how many customer picked up in the last 0- 30 days ,31-60 , 61days and above.
I was thinking to have like a stub dimension where by there will be a column for the PK and another for the range of days, but my concern is since the days from the date of call will always increase daily, then the corresponding pk will change as well and that will mean the fk in the facttable will keep changing for a row.
The other thought is to calculate the amount of customer of the date ranges outside of the warehouse using sql.
please is there a better way

kball15

Posts : 5
Join date : 2015-11-30

View user profile

Back to top Go down

Re: dimension(day ranges)

Post  ngalemmo on Mon Nov 30, 2015 1:30 pm

Store a relative date number in the date dimension.

You have two columns. One contains a sequence number assigned to the date chronologically from the beginning of the calendar. You use that column to maintain a second column which contains the sequence relative to 'today'. The second column is calculated daily as today's sequence value minus the other day's sequence value. The second column would contain 0 for today, negative values for future dates and positive values for past dates. You then simply group/filter on a range of these numbers. For example, values 1 through 30 would represent the last 30 days.

You can use the same technique for any calendar period. A typical data dimension would contain relative sequences for weeks, months, fiscal periods and so forth.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: dimension(day ranges)

Post  kball15 on Mon Nov 30, 2015 1:51 pm

Thanks, will work on it, and update you

kball15

Posts : 5
Join date : 2015-11-30

View user profile

Back to top Go down

Re: dimension(day ranges)

Post  nick_white on Tue Dec 01, 2015 5:26 am

Are you using a BI tool (SSRS, BO, OBIEE, Cognos, etc.)? If so then these should all allow you to set up calculated fields that do this quite easily
If not then I would implement the logic using DB views.

IMO - anything that has a value dependent on another moving value (such as "today") is much better implemented in a BI Tool or a DB View rather than stored using persistent values in a DB

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: dimension(day ranges)

Post  kball15 on Tue Dec 01, 2015 5:37 am

I am using ssas and ssrs, I added a column to the date dimension that subtract each date column from today to get the date(I will move the column to the ssas Data source view as suggested. Thanks

kball15

Posts : 5
Join date : 2015-11-30

View user profile

Back to top Go down

Re: dimension(day ranges)

Post  ngalemmo on Wed Dec 02, 2015 1:44 am

Either way works. You can do a daily update or calculate on the fly by doing a self join of the dimension to find today's row.

The difference is; doing a daily update allows you to control when 'today' is. If you are doing nightly batch loads of the data warehouse, you can control what the user sees by controlling when to declare 'today'. So if you have load issues, you can delay access to today's transactions by not advancing the date until the problem is resolved.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: dimension(day ranges)

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum