Help needed for date dimension which is not rolled up properly.
Hierarchy data, SSAS, SQL
I'm new to SSAS and I have dim_date table. Which have DateKey which is int and primary key (Eg data 19010101, 19010102 etc.,), date which is datetime(eg: 1901-01-02 00:00:00Z), year - smallint(Eg: 1901), month -tinyint(Eg: 01), monthname -varchar(January), day -tinyint (Eg: 1), dayname - Varchar (Eg: Tuesday), quarter -int(Eg: 1)I build a dimension using this table. I selected datekey, date, month, quarter and year as my attributes. I also created the attribute relation ships as quarter-year, month-quarter, date-month like that. when I created a hierarchy like below. Year- Quarter- Month- Date. But my hierarchy data is not rolled up properly. For example year 2079 have 4 th quarter. All the dates from the 4th quarter of years 1901 till 2079 rolled up under 2079. Can somebody help me why it is happening like that? Your help is greately appreciated.
Thank you.
