Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

dimension(day ranges)

3 posters

Go down

dimension(day ranges) Empty dimension(day ranges)

Post  kball15 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

Back to top Go down

dimension(day ranges) Empty Re: dimension(day ranges)

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

dimension(day ranges) Empty Re: dimension(day ranges)

Post  kball15 Mon Nov 30, 2015 1:51 pm

Thanks, will work on it, and update you

kball15

Posts : 5
Join date : 2015-11-30

Back to top Go down

dimension(day ranges) Empty Re: dimension(day ranges)

Post  nick_white 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 : 364
Join date : 2014-01-06
Location : London

Back to top Go down

dimension(day ranges) Empty Re: dimension(day ranges)

Post  kball15 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

Back to top Go down

dimension(day ranges) Empty Re: dimension(day ranges)

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

dimension(day ranges) Empty Re: dimension(day ranges)

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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