none
How to change the displayed label for attributes in a date dimension hierarchy

    Question

  • Hi guys,

    can't get rid of built-in label prefixes such as "Calendar {year}" on my date hierarchy.

    I just want it to be "2016" , "2017", etc.

    I'm using SQL server 2012 SP3 CU7.

    Any idea how to do it?

    Thanks in advance!

    Monday, March 06, 2017 11:39 AM

Answers

  •      

    Hi SSAS_IL,

    Thanks for your response.

    In this scenario, the easiest way is to add the new created time dimension table to your oracle data source view, build relationship to the exist fact table, then you may need to recreate a time dimension based on this table.

    To add the new created time dimension table to your oracle data source view, in your oracle data source view, right click on empty place, select "Add/Remove tables...".In "Add/Remove tables" windows, select the new created SQL data soure, add this new created time dimension table, click on "OK" to finish.

    After that, you can recreate a time dimension based on this table.
    In the first step of new dimension wizard,select use an existing table.

    then you can select the new created time table in your oracle data source view.

    After creating this time dimension, you can add it to the cube.
    To create date dimension use an existing table, you can refer to Creating Date dimension from scratch in Microsoft SSAS:https://www.codeproject.com/Articles/1060016/Creating-Date-dimension-from-scratch-in-Microsoft

    One more thing, Please kindly mark all the correct replies as answers. By doing so, it will benefit all community members who are facing similar issues. Your contribution is highly appreciated.


    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


    Wednesday, March 15, 2017 9:47 AM

All replies

  • Hi SSAS_IL,

    Thanks for your question.
    According to your description, you want to display with 2016 instead of Calendar 2016, right?

    If you do want to do this, you may set properties KeyColoumns, NameColumn and ValueColumn to the column which contain values "2016","2017" ,etc.
    Take Calendar Year in AdventureWorks as an example,to show 2016 instead of Calendar 2016 for dimension attribute Calendar Year, please go to the properties of dimension attribute Calendar Year, set properties KeyColoumns, NameColumn and ValueColumn to Date.CalendarYear(SmallInt).


    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

    • Proposed as answer by alexander fun Monday, March 13, 2017 10:52 AM
    Tuesday, March 07, 2017 3:06 AM
  • Thank you for your reply Willson,

    Unfortunately my properties look a bit different than yours.
    I think it has to do with schema not being generated (my SSAS is connected to an Oracle DB).
    So, what I get in KeyColoumns, NameColumn and ValueColumn is only one option: New Binding(WChar).
    When I try to change those values in the above attributes, I only get "Generate column" and "(none)" and a warning saying something about creating a new column when generating the schema.
    Trying to generate a schema fails on "Database is not MS-SQL".

    Any suggestions?

    Thanks!

    Tuesday, March 07, 2017 5:09 AM
  • Hi SSAS_IL,

    Thanks for your response.

    This should not be related to schema. 
    Can you explorer data in data sourece view for Date dimension table?
    Could you please share the screen shot of the properties for dimension attribute like i do?

    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

    Tuesday, March 07, 2017 5:31 AM
  • Will need my MSDN account to be verified in order to upload images... I'm too green :-)
    Tuesday, March 07, 2017 7:21 AM
  • Hi SSAS_IL,

    Thanks for your response.

    I have voted for you. Now you can upload images.

    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, March 10, 2017 9:58 AM
  • Are u using  a Server Date Dimension? or is it based on an table in your source?
    Friday, March 10, 2017 10:02 AM
  • Thanks Willson

    Tuesday, March 14, 2017 5:19 AM
  • Thanks for your reply.

    I don't have a date table in my database, so I let SSAS create a date dimension automatically from the wizard.

    Also, I'm using Oracle DB as my source.

    Tuesday, March 14, 2017 5:21 AM
  • Hi,

    Create your own individual Datedimension in your relational source.

    Tuesday, March 14, 2017 8:04 AM
  • Hi SSAS_IL,

    Thanks for your response.

    In this scenario, because you can not create a date dimension table in your Oracle DB, then you may need to create your own Individual date dimension table in your relational source as Yger said. After that, you will be able to  do the changing things for your date dimension hierarchy.

    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

    Tuesday, March 14, 2017 8:32 AM
  • Thanks Willson and Yger,

    We're getting somewhere!!

    I've build the DimDate table on my SQL DB that holds SSAS, created a new data source view to show my SQL SERVER tables, and created a dimension for that newly created table. It works fine

    However, when trying to add this dimension to my original cube (the one that was created from my Oracle DSV using dimensions based on Oracle tables - it won't let me!

    The Dim Date dimension is not showing in the list of available dimensions to be added to the cube.

    Looks like I'm almost there... would appreciate help for the last mile... Thanks 

    Wednesday, March 15, 2017 9:22 AM
  •      

    Hi SSAS_IL,

    Thanks for your response.

    In this scenario, the easiest way is to add the new created time dimension table to your oracle data source view, build relationship to the exist fact table, then you may need to recreate a time dimension based on this table.

    To add the new created time dimension table to your oracle data source view, in your oracle data source view, right click on empty place, select "Add/Remove tables...".In "Add/Remove tables" windows, select the new created SQL data soure, add this new created time dimension table, click on "OK" to finish.

    After that, you can recreate a time dimension based on this table.
    In the first step of new dimension wizard,select use an existing table.

    then you can select the new created time table in your oracle data source view.

    After creating this time dimension, you can add it to the cube.
    To create date dimension use an existing table, you can refer to Creating Date dimension from scratch in Microsoft SSAS:https://www.codeproject.com/Articles/1060016/Creating-Date-dimension-from-scratch-in-Microsoft

    One more thing, Please kindly mark all the correct replies as answers. By doing so, it will benefit all community members who are facing similar issues. Your contribution is highly appreciated.


    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


    Wednesday, March 15, 2017 9:47 AM
  • Thanks for your help Willson, it's working perfectly now!
    Tuesday, March 21, 2017 4:51 AM