Newbie Question

Topics: Resolved
Sep 23, 2010 at 4:28 AM


I have used the OLAP PivotTable Extension program to access the MDX query for my Excel 2007 reports using a PIVOT TABLE to access a cube.  I have scripted the MDX in another tool to do cache warming, resulting the in the report opening quicker for the end user.  My problem is that I have many other reports where I converted the PIVOT TABLE to OLAP FORMULAS, these reports are slow to refresh, and I would like do cache warming on them as well.  The problem is this tool does not show me the MDX info for these formulas.  Does anyone know how I can translate the various cube formulas used in the report into one MDX query I can execute via a script (i.e. warm the cache)?  I hope this makes sense - I am a cube newbie.  thx -ingsy

Sep 23, 2010 at 6:04 PM

You'll need to talk to your Analysis Services administrator and get them to run SQL Server Profiler connected to Analysis Services. Then you can capture the exact queries that are sent to the server, especially for the cube formulas. There's no API in Excel to get the exact MDX queries that are sent to the server for cube formulas.

Another approach is to log all queries sent to the server and automatically warm the cache with any slow queries. You might read up on cache warming and consider that possibility:!7B84B0F2C239489A!7701.entry

Marked as answer by furmangg on 6/10/2014 at 4:24 AM
Sep 26, 2010 at 5:16 PM

thank you very much, that was helpful.  cheers, ingsy