March 18, 2019 at 3:53 pm
Today, after I changed the relational database that feeds the cube from compatibility level 100 (2008) to 120 (2014), the cubes no longer process (we had to stop the process after taking 5+ hours instead of the usual 10 minutes). The problem is indeed CL change since after reverting back to 100, it fixes the issue.
I have not seen any documentation regarding this issue or I cannot even understand what is the issue, because SSAS was NOT changed at all... the only change was on the DB for the DB-engine side.
Please note again that I changed the CL , not on the cube/SSAS, but on the database that has the fact and dimensions. Any help is appreciated. Thank you very much in advance.
March 18, 2019 at 4:12 pm
Ransonian - Monday, March 18, 2019 3:53 PMToday, after I changed the relational database that feeds the cube from compatibility level 100 (2008) to 120 (2014), the cubes no longer process (we had to stop the process after taking 5+ hours instead of the usual 10 minutes). The problem is indeed CL change since after reverting back to 100, it fixes the issue.
I have not seen any documentation regarding this issue or I cannot even understand what is the issue, because SSAS was NOT changed at all... the only change was on the DB for the DB-engine side.
Please note again that I changed the CL , not on the cube/SSAS, but on the database that has the fact and dimensions. Any help is appreciated. Thank you very much in advance.
that is most likely related to the changes on CE engine in 2014 or high CL level.
if you are indeed in 2014 you either turn it off or try to change the queries/indexes that are causing the issue - probably the execution plan chosen is bad compared to the previous one - and not always possible to change the queries to behave as it should.
see http://www.sqlservercentral.com/articles/Cardinality+Estimator/123964/ for some insight into it.
if your instance is sql 2016 sp1 (I think) then you can use other options to allow for the query to behave, or to keep the old CE but benefit of the other goodies of the higher CL level
March 18, 2019 at 10:09 pm
Thank you for your reply. But even if such is the case, there is no way I can interject "CE" changes or any changes to the query. The cube "processing" is very automated - I mean, it's just a click: "Process" if done through SSMS. If I use SSIS , which is what I'm doing now, there are no options to add CE off command.
March 19, 2019 at 3:25 am
Ransonian - Monday, March 18, 2019 10:09 PMThank you for your reply. But even if such is the case, there is no way I can interject "CE" changes or any changes to the query. The cube "processing" is very automated - I mean, it's just a click: "Process" if done through SSMS. If I use SSIS , which is what I'm doing now, there are no options to add CE off command.
yes you can do something about it.
- indexes can be changed
- direct access to tables can be changed to views where some hints are possible
- force query plans can be used.
- if views already in used they can be changed
and a few more things.
Changing how the cube is processed can also affect its usage of the source DB.
you may already be aware of the following but for other readers of this here it goes.
Processing SSAS Multidimensional Databases
analysis services molap performance guide for sql server 2012 and 2014
and there are other documents on the net on SSAS processing performance
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply