Build OLAP database for Project Server 2016 very long time
Our Project Server 2016 (on-premise), take a long time to build OLAP (about 7.5 hrs). Currently, data row on table [pjrep].[MSPEpmAssignmentByDay] about 2,000,000. I found big problem with view [pjrep].[MSPEpmAssignmentByDayOlapView00007829-4392-48B3-B533-5A5A4797E3C9]
It's use table value function (TVF) to filter SiteId: [pjrep.MSPTVFEpmAssignmentByDay('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.