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:
Agg Element

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 :
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/
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”>
<Level name=”Store Country” visible=”true” column=”store_country” type=”String” uniqueMembers=”false” levelType=”Regular” hideMemberIf=”Never”>
<Level name=”Store State” visible=”true” column=”store_state” type=”String” uniqueMembers=”false” levelType=”Regular” hideMemberIf=”Never”>
<Level name=”Store City” visible=”true” column=”store_city” type=”String” uniqueMembers=”false” levelType=”Regular” hideMemberIf=”Never”>
<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 name=”Store Manager” column=”store_manager” type=”String”>
<Property name=”Store Sqft” column=”store_sqft” type=”Numeric”>
<Cube name=”Sales” visible=”true” cache=”true” enabled=”true”>
<Table name=”sales_fact_1997″ schema=”public”>
<DimensionUsage source=”Store” name=”Store” visible=”true” foreignKey=”store_id” highCardinality=”false”>
<Measure name=”Unit Sales” column=”unit_sales” formatString=”Standard” aggregator=”sum” visible=”true”>
<Cube name=”Warehouse” visible=”true” cache=”true” enabled=”true”>
<Table name=”inventory_fact_1997″ schema=”public”>
<DimensionUsage source=”Store” name=”Store” visible=”true” foreignKey=”store_id” highCardinality=”false”>
<Measure name=”Store Invoice” column=”store_invoice” aggregator=”sum” visible=”true”>
some thing about schema?
=>A schema defines a multiple dimensional 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

Differences between Reporting and Analysis – Concepts

Differences between Reporting and Analysis – Concepts


       1.The process of orga­niz­ing data into infor­ma­tional sum­maries in order to mon­i­tor how dif­fer­ent areas of a busi­ness are per­form­ing.


        1.The process of explor­ing data and reports in order to extract mean­ing­ful insights, which can be used to bet­ter under­stand and improve busi­ness performance.

       2.Report­ing trans­lates raw data into infor­ma­tion

        2.Analy­sis trans­forms data and infor­ma­tion into insights.

       3.Good report­ing should raise ques­tions about the busi­ness from its end users

        3.The goal of analy­sis is to answer ques­tions by inter­pret­ing the data at a deeper level and pro­vid­ing action­able rec­om­men­da­tions

      4.Report­ing shows you what is hap­pen­ing

      4.Analy­sis focuses on explain­ing why it is hap­pen­ing and what you can do about it.


       5.Reporting: Build­ing, con­fig­ur­ing, con­sol­i­dat­ing, orga­niz­ing, for­mat­ting, and sum­ma­riz­ing.

        5.Analy­sis:  focuses on dif­fer­ent tasks such as ques­tion­ing, exam­in­ing, inter­pret­ing, com­par­ing, and con­firm­ing.


       6.Report­ing fol­lows a push approach

         6.Analy­sis fol­lows a pull approach

       7.Reports are pushed to users who are then expected to extract mean­ing­ful insights and take   appro­pri­ate actions for them­selves (i.e.,     self-serve).


Dashboard: These custom-made reports com­bine dif­fer­ent KPIs and reports to pro­vide a com­pre­hen­sive, high-level view of busi­ness per­for­mance for spe­cific audi­ences. Dash­boards may include data from var­i­ous data sources and are also usu­ally fairly static.

7.Par­tic­u­lar data is pulled by an ana­lyst in order to answer spe­cific busi­ness ques­tions.

Ad hoc responses
Analy­sis pre­sen­ta­tions

Approach of Reporting and Analysis