I'be build a Tabular Cube with FactTable and four dimensions. Fact table has about 2.500.000 of rows (and about 20 calculated measures on the cube level) and dimension tables from 5k-50k of rows. When I connect to the cube from Excel and try to add dimension I have to wait like 15-20 seconds for this. Do you know some ways to improve the query performance of such tabular cube (SQL 2014) ?

enter image description here

Performance issue not visible (Year consolidated), filtering by Port works fast: enter image description here

Performance issue visible (Year expandeding to weeks takes about 15 sec, filtering by Port after next 15 seconds everytime): enter image description here

  • Is this in excel only? Or is it a server deployment of ssas? Hoq much ram does the server have. How much is free? If you reprat the process is it faster the 2nd time? – Sir Swears-a-lot Dec 15 '16 at 8:38
  • Cube has been deployed to the SSAS instance (SQL Server 2014 SP1 Ent) . From the Excel the users are connecting to the SSAS instance and select the cube to work with. Server has 256GB of memory, 8 cores. I've added memory usage screen to the main post above. The response time is the same no matter first ond second time. – Tomasz Wieczorkowski Dec 15 '16 at 14:51
  • As I've tested adding of the Dimensions works fast where the data on the report from the cube is consolidated. When the Year is expanded to weeks adding dimensions takes about 15 sec. So as I see the problem is with consolidating/expanding "Dates". When Date Dimension is expanded from Year to Weeks and Days it takes significant amount of time to add dimensions or filter data. When DateDimension is consolidated to Year cube works fast. Is there any way to speed up this ? – Tomasz Wieczorkowski Dec 16 '16 at 15:49
  • I work with multidimensional rather than tabular ssas, but wondering if indexing would help or how aggregations work with a tabular model. From what you describe its sounds like it has aggregations at year level but lower that that its hitting the underlying data. – Sir Swears-a-lot Dec 17 '16 at 3:06
  • 2
    What version of Excel? I believe you will see some performance improvement on Pivots like this in Excel 2016. The optimized the query it sends when you turn off subtotals. It makes an exponential difference the more fields you add to the pivot. – GregGalloway Dec 20 '16 at 2:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.