none
Modifying Date Dimension

    Question

  • Hello Everyone.

    I need some help in renaming some attributed in the date dimension in SSAS. Please see the pic below

    It is required that the Cube reflects fiscal year. I have done this by changing the parameters in AX 2012 SysAdmin-Setup->Analysis Services->date dimension. After refreshing the cude it works fine. But each fiscal year comes out like the image.

    I now have to change the name "Calendar 2011", "Calendar 2012" etc.... to Fiscal Year 2011", "Fiscal Year 2012"..... Any help would be greatly appreciated.


    • Edited by Lionelf07 Thursday, July 12, 2018 4:54 PM
    Thursday, July 12, 2018 4:52 PM

Answers

  • >>Actually the way i did it, It did not create a new column. When i crated the new name query i used REPLACE(YEAR_NAME, 'Calendar', 'Fiscal Year') AS YEAR_NAME and then unchecked YEAR_NAME in the query but select everything else. YEAR_NAME was already a column in the table BIDATEDIMENSIONSVIEW.

    You need to select this column in the data source view.  The steps are as follows

    1. Modify the named query in the data source view. Ensure the column is checked.

    2. In the dimension editor, drag the Year_Name column from the right hand pane to the left pane (attribute section)

    3. Process the dimension. You should be able to view the changes.

    Can you please post the complete screenshot of the dimension attributes? The last screenshot which you posted had a scroll bar and we were not able to confirm if Year_name was there as part of the attribute section.

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps

    • Marked as answer by Lionelf07 Tuesday, August 7, 2018 7:11 AM
    Friday, July 13, 2018 6:47 PM
  • Hi Lionelf07,

    Could you please verify if you have set proper attribute relationship in your dimension? And also please review your dimension usage tab between the date dimension and measure groups and check if the relationships are intact.

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps

    • Marked as answer by Lionelf07 Wednesday, August 8, 2018 4:50 AM
    Tuesday, August 7, 2018 9:27 AM

All replies

  • Hi Lionelf07,

    From the above description, I understand you want to rename the members of the dimension and not the attributes of the dimension.

    If this is the case,then you need to change it at the data source level.

    You can do it in 2 ways

    1. You can create view and add another custom column which replaces keyword Calendar with Fiscal.

    2.Or you can edit the dsv with a named query where you can add additional column which replaces keyword calendar with Fiscal.

    For more information on Replace function in SQL, please see this link.

    This will create a new attribute called Fiscal Year and you can use this attribute in reports.

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps

    Thursday, July 12, 2018 8:32 PM
  • Hi Lionelf07,

    Thanks for your question.

    Ram has already give you a solid solution, however, I would suggest you to use the second option, add a named calculation in the data source view of your SSAS project. This is especially useful when you did not have write permission to the underlying database.

    Assuming you have a column called Year contain data like below:
    1899,2011,2012,2013,2014,2015,2016,2017,2018,2019,2154
    Then please right click on the date table, select New Name Calculation...

    In the Create Named Calculation, set column name as FiscalYear, write expression as below:
    'Fiscal Year '+ CONVERT(CHAR (4), Year)

    In the date dimension, click on Year

    in dimension attribute Year window, you can set its NameCoulmn as FiscalYear.

    After deployment, you will get the desired results.

    If you have write permission to underlying database, in your scenario,the easy way is to update the underlying data, see below update T-SQL:

    update date set [Year Desc] = REPLACE([Year Desc],'Calendar','Fiscal Year'); 
    After deployment, you will get the desired results.

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, July 13, 2018 3:11 AM
    Moderator
  • Hello Yuri, Thanks a lot for giving me the idea. this is what I  have done

    1. open Visual Studio where I have the  cube project(this is DAX default cubes)

    2. I then open the view and find the view called "BIDATEDIMENSIONSVIEW

    3. I right click on this view and select replace table-> with named query, I then run the following query: 

    SELECT     REPLACE(YEAR_NAME, 'Calendar', 'Fiscal Year') AS YEAR_NAME, NAME, DATEDIMENSIONTYPE, ENDDATE, FIRSTDAYOFWEEK, HASHALFYEAR, HASMONTH, 
    HASQUARTER, HASTENDAYS, HASTRIMESTER, HASWEEK, HASYEAR, LABEL, PERIODENDDATE, PERIODSTARTDATE, STARTDATE, STARTDAY, STARTMONTH........

    FROM    BIDATEDIMENSIONSVIEW

    4. The value in column YEAR_NAME changed to "Fiscal Year"

    5. I then refresh the DSV then reprocess the cube but nothing has changed.

    Can you please tell me what I did wrong? I really need this to work as I am asked to implement it with very little time.

    Any help is greatly appreciated.  

    See screen shots..

    

                         


    Friday, July 13, 2018 4:54 AM
  • Hi Lionelf07,

    Thanks for your response.

    Could you please share the screen shot the user hierarchy used in your original post? Based on your screen shot, it seems the Year_Name is not the column used, otherwise, this will work for you.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, July 13, 2018 5:09 AM
    Moderator
  • Hello Wilson,

    Thanks for helping out. What I want to rename is the members of the dimension and not the attributes of the dimension. I tried it RAM way but when i refresh process my cube nothing happened. Am still getting Calendar 2011, Calendar 2012 etc. What i want is Fiscal 2011, Fiscal 2012.....

    All this I did in visual studio. 

    when i go to Views in AOT i still see Calendar 2011, Calendar 2012... is there something i should do to import what i have in visual studio into AOT? I thought AOT is independent of the cubes.

    Please advise, thanks

    Friday, July 13, 2018 5:17 AM
  • Sir Wilson, 

    I had to track down the column from which "Calendar" was coming from. Its coming from BIDATEDIMENSIONVIEW

    this is why i went and apply the query to it. the do a refresh on the DSV and reprocess the cubes. but am still getting the same old format (Calendar 2011.....)

    Friday, July 13, 2018 5:28 AM
  • Hi Lionelf07,

    Thanks for your response.

    Could you please share the screen shot of the user hierarchy and the date dimension used in your original post?


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, July 13, 2018 5:37 AM
    Moderator
  • Sir Wilson, I hope this is what you a re asking for. Forgive me if its not but please note am just only learning how to use these tools

    Friday, July 13, 2018 5:54 AM
  • Hi Lionelf07,

    Thanks for your response.

    I mean your Date dimension which you browser in your original post. see below screen shot:


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, July 13, 2018 6:09 AM
    Moderator
  • Sir Willson, here you go, sorry the 'all nighter' got the best of me.

    Please note this is the default date that SSAS generated when i created the cube from AX2012

    Friday, July 13, 2018 1:44 PM
  • Hi Lionelf07,

    what I am not able to see is the Year_Name attribute from your screenshot. Have you added the Year_Name attribute to your dimension?

    You should be able to see the new column Year_Name in the  which you have created from the below query in your date dimension. when you open the date dimension, you will have 3 sections - Attributes, Hierarchies and Data Source view.  Go to the DSV in your dimension and see if you have the new column Year_Name appearing. You just need to drag the Year name to the attibutes and process the dimension. The you will be able to see the changes.

    SELECT     REPLACE(YEAR_NAME, 'Calendar', 'Fiscal Year') AS YEAR_NAME, NAME, DATEDIMENSIONTYPE, ENDDATE, FIRSTDAYOFWEEK, HASHALFYEAR, HASMONTH, 
    HASQUARTER, HASTENDAYS, HASTRIMESTER, HASWEEK, HASYEAR, LABEL, PERIODENDDATE, PERIODSTARTDATE, STARTDATE, STARTDAY, STARTMONTH........

    FROM    BIDATEDIMENSIONSVIEW

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps

    Friday, July 13, 2018 2:07 PM
  • Actually the way i did it, It did not create a new column. When i crated the new name query i used REPLACE(YEAR_NAME, 'Calendar', 'Fiscal Year') AS YEAR_NAME and then unchecked YEAR_NAME in the query but select everything else. YEAR_NAME was already a column in the table BIDATEDIMENSIONSVIEW.

    I did it this way because it would not allow me to edit BIDATEDIMENSIONSVIEW I have to select the option select replace table and so it actually replace the BIDATEDIMENSIONSVIEW  with the name query. 
    Maybe am doing it wrong but i will continue fighting with it. If you have any other suggests I more than welcome them.

    Thanks much for your help

     

    Friday, July 13, 2018 4:03 PM
  • >>Actually the way i did it, It did not create a new column. When i crated the new name query i used REPLACE(YEAR_NAME, 'Calendar', 'Fiscal Year') AS YEAR_NAME and then unchecked YEAR_NAME in the query but select everything else. YEAR_NAME was already a column in the table BIDATEDIMENSIONSVIEW.

    You need to select this column in the data source view.  The steps are as follows

    1. Modify the named query in the data source view. Ensure the column is checked.

    2. In the dimension editor, drag the Year_Name column from the right hand pane to the left pane (attribute section)

    3. Process the dimension. You should be able to view the changes.

    Can you please post the complete screenshot of the dimension attributes? The last screenshot which you posted had a scroll bar and we were not able to confirm if Year_name was there as part of the attribute section.

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps

    • Marked as answer by Lionelf07 Tuesday, August 7, 2018 7:11 AM
    Friday, July 13, 2018 6:47 PM
  • Hello Ram,

    I was able to use named query to get what i wanted, thanks much for your help.

    One thing i notice though is that when i create my pivot table from the cubes and called in fiscal year, all the years have the same value. any idea as to what may have caused this. If I revert to calendar year i get the correct value. See images below. As you can see my new field gives me wrong data. this is using the sales cube.

    Tuesday, August 7, 2018 7:20 AM
  • Hi Lionelf07,

    Could you please verify if you have set proper attribute relationship in your dimension? And also please review your dimension usage tab between the date dimension and measure groups and check if the relationships are intact.

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps

    • Marked as answer by Lionelf07 Wednesday, August 8, 2018 4:50 AM
    Tuesday, August 7, 2018 9:27 AM
  • Ram, you are so right. All i had to do is create one attribute relation and it works fine. thanks much for you help
    Wednesday, August 8, 2018 4:52 AM