none
Build OLAP database very long time RRS feed

  • Question

  • Hi,

    Our Project Server 2016 (on-premise), take a long time to build OLAP (about 7.5 hrs). Currently, data row on table [pjrep].[MSP_EpmAssignmentByDay] about 2,000,000. I found big problem with view [pjrep].[MSP_EpmAssignmentByDay_OlapView_00007829-4392-48B3-B533-5A5A4797E3C9] 
    It's use table value function (TVF) to filter SiteId: [pjrep.MSP_TVF_EpmAssignmentByDay('487EED98-D954-4F64-9DAC-3A8821787318')

    I'm read in https://blogs.msdn.microsoft.com/psssql/2010/10/28/query-performance-and-multi-statement-table-valued-functions/ 
    "But if you use multi-statement TVF, it’s treated as just like another table. Because there is no statistics available, SQL Server has to make some assumptions and in general provide low estimate. If your TVF returns only a few rows, it will be fine. But if you intend to populate the TVF with thousands of rows and if this TVF is joined with other tables, inefficient plan can result from low cardinality estimate."

    Then i try modify view, not use TVF, and query direct to table [pjrep].[MSP_EpmAssignmentByDay] with "WHERE SiteId = '487EED98-D954-4F64-9DAC-3A8821787318'". 
    And result: time to process cubes only about 10 minutes !!!

    But, i can't slove this problem, because every day, when OLAP build, it will auto generate OLAP views again...

    I don't know why MS use TVF in views which large of records. 
    Any hotfix for this issue, please.

    Thanks

    Friday, August 17, 2018 6:21 AM

All replies

  • Hi Tung,

    I'm not sure what the driver was to move to TVFs. It might be good to open a support case with Microsoft to raise this query. I will see if I can get an answer and post back.

    Paul


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

    Friday, August 17, 2018 6:57 AM
    Moderator
  • Hi,

    the reason for TVFs ist, that there can be more than one instance per ContentDB. A userview is only available if there is only one instance - and OLAP build can't rely on that.

    Note: it is adviced to have only one instance per contentDB, but is is possible to have more than one.

    Regards
    Barbara

    Friday, August 17, 2018 7:19 AM
    Moderator