CRM, OLAP Cubes, and Business Intelligence

Customer Relationship Management, as a concept, brings together a number of various systems from functions across the business (sales, marketing, operations, external, etc) that allow the enterprise to create, maintain, and grow positive and productive relationships with customers. We might think of it as being the glue that brings front office and back office together and allows the business to de-silo what would otherwise be proprietary information across the organization.

No alt text provided for this image

But what good are all these data points if they aren’t utilized effectively? It would be easy to fall victim to information overload if we tried to explore the data from a particular axis or angle. This is where classic data mining and online analytical processing (OLAP) come in. If we think of various systems of record as one-dimensional axes on a graph, bringing these together in a three-dimensional cube and taking a particular block within that cube to analyze would be much more efficient. Rather than starting with the data and searching for questions to answer that might involve those points (as is tempting to do at times), we are able to start with a specific business question and use OLAP to answer it.

For example, assume I am a cosmetics manufacturer and want to know how much of my product actually goes out the door to consumers after it is sold to a distributor. I want to use that information to adjust my marketing efforts and potentially re-evaluate my production line. I have the following data points available by way of my existing business intelligence environment:

  • Production line data
  • Inventory balances in my warehouse
  • Marketing campaign data
  • Sales data from my company to the distributor
  • Sales data from the distributor to the end consumer

Rather than starting from one or two of these data points and throwing things against the wall to see what might stick, I can use OLAP capabilities to find the different relationships between these points, eventually driving my answer. Understand here that answering the initial question is simply a matter of reading one data point (the last one in this case)—however, a strategic approach that addresses the customer relationship is the end goal.

One caveat here. OLAP may be considered a predecessor to currently-understood data mining, depending on which view of business intelligence you find appealing. Strictly speaking, traditional OLAP has been used for a number of years already for marketing, forecasting, and sales. Data mining capabilities at present far surpass what has been traditionally available in the OLAP sense.

Reference

Connolly, T. & Begg, C. (2015).  Database Systems: A Practical Approach to Design, Implementation, and Management (6th ed.). London, UK: Pearson.

Decision Support Systems, Data Warehouses, and OLAP Cubes

As Tembhurkar, Tugnayat, & Nagdive define it, BI is “a collection of tools and techniques [that] transforms raw data into significant information useful for analyzing business systems” (2014, p. 128). BI has evolved from the earlier incarnations of Decision Support Systems, which served the same purpose(s) but were much more rudimentary compared to today’s implementations. These DSS solutions were often comprised of data warehouses (DWs) and online analytical processing (OLAP) engines. Both components worked together to serve the business needs: ETL and storage being handled by the data warehouse, and the front-end analysis handled by the OLAP system.

The data warehouse serves as the central repository for multiple systems of record, often heterogenous and disparate in the beginning. Data is typically replicated and stored in subject-area schemas (e.g., sales or employee data), most typically in fact and dimension tables as part of a SQL-backed relational database. The data warehouse itself can offer views and data marts pre-packaged. It supports the OLAP system. Like the OLAP system in its original form, the data warehouse is starting to be eclipsed by data lakes in enterprise environments that deal with a large amount of heterogenous data that often includes unstructured data. The difference between the two, for purposes of this comparison, is where the “T” (transformation) falls in ETL or ELT. In a data warehouse, the transformation happens before loading into the warehouse, as its purpose is to serve as a central common repository. In a data lake, the transformation happens after loading, as the lake does not impose any schemas or restrictions in order to achieve any kind of homogenous state.

The OLAP system is multi-dimensional, not unlike a three-dimensional spreadsheet. It is not a relational database but enables the analysis of the data in the data warehouse. The OLAP system enables what we typically understand as slicing and dicing the data. While these were sufficient in the early days of BI, the shift towards a DevOps culture and the proliferation of machine learning, predictive analysis, dashboarding, and envelope-pushing analytics capabilities have required more from a BI solution than rigid OLAP cubes.

References

Felzke, M. (2014). Data warehouse vs. OLAP cube. Retrieved from https://www.solverglobal.com/blog/2014/04/data-warehouse-vs-olap-cube/

Harris, D. (n.d.). ETL vs. ELT: How to choose the best approach for your data warehouse. Retrieved from https://www.softwareadvice.com/resources/etl-vs-elt-for-your-data-warehouse/

Tembhurkar, M. P., Tugnayat, R. M., & Nagdive, A. S. (2014). Overview on data mining schemes to design business intelligence framework for mobile technology. International Journal of Advanced Research in Computer Science, 5(8).