Writeback performance - what to expect?

• elee 62941

  SSC Journeyman

  Points: 96

  More actions

  October 30, 2017 at 7:42 am

  #150654

  Hi there,

  We've been implementing a financial planning application that happens to run on top of SSAS. We're hitting some issues with performance when entering data in the application, which uses writeback to store that data in the cube. For example, when I enter a number in a budget form, it generates a UPDATE CUBE statement to make the change and spreads the data across a bunch of member combinations. The problem is that this can take a long time, depending on how many combinations the cell affects.

   I benchmarked this particular use case and it is updating about 35 member combinations per second. Can someone characterize what type of performance to expect from UPDATE CUBE?I know OLAP/MDX isn't going to be nearly as fast as the SQL database engine at updates, but what order of magnitude are we talking about here? Is 1000 combinations per second doable?

  The cube has 2.2 million facts organized along 10 dimensions, the biggest dimension having about 7500 members and most dimensions having less than 100 members.

  This is on a single server running SQL Server 2016P1, Windows Server 2016 on a 12-CPU 2.6Ghz Xeon with 112GB of RAM.

  The writeback is already being done into a separate partition. Are there any other changes that could improve writeback performance on the SSAS side of things?

  Any thoughts would be much appreciated!

  Elliot