By following these processes, you define a model that describes your data in terms of dimensional analysis.

Example Using FoodMart Data

This example is based on the FoodMart sample data that can be installed with JasperReports Server. It illustrates the types of questions that can be answered interactively using Jaspersoft OLAP. Our goal in this section is to answer this question: what type of alcoholic beverage generates the most revenue?

Each step in this example shows the corresponding MDX query so that you can explore the data on your own.

To analyze the FoodMart view:

To drill-down, click next to the ALL PRODUCTS member; the next level of the PRODUCT hierarchy, Product Family, appears.

3.

Drill-down again, this time by zooming on the Drink product family. Use

Now we can see that, overall, wine makes up the majority of the dollar amount for this category.

b.

Deselect the

There are usually several ways to view the same data. The previous steps showed some of these ways. We could also make the STORE dimension a column; this might be a good approach when you have many different products to compare. Doing so results in a two-by-five grid, with the single-dimension layout becoming a two-dimension layout.

For another example, let’s make the table easier to understand by switching the rows and columns. This is called “pivoting,” that is, we are rotating this section of the cube. Pivoting gives us a five-by-two grid of data cells, which is often easier to read online and is better suited to printing on U.S. Letter-sized paper. While the previous example compares product subcategories by store, swapping the columns and rows lets us compare each store’s results by product.

We now see that the San Francisco store sold a fraction of the alcoholic beverages that other California stores sold, and that it sold no beer at all.

Leveraging the Ad Hoc Editor

The model we defined in Designing the Model and implemented in Applying the Model can also be leveraged by the Ad Hoc Editor. Select the sample FoodMart Mondrian connection as your data source when you open the editor; then slice, dice, and drill in the same manner as we’ve done in Jaspersoft OLAP. The resulting Ad Hoc view can be saved as a report which in turn can be scheduled for email distribution and incorporated into dashboards. FoodMart Sales data in an Ad Hoc View shows an Ad Hoc view of the same data as shown in the OLAP view in Pivoting the View.

In the course of this example, we have unearthed questions we hadn’t thought to ask previously: is the lack of beer sales part of a trend in this store’s overall beverage sales? We can answer other questions, such as whether the store’s overall sales lag across all products, or whether another product’s sales make up for the lack of beer sales.

Alternately, we could look at the best performing stores in each category or product to try to understand the secrets to their success. Ranking and sorting can be very helpful in this type of analysis when comparing many values. Your MDX statements can include any imaginable statistical functions. For example, you could include arbitrarily-complex expressions to show the top or bottom percent of any measure.

We can show analysis data as a graphical chart, just as might be defined in a report. This chart shows the same data as the sample above, but in a vertical 3D bar chart:

The chart focuses on aggregated data, that is, the totals for all the sales at any dimensional coordinates. This is not to imply that analysis supplants transactional reporting. Analysis provides transactional reporting on demand by showing your data in different contexts and different levels of roll-up.

Examining the basic row data is still very helpful in furthering your understanding of a particular area. For example, in the table, use to drill-through to the wine sales for San Francisco to show detailed transactional information. We’re looking at individual rows of data to understand this one store’s wine sales in December of 2012 (table is truncated).