The business environment is multi-dimensional, consisting of organisational units, products, customers, channels, time and so on. For this reason, multi-dimensional databases have been used for business modelling, decision support and executive information systems for about 15 years.
Multi-dimensional databases used to suffer from the perception that relational databases were the only tool an organisation needed. But that changed when Ted Codd, the father of relational databases, legitimised the concept by setting out 12 rules for Online Analytical Processing (Olap).
Shortly afterwards, Oracle and Informix bought Express and Metacube respectively; last year, Microsoft bought Panorama, and IBM has just licensed Essbase. This shows that Olap has been accepted into the computing mainstream. And, with the arrival of data warehousing, Olap became the best solution for datamarts.
Olap tools are used to build end-user applications such as plans, budgets, forecasts, models, sales analyses, financial consolidation systems and activity-based cost models. They also help users make better decisions by allowing them to work interactively with data, using software that keeps up with thought processes.
Users can choose from a range of interfaces to access their Olap tool. Spreadsheets are popular, with an additional menu item bringing up a dialogue box that allows users to select the data they want to view.
The data remains in the server, but the spreadsheet?s analytical functions can work on the cells that contain a link to the data. Visual Basic and other tools can be used to provide a custom front-end.
Traditionally, vendors have supplied their own proprietary interface that provides all the navigation, analysis and presentation tools, especially powerful graphing and visualisation tools.
Speedy response is critical to end-user acceptance, so the software usually relies on the power of a server to manipulate the data. Data is transferred from a data warehouse, accounting system or other application, often integrating summarised information from various sources.
Olap tools are different from query tools because they are interactive. This means that users can move around the dimensions, or up and down a hierarchy within a dimension. Olap tools can also provide a high degree of analysis.
Subject: London Underground
Activities: rail transportation
Installation: Cognos Power Play
Cognos Power Play is being used in the second phase of London Underground?s (LU) Line Targets Monitoring project. Peter Cruickshank, automatic fare collection software manager, says: ?We are trying to get people off paper reports and onto the Olap platform.?
A total of 800,000 ticket sales each day are loaded into a 130Gb data warehouse using Oracle RDB on a Digital Vax cluster. Thirty users are given data extract cubes with ticket sales volume and value information broken down over seven dimensions. Hierarchical dimensions are location (line, group, station and device) and date (financial year, accounting period, week and day).
There are also dimensions for device (ticket office and passenger multi-fare machines), ticket type (single, return, season) and ticket office sales by clerk. In addition, there are summaries of the time data by days of the week and hours of the day.
Power Play has replaced Cognos Powerhouse 4GL that used to generate paper-based reports. The 30 users distribute the results of their analyses to 120 managers because the organisation can?t afford to give Power Play to all users. LU is piloting the concept of publishing Power Play reports on its intranet.
Three cubes are distributed, the first covering tickets sold, revenue collected, non-issues and refunds. The second records the flow of passengers because ticket types are counted at automatic gates and uploaded to the data warehouse every 15 minutes. The last report is a service-based extract. ?A lot of our operations people are interested in it because, for the first time, they can evaluate the performance of services,? says Cruickshank.
LU has been using Power Play for about six months. ?Once definitions were established, the users got on very well,? says Cruickshank. ?It has reduced requests for additional reports because users can now do it themselves. They get information in a more timely manner because it is transmitted across the network. People can interact with their data and follow a thought process. They ask questions they never asked before, and it helps provide a better service to customers.?
Subject: Societe Internationale de Telecommunications Aeronautiques (SITA)
Activities: a co-operative which provides telecoms and information processing services for fares, passenger, baggage, airline management, as well as flight and air cargo operations in the air transport industry
Installation: Applix TM/1
At SITA?s London information processing centre, monthly management accounts used to be prepared using a separate set of linked Microsoft Excel spreadsheets for each accounting period. Tony Gill, chief accountant at the centre, says: ?It was a time-consuming job every month and the analysis was hard work.?
He has recently started using TM/1, a multi-dimensional database server which takes data from the mainframe accounting system and works with a spreadsheet client. ?The time taken to do things is considerably reduced,? says Gill. ?For instance, the monthly management accounts now take two to three days, whereas before they used to take between eight and ten days. And now it takes one day, not a week, to set up the budget.?
?It is easy to find out what the money is being spent on,? says Gill. ?You can view salaries by cost centre, instead of having to look at 12 different spreadsheet files. You can do the same for any cost element or look at a trend on a monthly basis. It is easy to see what is happening in the business because you can readily extract data.?
Michael Grunwell, finance and business analyst at SITA, adds: ?We can use Microsoft Excel as a front-end which makes TM/1 easy to use because we are all familiar with it. Once data is displayed in the spreadsheet, you can work with it and present it just like any other spreadsheet. It is easy to use and the updates are tremendous. It took half a day to create a complete revenue budget which would otherwise have taken a week.?
?I couldn?t manage without TM/1,? says Grunwell. ?It is our everyday tool and 80 per cent of what we do uses its data. We are pushing it to new heights every day.?
Subject: 3Com Europe
Activities: data networking
Installation: Arbor Software?s Essbase
Managers at 3Com Europe used to run the business with daily, weekly and monthly management reports. These were prepared using a complex set of linked spreadsheets. ?As the business grew, these became unwieldy,? admits Tony Perks, European director of MIS. ?We couldn?t have built a solution with a relational database because it would have involved too many programmers.?
The company became the first Arbor site in the UK, with data extracts from legacy transaction systems being transferred into Essbase.
William Coker, European planning analyst at 3Com, says: ?We found that solving a complex spreadsheet in a multi-dimensional environment was relatively straightforward, because Essbase overcame the complexities.? One or two templates replaced 25 original linked sheets.
The applications transferred include management reporting, forecasting and sales analysis. Essbase also calculates critical business indicators, such as revenue, units, margin and expenses. 3Com designed a Visual Basic front-end application to help users make individual selections through a series of dialogue boxes.
?When we saw what Essbase could do we were amazed, and it grew very quickly,? Perks recalls. ?We could change the units to dollars or to customers and get an instant response. We started to think beyond the limitations of two-dimensional spreadsheets.?
Essbase has been rolled out to 3Com?s European subsidiaries and now supports 500 users. ?It has become a strategic and mission-critical product for us,? says Cocker. ?Essbase has improved timeliness of information and has reduced the manual effort involved. Month-end management reports are now available two hours after cost figures are received. The amount of work needed to produce the reports has been reduced by 80 per cent.?
Perks says: ?Essbase has moved us from data production to business analysis, turning raw data into real-time business information.? Implementing Essbase has turned a daily problem into a continually improving value-added service.?
1 When you give users direct access to information, ensure that they understand the definition of the data in the dimensions.
2 Olap software must allow users to interact with their data and be fast enough to allow them to follow a train of thought.
3 Spreadsheet applications are much easier to manage when they are linked to data through an Olap tool.
4 Proprietary front-end interfaces with navigation tools and graphics can add more power than a spreadsheet interface.
5 Avoid creating too many dimensions.
An explanation of Olap terms
A hypercube is a single multi-dimensional structure that creates data cells for every combination of dimension members so that each cell has an identical number of dimensions.
A multicube is a series of separate multi-dimensional structures, each of which contains a sub-set of the overall dimensions. This structure is more flexible and can be tuned more easily than hypercubes.
In a multi-dimensional structure not every cell is populated. This is because not every product is sold in every market, products are introduced to, or taken off, the market, forecasts may be made to product group level, but not down to individual product and so on.
Disk-based Olap products leave data on the hard disk while the application is being used, loading data into memory when requested by the user. This minimises the need for expensive memory, but hard disk access can become a bottleneck.
Some Olap products choose to load the entire database into memory to speed up response times. This approach may be more expensive initially, but is generally faster than a disk-based tool.
With a multi-dimensional Olap product, data is sent and stored in a separate multi-dimensional database. This is the traditional solution and is ideal for decision support applications
A relational Olap product only works with a data warehouse and leaves data in its original relational database. This means there is no need to support and maintain a separate multi-dimensional database. It works best with very large databases which have few dimensions and simple data structures.
There is no problem with sparsity or database explosion, but relational databases are slower than the highly-tuned proprietary multi-dimensional databases.
Cotton seedling freezes to death as Chang'e-4 shuts down for the Moon's 14-day lunar night
Fortnite easily out-earns PUBG, Assassin's Creed Odyssey and Red Dead Redemption 2 in 2018
Meteor showers as a service will be visible for about 100 kilometres in all directions
Saturn's rings only formed in the past 100 million years, suggests analysis of Cassini space probe data
New findings contradict conventional belief that Saturn's rings were formed along with the planet about 4.5 billion years ago