In the first part of the article, I introduced the steps to setup & config the Mondrian OLAP server deploying on Tomcat (application) server, to run against the test database on MySQL (database) server.
By clicking the URL http://localhost:8080/mondrian, you will see the following Mondrian examples page:
By clicking each of the examples, manipulating the OLAP control panel provided by JPivot interface, you can navigate through the OLAP dimensions, drill through, and control the display layout.
JPivot pivot table
This example uses JPivot's interface (JSP tag - mondrianQuery) to send query to Mondrian's API (Mondrian query executor) to access Sales cube (whose schema information is defined in FoodMart.xml file).
select
{[Measures].[Unit Sales], [Measures].[Store Cost],
[Measures].[Store Sales]} on columns,
{([Promotion Media].[All Media],
[Product].[All Products])} ON rows
from
Sales
JPivot pivot table by XMLA
This example uses JPivot's interface (JSP tag - xmlaQuery) to access Sales cube via XML for Analysis interface (Mondrian as XMLA provider).
This example use the same MDX query as above.
JPivot with 4 hierarchies
This example uses JPivot's interface (JSP tag - mondrianQuery) to send query to Mondrian's API (Mondrian query executor) to access Sales cube, but by 4 dimensions (Gender, Martial Status, Customers, Product), the MDX query as:
select
{[Measures].[Unit Sales],
[Measures].[Store Cost],
[Measures].[Store Sales]} on columns,
{([Gender].[All Gender],
[Marital Status].[All Marital Status],
[Customers].[All Customers],
[Product].[All Products] ) } on rows
from
Sales
where
([Time].[1997])
JPivot with role 'California Manager' set
This example also uses JPivot's interface (JSP tag - mondrianQuery) to send query to Mondrian's API (Mondrian query executor) to access Sales cube with role limitation setting to "California manager". So, it only show the data that's available to "California manager".
select
{[Measures].[Unit Sales],
[Measures].[Store Cost],
[Measures].[Store Sales]} on columns,
{([Marital Status].[All Marital Status],
[Customers],
[Product].[All Products])} on rows
from
Sales
where
([Time].[1997])
JPivot with arrows
This example also uses JPivot's interface (JSP tag - mondrianQuery) to send query to Mondrian's API (Mondrian query executor) to access Sales cube with the following MDX query:
with member
[Measures].[ROI] as
'(([Measures].[Store Sales]-[Measures].[Store Cost])/ [Measures].[Store Cost])',
format_string =
IIf((((([Measures].[Store Sales]-[Measures].[Store Cost])
/[Measures].[Store Cost]) * 100.0) > 150.0),
"|#.00%|arrow='up'",
IIf((((([Measures].[Store Sales]-[Measures].[Store Cost])
/[Measures].[Store Cost]) * 100.0) <>
"|#.00%|arrow='down'",
"|#.00%|arrow='none'"))
select
{[Measures].[ROI],
[Measures].[Store Cost],
[Measures].[Store Sales]} ON columns,
{[Product].[All Products]} ON rows
from
[Sales]
where
[Time].[1997]
This example introduce the conditional style to ROI measure display with image.
JPivot with colors
This example also uses JPivot's interface (JSP tag - mondrianQuery) to send query to Mondrian's API (Mondrian query executor) to access Sales cube with the following MDX query:
with member
[Measures].[ROI] as
'(([Measures].[Store Sales]-[Measures].[Store Cost])/
[Measures].[Store Cost])',
format_string =
IIf((((([Measures].[Store Sales]-[Measures].[Store Cost])/
[Measures].[Store Cost]) * 100.0) > 150.0),
"|#.00%|style='green'",
IIf((((([Measures].[Store Sales]-[Measures].[Store Cost])/
[Measures].[Store Cost]) * 100.0) <>
"|#.00%|style='red'", "#.00%"))
select
{[Measures].[ROI],
[Measures].[Store Cost],
[Measures].[Store Sales]} ON columns,
{[Product].[All Products]} ON rows
from
[Sales]
where
[Time].[1997]
This example introduce the conditional style to ROI measure display with color.
Various queries formatted using the Mondrian tag-library
This example shows the use of Mondrian MDX tag-library to transform & format the data layout.
Basic interface for ad hoc queries
This example provides a way to execute the ad-hoc MDX query against Mondrian OLAP server. MDXQueryServlet receives MDX queries, executes them, and formats the results in an HTML table.
XML for Analysis tester
This example provides another way to execute ad-hoc MDX query against Mondrian OLAP server via XML for Analysis interface. DefaultXmlaServlet receives MDX queries, executes them, and formats the results in an HTML table.
