How to build an “analytics lake” using Cross Model Mapping - Pyramid Analytics
Pyramid Analytics addresses the common BI challenge of combining visualizations from multiple disparate data models by introducing "Cross Model Mapping," a feature that enables users to build an "Analytics Lake"—a unified dashboard integrating data elements from various sources without the complexity, time, and inflexibility of traditional data lakes or the limitations of single-model BI tools—thereby allowing faster, scalable, and governed multi-source analytics within a single interface.
Analysts building dashboards often encounter challenges when trying to combine visualizations from different data models (for example, a sales analysis from an SAP-based data model and marketing KPIs from an Oracle-based data model). Many BI tools only allow a single data model per dashboard or project.
One traditional approach has been to use single data sets in the era of data lakes, pooling all data into a single source to achieve a homogenized single source of truth. However, data lakes are complex, time-consuming to build, and lack agility for rapidly changing data needs.
Pyramid Analytics offers a different approach: combining data and analyses from multiple sources without the need for a traditional data lake. This method, called an “Analytics Lake,” allows users to combine multiple elements from different data models and sources in the same dashboard analysis, without the extensive frameworks required by data lake solutions. An analytics lake is significantly faster and easier to deliver and deploy.
The Problem
Users often need to display data elements from different data models in a single dashboard, such as a finance KPI next to an HR KPI and a marketing KPI. Analysts also struggle to use common filters or data elements across models due to differing data structures.
Most self-service BI tools (e.g., Qlik, Power BI, Tableau) do not allow multiple visualizations from different models in the same dashboard. Instead, they encourage users to export and reimport data to create a single data model, which can breach data governance, duplicate analytic layers, decentralize the “truth,” and reduce the value of the underlying data warehouse.
Alternative strategies like composite data models (merging multiple models virtually) often have performance issues, do not scale well, and are usually limited to relational sources.
Pyramid’s Solution: Cross Model Mapping
Pyramid provides tools called “Cross Model Mapping,” enabling business users to create frameworks and rules to connect different data models without the issues mentioned above. Advanced mapping can be performed for data models, hierarchies, and members, all through a simple point-and-click wizard interface.
Dashboards can now include visualizations from multiple data models, each potentially comprising different sources, without the need for a data lake or a lengthy project to deliver a unified business view.
Business Case Example
Shane, a BI Analyst at E&G Manufacturers, faces the following scenario:
- E&G uses SAP BW for ERP analysis.
- HR data is sourced from a SQL Server-based system.
- Marketing data resides in a Redshift database.
- Legacy manufacturing data is stored in Oracle.
Each system is encapsulated in a distinct data model, maintaining data governance and a single version of the truth. The CEO requests a master dashboard with KPIs and charts from all four systems.
Shane wants to use his existing sales dashboard from SAP and integrate graphs and grids from the other systems, with common filters and interactions across all visualizations. Using Pyramid’s Cross Model Mapping, Shane defines the relationships between the systems, enabling fluid interactions across all four models.
Simple Model Mapping
The SAP and Oracle data models use different dimensions and table names, though the members are identical. For example, SAP uses YR_SALES, PROD, and MONTH, while Oracle uses Year, Products, and datekey month name. Shane uses the model mapping wizard to align the two models by mapping the disparate dimensions using their unique names.
Common Hierarchies with Different Members
Before mapping the SAP model to the marketing model, Shane ensures that team names in SAP (e.g., Seekers, Hunters) are mapped to the team numbers used in the marketing system (e.g., Team 1, Team 2).
| SAP Model | Marketing |
|---|---|
| SALES_TEAM | Sales Team |
| Seekers | Team 1 |
| Hunters | Team 2 |
| Dynamos | Team 3 |
| Rockets | Team 4 |
| Orbits | Team 5 |
Advanced Mapping Feature
Shane uses the advanced mapping feature to apply a “SAP and Marketing” model mapping by selecting the models, the sales team dimensions in both models, and applying Pyramid’s native PQL code to map the members in both directions. This enables both reports to sync members and interact with each other in the dashboard.
Combined Dashboard
After mapping the four disparate models, Shane can combine all visuals in one dashboard. The year and sales team slicer will filter all visuals, and product selections will interact across all visuals.
Summary
Most BI tools restrict dashboards to a single data model, making it difficult to combine visualizations from different sources. Pyramid’s cross-model mapping wizard allows users to define relationships between hierarchies and members from disparate models, enabling dashboards to include elements from multiple data models and sources. This is achieved through a simple, no-code, point-and-click interface.
While building a single data source (the “Data Lake”) is a complex and lengthy process, the “Analytics Lake” approach provides a faster, easier, and more agile solution for achieving a unified view of organizational data.