Geeks With Blogs
Prakash P April 2008 Entries
Order by Desc in a SSAS dimension
I am not sure if there is any better way, But I am sure there is no obvious way of "Order by Desc" in a SSAS dimension. The actual requirement was to "order by asc" for Hierarchy1(with 2 levels) and "Order by desc" for Hierarchy2(with 2 levels) , both hierarchies under the same dimension. This is where Row_Number() function was helpful. We included 2 new columns Sort_Order_Asc and Sort_Order_Desc with the following definition: Row_Number() Over(Order by Date) AS Sort_Order_Asc Row_Number() Over(Order ......

Posted On Friday, April 18, 2008 7:22 AM

Creating a Time Hierarchy with only data for Last 2 years
An interesting problem that I had faced! I had a time dimension which required a hierarchy with only data for Last 2 years. My source view obviously had data for all the periods and not only for 2 years. The Hierarchy had 2 levels Month(Ex: Feb 2006) and Date(19th March 2007). I could have created a new view to implement the requirement but thought it would be better if I can do it from the existing view. So the solution was as follows: 1. Created 2 new colums with the following logic LAST24_MONTHS ......

Posted On Thursday, April 17, 2008 8:02 AM

Copyright © SQLBI | Powered by: