none
Project Server 2016 on SQL Server 2017 - OLAP Cube dimension broken RRS feed

  • Question

  • Hi,

    we have a test environment with Project Server 2016 (June 2018 CU applied, LP German) with an underlying SQL Server 2017. Cube build is completed successfully and can be accessed.

    However, if you connect to the PortfolioAnalyzer in Excel 2016 and try to drill down into the RSP dimension, we get an "Internal Error. Unexpected exception" error. The first level is displayed ok, but we are not able to click on the + sign to get the underlying values.

    So I tried to browse the dimension directly in the SQL Management Studio - and again so the "Internal Error. Unexpected exception" error trying to open the RSP dimension.

    Same behavior for other dimensions with more then 1 level.

    Any ideas on what might be wrong?

    PS: We had a development environment with Project Server 2016 June CU 2018 on SQL Server 2014, same configuration of PWA and services, etc. as the test environment. There the drilldown into RSP was working fine. So is this something related to SQL Server 2017?

    Monday, October 8, 2018 7:01 AM

Answers

All replies

  • Hello,

    Not sure it will be a SQL 2017 issue, it could be a data issue. Are you able to roll over the test environment data to the dev environment and see if the cube then builds on dev with the test environment data? You could check the logs / a SQL Profile trace to see if that gives you any clues: https://docs.microsoft.com/en-us/sql/analysis-services/instances/log-operations-in-analysis-services?view=sql-server-2017

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Monday, October 8, 2018 6:42 PM
    Moderator
  • Hi Paul,

    thanks for your answer. The test environment was build using a database copy from the dev environment - so the data on both environments is the same which suggests some other than a data issue to me.

    I'll try the SQL Profile trace and the error logs.

    Also just realized - the issue also occurs for standard dimensions like Task List - I see the top Level for the Task list when I click on browse and as soon as I try to drilldown I get the unexpected error

    Tuesday, October 9, 2018 6:18 AM
  • After digging into logs and SQL Trace I'm not much smarter than before.

    At some occasions mini dumps are created by the OLAP process, however not every time I build a cube or access the hierarchy. 

    The trace shows no errors for the cube build itself. An error is only logged when trying to access the dimension. The statement called before the error occurs is e.g. the following for the Task list dimension

    WITH MEMBER [Measures].[-DimBrowseLevelKey 0-] AS '[Task List].[Task List].currentmember.properties("key0", TYPED)'
     
    SELECT { [Measures].[-DimBrowseLevelKey 0-] } ON 0,
    Head( [Task List].[Task List].&[{BF36D6AB-EA51-E811-80D6-005056972041}].Children, 1000) ON 1
    FROM [$Task List]
    CELL PROPERTIES VALUE
     
    <PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">
              <Catalog>SharePoint_TEST_ContentDb_ProjectServer_PWA</Catalog>
              <ShowHiddenCubes>True</ShowHiddenCubes>
              <SspropInitAppName>Microsoft SQL Server Management Studio</SspropInitAppName>
              <Timeout>3600</Timeout>
              <LocaleIdentifier>1031</LocaleIdentifier>
              <ClientProcessID>8308</ClientProcessID>
              <Format>Multidimensional</Format>
              <AxisFormat>TupleFormat</AxisFormat>
              <Content>SchemaData</Content>
              <MdxMissingMemberMode>Ignore</MdxMissingMemberMode>
              <DbpropMsmdActivityID>2283ca4f-287c-43b7-a3e3-34c298b714dd</DbpropMsmdActivityID>
              <DbpropMsmdCurrentActivityID>2283ca4f-287c-43b7-a3e3-34c298b714dd</DbpropMsmdCurrentActivityID>
              <DbpropMsmdRequestID>7f31aac9-f8ee-41e5-a096-0870c88454ed</DbpropMsmdRequestID>
            </PropertyList>


    • Edited by Gáski Wednesday, October 10, 2018 9:56 AM
    Wednesday, October 10, 2018 9:55 AM
  • The issue was solved by updating SQL Server 2017 to CU 12.

    We created a ticket with MS Support mid of October. According to MS Support the issue occurred due to a known issue with translation in parent/child dimensions: 

    https://support.microsoft.com/en-us/help/4465248

     

    “RSP_resource” und “timesheet task list“ are PC dimensions with translations.

     

    The cumulative update 12 with the fix has been luckily supplied in October:

    https://support.microsoft.com/en-us/help/4464082/cumulative-update-12-for-sql-server-2017

    • Marked as answer by Gáski Monday, December 17, 2018 1:47 PM
    Monday, December 17, 2018 1:47 PM