Part III: The Art of Data Modeling

4.0 Module 4: Fundamentals of Information Modeling

The SAP HANA Information Modeler can be considered the very heart of the HANA system. It is here that raw data is transformed into meaningful, high-performance analytical structures. This module will explore the foundational concepts that underpin all data modeling activities in HANA, from the structure of tables to the critical distinction between facts and dimensions.

——————————————————————————–

4.1 The Role of the Information Modeler

The purpose of the SAP HANA Information Modeler is to create powerful modeling views on top of physical database tables. These views are not just simple projections of data; they are designed to implement complex business logic, perform aggregations, and prepare data for consumption by analytical and reporting tools.

Key features of the Information Modeler include:

• It works exclusively with column-based tables to leverage their performance benefits.
• It creates various types of “Information Views” (Attribute, Analytic, and Calculation Views).
• These views are designed to be consumed by front-end reporting tools like SAP Lumira, SAP Analysis for Office, or even third-party tools such as Microsoft Excel.

4.2 Core Concepts: Fact vs. Dimension Tables

A fundamental concept in data warehousing and modeling is the distinction between fact and dimension tables.

• Fact Table: A fact table contains the measurable, quantitative data of a business process. Its columns consist of primary keys for related dimension tables and the numerical values, or measures, that are to be analyzed.
  • Examples of measures: Number of units sold, Total Price, Average Delay Time.
• Dimension Table: A dimension table contains descriptive, master data that provides context to the measures in the fact table.
  • Examples of dimensions: Customer data (name, address), Product details (name, category), Time information (day, month, year).

To illustrate this relationship, consider a company that sells products to customers. Every sale is a business event, or a “fact.” The fact table would record these sales, containing a customer ID, a product ID, a date ID, and the measures for that sale, like quantity and sales revenue. The dimension tables would hold the detailed information about the customer (Customer table), the product (Product table), and the date (Time table). By joining the fact table with the dimension tables, an analyst can answer questions like, “What was the total sales revenue for a specific product category in the last quarter?”

4.3 A Critical Distinction: Row vs. Column Store

As previously noted, SAP HANA Modeler Views can only be created on top of column-based tables. This exclusivity is a deliberate design choice. SAP HANA prioritizes analytical query performance—which thrives on column-based operations—over the transactional efficiency of full-row retrieval common in traditional OLTP systems. This trade-off is the key to understanding HANA’s core purpose.

Column Store

In a column-store table, data is stored vertically, column by column, rather than horizontally in rows. This means all values for a single column are stored contiguously in memory. This structure offers several key benefits:

• Faster Operations: Read and write operations are significantly faster, especially for analytical queries that only need to access a few columns from a wide table.
• Data Compression: Because all data in a column is of the same type, it can be compressed very effectively. Two primary methods are used:
  • Dictionary Compressed: Unique values in a column are stored once in a dictionary, and the column itself stores only small integer codes that point to the dictionary.
  • Run-length Compressed: For columns with many repeating values, the system stores the value once along with a multiplier indicating how many times it repeats consecutively.
• High-Speed Aggregations: Calculations and aggregations (like SUM, AVG, COUNT) are extremely fast because the engine only needs to process the data from the relevant columns, which are stored together.

Row Store

In a row-store table, data is stored horizontally, with all the values for a single record stored contiguously. This is the traditional structure used in most transactional databases.

Functional Differences

The choice between column and row storage depends entirely on the workload.

• Use Column Store when: The query involves aggregations or calculations on a subset of columns. For example, a query like SELECT SUM(Sales) FROM Sales_Table WHERE Date = ‘2023-10-26’ is highly optimized on a column store because the database only needs to read the Sales and Date columns.
• Use Row Store when: The query needs to retrieve an entire record (i.e., all columns). A query like SELECT * FROM Employee_Table WHERE Employee_ID = 123 is more efficient on a row store because all the data for that employee is stored in one place.

These fundamental table structures are the building blocks, which are then organized into larger logical structures known as schemas.

——————————————————————————–

5.0 Module 5: Data Warehouse Schemas

Schemas are the logical blueprints that define how data is organized within a data warehouse. They provide a structured way to join fact and dimension tables to facilitate business analysis and reporting. This module will examine the three primary schema types used in SAP HANA to model these relationships.

——————————————————————————–

5.1 The Star Schema

The Star Schema is the simplest and most common schema structure. Its design is characterized by a central fact table connected directly to a set of dimension tables. Each dimension is represented by a single table, and these dimension tables are not normalized further. The resulting diagram resembles a star, with the fact table at the center and the dimension tables radiating outwards.

• Example: Consider a FactSales table containing measures like units_sold and dollars_sold. This central fact table would be joined to four separate dimension tables:
  • DimTime (containing date information)
  • DimItem (containing product details)
  • DimBranch (containing store branch information)
  • DimLocation (containing geographical data)

The FactSales table would hold the primary keys from each of these dimension tables, allowing for a direct join to retrieve the contextual data needed for analysis.

5.2 The Snowflake Schema

The Snowflake Schema is an extension of the star schema. In this model, some of the dimension tables are normalized into smaller, less redundant sub-tables. This means that a dimension table might be broken down into multiple related tables to minimize data redundancy. The resulting structure resembles a snowflake, as the central star shape now has smaller branches extending from its points.

• Example: Continuing with the sales model, the DimItem table might be normalized. Instead of storing supplier information directly in DimItem, a separate DimSupplier table could be created and linked to DimItem. Similarly, the DimLocation table could be normalized into DimCity and DimCountry tables. While this reduces data redundancy, it can increase query complexity due to the need for more joins.

5.3 The Galaxy Schema (Fact Constellation)

The Galaxy Schema, also known as a Fact Constellation, is a more complex model characterized by the presence of multiple fact tables that share some common dimension tables. This structure is often used to model different but related business processes within a single data warehouse.

• Example: A business might have one fact table for sales (FactSales) and another for shipping (FactShipping). Both of these fact tables would share common dimensions like DimTime and DimItem. However, FactSales might link to a DimBranch dimension, while FactShipping links to a DimShipper dimension. This allows for integrated analysis across different business processes.

The practical implementation of these schemas in HANA begins with the creation of the underlying tables and the organization of models into logical containers called packages.

——————————————————————————–

6.0 Module 6: Building Blocks: Tables and Packages

Before complex information views can be constructed, the foundational database objects—tables and packages—must be created. Tables house the raw data, while packages provide an organizational structure for the data models. This module provides a practical guide to performing these essential tasks in SAP HANA Studio.

——————————————————————————–

6.1 Creating Tables in SAP HANA

There are two primary methods for creating tables in the HANA database: using the SQL Editor or the graphical user interface (GUI).

Method 1: SQL Editor

1. Open the SQL Console: This can be done by right-clicking on a schema in the Catalog tab and selecting “Open SQL Console.”
2. Write the CREATE TABLE Statement: Use standard SQL syntax to define the table. It is crucial to specify COLUMN for column-based storage, as this is required for use in the Information Modeler.
3. This statement creates a column-based table named Test1 with two columns, ID and NAME, and defines ID as the primary key.
4. Execute the Statement: Click the “Execute” button in the SQL editor toolbar. A confirmation message will appear in the log, indicating successful execution.
5. Refresh the View: Right-click the Table folder under your schema and select “Refresh.” The new table will now be visible.
6. Populate the Table (Optional): Use INSERT statements to add data to the newly created table.

Method 2: GUI Option

1. Initiate Table Creation: Right-click on the Table folder under the desired schema and select New Table.
2. Define Table Properties: A new window will open. Here, you will:
   • Enter the Table name.
   • Confirm the correct Schema name.
   • Select the Table type (COLUMN Store or ROW Store).
3. Define Columns: In the main panel, add columns by clicking the + sign. For each column, define its name, data type, length, and whether it is part of the primary key.
4. Execute: Click the “Execute” button (or press F8) to create the table.

Authorizing Schema Access

For the HANA Modeler to use tables from a specific schema, a special system user named _SYS_REPO must be granted access. This is a critical one-time step for each schema used in modeling. The following command must be executed in the SQL Console:

GRANT SELECT ON SCHEMA “<SCHEMA_NAME>” TO _SYS_REPO WITH GRANT OPTION;

This command gives the repository user the necessary privileges to read the tables and build views on top of them.

6.2 Organizing Models with Packages

All information models (views) are stored in the Content tab and must be organized into Packages. Packages act as folders or containers for grouping related modeling objects.

Creating a New Package:

1. Right-click on the Content node in the Systems view and select New -> Package.
2. In the dialog box, provide a Name and a Description for the package.
3. Click OK. The new package will appear under the Content node.

You can also create sub-packages by right-clicking on an existing package. Within a package, you can create several types of objects, including:

1. Attribute Views
2. Analytic Views
3. Calculation Views
4. Analytic Privileges
5. Procedures
6. Decision Tables
7. Sub-Packages

With our tables created and a package structure in place, we are now ready to build the actual information models—Attribute, Analytic, and Calculation Views—within them.

——————————————————————————–

7.0 Module 7: Information Views in Detail

Information Views are the core components of SAP HANA modeling. They are powerful analytical structures that transform raw data from database tables into meaningful, performance-optimized models for reporting and analysis. This module provides a detailed, step-by-step exploration of the three primary view types: Attribute, Analytic, and Calculation Views.

——————————————————————————–

7.1 Attribute Views

Purpose and Characteristics

Attribute Views are used to model master data. They are built on top of dimension tables and are analogous to “characteristics” in SAP Business Warehouse (BW). Their primary purpose is to provide context and descriptive attributes for analysis.

• Key Characteristics:
  • Represent master data (e.g., Customer, Product).
  • Used to join multiple dimension tables or other Attribute Views.
  • Consumed within Analytic and Calculation Views to provide dimensional context.
  • Can be used to filter and limit the attributes from large dimension tables for better performance.

Creation Walkthrough

1. Initiate Creation: Right-click on your package, navigate to New, and select Attribute View.
2. Define Properties: In the new window, provide a Name and Description. Select the subtype, which can be Standard, Time (for time-based dimensions), or Derived.
3. Understand the Panes: The modeling editor opens with three main work panes:
   • Scenario Pane: Contains the Data Foundation (where tables are added) and the Semantic Layer.
   • Details Pane: Shows the tables, their columns, and the joins between them.
   • Output Pane: Lists the columns that will be exposed by the view.
4. Add Objects: Click the + icon next to Data Foundation to add one or more dimension tables or existing Attribute Views.
5. Join Tables: In the Details Pane, drag a column from one table to a corresponding column in another table to create a join (e.g., joining on a primary key).
6. Select Output Attributes: In the Details Pane, select the columns you want to expose, right-click, and choose Add to Output.
7. Activate the View: Click the Activate button (green circle icon). A non-activated view is marked with a diamond icon; upon successful activation, the diamond disappears.
8. Preview Data: Right-click the activated view and select Data Preview. You can drag attributes to the Label Axis to see the master data.

7.2 Analytic Views

Purpose and Characteristics

Analytic Views are designed to model data in a classic Star Schema structure. They join a central fact table (containing measures) with one or more Attribute Views (representing dimensions). They are optimized for performing complex calculations and aggregations.

• Key Characteristics:
  • Designed specifically for Star Schema queries.
  • Contain at least one fact table and are joined to multiple dimensions (via Attribute Views).
  • Used to perform aggregations on measures (e.g., SUM, COUNT, MIN, MAX).
  • Similar in concept to InfoCubes in SAP BW.

Creation Walkthrough

1. Initiate Creation: Right-click your package, go to New, and select Analytic View.
2. Add Tables and Views: The editor for an Analytic View has two main panes in the scenario:
   • Data Foundation: Add your fact table and any direct dimension tables here. Join them as needed.
   • Star Join: Add your previously created Attribute Views here and join them to the fact table.
3. Define Output: Select the desired columns from your tables and add them to the output.
4. Define Measures: Go to the Semantic Layer. Here, you must identify the columns from your fact table that are measures (e.g., SALES_AMOUNT). Select these columns and click the “Mark as Measure” icon. This changes their data type to “measure.”
5. Activate and Preview: Activate the view. In the Data Preview, you can now drag attributes to the Label Axis and your newly defined measures to the Value Axis to perform analysis.

7.3 Calculation Views

Purpose and Characteristics

Calculation Views are the most powerful and flexible view type. They are used to perform complex calculations that are not possible in Analytic Views. They can consume tables, Attribute Views, Analytic Views, and even other Calculation Views.

• Key Characteristics:
  • Can perform complex calculations and implement advanced business logic.
  • Can be created graphically or with SQL script.
  • Include built-in nodes for operations like Union, Join, Projection, and Aggregation.
  • Can combine data from multiple source views.

Creation Methods

• Graphical Calculation Views: Built using a drag-and-drop interface with graphical nodes (e.g., join, union) to define the data flow and logic.
• SQL Script based Calculation Views: Defined using SQLScript, a set of SQL extensions for HANA, allowing for highly complex, programmatic logic.

Data Categories

When creating a graphical Calculation View, you must select a data category:

• Cube: Designed for multidimensional reporting. The default node is Aggregation. This type supports a Star Join.
• Dimension: Designed to create a reusable dimension. The default node is Projection.

Example: Calculation View with Star Join

A Star Join within a Calculation View simplifies the design process, allowing fact tables to be used directly without needing to create Analytic Views first.

• Business Scenario: Find complete employee details (name, joining date, salary, bonus) by combining data from two fact tables and two dimension tables.
• Sample Table Creation (SQL):
• Creation Walkthrough (Graphical with Star Join):
  1. First, we must represent our master data tables (Empdim, Empdate) as reusable dimensions. To do this, create two separate Calculation Views of the type Dimension, one for Empdim and one for Empdate. Activate both.
  2. Next, create the primary Calculation View of type Cube with the “With Star Join” option enabled. This will be our central modeling object.
  3. Within this view, drag two Projection nodes onto the canvas. A Projection node is used to select and filter columns from a data source. We will use one for each fact table (Empfact1 and Empfact2) to prepare their data.
  4. Now, add a Join node. We need to combine the data from our two fact tables based on the common employee ID. Connect the outputs of both Projection nodes to this Join node and define the join condition on empId.
  5. The output of this join now represents a unified fact dataset. Connect the output of the Join node to the central Star Join node.
  6. Finally, add the two Dimension Calculation Views created earlier (e.g., Empdim_CV, Empdate_CV) to the model and join them to the Star Join node on their respective keys (empId and date).
  7. Map the final output columns in the Semantic Layer, define measures (Sal, Bonus), and activate the view.
• Benefits of Star Join: It simplifies the design, as you don’t need to create separate Analytic Views. It also supports 3rd Normal Form (3NF) designs directly within the view.

Alternative Method (Without Star Join)

The same result could be achieved through a more traditional, multi-layered approach. First, you would create two separate Attribute Views on the Empdim and Empdate tables. Next, you would create two separate Analytic Views. The first Analytic View would join the Empfact1 table to both Attribute Views. The second Analytic View would do the same for the Empfact2 table. Finally, you would create a Calculation View that uses a Join node to combine the results of the two Analytic Views. This method is more modular but requires creating more individual objects to achieve the same outcome.

Having mastered the art of transforming raw data into valuable information models, it is imperative that we now turn our attention to securing these assets.

——————————————————————————–