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:
| 1. | We’ll begin by looking at a top-level summary of sales for all products. An aggregated view like this lets us dive into the data in any direction of interest. In this example, we look at data for the month of December. |
| |
| Top-level Summary for December |
| select {[Measures].[store Sales]} ON COLUMNS, Hierarchize({[Product].[All Products]}) ON ROWS from [sales] |
| 2. | Drill-down is the most basic function in analysis. |
To drill-down, click 
next to the ALL PRODUCTS member; the next level of the PRODUCT hierarchy, Product Family, appears.
| |
| Expand All Products |
| select {[Measures].[store Sales]} ON COLUMNS, Hierarchize(Union({[Product].[All Products]}, [Product].[All Products].Children)) ON ROWS from [sales] where [Time].[2012].[Q4].[12] |
| 3. | Drill-down again, this time by zooming on the Drink product family. Use  |
, make STORE a row, and select All Stores> USA > CA (for instructions on adding a dimension, refer to Cube Configuration| |
| Zoom on the Drink Product Family |
| select {[Measures].[store Sales]} ON COLUMNS, [Product].[Drink].Children ON ROWS from [sales] where [Time].[2012].[Q4].[12] |
| 4. | By zooming, we could drill-down to the lowest level of data in the cube. Let’s instead focus our attention by zooming on the ALCOHOLIC BEVERAGES product department, then the BEER AND WINE product category. |
| |
| Expand Beer and Wine Subcategories |
| select {[Measures].[store Sales]} ON COLUMNS, [Product].[Drink].[Alcoholic Beverages].[beer and Wine].Children ON ROWS from [sales] where [Time].[2012].[Q4].[12] |
Now we can see that, overall, wine makes up the majority of the dollar amount for this category.
| 5. | Let’s see if that finding holds true across different stores. To do so, we add another dimension, which creates a crossjoin. Analytic views make creating a crossjoin easy. |
| a. | ). Adding the STORE dimension to rows allows you to see the breakdown within the store as well as across different stores with respect to certain products (if we wanted to compare different products across stores, we would make PRODUCTS a row instead of STORES). |
| |
| Adding the All Stores Dimension |
| select {[Measures].[store Sales]} ON COLUMNS, Crossjoin([Product].[Drink].[Alcoholic Beverages].[beer and Wine].Children, {[store].[All Stores]}) ON ROWS from [sales] where [Time].[2012].[Q4].[12] |
| b. | Deselect the  |
| |
| Stores in California |
| select {[Measures].[store Sales]} ON COLUMNS, Crossjoin([Product].[Drink].[Alcoholic Beverages].[beer and Wine].Children, [store].[uSA].[CA].Children) ON ROWS from [sales] where [Time].[2012].[Q4].[12] |
With Jaspersoft OLAP, the data can be “sliced and diced” in many ways, allowing you to focus the viewer’s attention on the most relevant data. 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.
| |
| Stores as Columns Instead of Rows |
| select Crossjoin({[Measures].[store Sales]}, [store].[uSA].[CA].Children) ON COLUMNS, [Product].[Drink].[Alcoholic Beverages].[beer and Wine].Children ON ROWS from [sales] where [Time].[2012].[Q4].[12] |
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.
| |
| Pivoting the View |
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”.
| |
| FoodMart Sales data in an Ad Hoc 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.
| select [Product].[All Products].[Drink].[Alcoholic Beverages].[beer and Wine].Children ON COLUMNS, Crossjoin([store].[All Stores].[uSA].[CA].Children, {[Measures].[store Sales]}) ON ROWS from [sales] where [Time].[2012].[Q4].[12] |
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:
| |
| Chart of Beer and Wine Sales in California |
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).
| |
| Drill-through Table of San Francisco Beer And Wine Sales |
Recommended Comments
There are no comments to display.