Using the project Schema Workbench, create an OLAP Cube by the following steps:
Create a connection (Option -> Connection). The data source should be a datawarehouse that integrates information from several sources (See: Data Integration)
In the Schema, include a cube
In the cube, include the table that contains the information to measure (e.g., sales)
In the cube, include all required dimensions.
For each dimension include at least one Hierarchy
For each dimension include one table that includes the information required in the hierarchy
For each Hierarchy include at least one level
Include in the cube at least one measure.
Next cube has been created for the proposed scenario.
The cube is stored as XML file.
Publish the cube (File -> Publish).
Open the cube in the server through the option “Create new” -> “JPivot view” (See: Pentaho Business Analytics)
Using the project Pentaho Report Designer, it is possible to create reports through the “Report Design Wizard”. It allows creating a report by the following steps:
Select a template
Add a data source using the icon “plus”. The data source should be a datawarehouse that integrates information from several sources (See: Data Integration)
Write a SQL query for consulting the desired information from the database.
In order to present the ETL process using the project Pentaho Data Integration, a small case study is proposed. The case study consist in a Car Dealer Business, which has the following basic information sources:
Sales database implemented in MySQL called ibis_dealer_sales. The relational model is presented in the next figure.
CRM database implemented in MySQL called ibis_dealer_crm. The relational model is presented in the next figure.
Car details, which is an Excel file. Its data is presented in he next figure
The objective is creating an ETL process for including all required information in the datawarehouse presented in the next figure
For this case study, a Job is created. This job includes all transformations of the ETL process. The following job includes four transformations. They are executed sequentially.
The first transformation, has the following steps:
crm_customer gets the table customer from the database ibis_dealer_crm
crm_category gets the table category from the database ibis_dealer_crm
Concat name allows concatenate the first name and last name of the customer
Stream lookup allows joining information from both sources based on the primary key of the table category and foreign key (which relates the customer with the category) of the table customer.
dwh_customer exports all information to the data base ibis_dealer_dwh
Each step in the transformation that requires information from one database allows creating connection. Next wizard is used for this task. It allows including all required parameters for the connection.
The second transformation, has the following steps:
sales_branch gets the table branch from the database ibis_dealer_sales
sales_city gets the table city from the database ibis_dealer_sales
sales_country gets the table country from the database ibis_dealer_sales
Stream lookup allows joining information from both sources based on the primary key of the table city and foreign key (which relates the branch with the city) of the table branch.
Stream lookup allows joining information from previous sources based on the primary key of the table country and foreign key (which relates the city with the country) of the table city.
dwh_customer exports all information to the data base ibis_dealer_dwh
The third transformation, has the following steps:
sales_car gets the table car from the database ibis_dealer_sales
Microsoft Excel Input gets the information from the excel file
Stream lookup allows joining information from both sources based on the attribute idmodel the table car and the column Id model of the excel file.
dwh_car exports all information to the data base ibis_dealer_dwh
For configuring the excel file, it is necessary to take into account the following configurations by double clicking the step.
In the tab “Files”, add the excel file
In the tab “Sheet” get the sheetnames. Select the first row and the first column of the information (e.g., in the file presented, the cell with the text “Id Model”, which corresponds whit the first cell is in the column A row 2).
In the tab “Fields” get fields from header row.
The fourth transformation, has the following steps:
sales_sale gets the table sale from the database ibis_dealer_sales
sales_car gets the table car from the database ibis_dealer_sales
Stream lookup allows joining information from both sources based on the attribute primary key of the table car and the foreign key (which relates the city with the country) of the table sale.
Calculator allows calculating the profit for each sale.
dwh_sale exports all information to the data base ibis_dealer_dwh
Finally, by executing the job, all information is extracted from the 3 sources, transformed based on the requirements and loaded in the data warehouse.
GMF allows the creation of diagram models by partitioning some of its components (See GMF Partitioning). Eugenia allows to create a GMF editor through a simple script (See GMF Editor using Eugenia).
Eugenia provides several options to create customized editors. However, sometimes the patitioning strategy is necessary in order to improve the visualization of the editor.
Basic GMF editor using Eugenia
In order to create one basic editor, next metamodel called model.ecore is used. The objective is creating one diagram that allows creating storages, which can have relations to containers, which can contain elements. Storages and elements has svg icons.
Next Emfatic Source script is created by selecting model.ecore -> richt click -> Generate Emfatic Source and complemented with the customized features of the editor
@namespace(uri="partitioning", prefix="partitioning")
package partitioning;
@gmf.diagram(model.extension="partitioning", diagram.extension="partitioning_diagram")
class Model {
val Storage[*] storages;
val Container[*] containers;
}
@gmf.node(figure="svg", svg.uri="platform:/plugin/emf.examples.partitioning/icons/storage.svg", label.icon="false", label="name", label.pattern="{0}", size="60,60")
class Storage {
attr String[1] name;
@gmf.link(target.decoration="arrow", source.decoration="none", style="dash", color="0,0,0")
ref Container[*] storageContainers;
}
@gmf.node(figure="rounded", label.icon="false", label="name", label.pattern="{0}", size="200,200")
class Container {
attr String[1] name;
@gmf.compartment(layout="free")
val Element[*] elements;
}
@gmf.node(figure="svg", svg.uri="platform:/plugin/emf.examples.partitioning/icons/element.svg", label.icon="false", label="name", label.pattern="{0}", size="60,60")
class Element {
attr String[1] name;
}
Projects presented in next figure have been generated. SVG icons are located in the folder icons of the project emf.examples.partitioning
Next figure presents the editor created
Partitioning using Eugenia
Now, the purpose is creating one diagram that allows creating storages, which can have relations to containers. Moreover, if doble clicking the container, a new diagram will be opened for drawing elements. This partitioning can be done using GMF (See: GMF Partitioning); however, in this case Eugenia is useful in order to customize SVG icons.
Model editor (main diagram)
For creating the main diagram which can contain storages and containers, the Emfatic Source has been modified. In this case, the @gmf anotation for the class Element, and for the relation elements in the class Container have been removed. The script is as follows.
@namespace(uri="partitioning", prefix="partitioning")
package partitioning;
@gmf.diagram(model.extension="partitioning", diagram.extension="partitioning_diagram")
class Model {
val Storage[*] storages;
val Container[*] containers;
}
@gmf.node(figure="svg", svg.uri="platform:/plugin/emf.examples.partitioning/icons/storage.svg", label.icon="false", label="name", label.pattern="{0}", size="60,60")
class Storage {
attr String[1] name;
@gmf.link(target.decoration="arrow", source.decoration="none", style="dash", color="0,0,0")
ref Container[*] storageContainers;
}
@gmf.node(figure="rounded", label.icon="false", label="name", label.pattern="{0}", size="200,200")
class Container {
attr String[1] name;
val Element[*] elements;
}
class Element {
attr String[1] name;
}
Next figure presents the editor generated with this new script
Container editor (sub diagram)
In order to create the container diagram, a new EMF empty project has been created. In this project, The next Emfatic Source script called container.emf has been created. This script includes only the concepts Container and Element.
@namespace(uri="partitioning", prefix="partitioning")
package partitioning;
@gmf.diagram(model.extension="partitioningcontainer", diagram.extension="partitioningcontainer_diagram")
class Container {
attr String[1] name;
val Element[*] elements;
}
@gmf.node(figure="svg", svg.uri="platform:/plugin/emf.examples.partitioning/icons/element.svg", label.icon="false", label="name", label.pattern="{0}", size="60,60")
class Element {
attr String[1] name;
}
Next figure presents the editor generated with this script
Linking Diagrams
So far, both editors are independent. In order to join both editors, the files container.gmftool and container.gmfgraph have been copied to the model editor in the folder model.
Based on the files model.ecore, container.gmftool and container.gmfgraph the file container.gmfmap is created (See GMF Partitioning).
Note 1: Be sure that the file container.gmfmap has been created properly. Compare with the file container.gmfmap created in the independet project.
Note 2: The purpose of creating this file again, is because if this file is copied, it can point to the metamodel (in this case container.ecore) of the previous project, insted of the proper metamodel (in this case model.ecore).
Note 3: If the file is copied, also it can be changed by replacing the name of the metamodel using one plain text editor. However, this solution can generate further errors.
Based on the file container.gmfmap the file continer.gmfgen is created (See GMF Partitioning).
The file container.gmfgen must be configured (See GMF Partitioning)
Select Gen Editor Generator > Gen Diagram > Gen Top Level ContainerEditPart
Right click > New Child > Open Diagram Behaviour
Select Gen Editor Generator > Gen Diagram > Gen Top Level ContainerEditPart > Open Diagram Behaviour
Set Diagram Kind with the Gen Editor Generator ModelID value of the container.gmfgen. In this case PartitioningContainer
Set Editor ID with the Gen Editor View ID value of the container.gmfgen. In this case partitioning.diagram.container.part.PartitioningDiagramEditorID
Change Edit Policy Class Name Editor. It has a default value. (e.g. OpenDiagramContainerEditPolicy)
Create Diagram Projects
Create both diagram projects
Select Generator Model of the main diagram. In this case model.gmfgen
Right click > Create diagram code
Select Generator Model of the sub diagram. In this case container.gmfgen
Right click > Create diagram code
If SVG icons are used, next dependences must be included: org.eclipse.gmf.runtime.lite and org.eclipse.gmf.runtime.lite.svg. Those dependences must be added in the file plugin.xml of the project diagram. These dependences require GMF SDK Experimental
Results
Now, it is posible to create models with storages and containers. By double clicking on containers, a new diagram is opened for creating elements inside the correspondent container.
GMF allows the creation of diagram models by partitioning some of its components. It means, one diagram model can expand certain components (by double clicking) in order to visualize better those components.
For this purpose, next metamodel is used. The objective is creating one diagram that allows creating only containers. Double clicking on one container, a new diagram allows creating elements and relations
It is very important to set values to the package. In this case, the name is "partitioning"
EMF Editor Generation
The EMF Editor contains all necesary implentation for creating models. It can be created by the following steps
Create genmodel
Select the metamodel. In this case model.ecore
Right click > Generate EMF GenModel
Create Editor code
Select the metamodel. In this case model.ecore
Right click > Generate EMF editor
The genmodel file is created. In addition the projects edit, editor, and test are created as well
Model editor (main diagram)
This process allows creating the main diagram. In this case the main diagram is used for creating containers This diagram is based on the EClass Model
Generate Simple Graphical Definition Model (.gmfgraph)
Select the metamodel. In this case model.ecore
Right click > New > Other > Graphical Modeling Framework > Simple Graphical Definition Model
Select the root EClass. In this case Model
Only specify the EClasses of the nodes that will exist. In this case Container
Generate Simple Tooling Definition Model (.gmftool)
Select the metamodel. In this case model.ecore
Right click > New > Other > Graphical Modeling Framework > Simple Tooling Definition Model
Select the root EClass. In this case Model
Only specify the EClasses of the nodes that will exist. In this case Container
Generate Guide Mapping Model Creation (.gmfmap)
Select the metamodel. In this case model.ecore
Right click > New > Other > Graphical Modeling Framework > Guide Mapping Model Creation
Select Diagram Palette
Select Diagram Canvas
Select the nodes. In this case Container. Delete other nodes and links
Create Generator Model (.gmfgen) and Model Diagram Code
Select Mapping Model. In this case model.gmfmap
Right click > Create generator model
Select Generator Model. In this case model.gmfgen
Right click > Create diagram code
Container editor (sub diagram)
This process allows creating the diagram for creating elements and relations. This diagram is based on the EClass Container. This diagram will be open by double clicking on one container from the Model diagram
Generate Simple Graphical Definition Model (.gmfgraph)
Select the metamodel. In this case model.ecore
Right click > New > Other > Graphical Modeling Framework > Simple Graphical Definition Model
Give a different name (e.g. container.gmfgraph)
Select the root EClass. In this case Container
Only specify the EClasses of the nodes that will exist. In this case Element and Relation
Generate Simple Tooling Definition Model (.gmftool)
Select the metamodel. In this case model.ecore
Right click > New > Other > Graphical Modeling Framework > Simple Tooling Definition Model
Give a different name (e.g. container.gmftool)
Select the root EClass. In this case Container
Only specify the EClasses of the nodes that will exist. In this case Element and Relation
Generate Guide Mapping Model Creation (.gmfmap)
Select the metamodel. In this case model.ecore
Right click > New > Other > Graphical Modeling Framework > Guide Mapping Model Creation
Give a different name (e.g. container.gmfmap)
Select Diagram Palette
Select Diagram Canvas
Select the nodes. In this case Element. Select the links. In this case Relation. Delete other nodes and links
Verify Guide Mapping Model
Open the mapping model. In this case container.gmfmap
Select Mapping > Top Node Reference > Node Mapping. Check the correct information for Element, Diagram Node, and Tool
Select Mapping > Top Node Reference > Link Mapping. Check the correct information for Containment Feature, Element, Source Feature, Target Feature, Diagram Node, and Tool
Create Generator Model (.gmfgen)
Select Mapping Model. In this case container.gmfmap
Right click > Create generator model
Linking Diagrams
Configure container.gmfgen
Open the generator model. In this case container.gmfgen
Change Package Name Prefix (e.g. partitioning.diagram.container)
Select Gen Editor Generator > Gen Plugin
Change ID (e.g. emf.examples.partitioning.diagram.container)
Change Name (e.g. Partitioning Container Plugin)
Select Gen Editor Generator > Gen Editor View
Get the Editor View ID (It will be used in the next configuration). In this case partitioning.diagram.container.part.PartitioningDiagramEditorID
Configure model.gmfgen
Open the generator model. In this case model.gmfgen
Select Gen Editor Generator > Gen Diagram > Gen Top Level
Right click > New Child > Open Diagram Behaviour
Select Gen Editor Generator > Gen Diagram > Gen Top Level > Open Diagram Behaviour
Set Diagram Kind with the Gen Editor Generator ModelID value of the container.gmfgen. In this case PartitioningContainer
Set Editor ID with the Gen Editor View ID value of the container.gmfgen. In this case partitioning.diagram.container.part.PartitioningDiagramEditorID
Change Edit Policy Class Name Editor. It has a default value. (e.g. OpenDiagramContainerEditPolicy)
NOTE 1: This step is optional.
NOTE 2: This step is necessary if the main editor (in this case, the one generated by partitioning.gmfgen) has concepts that contain other concepts.
NOTE 3: If the main editor has concepts that contain other concepts, the other containing elements have by default the instance "Open Diagram Behaviour". I suggest to remove those instances; otherwise, the next exception can be presented when double clicking:
org.eclipse.core.commands.ExecutionException: Can't open diagram
at iArchimate.diagram.edit.policies.OpenDiagramEditPolicy$OpenDiagramCommand. doExecuteWithResult(OpenDiagramEditPolicy.java:106)
at org.eclipse.gmf.runtime.emf.commands.core.command.AbstractTransactionalCommand. doExecute(AbstractTransactionalCommand.java:247)
at org.eclipse.emf.workspace.AbstractEMFOperation.execute(AbstractEMFOperation.java:150)
at org.eclipse.core.commands.operations.DefaultOperationHistory.execute (DefaultOperationHistory.java:513)
Create Diagram Projects
Create both diagram projects
Select Generator Model of the main diagram. In this case model.gmfgen
Right click > Create diagram code
Select Generator Model of the sub diagram. In this case container.gmfgen
Right click > Create diagram code
Test
Launch an eclipse application
Create a Project
Create a Diagram. In this case Partitioning Diagram
Open the diagram
Draw containers
Select one created container and double click. The container editor will be opened.
Draw elements and relations.
Select other created container and double click. The container editor will be opened.
