Data Cubes are Critical to Financial Analytics — Here’s Why
Early this year FEI released an article predicting the top seven trends for corporate finance in 2018. It’s easy to see why broader adoption of BI was at the top of their list. When things don’t go according to plan, someone needs to figure out why and course correct.
By Piyush Jain, Vice President of Engineering, Centage Corporation
In other words, when actuals differ from plan, the VPs of Sales, Marketing, Customer Care and so on need to drill down into the data to decipher why those variances occurred. BI allows business executives access to complex and malleable data so they can either take steps to mitigate the problem or rejigger focus if a particular line of business or product is performing far better than expected.
However, it’s important to note that some BI tools are better than others in terms of providing business people the insights they need quickly and easily. Explaining why can get pretty complicated, but in my opinion, it’s worth the effort to understand the basics. Ultimately, the type of database that drives any given BI tool will have a huge impact on its flexibility. And that flexibility, in turn, will determine whether or not end users will actually use the BI tool on their own or request custom reports from IT.
Relational vs. Multidimensional Database
Basically, there are two types of databases: relational databases and multidimensional databases (a cube is the latter). In the world of databases, a “transaction” is a unit of work or a task to be performed. If you’re an insurance company, the transactions in your database are probably the records for policies your customers who have signed up for.
Relational databases are more suited for transactional applications rather than analysis. Data is organized into tables, resulting in a collection of data that’s related to one another. Each table is made up of columns that identify the type of data that’s stored in each row, along with a unique key that identifies a row. Going back to our insurance example, transactions represent customers who’ve signed up for a policy, stored in a table, and the rows contain details of the policy, customer and selling agent.
The challenge with this approach is that a policy table only contains unique keys that identify a policy ID, customer ID, agent ID, etc. The report will have to combine information from multiple tables that store the detail information to present the data in a user-friendly format. And that, in turn, limits the business user’s ability to do custom searches. So let’s say the VP of marketing for the insurance company wants to slice and dice customer data based on gender, age or marital status in order create targeted marketing campaigns. With a BI tool built on a relational database, he or she will need to request the IT department to pull appropriate data points from the database and arrange it into a format that’s intuitive to how the marketer will use it. This can take weeks. It also takes time and attention away from other IT projects that may be critical to the company.
Build BI Tools on Cubes
A multidimensional database (aka cube) takes a completely different approach to creating and storing transactions. The idea behind cubes is to record and store all of the data points in a way that facilitates easy retrieval, reporting and analysis.
With this approach, all of the data points collected as part of the transaction — name, age, address, gender, marital status, etc. — are known as dimensions. The database arranges the dimensions in such a way that allows users to search by dimension — such as age or marital status — as well as by the transaction or customer record. The database also allows users to define hierarchical relationships in their dimensions, for example, cities that roll up into states, which in turn roll up into regions. The tool will automatically calculate the values for each roll up, which is the exact functionality that allows a business user to search for criteria important to him or her.
For instance, let’s say a marketing team wants to create a list of women in the east coast, aged 24–45 who purchase a 30-year term life insurance policy. A BI tool built on cubes allows them to search the database and create a list of customers who match their criteria quickly and easily, and without the help of the IT team. In fact, any user can search on any dimension as a matter of course.
There’s another benefit of BI tools built on cubes, and that is the flexibility they offer in terms of the dimensions. Not all companies need to collect the same data points. Whereas a customer’s marital status is important information for a life insurance company, it’s irrelevant to a large number of other businesses. The cube approach allows companies to determine which dimensions are important to them and should be collected, without any complex customization in the initial set up.
This last point is critical, because it means that coming out of the gate, a cube-based BI tool will allow end users to retrieve the kind of insights that allow them to spot and act on important trends and make smarter decisions faster.
Piyush Jain is Vice President of Engineering at Centage. With over 20 years of experience leading development teams for enterprise products, Mr. Jain is responsible for all aspects of product development at Centage. He brings deep expertise designing and delivering scalable analytics products, web and cloud-based architectures, OLAP, Big Data, Corporate Performance Management and Business Intelligence applications. Follow on Twitter @Centage.