Creating Palo OLAP cubes and dimensions with Talend

Talend Open Studio has a set of components to manage Palo Cubes and perform complex ETL processing. The main components are:

  • tPaloDatabase
  • tPaloCube
  • tPaloDimension
  • tPaloOutputMulti

This article covers how to create Palo databases, dimensions and cubes structure with Talend, while to load data in Palo cubes see the article Load data in Palo cubes with Talend.

Palo has a very interesting Open Souce OLAP approach that uses an efficient in Memory-OLAP server and Excel (or Open Office) as visualization tool. Palo allows OLAP cubes to be viewed on a web server, offering a cool (and sometime better) alternative to Mondrian/Jpivot. Talend Open Studio makes possibile to process complex data integration procedures to build reliable Palo OLAP cubes.

talend palo palette cubetalend palo palette

 

Step 1: Create a Palo database with Talend tPaloDatabase

Palo collects OLAP cubes in a database, so the first task is to create a new database. This step is quite straightforward, simply put a tPaloDatabase component (from Business Intelligence -> OLAP Cube -> Palo palette) on a newly created job and give a name to the database field (for example "MyDB"):

Create Palo database with tPaloDatabase

The default values are:
hostname: "localhost" (make sure Palo MOLAP Server service is active)
server port: "7777"
username: "admin"
password: "admin"
database: "MyDB"
action on database: "Create database"

Now run the job, a new Palo database named MyDB will be created.

 

Step 2: Add Palo dimensions with tPaloDimension

The next sep involves the creation of Customers, Categories and Measures OLAP dimensions. (Measures in Palo are stored as special dimension). These dimensions will be used in the creation of a new Palo Cube in the step 4.

Using tPaloDimension these tasks are really easy.

Create Palo dimension with tPaloDimension

The component parameters are as follow:

Database: "MyDB"
Dimension: "Customers"
Action on dimension: "Create dimension if not exists"

Then save and run the job, a new (empty) "Customers" dimension will be created. Use this component in the same way to create a new dimension called "Categories".

 

Step 3: Create a Measures dimension

OLAP Measures in Palo are defined in a single dimension, with element values as measure names. For example to add a "PiceList" and "Quantity" measure we'll create a dimension named "Measures" with two numeric elements: "PriceList" and "Quantity".

The creation of a Measures dimension can be achived in tPaloDimension enabling "Create dimension elements".

Measure elements names should be generated from a flow and mapped as columns in the tPaloDimension component. This can be achived with the followin procedure:

  • use a tFixedFlowInput component, with two column "ListPrice" an "Quantity" as output schema. As component parameter set "Use single table". Then assign to the column ListPrice the value "ListPrice" and to the column Quantity the value "Quantity", like in the following picture:

Talend tFixedRowInput

  • finally link the tFixedFlowInput to a new tPaloDimension component with these settings:
    Database: "MyDB"
    Dimension: "Measures"
    Create dimension elements: checked
    Consolidation type: None
    SchemaType: copyt the same schema of tFixedFlowInput compoent
    Inputcolumns:
    PriceList; Numeric; Add or Update
    Quantity; Numeric; Add or Update

The final result should be the same of the picture below:

Create Palo measures with Talend tPaloDimension

Now save and run the taks, a new "Measures" dimension with "PriceList" and "Quantity" OLAP measures will be created.

 

Step 4: Create Palo OLAP Cubes with Talend tPaloCube

When you have created the "Customers", "Categories" and "Measures" dimension is time to collect them in a new Palo Cube. This task can be achived using Talend tPaloCube component, configured as in the following picture:

create Palo cubes with talend tPaloCube

The parameters are:

Database: "MyDB"
Cube: "MyCube"
Action on cube: "Delete cube if not exists and create" (Palo cube needs to be rebuild when its structure changes)
DimensionsList:
"Categories"
"Customers"
"Measures"

Now save and run the job, a new Palo Cube will be created. To verify the creation process you can use the Palo Suite web panel, like in this picture:

Palo web cube creation with Talend Open Studio

 

Step 5: Use Talend as powerful ETL tool to load Palo OLAP cubes

The next step is to use Talend Open Studio as ETL tool to load data into Palo cubes. This argument is discussed in the article Load data in Palo cubes with Talend.

tags: