SpagoBI JPivot Engine

SpagoBIJPivotEngine is an OLAP engine based on the well-known JPivot OLAP client. The engine comes with an embedded version of Mondrian server.

1 Document template creation

To use SpagoBIJPivotEngine along with the embedded version of Mondrian you have to follow these steps:

1) Create a mondrian schema that maps your phisycal relational schema to the dimensional one used by mondrian. You can do it by hand, starting from an existing schema and than modifying it properly, or using some graphical design tool

2) Add your mondrian schema to the SpagoBI's resources directory under the Olap subfolder (path_to_resources_dir/Olap). The actual location of this folder is defined as jndi resource into your application server. In tomcat it is defined into the conf/server.xml file as follow ...

<Environment name="spagobi_resource_path" type="java.lang.String" value="../resources"/>

in this example configuration the resources directory is located directly under the root of the application server.

3) Add to the engine-config.xml file the newly created schema in the schema configuration block has shown down here

	<SCHEMA catalogUri="/Olap/FoodMart.xml" name="FoodMart" />
	<SCHEMA catalogUri="/Olap/SbiMonitor.xml" name="SpagoMonitor" />

4) Restart the server an create a new OLAP document

5) In the document detail page use the template builder to automatically create the template for the given document


In olap engine the template specifies the target schema to use, the selected cube inside the target schema and the starting MDX query to exucute on the selected cube


here it is an example of the template generated by the wizard


6) Execute the newly created olap document

You can also add analytical drivers in the MDX: this will affect only the initial view of the OLAP cube (i.e. you can use the OLAP navigator to explore all the cube and change the view). Follow the following steps:

7) Enter the document details and enter the template creation functionality. Suppose you have the following view and you'd like to let the user choose between product families (Drink / Food / Non-Consumable);


8) Click on the MDX button


9) Replace Drink with a placeholder ${name of placeholder} and put the same name in the form below to associate the placeholder to a parameter (best practice: use same name of the placeholder also as parameter's name); then click on the "+" button.


You have just defined a parameter:


10) Save the template and go back to the document details' page

11) Define a document's parameter with the URL name equal to the parameter's name, and associate it to an analytical driver.

2 Profiled data

Since SpagoBI 2.3 there is a mechanism that permits to define profiled access to OLAP analysis using SpagoBIJPivotEngine. This mechanism has been improved with SpagoBI2.6; we are goingo to describe firstly the latest way to profile access a then we'll give also informations on how to on previous SpagoBI's versions.

It is based on schema definition processing and profile attributes.

Let's look at an example based on Foodmart data warehouse: suppose that the SpagoBI users have a profile attribute "family" with values "Food", "Drink", etc… that defines visibility on data based on product family: the users with family = Food should see data relevant to Food product family only, those ones with family = Drink should see data relevant to Drink product family only, etc… The following xml code represents the non-profiled Product dimension definition (inside FoodMart.xml schema definition file):

<Dimension name="Product">
  <Hierarchy hasAll="true" allMemberName="All Products" primaryKey="product_id" primaryKeyTable="product">
    <Join leftKey="product_class_id" rightKey="product_class_id">
      <Table name="product"/>
      <Table name="product_class"/>
    <Level name="Product Family" table="product_class" column="product_family" uniqueMembers="true"/>
    <Level name="Product Department" table="product_class" column="product_department" uniqueMembers="false"/>
    <Level name="Product Category" table="product_class" column="product_category" uniqueMembers="false"/>
    <Level name="Product Subcategory" table="product_class" column="product_subcategory" uniqueMembers="false"/>
    <Level name="Brand Name" table="product" column="brand_name" uniqueMembers="false"/>
    <Level name="Product Name" table="product" column="product_name" uniqueMembers="true"/>

while the following represents the filtered dimension definition based on product family:

<Dimension name="Product">
  <Hierarchy hasAll="true" allMemberName="All Products" primaryKey="product_id">
    <View alias="Product">
      <SQL dialect="generic">
              p.product_id as product_id,
              p.product_name as product_name,
              p.brand_name as brand_name,
              pc.product_subcategory as product_subcategory,
              pc.product_category as product_category,
              pc.product_department as product_department,
              pc.product_family as product_family
              product as p, product_class as pc
              p.product_class_id = pc.product_class_id
              and pc.product_family = '${family}'
    <Level name="Product Department" column="product_department" uniqueMembers="false"/>
    <Level name="Product Category" column="product_category" uniqueMembers="false"/>
    <Level name="Product Subcategory" column="product_subcategory" uniqueMembers="false"/>
    <Level name="Brand Name" column="brand_name" uniqueMembers="false"/>
    <Level name="Product Name" column="product_name" uniqueMembers="true"/>

Note the pc.product_family = '${family}' condition in the where clause of the query. You have to specify the profile attribute to be used for filtering in the OLAP document template:

   <ATTRIBUTE name="family" />
   <ATTRIBUTE name="department" />

The value of the "family" user profile attribute will replace the ${family} placeholder in the dimension definition.

You can filter more than one dimensions/cubes and use more profile attributes, remembering to define them in template.

The engine substitutes into the query the exact value of the attribute; in case of a multi value attribute to insert in an SQL IN clause you will have to give the attribute a value like 'value1', 'value2', and insert into the query a condition like and pc.product_family IN (${family})

For SpagoBi versions previous to 2.6 you can define only one attribute in the template, and it's value will replace all the $P{ anchors in the query, where filter is a key word.

Then for example into the template you'lldefine

<ATTRIBUTE name="family" />
while in the query
and pc.product_family = '${filter}'

3 Cross navigation configuration

Since SpagoBI 2.3 you can define a cross navigation link between a SpagoBIJPivotEngine document and another SpagoBI document or a customized view; the target document can be of any type (report, map, QbE query, etc....).

The definition of the cross navigation link comes with the document template. Let's look at an example:

    <TARGET documentLabel="QBE_FOODMART_SALES" customizedView="Sales by product family" target="tab" title="New title" >
      <TITLE><![CDATA[Go to Sales by product family]]></TITLE>
      <DESCRIPTION><![CDATA[Go to Sales by product family]]></DESCRIPTION>
        <PARAMETER name="family" scope="relative" dimension="Product" hierarchy="[Product]" level="[Product].[Product Family]" />

  • New Title: This parameter is NOT mandatory. Replace it with the TITLE you want to give to the Analytical Document on which you want to go on cross navigation. This title will be placed either in a new tab or in the breadcrumbs. If blank the DEFAULT value is the document's NAME.
  • target: This parameter is NOT mandatory. Replace it with one of the following values:
self: The new document will be opened in the same page and the breadcrumbs will be updated;
tab: The new document will be opened in a new tab when execution is done inside the Documents Browser. In case it is not executed in the Documents Browser it will be opened in the same page and the breadcrumbs will be updated;
update: If the new Document is the same as the source document, no breadcrumbs will be shown and the document will be reexecuted in the same page with the new parameters passed by the cross navigation.

In this case the target of the link is the customized view (i.e. a previously saved query) with name Sales by product family of document with label QBE_FOODMART_SALES (a QbE document). With this template, the cross navigation will be enabled: you will see the Cross Navigation button on the toolbar:


and when you click on it you will see a green little arrow on each cell:


If you click on an arrow, a context menu will show you the configured title of the link; when you click on the context menu, the target document will be executed. Depending on the selected cell, the analytical driver family of the target document will have a different value: it will be the name of the context member (of the selected cell) of the Product dimension, [Product] hierarchy, at [Product].[Product Family] level. Look at the following table for some examples:

Context member on Product dimension"family" analytical driver value
[Product].[All Products][no value: it will be prompted to the user]
[Product].[All Products].[Food]Food
[Product].[All Products].[Drink]Drink
[Product].[All Products].[Non-Consumable]Non-Consumable
[Product].[All Products].[Food].[Snacks]Food
[Product].[All Products].[Food].[Snacks].[Candy]Food
In general you can define more than one target (all available targets' titles will be displayed in the context menu) and more than one parameter for each target. A target is a SpagoBI document of any type or a customized view (if you want a main document to be the target, remove the "customizedView" attribute of the TARGET tag). Parameters can be relative (as seen in the example) or absolute: in the latter case the syntax is:

<PARAMETER name="year" scope="absolute" value="2005" />

4 How to hide buttons (from SpagoBI 2.4)

From version 2.4 of SpagoBI it is possible to hide one or more buttons of the Olap analysis. The right sintax to use in the Olap template is the following one:

    <BUTTON_CUBE visible="false" />
    <BUTTON_MDX visible="false" />
    <BUTTON_ORDER visible="false" />
    <BUTTON_FATHER_MEMBERS visible="false" />
    <BUTTON_HIDE_SPANS visible="false" />
    <BUTTON_SHOW_PROPERTIES visible="false" />
    <BUTTON_HIDE_EMPTY visible="false" />
    <BUTTON_SHIFT_AXIS visible="false" />
    <BUTTON_DRILL_MEMBER visible="false" />
    <BUTTON_DRILL_POSITION visible="false" />
    <BUTTON_DRILL_REPLACE visible="false" /> 
    <BUTTON_DRILL_THROUGH visible="false" />
    <BUTTON_SHOW_CHART visible="false" />
    <BUTTON_CONFIGURE_CHART visible="false" />
    <BUTTON_CONFIGURE_PRINT visible="false" />
    <BUTTON_FLUSH_CACHE visible="false" />
    <BUTTON_SAVE visible="false" />

Created by angioia on 2009/04/27 10:20
Last modified by Davide Zerbetto on 2014/07/09 17:46

This wiki is licensed under a Creative Commons 2.0 license
XWiki Enterprise 2.7.33694 - Documentation