Talend Open Studio has a set of components to manage Palo Cubes and perform complex ETL processing. The main components are:
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.
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"):
The default values are:
hostname: "localhost" (make sure Palo MOLAP Server service is active)
server port: "7777"
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.
The component parameters are as follow:
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:
- finally link the tFixedFlowInput to a new tPaloDimension component with these settings:
Create dimension elements: checked
Consolidation type: None
SchemaType: copyt the same schema of tFixedFlowInput compoent
PriceList; Numeric; Add or Update
Quantity; Numeric; Add or Update
The final result should be the same of the picture below:
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:
The parameters are:
Action on cube: "Delete cube if not exists and create" (Palo cube needs to be rebuild when its structure changes)
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:
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.