SteelWheels

Overview of a Mondian Schema v3 ..

Workshop - Overview of SteelWheels Schema

Welcome to the Overview of SteelWheels Schema workshop. In this guided demonstration, you will explore the SampleData database and the SteelWheels Mondrian schema using Schema Workbench and JDBC Explorer.

The SteelWheels schema serves as a practical example of how multidimensional database structures are mapped through Mondrian schemas. This schema represents a typical business scenario involving sales transactions, customer information, product details, and temporal data, making it an ideal learning resource for understanding dimensional modeling concepts.

In this guided demonstration, you will:

• Use JDBC Explorer to view the database - Navigate through the physical database structure of SampleData, examining tables, columns, and relationships that form the foundation of the SteelWheels schema

• Describe the purpose and value of Schema Workbench - Understand how Schema Workbench enables you to create and maintain Mondrian schemas, which are logical models that map to multidimensional database structures for business intelligence and analytics

• Examine an existing schema - Explore the SteelWheels.xml schema in detail, including its cube structure, fact tables, dimensions, hierarchies, and measures

• Describe the key components of a schema - Identify and understand essential schema elements such as:

  • Cubes and virtual cubes

  • Fact tables and dimension tables

  • Dimensions and hierarchies

  • Levels and member properties

  • Measures and aggregators

  • Degenerate dimensions

  • Annotations for enhanced functionality

SteelWheels Schema

---

1. Start Schema Workbench:

Windows (Powershell):

cd \
cd Pentaho/design-tools/schema-workbench/
./workbench.bat

Linux:

cd
cd Pentaho/design-tools/schema-workbench/
./workbench.sh

Ensure that the Pentaho Server is up and running:

Follow the guide below to understand how a Schema is defined:

1. JDBC Connection

JDBC Connection

Before creating any schema components, you must configure a database connection by selecting Options > Connection from the menu and providing essential connection parameters including the connection name, database type (such as Hypersonic, MySQL, Oracle, or PostgreSQL), access method (Native JDBC), host name, database name, port number, and authentication credentials.

Schema Workbench supports a vast range of relational databases through JDBC drivers, allowing you to connect to most common database systems.

If you're using the Pentaho Lab then the driver has already been copied to the /lib directory.

To create a JDBC connection you will need to copy the JDBC driver for your database into the PSW install directory ...\schema-workbench\lib.

Restart the Pentaho Schema Workbench, to register the driver.

JDBC Connection - mysql:sampledata

1. To connect to the sampledata database, from the menu select Options > Connection.

JDBC Connection

1. In the Database Connection dialog, type or choose the following:

Field	Value
Connection name	hsqldb:sampledata
Connection type	Hypersonic
Host Name	localhost
Database Name	sampledata
Port Number	9001
Username	pentaho_admin
Password	password

1. Click Test.

JDBC Connection - hsqldb:sampledata

1. Click OK to dismiss the Message Box dialog and click OK to close the Database Connection dialog.

---

JDBC Explorer

1. To view the SampleData database in JDBC Explorer, from the menu select File > New > JDBC Explorer.

JDBC Explorer

1. To view the physical tables, expand PUBLIC.

2. To view the columns in the CUSTOMER_W_TER table, expand CUSTOMER_W_TER.

JDBC Explorer - sampledata

1. To close JDBC Explorer, in the top-right corner of the JDBC Explorer window, click the X icon.

2. Schema

Steel Wheels Schema

1. From the menu, select File > Open.

2. Navigate to: Workshop--Busines-Analytics\PSW\schemas\.

Steel Wheels Schema

1. Select: SteelWheels.xml.

2. Click: Open.

3. To view the schema, in the left pane, expand Sales_2003_2005.

Notice the fact table, dimensions, and measures.

1. To view the fact table, in the left pane, click Table: ORDERFACT.

2. In the left pane, expand Markets.

Dimensions

The Markets dimension consists of a hierarchy with four levels with the CUSTOMER_W_TER table.

1. To view the annotations for the Country level, in the left pane, expand Markets and click Data.Role.

Annotations

1. In the left pane, expand Customers.

Dimensions & Members

The Customers dimension consists of a hierarchy with one level (Customer) and six-member properties.

1. (Optional) View the Product and Time dimensions.

2. In the left pane, expand Order Status.

Degenerate Dimensions

The Order Status dimension does not have its own table. This is an example of a Degenerate dimension.

1. To view the Quantity measure, in the left pane, click Quantity.

Measures

Notice the aggregator, column, and formatString.

1. (Optional) View the Sales measure.

2. To close the schema, in the top-right corner of the schema window, click the X icon.