Slow ASO Data Retrieves in Smart View

A client asked me last week to take a look at one of their ASO cubes and help them determine why their data retrieves in Smart View were taking so long. To set a base, I tested in Excel and it took 81 seconds for the top level of every dimension to return data when doing an ad-hoc analysis in Smart View. With UAT approaching, I knew it was not going to fly.

Wherever I could, I changed dimensions to Stored instead of Dynamic. Stored dimensions in an ASO cube are reserved for those in which no calculations or other arithmetic function will be performed on the members of an outline outside of “+” or “~”. These dimensions are simply aggregated according to the outline unlike dynamic hierarchies that include calculations or consolidation options that go against the simple outline structure. Wherever possible (!), use a stored hierarchy. If you…