November 29, 2007 at 3:57 am
Hello
I have not been doing BI SQL dev work for very long so pardon me if this seems like a simple question.
I am selecting data into a reporting database using a an SSIS package, maybe turn the reporting db into a Cube if needed. The datetime data I show in the reports however needs to be grouped and shown in a specific calendar grouping as follows the month starts on the first Monday of the month and the month ends on the last Sunday before the first Monday( for example this coming December would start on the 3rd of Dec because it is the first Monday, and end on the 6th of January because January starts on the 7th because it is the first Monday) so the data would need to be grouped by this custom month, and by week for example week beginning Dec 3rd 2007, 10 Dec 2007, etc. .
What is the best way to achieve this, Should I populate the reporting database with two extra columns that state that this row of data belongs to this month, and this week - and if this is the best way - how can I do that ?
Should I move this to an analysis Service Cube - can SSAS 2005 offer a better solution?
Should this be done on the actual reports? -
Any help would really be appreciated
Many Thanks
Olaf
November 29, 2007 at 6:15 am
I use a Days dimension table. Day is a dimension in the fact table. The Days table (containing 10yrs, or whatever you need) can have whatever grouping you need and additional can be added without adding columns to your fact table. Look up Ralph Kimball and dimensional design.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply