Using Pentaho Schema Workbench (PSW)

Schema -:
A schema defines a multi-dimensional database. It contains a logical model, consisting of cubes, hierarchies, and members, and a mapping of this model onto a physical model.

The logical model consists of the constructs used to write queries in MDX language: cubes, dimensions, hierarchies, levels, and members.
The physical model is the source of the data which is presented through the logical model. It is typically a star schema, which is a set of tables in a relational database; later, we shall see examples of other kinds of mappings.

Schema files :
Mondrian schemas are represented in an XML file. An example schema, containing almost all of the constructs we discuss here, is supplied as demo/FoodMart.xml in the Mondrian distribution. The dataset to populate this schema is also in the distribution.

The structure of the XML document is as follows:
<schema>
<cubes>
<AggName>
Agg Element
</AggName>
<Dimension>
<Hirerchy>
</hirerchy>
</Dimension>
</cubes>
</schema>

You can see example:
Step-1 Open your Schema Workbench
Step-2 Go to File ->New ->Schema.
Step-3 Click On Schema and set the name of schema like foodmart
Step-4 Right Click On schema and select Add cube
Step-5 click on add cube and set the name of add cube like sales or whatever        you want.
Step-6 Right Click On cube and select a add table  and set the schema like public and name like select database table  means  sales_fact_1997
Step-7 again right click on cube and add  dimension and click dimension
<Dimension type=”StandardDimension” visible=”true” `        foreignKey=”store_id” name=”Dimension Test”>
double click on dimension you can see the hierarchy and set the hierarchy <Hierarchy name=” Hierarchy Test” visible=”true” hasAll=”true”>
Step-8  Inside hierarchy right click on hierarchy and create add table and click table and then provide name and schema name like :<Table name=”store” schema=”public”>
Step-9 In hierarchy you need to set a Level i.e right click on hierarchy and create add level. <Level
name=”Level Test” visible=”true” table=”store” column=”store_country” nameColumn=”store_country” uniqueMembers=”false”>
Step-10 Inside Cube right click on cube and create Add Measure and click Measure and <Measure name=”Measure Creation” column=”customer_id” datatype=”Integer” aggregator=”count” visible=”true”>
Step-11 According to requirement You can create a lots of cube ,procedure is same
Step-12 Now publish the server what you create a Schema ———-
Step-13 click option and click connection set the
connection name =foodmart
select the connection type what you want like select postgresql
Host Name :192.168.2.9
Database Name: foodmart
Port Number:5432
Username :postgres
Password :postgres
Access like : Native (JDBC)
Click Test Button  Connection is done if not come any problem then click ok…

Step-14 Now To publish In server local server type in browser: localhost:7080/pentaho and use username and password what they are provided thats it.
Step-15 Go to Schema Inside File ->click on publish Button set server  url:http://localhost:7080/pentaho/
user:Admin
password:password
Pentaho or JNDI Data source: foodmart click next to public button
Message will shown on screen connection successfully.
Step-16 Now go to type in browser :localhost:7080/pentaho    click file->New-    >JPivot view. Then select schema and cube what you were creating. And click and check all icon you can see all the output type….
Step-17 this is process to create a schema and deployed in local server…….
This code will execute properly——–Schema1.xml
<Schema name=”foodmart”>
<Dimension type=”StandardDimension” visible=”true” highCardinality=”false” name=”Store”>
<Hierarchy visible=”true” hasAll=”true” primaryKey=”store_id”>
<Table name=”store” schema=”public”>
</Table>
<Level name=”Store Country” visible=”true” column=”store_country” type=”String” uniqueMembers=”false” levelType=”Regular” hideMemberIf=”Never”>
</Level>
<Level name=”Store State” visible=”true” column=”store_state” type=”String” uniqueMembers=”false” levelType=”Regular” hideMemberIf=”Never”>
</Level>
<Level name=”Store City” visible=”true” column=”store_city” type=”String” uniqueMembers=”false” levelType=”Regular” hideMemberIf=”Never”>
</Level>
<Level name=”Store Name” visible=”true” column=”store_name” type=”String” uniqueMembers=”false” levelType=”Regular” hideMemberIf=”Never”>
<Property name=”Store Type” column=”store_type” type=”String”>
</Property>
<Property name=”Store Manager” column=”store_manager” type=”String”>
</Property>
<Property name=”Store Sqft” column=”store_sqft” type=”Numeric”>
</Property>
</Level>
</Hierarchy>
</Dimension>
<Cube name=”Sales” visible=”true” cache=”true” enabled=”true”>
<Table name=”sales_fact_1997″ schema=”public”>
</Table>
<DimensionUsage source=”Store” name=”Store” visible=”true” foreignKey=”store_id” highCardinality=”false”>
</DimensionUsage>
<Measure name=”Unit Sales” column=”unit_sales” formatString=”Standard” aggregator=”sum” visible=”true”>
</Measure>
</Cube>
<Cube name=”Warehouse” visible=”true” cache=”true” enabled=”true”>
<Table name=”inventory_fact_1997″ schema=”public”>
</Table>
<DimensionUsage source=”Store” name=”Store” visible=”true” foreignKey=”store_id” highCardinality=”false”>
</DimensionUsage>
<Measure name=”Store Invoice” column=”store_invoice” aggregator=”sum” visible=”true”>
</Measure>
</Cube>
some thing about schema?

=>A schema defines a multiple dimensional database.it consist a logical model,consisting of cubes, hierarchies and member,and mapping on this model onto a physical model

For any questions on Pentaho Schema Workbench, please get in touch with us @ Helical IT Solutions

Best Open Source Business Intelligence Software Helical Insight is Here

A Business Intelligence Framework

Free
Download