What are Analysis Cubes and why do you need them?

Analysis Cubes for Excel is an ad hoc reporting tool that comes out of the box with Microsoft Dynamics GP. Unlike other reporting tools the cubes require minimal set up effort and can provide instantaneous value. Users can quickly and easily access system data through Excel and start slicing, dicing, and digging down for reliable and valuable information.

From analysts to C-Level executives, all users within an organization have a reason to use Analysis Cubes. Analysts can leverage pivot tables or cube formulas to efficiently pull data and generate reports on the fly for quick analysis. Users can also construct more complex reports that combine different pivot tables or charts all in one worksheet. Filters can be included in these reports so they can be easily updated and refreshed in the click of a button. These reports are a quick and easy way for management to make sound business decisions knowing they are looking at accurate, up to date data. Additionally, many users are more experienced and feel at home when they are in Excel and the Analysis Cubes utilize native Excel functionality.

Analysis Cubes for Excel leverage Integration Services and Analysis Services to take the data from the source system database to a Data Warehouse to Excel as shown in the diagram below:

Once you have connected to the data source within Excel and imported the appropriate cube and pivot table (Financial, Sales, Payables, etc.) you will see a pivot table with a Field List containing Measure Groups and Dimensions that you can rearrange to find the data you are interested in. The Dimensions are hierarchical meaning they contain related attributes that allow you to drill down for more detailed analysis. For example, you could choose to select Customers by Location and drill down to different nations then drill down to different states, and ultimately drill down to individual cities.

Below is a screen shot showing the Financials Cube and accompanying Field List. You can see that the ‘Assets’ Account category has been expanded as well as the ‘Current Assets’ category beneath it and the GL transaction amount is displayed for each year. This type of analysis can be done in all Cube Modules and drill downs can go as far as the transactional level.

In addition to comparing date figures and drilling down Analysis Cubes can be filtered, sorted, or searched through. Value filters and sort parameters can be modified to show top figures, figures containing only certain values, or top performers. Label filters can be modified to show only attributes that qualify under certain restrictions.

Analysis Cubes can also leverage the following tools within Excel:

• Slicers
• Data Bars
• Charts
• Spark Lines
• Cube Formulas

When you combine everything that Analysis Cubes for Excel can do, you can build reports and dashboards like the ones below very quickly.

If you use Microsoft Dynamics GP and understand the importance of reliable data, there is no reason you shouldn’t be using Analysis Cubes for Excel.