March 25, 2011 at 2:39 pm
I have a cube that has a dimDate table with dates from 1970 - 2098, mostly because of birthdays and the fact that the software we use allows them to put in garbage for dates in some cases.
The thing is, I don't want to show 1970-2098 in a pick list or a dropdown, I want to only allow people to choose 2002-2012 because that's the data we're concerned with.
(I also think that we have too many relationships set up between a lot of the columns which is slowing things down immeasurably, but that's another topic)
how can I allow facts to be sliced by a date range that doesn't include the whole dimDate table? I tried trimming down the dimDate table by using a query, but 1970-2035 is the best I can do because of weird references. Is there a way to create a view off of my dimDate table for the years I want, and create a separate dimension that I associate with the columns I want to slice and use that instead of the huge dimDate table?
April 29, 2011 at 12:44 pm
Did you figure this out? If not, maybe something along these lines will work for you...
http://www.sqlservercentral.com/Forums/Topic411760-147-1.aspx
http://bennyaustin.wordpress.com/2011/03/23/ssas-consider-cube-browsing-role-playing-dimensions
May 5, 2011 at 11:33 am
Matthew,
Your instinct seems correct to me. At my current client, we have one master date table (let's call it DateMaster) which is built well into the future. We then have a view built on DateMaster (let's call it dimDate) which restricts the members through the most recent business date. We have two database dimensions, one on the view and one on the table which are then roll-played many, many times. Future-looking dates are keyed to DateMaster and retro-looking dates are keyed to dimDate.
Good Luck,
Chris
Chris Umbaugh
Data Warehouse / Business Intelligence Consultant
twitter @ToledoSQL
May 9, 2011 at 5:30 am
Hi,
In addition to what has been already said, I would like to share my experience with this.
We have in our system as well Dates ranging from 1930 to 2049 and whwnever I would create reports, the drop down would show stupid entries for say 1936...Who would be anyone interested to see Sales of these Years, (even if the Data was present).
The solution was to identify some business conditions which can filter irrelevant Dates.
In my case, forcing the Condition, that only those Years by visible which have No_Of_Employees>0, This was a big relief, no future Date was now visible and the past Dates were also limited. Main advantage was that there was no hassle of creating a separate view. The filter was done on report itself with all Data intact.
Below is a sample code used in the DataSets used in report.
{ FILTER( { ORDER([DATE].[YEAR].[All].CHILDREN,[Measures].[ParameterCaption],DESC) }, ( [Measures].[No_Of_Employees) > 0 ) }
Please let me know if it helps...
Regards,
Athar Iqbal
May 9, 2011 at 7:02 am
Lots of good advice:)
The biggest issue is it isn't in reports but in Dundas Dashboards and Perf Point Dashboard Designer. I haven't found a way to limit it in Dundas which is why I was thinking of creating a view off the dimDate that only had say 2005-2012
thanks!
May 10, 2011 at 1:57 am
Some suggestions :
1.You can add an attribute 'Decade'. That way, your user can easily select the Decades that are relevant. Add the Decade on top of a Decade-Year drill down hierarchy.
2. You can define a named set in the cube which is composed of the current year and the years before (e.g. 10 years) and after the current year. This would act as a moving window.
3. You can add an attribute 'IsAReleventReportingYear' in a view on the Date dimension, used by the cube for the Date dimension. All relevant years are marked with 'True' either hardcoded or as a moving window (similar to suggestion 2).
Franky
Franky L.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply