We’re borrowing from a Product Manager by the name of Steve Little at Jet Reports today to talk about a powerful tool that many are just beginning to understand. “Cubes,” sometimes referred to as “OLAP (or Online Analytical Processing) Cubes” are a handy way to organize, extract and report upon the many dimensions of your company’s stored data. Today, we’ll keep it simple, but we hope illuminating, by following the Little’s comments from the Jet Reports Blog.
(Jet Reports, by the way, is a powerful reporting tool that acts as an extension of Microsoft Excel. It allows users of full-fledged ERP systems like Microsoft Dynamics to build, save and update reports of data that resides inside the databases and tables of their ERP system.)
In his original post (found here) Little starts with a simple hypothetical question one might ask: “How much profit did we make selling Sno-cones to Icelanders last year?” In a Dynamics NAV database you might need to combine data from 6 or more tables (Customer, Region, Item, etc.) to deduce the answer. Then you’d have to “mash up the data and extract the data to get the information that you need.” That can be a slow and resource intensive process, Little points out. What you really want is a fast and easy process – and that’s where Cubes come in.
Cubes make it easy to answer questions like this by reorganizing a copy of the data so that it’s easy and fast to get information out. They are a multi-dimensional way of organizing data – which means they offer a near instant analysis of large amounts of data.
A few term definitions may help, as you ponder the illustration above:
- Dimension: a category used by which to slice your data (say, by Company, Salesperson or Item)
- Measure: a calculated numeric value (like a sum, count or average)
- Level: a grouping within a dimension (like Customers by Salesperson, or Territory)
- Hierarchy: a group or organizational level (like date by year, quarter or month)
Our three-dimensional cube illustration would be an example of a way to view sales and profits of products and territories over a period of years, for example.
Cubes such as those utilized by the Jet/Excel combination allow ERP system users to use a tool like Excel to extract data from their ERP databases and thereby improve and maximize their reporting capabilities. If you’re handy with Excel, you can learn to master cubes. In the Jet Report Blog, Little closes by dispelling 3 common myths about cubes:
Myth #1- Cubes require months of planning and implementation before users can get value out of them. As of maybe three years ago, this myth was probably true. It would take months or years to get any value out of them. With Jet Enterprise, it’s typically installed and running in two hours or less for Dynamics customers. With pre-built Cubes, it’s easy to go in and get that done without the months and months of planning.
Myth #2- Customizing cubes is slow and difficult. The Jet Data Manager, which is included with Jet Enterprise, allows you to customize or create Cubes in a simple drag-and-drop interface. You can add dimensions, measures, or create unique measures on the spot and apply these into the Cubes – often in minutes, with no programming.
Myth #3- Cubes are a luxury, applicable only to large companies with BI development teams and large budgets. This is no longer true. Jet Reports has over 600 customers currently running Cubes. You no longer have to have a large company with a BI development team to be able to get value out of the Cubes.