Code

Rules

Frontend: Visualizations, Dashboards and Reports

Backend: ETL/ELT, Database and Data Lake

Frontend and Backend Code

When to move code

Enterprise Analytic and Business Intelligence (BI) systems have many Business Users viewing and interacting with visualizations, dashboards and reports (analytic objects). “Ad Hoc” users also create analytic objects. These analytic objects can have high-value for the Organization and may need to be integrated with the Enterprise Data Warehouse (EDW) and/or Data Lake. They contain business logic, rules and code.

Possible reasons to move Frontend work to the Backend:

  1. Traceability and Accuracy:  Text, Calendars or Numbers not matching between “Analytics Objects.” Equally as important as security.
  2. Security:  Ability to securely share at all organizational levels (e.g., Sales Person or Managers at different organizational levels - Hierarchy/Dimension).
  3. Integration:  Need to relate and compare Frontend “Analytic Objects” among each other or with other Corporate Data.
  4. Interactive: Data Model refresh speed and removing manual preparation steps/time. A two second refresh versus a 40 second refresh, means your data model is 20 times faster.
  5. Next-level: Once Business Rules and code are moved to the Backend, higher-level analysis and computations can be performed in the database for better Frontend Viewing, Interacting and Ad Hoc building.
  6. Augmented capabilities: Understand the features and benefits for each platform.

"Analytics and business intelligence (ABI) platforms are characterized by easy-to-use functionality that supports a full analytic workflow — from data preparation to visual exploration and insight generation — with an emphasis on self-service usage and augmented user assistance."

Gartner Magic Quadrant for Analytics and Business Intelligence Platforms February 15, 2021

Analytics and Data Mgmt. Landscape

"Data and analytics leaders are facing intensive pressure to provide “trusted” data that can help business operations to run more efficiently and making business decisions faster and with greater confidence."

Gartner Magic Quadrant for Data Quality Solutions March 15, 2021

Informatica, IBM, SAP, SAS, Talend and Precisely are leaders in leaders in Gartner's Magic Quadrant for Data Quality Solutions

Sales Manager scenario (frontend)

A Sales Manager has joined the Company and has used MS Access and Power BI in the past. The new company uses Tableau and Business Objects. The Sales Manager has built a “Quarterly Projected Customer Sales” Tableau visualization using Business Objects report downloads (source is EDW tables), Tableau EDW tables, MS Access tables - transformations (unique dimensions and facts) and external Excel industry numbers. It also rolls-up to Sales Representative, Districts, Regions and to the Company (with level rules and computations).

Leadership would like to use the quarterly projections for next quarters’ sales targets or plans. This is where business logic, dimensions, facts, computations and other code can be moved to the EDW for shared use with users or other systems. Perhaps a CRM or another sales management application that is used to communicate sales-to-plan numbers. The Sales Manager’s original visualization can use the new EDW tables with adjustments for production use, as well as, building additional visualizations around sales targets and attainment.

Frontend Functions (Examples: Tableau, Power BI and Excel)

Tableau and Power BI are both leading platforms in the Gartner 2021 Magic Quadrant for Analytics and Business Intelligence Platforms

When you write an Excel formula like =SUM(I2:J2), it is a Function. If Statements, Sum, Average, Minimum and Maximum are as well. Functions are code or "Rules" used in the Frontend and Backend. Functions on the Backend are used in SQL. Below is a Function being placed in an Excel cell. The same basic method is used across Visualizations, Dashboards and Reports for builds (frontend tables, formulas and functions).

If Statement (If-Then-Else) / Algorithm

Below is an Excel If Statement which acts as virtual code on the frontend (like a dimension attribute) and can also be used to create a dimension column on the backend. Database algorithms are used for categorization/dimension, calendars/dimension, measures and computations.

Below are statements for SQL, Scala, R and Python

Microsoft, Google, Amazon Web Services and IBM are leaders in Gartner's 2021 Magic Quadrant for Cloud AI Developer Services

Transforms at all Levels

Below is an illustrative Table with Columns. Tables can have original (raw), removed, combined and other columns created based on SQL, Functions, R, Python, Scala and Java (logic, text and computations).

Leverage and integrate the valuable visualization, dashboard and report logic and code created by business analysts and users. Each Analytics and Data Management layer has rules and code. We can help find the right balance on which tools have temporary or permanent Company rules and code.