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.

All Forums  SQL Server 2005 Forums  Analysis Server and Reporting Services (2005)  Date Dimension in SSAS

Author  Topic  vusil Starting Member 22 Posts Posted - 2010-03-25 : 10:26:47 Hi Guys, Please can I ask for help regarding a cube in SQL 2005 which does not return data for specific periods. The cube has a date dimension with 2 hierarchies, each with 4 levels. The one hierarchy is the calendar date (01 January 2010 - 31 December 2010) with levels (Year, Quarter, Month, Day). The default member for this hierarchy is current year-month. The other hierarchy is the fiscal date i.e. financial year running from 01 November 2009 to 31 October 2010 with levels (FiscalYear, FiscalQuarter, Month, Day). The default member is ALL. The default member for all the other dimensions in the cube is ALL. When querying the cube for data for the 1st quarter of the fiscal year (Nov 2009, Dec 2009, Jan 2010)the only data returned is for Jan 2010. No data gets returned for any dates prior to Jan 2010. However, when the default member for the calendar date hierarchy is changed to ALL then the data is returned correctly. I would like to keep the calendar date hierarchy as is - defaulting to the current year and month - but still be able to query the cube for past data based on the fiscal date hierarchy. How can this be achieved? Thanks in advance. Regards