Cube

Overview

The Cube in ReOrc's Semantic Layers is a structured data model that enables efficient data querying and analytics. It organizes data into measures and dimensions, allowing users to derive insights with ease. Cubes help abstract complex queries, improving performance and usability for business intelligence and reporting

Cube Concept

A Cube is a virtual data model that represents a multidimensional dataset, composed of:

• Measures: Numeric values that can be aggregated (e.g., revenue, sales count).

• Dimensions: Categorical values used for grouping and filtering (e.g., product category, region, date).

• Primary Key (PK): A unique identifier that ensures data integrity within the cube.

• Foreign/Unique Keys: Each cube will contain zero, one, or many foreign or unique keys used to connect to other data models/tables.

• Data Model Representation: Each data model/table is created as one cube.

• Reusability: Each cube can be reused as an input to create views.

• Integration with BI Tools: Dimensions and measures within a cube are used to feed data into BI tools for reporting and visualization.

Cube Data Type

Data type from model or table from warehouse will be generated into Generic Data Type includes:

• String

• Boolean

• Date

• Time

• Integer

• Decimal

Measures and dimensions

As mentioned above, you create a cube to model a semantic modeling case with your data. When creating a cube, you start by specifying the target tables and defining their relationships. From the available columns in these tables, you extract dimensions and further define measures from the dimensions.

• Dimensions are non-aggregated columns in your dataset, which you can use to segment or filter your data. They act as descriptive attributes for measures. For instance, dimensions like order_status or payment_method can filter or categorize orders.

• Measures are aggregations performed on columns in your model. They represent metrics or calculations, such as order_count (total number of orders) or average_order_value (average value of a sale, calculated as sales amount ÷ order count). Measures can be final metrics or building blocks for more complex calculations.

With a cube defined, when an end user asks: "What are the monthly revenue this year?". They can construct a data query using:

• Measures: revenue (aggregated through using sum on orders' amount)

• Dimensions: order_date dimension with month granularity

Create Cube

Option 1: From Current Project

To create a cube, follow these steps:

Ensure the reference data model has been:

• Generated Schema. Refer to Model schema

• Materialized in Production environment by creating a pipeline job in Production environment. Refer to Job

1. In the Cube tab, click on the + icon and select New Cube.

2. In the opened modal:

   1. Select data source. It is defaulted to current project

   2. Select the data models that you would like to create as Cube

   3. Click on Create button

   
3. Cube(s) created successfully under Cube folder.

4. In the Fields pane, mouse over to the dimension that can be uniquely identify the record in the cube and click on ..., select Set as Primary Key.

Option 2: From Data Warehouse Connection

Please ensure the Production Environment Data Connection of the Analytics Databases has been set up. Refer to Connections

1. In the Cube tab, click on the + icon and select New Cube.

2. In the opened modal:

   1. Select Warehouse.

   2. Select the tables that you would like to create as Cube

   
3. Cube(s) created successfully under Cube folder.

4. In the Fields pane, mouse over to the dimension that can be uniquely identify the record in the cube and click on ..., select Set as Primary Key.

Preview Cube Result

In the Display pane, you can select the dimensions & measures that you would like to preview cube by clicking on + Add under the Fields section.

You can further filter the data by adding in the dimensions or measures into the Filter section and sort by selected field under Sort Section.

Click on Preview button to preview the result of the selected fields & criteria.

Corresponding to the preview result on the selected fields & conditions, the SQL will be generated on the Generated SQL tab.

Organize Cubes

You can organize the cubes into folders that reflect the analysis area and help maintain a clean project structure.

To create a new folder, click on the action button of an existing folder and select Add sub-folder:

To move a model into the new folder, click on the action button of the model and select Move to. Then select the target folder from the list.