Jump to content
JasperReports Library 7.0 is now available ×
  • This documentation is an older version of Jaspersoft OLAP Ultimate Guide. View the latest documentation.

    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 ja-expand-position-nav-table_14x14.jpg.bff285bb048cd6f16689719b657c1957.jpg 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 ja-pro-zoom-on-drill.png.4c45e52dd575775d8887e17280d9fd97.png
    Select ja-pro-change-data-cube.png.f792e424148fee42c5633c60868a7b4d.png, 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 ja-pro-zoom-on-drill.png.71bff1ed5079b1383fc05207ce3fbdb8.png


    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 ja-pro-zoom-on-drill.png.65fad5a7d1e6a94cac64f25a2b240a35.png 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

    User Feedback

    Recommended Comments

    There are no comments to display.

    This is now closed for further comments

  • Create New...