Pentaho schema workbench graphic tutorial,

Pentaho schema workbench graphic tutorial,
Pentaho schema workbench graphic tutorial

 

The following is a simple example to describe how to use schema workbench. The table example shows a simple sales table, product, product category, and customer dimension table on the network. The logic is simple and easy to understand.

1. Create a sample database

1.1. Create a table SQL

There are four tables, one fact table and three dimension tables. The statement for creating a table is as follows:

/** Sales table */

Create table Sale (

SaleId intnot null,

ProId intnull,

CusId intnull,

UnitPricefloat null, -- unit price

Number intnull, -- quantity

ConstraintPK_SALE primary key (saleId)

)

/** User table */

Create table Customer (

CusId intnot null,

Genderchar (1) null, -- Gender

ConstraintPK_CUSTOMER primary key (cusId)

)

/** Product table */

Create table Product (

ProId intnot null,

ProTypeIdint null,

ProNamevarchar (32) null,

ConstraintPK_PRODUCT primary key (proId)

)

/** Product category table */

Create table ProductType (

ProTypeIdint not null,

ProTypeNamevarchar (32) null,

ConstraintPK_PRODUCTTYPE primary key (proTypeId)

)

1.2. Insert sample data

Insert into Customer (cusId, gender) values (1, 'F ')

Insert into Customer (cusId, gender) values (2, 'M ')

Insert into Customer (cusId, gender) values (3, 'M ')

Insert into Customer (cusId, gender) values (4, 'F ')

Insert into producttype (proTypeId, proTypeName) values (1, 'electric appliance ')

Insert into producttype (proTypeId, proTypeName) values (2, 'dig ')

Insert into producttype (proTypeId, proTypeName) values (3, 'furniture ')

Insert into product (proId, proTypeId, proName) values (1, 1, 'washing machine ')

Insert into product (proId, proTypeId, proName) values (2, 1, 'TV ')

Insert into product (proId, proTypeId, proName) values (3, 2, 'mp3 ')

Insert into product (proId, proTypeId, proName) values (4,2, 'mp4 ')

Insert into product (proId, proTypeId, proName) values (5, 2, 'Digital camera ')

Insert into product (proId, proTypeId, proName) values (6, 3, 'chair ')

Insert into product (proId, proTypeId, proName) values (7, 3, 'table ')

Insert into sale (saleId, proId, cusId, unitPrice, number) values (1,340.34, 2)

Insert into sale (saleId, proId, cusId, unitPrice, number) values (2,140.34, 1)

Insert into sale (saleId, proId, cusId, unitPrice, number) values (3,240.34, 3)

Insert into sale (saleId, proId, cusId, unitPrice, number) values (4,540.34, 4)

Insert into sale (saleId, proId, cusId, unitPrice, number) values (5,4, 1, 80.34, 5)

Insert into sale (saleId, proId, cusId, unitPrice, number) values (6,5, 2, 90.34, 26)

Insert into sale (saleId, proId, cusId, unitPrice, number) values (7, 6, 3,140.34, 7)

Insert into sale (saleId, proId, cusId, unitPrice, number) values (4,640.34, 28)

Insert into sale (saleId, proId, cusId, unitPrice, number) values (9, 6, 1,140.34, 29)

Insert into sale (saleId, proId, cusId, unitPrice, number) values (2,740.34, 29)

Insert into sale (saleId, proId, cusId, unitPrice, number) values (30.34, 3, 28)

Insert into sale (saleId, proId, cusId, unitPrice, number) values (1240.34, 4, 72)

Insert into sale (saleId, proId, cusId, unitPrice, number) values (1,314.34, 27)

Insert into sale (saleId, proId, cusId, unitPrice, number) values (45.34, 2, 27)

 

2. schema Overview

Schema defines a multi-dimensional database. A logical model is provided to write query statements in the MDX language. The logical model includes several concepts: Cubes, Dimensions, Hierarchies, Levels, and Members ). A schema file is an xml file for editing the schema. Form the logical model and the physical model of the database in this file. Schemaworkbench is easy to use to create xml files.

3. Create a schema file through schema workbench

3.1 configure database connection

To create a database connection, you need to copy the database driver to the drivers folder under the schema workbench directory in advance, and then start the workbench program from Options à connection... Or click the toolbar as shown in.

Enter the connection property. The test is successful. Confirm to close the window.

3.2 Create a schema file

From the File menu or click the create button on the toolbar to create a schema. the pop-up interface is as follows:

Name the schema SaleSchema and save it;

3.3 create a cube

A Cube is a collection of dimensions and measurements. In Cube, Dimension and Measure share a fact table.

Select the schema node in the left-side view, click the cube button in the toolbar or right-click the menu, add a cube, name the cube SalesCube, and save it.

Right-click the cube node, add a fact table, and select the corresponding fact table sale. For example:

Click the edit mode button on the toolbar to view the generated xml file.

3.4 add customer dimension

A dimension is a set of Hierarchies. A Dimension generally has a corresponding dimension table. hierarchies and Hierarchies constitute Hierarchies. Right-click the cube to add a dimension and name it dimCustomer. Select foreignKey, that is, the foreign key used to reference the customer table in the fact table.

Add the corresponding level. By default, we have already created a level. Expand the dimCustomer dimension to see it. Modify the Level Attribute. The name can be blank. Modify the values of the allMemberName, allMemberCaption, and primaryKey fields (primary keys of the dimension table), and add the dimension table Customer.

3.4.1 Add level

Is part of Hierarchy. There are many attributes, and they are the key to schema writing. Using them can form a structure tree. The order of Level determines the position of Level in this tree, the top Level is at the first Level of the tree, and so on.

Select the column name corresponding to the level.

3.5 add Product Dimension

Because the product dimension is made up of two tables, it is a little complicated. You can also right-click the cube node to add a dimension. Enter a name and a foreign key in the fact table.

Modify the dimension information that is added by default. Right-click the dimension node and add join, which are Product and ProductType. Therefore, you must specify the primary table in the primaryKeyTable attribute of the dimension information.

Set the left and right tables for join, and then set the leftKey and rightKey for join Association. After completing this, set primaryKeyTable in dimension information, and set primaryKey to proId automatically.

3.5.1 increase the productId level

Right-click a dimension node to add a level, and fill in the corresponding attribute value. The table and column corresponding to the level, and whether the column value is a unique member.

3.5.2. added the productTypeId level.

Set the productTypeId level in the same step;

3.6 add Measurement

Measure is the value to be calculated and the core content of the operation.

3.6.1 increase sales volume measurement

Right-click the cube to add a measurement, input field, data type, and calculation method.

3.6.2. Increase the overall sales volume

The sales volume is calculated by the unitPrice * number expression and cannot be implemented by simply setting a column. You need to right-click the measurement and add the expression, and then edit the value of the expression.

 

The expression content is as follows:

3.6.3. Add Calculation member type measurement

Right-click the cube to add a calculation metric. The formula is added by default, the formula content is edited, and the calculation member is added.

Edit the formula content:

 

3.7. Verify xml results

Create an mdx query, load the corresponding schema file, enter the mdx query statement, and click Run test. No error is displayed, as shown in.

Select

{[Measures]. saleNumber, [Measures]. saleAmount, [Measures]. avuplice}

Oncolumns,

{([DimProductType]. [allProduct], [dimCustomer]. [allCustomer])}

Onrows

From [salesCube]