April 28, 2009 at 5:00 am
On my time dimension i have got fields like Fiscal Month Name and Fiscal Month No. I would like to get the cube in ascending order of Fiscal MonthNo. ie. From APRIL to March. but i am getting the cube in January to december order. How can i re arrange my cube data to APRIL to March by using the FiscalMonthName and Fiscal Month No fields. i already tried the order by Attribute property on Fiscal MonthName to Fiscal Month No and Value column as fisalMonthNo still the cube is n't the correct order.
Fis Month Name Fiscal Month No
JANUARY 10
FEBRUARY 11
MARCH 12
APRIL 1
MAY 2
JUNE 3
JULY 4
AUGUST 5
SEPTEMBER 6
OCTOBER 7
NOVEMBER 8
DECEMBER 9
May 6, 2009 at 3:04 am
Hi,
Drag your Monthname and Fiscal Month No to the Attributes pane for your dimension.
Create a hierarchy of English Month Name, and Fiscal Month No underneath.
Right Click English Month Name in the Attributes pane and select properties.
Change the KeyColumns to FiscalMonthNo
Change the NameColumn to MonthName
Change the OrderBy from Name to Key
In your Attribute Relationships break the connection between DateKey and MonthName
Make a connection from FiscalMonthNo to MonthName (so Date Key -> Fiscal Month No -> Month Name
Deploy the project and check in Browser to see if they're displayed April -> March
I think that's it, there's more to it than that to ensure no duplicate keys when using more than one year, but hopefully this will put you on the right path.
Reply and let me know if there's any problems,
Cheers, Jode
ps. you can remove the fiscalmonthno from the hierarchy after, its' just to check its all in the correct order.
May 6, 2009 at 6:04 am
Hi Jody,
i tried the way that you are suggested, but it didn't work. i can see the attributes are arranged by the fiscalMonthNo in proper order ie from APRIL-March and also the Fiscal MonthNo also arranges in ascending order from 1to 12. But in cube browser fiscalMonthName always from January-February. If I add FiscalMonthNo and FiscalMonthName to the cube browser, i can see the desired result from APRIL-March. if i take it away the fiscalMonthNo Monthname will automaticcaly change to January-December. Monthname in cube browser didn't preserve the orderby fiscalMonthNo attribute relation.
regards
sebastian
May 6, 2009 at 7:40 am
Hi Sebastian,
I'm not sure of the reason for that. I set up a test to have a go.
When I query the cube with mdx from management studio it displays from April to March as you'd hope.
For instance if we'd changed the DimDate in AdventureWorksDW2008 to add a new column FiscalMonthNo and updated it for each month. Then built the cube as above - using FactCallCenter in this example. When running something like...
select [Fact Call Center].[Fact Call Center ID] on columns,
[Dim Date].[English Month Name].members on rows
from [Adventure Works DW2008]
... it comes back in the correct order. April -> March. If you use Excel it may come back correctly to, which I guess is how your users are querying it? In Cube browser I get the same incorrect result as yourself.
I'll have another look in a bit, but maybe that helps.
cheers, Jode
May 6, 2009 at 8:35 am
Can't seem to edit my last post, so apologies for the double email you may get.
Tried in Reporting Services (2008) and couldn't get it to order correctly, despite all manner of sorting attempts, so i'll have to crack out a book and work on that, unless someone else can advise.
However tried in Excel (2007) and it displays in the correct order.
So the method I described above seems okay if you're presenting your data through Excel or SSMS. Through SSAS Cube Browser and Reporting Services.. not so good.
---
Edit : Looks like SSRS ignores the orderby key by design. So had to bring the fiscalno into the report itself and order on that (and change its visibility to false) to get the months in the correct fiscal order.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply