From Spreadsheets to OLAP Database Analytics
Most companies function in the online transaction processing (OLTP) space. The traditional database used primarily for keeping records of financial transactions and sales. The data gathered from these systems are then exported into Excel placed into pivot tables. Employees analyze the tables and place them into sales and financial reports. All useful business information gained from these reports is typically a month old, required 40+ man hours and only displays a quarter. Questions like profitability by customer and quality information such as product defects and product or process cost analysis is not cost effective.
We are in the 21st Century. Imagine an engineering department using pencil and paper for product design in a world of rapid prototyping and 3d printing. How long would that company be in business? As impactful as the switch is from pencil and paper to 3d design software, so too is the switch from traditional Excel spreadsheets to integrated Online Analytical Processing (OLAP) database functionality.
Jedox is software that enables users to access OLAP data through Excel and connects to SAP, Salesforce, and Oracle among others. This means that users can seamlessly access a database through their Excel add-in and update reports instantly. Gone are the days of composing a report and emailing it to the controller. Gone are the days of weekly meetings showing updated sales figures and hours spent on composing reports. It is instant access to your company’s data on demand and without a steep learning curve.
Most companies are trying to gain actionable information with bits and pieces of Excel sheets. To make the move from spreadsheets to the OLAP space, we will look at converting Excel data into a three-dimensional cube.
Multidimensional Data Cubes
Construction of a Data Cube starts with identifying common dimensions that are found across your Excel sheets. These are things such as Dates, Units of Measure, Products, and Sales Regions. We add elements to dimensions. For example, the dimension ‘Sales Region’ would have elements like ‘Europe’, ‘West’, ‘Germany’ and ‘France’. When we add dimensions we order them in a hierarchy to see totals roll up. Here’s a simplified structure
Depending on your data, a sample structure would look something like this:
Shown above is a basic cube with four dimensions. The process of creating a cube can be done through the Jedox Excel add-in within minutes. Another option is to construct Load Sheets. They have to be setup in Jedox web through the Integrator and are mainly done for maintaining a database and keeping track of changes.
Slice & Dice Your Data
Once the cube is setup, it is possible to slice your data any way you want as well as create rules for elements. Using a Jedox function called Paste View, you can see your sales gross profit and inventory filtered by dates, regions or products. It is also possible to setup rules to calculate additional values such as gross margin.
The second part of this blog series will highlight some spreadsheet features within Jedox web. The items discussed will be DynaRanges, Comboboxes, Buttons, Lists and Success Charts. These are all unique features within Jedox that allow for easy data display and data selection. It takes generating reports to the next level and its similarities to excel makes it easy to learn.