Skip to main content

Pentaho (Community Edition)

I.                   Objectives

This course is focused on the use of Pentaho components to perform data transformation processes, modeling data sources and visualizing them using classic reports, on-demand reports and control panels.

At the end of the course, the student will have sufficient knowledge to:

Install the product and the corresponding Java version.

Perform data transformation (ETL) from different data sources to different destinations.

Model a star data source with snowflake.

Create online and classic (pixel-perfect) reports.

Use parameters to create user-configurable reports.

Create control panels by accessing different data sources.

II. Requirements

SQL knowledge.

Optional:

Knowledge of other ETL tools.

III.Duration

25 hours.

IV. Methodology

The course is developed through theoretical presentation accompanied by practical demonstrations and explanations of the results obtained.

The student carries out the creation of objects (transformations and PDI jobs, PDR reports, PSW schemes, online reports with Saiku or other tools, control panels with CDE) with different data sources, mainly relational (MySQL).

Resolution of doubts about the concepts presented.

V.               Content

Introduction to Pentaho Community Edition and its components.PCE components.Java requirements.

Installation of components.

PDI. Pentaho Data Integrator.Connections to databases.Import and export using tables.Import and export from plain text files, csv, Excel, xml, etc.Using calculator and formulas.Using lookup, group by, split, pivot.Modifying Kettle properties.Creating jobs.Using variables.Using parameters.Job flow and error management.

Using PDI from terminal.

PSW. Pentaho Schema Workbench.Modeling dimensional schema (star model) with normalization (snowflake).Publishing schemas.Defining fact table.Adding dimensions.Creating hierarchies.Modeling snowflake.Creating calculated measures.Creating calculated members.

Using summary tables (aggregate).

BIS. Pentaho BI ServerConfiguring and starting server.Server logs.Accessing the system.Configuring users.

Reports on request.

JPivot.

Pivot4J.Saiku.Download and upload files.

Planner.

PRD. Pentaho Report DesignerCreating classic reports (pixel-perfect).Using headers, footers, document setup, label area, data area.Pagination and including date and time in report.Formatting columns.Creating groupings.Creating totals.Using user filters:Simple filters.Using filters with text strings.Key-value filters.Nested parameters.Default values.Options for “no data found”.Subreports in reports.Charts in reports:Pie chart.Bar chart.Subreports with charts.Spark-line charts.

Pivot tables in reports.

CDE. Community Dashboard EditorDashboard concept.Introduction to the three layers of CDE.Page layout.Components.Data sources.Creating CCC charts.

Incorporating charts and other data visualization elements into the dashboard.

Oracle BI 12c: Creating Repositories

I.                   Objectives

This course is focused on creating the OBI 12c repository (modeling source data so that the tool can generate reports).

Upon completion of the course, the student will have sufficient knowledge to:

Explain the differences between the different relational models.

Develop a repository from relational sources (star, snowflake, and normalized models), including partitioning, summary tables, hierarchies (level and value, parent-child), derived measures, time series measures, and variables for dynamic reports.

Correctly define the keys for table joins and hierarchy levels.

Publish models and verify their correct operation.

Set basic security for users and roles, activate caching to improve performance, and multi-developer development with MUDE or model merging.

II. Requirements

SQL knowledge.

Optional: Knowledge of Data Warehouse and star model.

Advanced use of databases.

III. Duration

From 20 to 28 hours (1 or 2 modules).

IV. Methodology

The course is developed through theoretical presentation accompanied by practical demonstrations and explanations of the results obtained.

The student carries out the modeling of a dimensional data source (star model) with normalized tables (snowflake) and different complexities to test the characteristics of the product.

Resolution of doubts about the concepts presented.

V.                Content

1.                  Module I

Duration: 20 hours

Introduction to Oracle BI EE. Architecture. Administration Tool.

Data models. Normalized. Star.

Snowflake.

Project creation.

Physical layer creation. Physical layer objects. Database. Functionalities. Connection pool. Data source access parameters. Importing data sources. Loading tables and columns. Definition of keys and table joins. Physical model. SELECT type data sources. Deployment and redeployment. Use of object aliases.

Use of display folders and shortcuts.

Creating a logical layer (correspondences and business model).Creating a dimension and fact table.Renaming dimension attributes.Using logical sources and table correspondences.Logical joins.Determining aggregation function for measures.

Ordering data based on another column.

Creating a presentation layer.Preparing objects for the user.Renaming folders and columns.Using aliases.

Nested folders.

Model validation.

Requirements for a correct model.

Publishing the model.Using datamodel.cmd.Enabling query log. Reviewing log in Fusion Middleware, Presentation Service and server file.

Repository connection modes (offline and online).

Advanced modeling:Snowflake.Avoiding circular relationships.

Short paths in snowflakes.

Calculated measures:Creating derived measures.

Common functions (Rank, Rsum…).

Hierarchies:Level hierarchies:Defining level hierarchies.Defining and behaving primary logical level keys, display keys, level keys.Modeling time hierarchy.Modeling dimension hierarchies.Bifurcated hierarchies.Preferred paths for further analysis.Defining and using numerical values ​​for level elements.Parent-child hierarchies:Creating help table.Modeling hierarchy.Level measures:Creating level measures.Shared measures.

Presentation hierarchies.Automatic creation of presentation hierarchies.

Manual creation.Different data presentation paths.

Time series:Defining chronological keys.

Time series measures:

Aug, ToDate, and PeriodRolling.

Summary (aggregate) tables.Defining summary tables.Physical and logical modeling.

Creating logical sources.

Partitioning. Partitioning types.

Value-based partitioning modeling.

Variables. Variable types (repository and session). Using variables in reports to generate dynamic filters.

Using row-wise session variables (bulk loading of variables).

Usage tracking:

Description of system tables. Enabling usage tracking. Creating a model for usage analysis.

Testing the model.

Setting implicit fact column.

Determining implicit fact for models with multiple fact tables and reports that only use dimensions.

  1. Module II

Duration: 8 hours

Security: OBI standard security layers. Creating Weblogic users and groups. Linking to application roles. Setting permissions on objects.

Row-level security.

Cache: Activate the cache system. Table purge configuration. Manual purge of cache entries. Check cache operation. Create agent to populate cache.

Cache management.

Multi-developer environment

Oracle Data Integrator 12c

I.                   Objectives

This course is focused on the installation and use of the main functionalities of Oracle Data Integrator 12c.

Upon completion of the course, the student will have sufficient knowledge to:

Install the development environment on a computer and configure an external agent.

Create mappings with different data integration methods, including user functions.

Use the most common knowledge modules in ETL processes.

Create procedures, packages, and load plans to carry out a complete data integration.

II. Requirements

SQL knowledge.

Optional:

Knowledge of other ETL tools.

III. Duration

25 hours.

IV.Methodology

The course is developed through theoretical presentation accompanied by practical demonstrations and explanations of the results obtained.

The student carries out the creation of objects (procedures, correspondences, models, topography, etc.) with different data sources, mainly relational (Oracle and MySQL).

Resolution of doubts about the concepts presented.

V. Content

Introduction to Oracle Data Integrator 12c, integration and administration. EL-T approach. Declarative design.

Knowledge modules.

Product installation. Prepare Java environment.

Installation of Middleware and ODI on Linux.

Create Master and Work repository. Using the RCU tool.

Using the ODI user interface and connection to the database.

Topology concepts. Description and creation of physical and logical architectures. Creation of contexts. Data servers and physical schemes. Access to source data and target schemas.Server connection parameters.Creation of logical schemas.Association of logical and physical objects through contexts.

Description and creation of physical and logical agents.Installation of Standalone Collocated Agent.

Topology configuration.

Projects.Project creation.Description of objects: Folders, packages, correspondences, procedures, variables, sequences, user functions, knowledge modules, bookmarks.Organization and scope of projects.Export and import of objects.

Introduction to knowledge modules.

Creation of models.Model folders.Datastore concept.Reverse engineering from relational models.Working with XML sources.Concepts of restrictions and indexes.Primary and foreign keys (references).NOT NULL or mandatory columns.Conditions (check type restrictions).Establish automatic distribution.Establish restriction checkpoint (flow, static).

Verify compliance with restrictions.

Mapping. Basic mappings. Use of different technologies (MySQL > Oracle). Use of Flow Control.

Use of Control Append.

Use of Incremental Update. Use of mapping components: Join, Aggregate, Filter, Lookup…

Specification of the staging area location.

Use of Operator Navigator. Check the execution log. See the sessions, steps and tasks carried out.

Review the code generated by ODI.

Sequences. Types of sequences.

Use of native sequences.

User functions. Scope of use. Implementation. Multiple technologies.

Creating a user function.

Procedures. Procedure description. Creating a procedure. Tasks and properties. Optional commands.

Modifying behavior using options.

Packages.Description.Creating a package.Package diagram panel and tools.Using variables in packages.Updating the value.Verifying and making decisions.

Controlling execution.

Version management.Creating versions in objects.

Comparing changes.

Scenarios.Description.Creating a scenario.

Managing versions in scenarios.

Load plans.Description.

Creating load plans with scenarios.

Change management. Change Data Capture.Description of the CDC system.Implementing and reviewing objects created in the database. Enabling the system.

Creating correspondence with CDC function.

Oracle Analytics Server: Analytics and P.C.

I.                   Objectives

This course is focused on creating reports (analysis) and dashboards in Oracle Analytics Server

Upon completion of the course, the student will have sufficient knowledge to:

Create reports with tables and graphs using user and dynamic filter options, groupings and advanced selections with hierarchy members.

Create different types of graphs and configure their properties. Add them to reports and establish master-detail links.

Use of key process indicators, KPIs, and follow their trend based on dimensions.

Create dashboards, with dynamic filters using prompts and conditioning the display of objects.

Use of tools: Delivers, for automatic and scheduled delivery of reports; briefing books

Use of the new Oracle Analytics Server tools for data visualization.

II. Requirements

There are no specific requirements.

Optional:

Knowledge of SQL and data analysis.

III. Duration

30 hours.

IV. Methodology

The course is developed through theoretical presentation accompanied by practical demonstrations and explanations of the results obtained.

The student carries out the creation of analyses using different characteristics of the product of varying complexity. A control panel is developed with the previous analyses.

Resolution of doubts about the concepts presented.

V.               Content

Introduction to Oracle Analytics Server and Oracle Analytics Cloud.

Architecture.

Data modeling. Create and publish a model. Create fact and dimension tables. Create hierarchies.

Additional data modeling techniques.

User interface. Subject areas and relationships with the repository. Main page, favorites and catalog.

Introduction to the analysis editor.

Creating viewsCreating analysesOrdering dataViews in analysesEditing charts

Creating calculated items

FormattingFormatting columns

Conditional formatting

Filters and promptsData filters

Using prompts and variables

Grouping and selection stepsUsing selection stepsCreating and using groups

Creating and using calculated items

Advanced use of the analysis editorView editorsCompound Layout panel featuresUsing sections (page, section, measures, rows, columns, exclusion)

Chart editorDefining general chart propertiesModifying a chart’s styleUsing titles and labelsConditional formattingAdding totals and subtotalsUsing bins (combining values)

Using the master-detail function

Graphical ObjectsAdvanced OptionsUsing KPIs

Additional Graphical Objects

Dashboards.Interactive Analytics.Creating Dashboards.Expressions.Adding Subpages.Adding Dashboard Actions.Using Prompts.Personalization.

Adding Content.

Briefing Books.Creating Briefing Books.

Using Briefing Books.

Delivers.Creating Automatic Content with Deliveries.

Creating Agents.

Cache Management.

Oracle Data Visualization.Projects and Data.

Creating Visualizations and Stories.

Pentaho Data Integration – Kettle (CE)

I.                   Objectives

This course is focused on the use of the Pentaho ETL component: Pentaho Data Integration.

Upon completion of the course, the student will have sufficient knowledge to:

Install the product and the corresponding Java version.

Perform data transformation (ETL) from different data sources to different destinations.

II. Requirements

SQL knowledge.

Optional:

Knowledge of other ETL tools.

III. Duration

20 hours.

IV. Methodology

The course is developed through theoretical presentation accompanied by practical demonstrations and explanations of the results obtained.

The student carries out practices with the product for each concept explained. Different data sources will be used, including text files, spreadsheets and databases, mainly relational (MySQL).

Resolution of doubts about the concepts presented.

V.               Content

Introduction to Pentaho Community Edition and its components. PCE components. Java requirements.

Java and PDI installation.

PDI. Pentaho Data Integrator. Databases:Connections to databases.Using shared connections.Basic components of transformations:Creating transformations.Importing and exporting using tables.Importing and exporting from plain text files, csv, Excel, xml, etc.Using calculator and formulas.Selecting columns and data filters.Using lookup, group by, split, pivot.Merge join.Mapping.Jobs, variables and properties:Modifying Kettle properties.Creating jobs.Using variables.Using parameters.Job flow and error management.Additional elements:Using PDI from terminal.Running code in database.Dynamic processing of files.Moving files in the operating system.Writing in the PDI “log”.Waiting for files (filewatcher).Checking for file existence.Checking for table existence (BBDD).

Practices with transformations and jobs.

© Juan de Juan.