title: Large OLAP Dashboards Are Performing Extremely Poorly
body: <p><strong>High-Level Problem:</strong> Excel doesn't seem to be able to handle some of the more complex dashboards I am try to build, and I am hoping for some suggestions on how to alleviate this issue.</p>
  
  <p><strong>My job/requirements:</strong> I build executive and sales facing dashboards and reports for our organization in excel through an OLAP cube connection - the tool has been great for running quick analysis - but some of the larger projects that I run through this connection often crash or take painstakingly long to refresh. Here are the requirements I have, and why I am using excel:</p>
  
  <ul>
  <li><strong>Need to manipulate/alter our data freely</strong> - for example, if I am pulling customer data, where I have all 50 states in the columns, and all 10,000+ customers in the rows, I need to be able to run a formula adjacent to the data where I am comparing that customer/state data to company wide customer/state data.</li>
  <li><p><strong>End product (dashboard/table) needs to be visually pleasing</strong> as the end users are our sales team/executives. </p></li>
  <li><p><strong>Need to be able to re-run/refresh the same analysis easily</strong>. Most of the dashboards I run get sent out on a daily or weekly cadence, so each refresh of the dashboard needs to take no more than a few minutes, ideally seconds.</p>
  
  <p><strong>- Need built in time intelligence functionality</strong>: I need the dashboard I am building to know what day it is. Excel has the = today() formula. </p></li>
  </ul>
  
  <p><strong>Symptoms of issue:</strong> Right now, when I run more complex queries, data refreshes can take upwards of 15 minutes. Some of these files are approaching north of 200kb. For these problem files, further development is painfully slow and inefficient, because even the simplest of formulas (like a vlookup column off of the OLAP table) forces my computer to use all 8 processors and hogs about 95% of the 32gb of RAM.</p>
  
  <p><strong>Specifics of the analysis I am running:</strong> Much of this analysis requires pulling tens (and sometimes hundreds) of thousands of rows, and these tables are sometimes dozens of columns wide, as I might be pulling sales data by state or by month. Some dashboards often have half a dozen of these OLAP tables - each with its own purpose. There is no way to reduce the amount of data that I am pulling - I need all of the data to reach the conclusions I am trying to reach.</p>
  
  <p>For example, one of the projects I am currently working on requires me to take 24 months of sales data, broken out by state (as columns, on the x axis of the OLAP table) and broken out by individual customer (over 10,000) on the rows. This calculation alone takes forever to process and refresh, and causes all other subsequent formulas to run extremely slow, even simple vlookup columns adjacent to the OLAP cube.</p>
  
  <p><strong>Research I have done:</strong> I have looked into alternatives to excel, but the largest issue I face is that I still have a set of requirements that need to be met, and the obvious tools at my disposal don't seem to meet those requirements at first glance. Again, the end product of what I am building must be easily consumable for our end users (sales reps and execs). If I were to run a sql query through Microsoft SQL Server to pull the data I am looking for, I would need to be able to refresh the data on a daily basis - but more importantly,  I would need to be able to easily manipulate and build out extra calculations and formulas off of the query. Excel seems like it is the only tool that checks those boxes, and yet it also feels like a dead end.</p>