Post reply

Setting data types in Analysis services

  • February 10, 2010 at 9:09 pm

    #220443

    G'Day all

    A small but very annoying issue:

    I built a cube in Visual Studio (2005), I added a calculated Attribute "dayason" using

    day(DATEASON) expecting an interger to be returned.

    I then used an Excel Pivot to have a look at the data and my dayason was sorted as 1,10,11,12,...19,2,20,21 - so unless I am mistaken the value is seen as text.

    I tried cast(day(DATEASON) AS int) - even though this is a bit beyond belt and braces...

    The property of the attribute is System.Int32 - yet it behaves like text (varchar).

    Any ideas on how to fix this?

    All help and suggestions very gratefully accepted!

    PS

    Do or do not. There is no try...

  • February 15, 2010 at 7:27 am

    #1118796

    Hi.

    I recreated your problem in one of my cubes.

    I added a calculated attribute 'Test' as day(Calendar_Date) in my time dimension.

    I did this in the data source view by using a named query to select the data from the time table in the data warehouse.

    When I processed my time dimension, the attribute 'Test' was indeed sorted as text.

    This is because the OrderBy property of the attribute was set to Name.

    If you change this to Key, you will get the correct sorting (1,2,3,...).

    Hope this helps.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • February 15, 2010 at 12:46 pm

    #1118928

    Thanks very much! I will implement today and no doubt case much joy!

    Regards

    Paul

    Do or do not. There is no try...

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply