locked
PowerPivot and SQL Server 2008 Analysis Service Time dimension RRS feed

  • Question

  • Hi everyone,

    I'm running Excel 2010 (14.0.6117.5003) with PowerPivot (10.50.2500.0) and I'm getting data from SQL Server 2008 Analysis Service (10.0.1600.22) Database OLAP.

    When I import the time dimension, the data era imported as text, and there is no way to convert it as a DateTime.

    I tried changing column type, I tried using value function, I tried parsing the text.

    The language of Excel and PowerPivot is Italian, SQL Server is English.

    What can I do?

    Thanks in advance

    Francesco

    Monday, June 11, 2012 8:52 AM

Answers

  • I've open a support case about that: this is a bug and it is not yet solved.

    They gave me a workaround, but it is not applicable to my situation.

    Here the summary:

    PROBLEM DESCRIPTION:

    ==========================

    Customer is running Excel 2010 (14.0.6117.5003) with PowerPivot (10.50.2500.0) and extracting data from an SQL Server 2008 Analysis Service (10.0.1600.22) Database OLAP.

    When a date/time dimension is imported, the data is imported as text (Data type and Format), and there is no way to convert it as a Date. When selecting Data type DATE and error comes up:

    "Failed to change column data type."

    Customer can change the data type for other columns, for example from Text to Decimal number.

    Changing the Data type of this dimension has not been tried before so we cannot tell if this ever worked correctly.

    Steps to reproduce:

    1. Open Excel; go to the PowerPivot tab

    2. Launch Power Pivot window

    3. From Database – From Analysis Services or PowerPivot

    4. Connect to the database from which you are extracting the data

    5. Select the dimensions you want to use (including a Date/Time dimension)

    ex. repro with Adventure works; dimension Date”, measure “Sales Amount”.

    query: SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS, NON EMPTY { ([Date].[Calendar].[Date].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    6. Select one of the columns containing the date

    7. Go to the Formatting section and select the Date data type

    Actual result: error "Failed to change column data type."

    Expected result: the data type should be changed

    The issue is reproducible on any Windows 7 / Excel 2010 PC in the environment, with any OLAP database containing this dimension.

    Customer has the operating system, Office and regional settings in Italian and English Analysis services cube.

    CAUSE:

    ==========================

    problem in our product while working with different locales

    RESOLUTION:

    ===========================

    The issue has been reported to our product group and they are investigating on the issue, considering the impact that a fix in this area of the product may have overall and the extent to which this is impacting our customers.

    For now, the workaround we have to prevent the problem from occurring would be to work with the formats on English in Control Panel.

    Thursday, June 28, 2012 2:44 PM

All replies

  • how exactly do you import the time dimension?
    have you added all attributes of the dimension to the MDX-query?

    maybe you could post an example of the current powerpivot table


    - www.pmOne.com -

    Tuesday, June 12, 2012 9:39 PM
    Answerer
  • I've open a support case about that: this is a bug and it is not yet solved.

    They gave me a workaround, but it is not applicable to my situation.

    Here the summary:

    PROBLEM DESCRIPTION:

    ==========================

    Customer is running Excel 2010 (14.0.6117.5003) with PowerPivot (10.50.2500.0) and extracting data from an SQL Server 2008 Analysis Service (10.0.1600.22) Database OLAP.

    When a date/time dimension is imported, the data is imported as text (Data type and Format), and there is no way to convert it as a Date. When selecting Data type DATE and error comes up:

    "Failed to change column data type."

    Customer can change the data type for other columns, for example from Text to Decimal number.

    Changing the Data type of this dimension has not been tried before so we cannot tell if this ever worked correctly.

    Steps to reproduce:

    1. Open Excel; go to the PowerPivot tab

    2. Launch Power Pivot window

    3. From Database – From Analysis Services or PowerPivot

    4. Connect to the database from which you are extracting the data

    5. Select the dimensions you want to use (including a Date/Time dimension)

    ex. repro with Adventure works; dimension Date”, measure “Sales Amount”.

    query: SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS, NON EMPTY { ([Date].[Calendar].[Date].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    6. Select one of the columns containing the date

    7. Go to the Formatting section and select the Date data type

    Actual result: error "Failed to change column data type."

    Expected result: the data type should be changed

    The issue is reproducible on any Windows 7 / Excel 2010 PC in the environment, with any OLAP database containing this dimension.

    Customer has the operating system, Office and regional settings in Italian and English Analysis services cube.

    CAUSE:

    ==========================

    problem in our product while working with different locales

    RESOLUTION:

    ===========================

    The issue has been reported to our product group and they are investigating on the issue, considering the impact that a fix in this area of the product may have overall and the extent to which this is impacting our customers.

    For now, the workaround we have to prevent the problem from occurring would be to work with the formats on English in Control Panel.

    Thursday, June 28, 2012 2:44 PM