Version 1.2 (1/10/2022)

Introduction

AMS software provides a powerful tool for exploring the data in your AMS databases. The tool is the AMS Cube viewer. The Cube viewer lets you explore the data in an AMS Cube. An AMS Cube is a database produced by your AMS server and delivered to the Inbox of your workstation at your request. You make this request via a request screen, much like you request a report in AMS. Once the Cube is produced and delivered to your Inbox, you use the Cube viewer to arrange, sort, filter, view, report, and export your data to Excel.

This document explains how to request a Cube from your AMS server and use the Cube Viewer.

Terminology

Before getting into the details of requesting and using Cubes, we need to explain some of the terms used when talking about Cubes.

Your AMS Server – This is the server you connect to when using AMS. Usually, this server is at your place of business. Some AMS customers use the internet to connect to and use one of the servers in the AMS offices. Your AMS server, wherever it is, contains your databases and the AMS software that works with that data.

AMS Client – This is the software on your workstation that lets you interact with the AMS software on the server, which, in turn, works with the data in your databases.

Database – one or more files containing your data.

Cube – a portable database in a single data file produced by the AMS server and delivered to the AMS Client on your workstation. Like an AMS report, this database can be saved on the files tab in the AMS Client or saved outside of AMS on a fileserver. Also, like AMS reports, Cubes can be emailed to other AMS users. The Cube database consists of Dimensions and Measures.

Dimension – a code field and its related values and descriptions is called a Dimension. A Dimension can represent Areas or places such as states, regions, divisions, cities, and customers. A Dimension can also mean things or items such as Brands, Parts, and Bottle sizes. Dimensions can also be time: Years, Months, Days, and Dates.

Group Dimension – also called a hierarchical dimension - is a single Dimension containing “regular” but “nested” dimensions in a pre-arranged order. An example would be a Group called “Areas,” which contains Regions, States, and Cities.

Measure – a quantitative value telling how much of something? For example, how many Cases? How many dollars? Depending on the Cube, the dollars could be further defined as budget dollars, actual dollars, and variance dollars. The cases could be further defined as on-hand, on-order, on-future, and available. A Measure can also be qualified by time for example Cases sold this year or Cases sold last year.

Finding Cube request screens in AMS

Your AMS server contains data that serves many departments within your business. It would not be possible or practical for a single Cube to contain all the data for your company. Such a cube would be too large and too awkward to use efficiently. Therefore, each Cube contains only the data that you request from a particular program on your AMS server. You tell that program what you want in your Cube by filling out a request screen. Your first task is to locate the correct request screen for the type of data you want in your Cube. Because the AMS software system has over 1,000 screens this can be a bit of a challenge. The quickest way to find Cube request screens is to use the Search feature of the AMS Client menu.

Type the word “cube” into the menu search field as shown below.

The system highlights the 16 Cube request screens currently available as shown below. Scroll the menu tree to locate the Cubes that are of interest to you. You can then use the right-click menu to add those menu choices to your favorites tab or the AMS Client’s toolbar.

	Menu	Description
1	BIISM.GCC	Generate Composition cube
2	BIBIM.GAC	Generate barrel attribute cube
3	INISM.GIC	Generate inventory cube
4	INITM.ITC	Generate Inventory Transaction Cube
5	AL.CUB	Generate Allocations Cube
6	OERPM.GO3	Generate Order Cube
7	PSRPM.GOC	Generate orders cube
8	SA.GS3	Generate sales cube: crnt vs budget/prior CS/$/cost
9	SA.SDC	Sales Detail Cube
10	DDRPM.SDC	Generate Sales depletions & budget cube
11	RD.CUB	Generate Retail Depletions Cube
12	PA.GEC	Generate Expense Analysis cube
13	PA.GPC	Generate Profitability cube
14	PA.FSC	Field Spending Cube
15	PO.GPC	Generate purchase order detail cube
16	GLJMM.GGC	Generate G/L activity cube

Understanding Cube Request Screens

We will be using the Wholesale Orders Cube in our examples. Access the request screen for this Cube on menu OERPM, option GO3.

Kommand Line

You can bring the request screen up quickly by typing the command OERPM.GO3 in the small command line in the lower-left corner of the AMS Client.

By the way, you can move your cursor to the command line quickly and without using your mouse by holding down the Control key and pressing K

(K for “Kommand”)

The Wholesales Orders screen appears below. Much of the request screen looks like an AMS report request screen. What is unique, however, is the section outlined with the red border, titled Cube Dimensions.

A word before falling off the cliff .... Limit the quantity of data retrieved for your Cube

Before we discuss choosing Cube Dimensions, we should discuss how to limit the quantity of data retrieved for your Cube. Let’s remember that your AMS server may be holding lots of data, far more than you could put into a single report or Cube. If you request too much data, the server may take several minutes to make your Cube, and the Cube could be several megabytes in size. You must provide the software guidance so that it can sift through your data and retrieve what you are interested in. When a Cube is based on history (also called transactions), you will want to tell the software what window of time is interesting to you. Because orders are a form of history, in the example pictured above we used a range of order dates to limit the volume of data retrieved. We specified order dates between 09-01-19 and 08-30-21. If your business processes lots of orders you might want to start with a more limited range of dates. In addition to limiting data retrieval by date, you can also use other fields. The most powerful is the IRW SELECT field. In that field, you can type free-form “equations” to limit data retrieval. Examples of these equations are: STATE = CA, NV, AZ, NM BRAND = DI, DA REGION = WEST PART = NW* STATE <> CA

This document will not go further into selecting data because the techniques used are the same as those used when requesting AMS reports, and we presume you already know how to request reports.

It is important to note that the Cube viewer itself can also filter or hide data, and sometimes, it is useful to request a bit more data and use the filters in the Cube viewer to hide that data later. The downside of this approach is the extra time the server will take to produce the larger Cube. Also, sometimes the request screen provides a more concise way to limit the scope of the data than the Cube viewer does.

When you request an AMS report, often you are asked how you want your data sorted (sequenced), grouped, and totaled. You will not see anything about sorting or totaling on a Cube request screen. That is because those decisions are made in the Cube viewer. You may group, total, and sort using Dimensions.

Cube Dimensions

A Dimension is a code field and its related values and descriptions. A Dimension can represent Areas or places such as states, regions, divisions, cities, and customers. A Dimension can also represent things or items such as Brands, Parts, and Bottle sizes. Dimensions can also be time, expressed in years, months, days, and dates.

When choosing Dimensions for your Cube keep these points in mind:

Requesting more Dimensions increases your flexibility in viewing your data
But requesting more Dimensions causes the server to take longer to make your Cube and will result in a larger, slower Cube.
Some Dimensions have few values, and some have many. For example, you may only have 5 Regions, but you may have thousands of customers. The more values a Dimension has, the larger your Cube will be and the slower it will operate. So requesting the customer Dimension when you really are only interested in totals by region could waste your time.
Grouped Dimensions are powerful but perform poorly when composed of one or more Dimensions with lots of values. For example, a Group Dimension containing thousands of customers or thousands of individual orders may produce a nearly un-usable Cube. In extreme cases, the server will fail to produce the Cube, or the Cube viewer will malfunction when asked to display such a bulky Grouped Dimension.
Therefore, request the Dimensions you need and leave out those you do not.
The request screen may offer Dimensions that your company does not use. For example, you may be offered “region” as a Dimension, even though your company does not use regions. In that case, the region Dimension will not be useful to you if you request it.
When experimenting with building and using a Cube, you may want to limit the volume of data requested while learning about the Dimensions that are available. For example, use a restrictive date range or limit your request to a single, small state or sales region. Once you become sure of the Dimensions, you can increase the volume of data. This approach will save you time.

Each Cube request screen offers Ungrouped Dimensions that are unique to it. Dimension numbers may not refer to the same dimension on two different request screens. Dimensions are specified with a two-digit number. You can use the handy lookup tool to locate the Dimension you want. Activate the lookup by clicking the glyph to the right of the field with your mouse...

...or use the F12 key or the Alt- DownArrow keys. Make your selection by typing and pressing enter or by highlighting a dimension with your mouse and selecting it.

Search and Filter the Lookup Dialog

In the Lookup Dialog, instead of typing an unfamiliar two-digit code, try pressing the Tab key to move the cursor to the second search field to search for dimensions by their description like “state.” You can also use the DownArrow key to move into the grid to search for and highlight the Dimension you want, and then press enter to select it.

Note that the fields used to hold selected dimensions is of no consequence. The system will process each dimension number, regardless of where they appear on the screen.

Notice that there are three areas on the screen where you can select Dimensions. They are labeled Group1, Group2, and Ungrouped. Let’s discuss Grouped Dimensions.

Group Dimension

A Group Dimension also called a hieratical dimension, is a single Dimension containing nested “regular” dimensions in a pre-arranged order. An example would be a Group called “Areas,” which contains Regions, States, and Cities.

Group1 and Group2 are interchangeable and simply facilitate the request of up to two Grouped Dimensions in the same Cube. The order you request Dimensions within a group matter: you should progress from general to specific. For example, if your “regions” consist of several “states,” ask for “regions,” then “states,” not “states,” then “regions.” After “states,” you could then, if you wish, request city and customer. Recall that Group Dimensions perform poorly when a large number of data values are requested, so if you have thousands of customers, you might not want to include the customer Dimension in a Dimension group.

When you learn to operate the Cube viewer, you will see that Ungrouped Dimensions can, in fact, be grouped after all. By this, we mean you can view customers within cities and cities within states and states within regions. In fact, you can view customers within regions and skip the cities and states at your whim.

Since you can group ungrouped dimensions when viewing a cube, why bother with Grouped dimensions? Grouped dimensions have at least two advantages:

The Dimensions are pre-grouped and are already arranged in a logical order
Grouped Dimensions take up less space on the screen

When requesting un-grouped Dimensions, the order in which you request the Dimension does not matter much. They will, however, appear on the Dimension bar in the order requested on the screen, so as you get better at requesting Cubes, you may want to pay some attention to the requested order, so related Dimensions are together.

Although the previous images show the same dimensions in both the grouped and ungrouped areas of the request screen, doing so is not required. For example, dimensions 04 and 07, and 17 and 18. Doing so gives you the flexibility to use these dimensions as a named group dimension (i.e., AREAS or ITEMS), or individually as ungrouped dimensions (i.e., 04 as Bill To State, 07 as Customer)

Submitting your request to the server

When you think you have the screen filled out correctly, press the enter key. The server will check your request and may point out problems by positioning the cursor on the field in error and issuing an error message on the message line at the bottom of the screen. When the screen is error-free, press the F1 key to submit your request and close the screen or, press the F2 key to submit your request and leave the screen open. In either case, the server will start collecting data for your Cube and will deliver it to your Inbox when it is ready. Cubes take much longer to build than reports. You may find yourself waiting what seems like minutes for very large cubes to be delivered. Once the Cube has been delivered to your Inbox, the AMS client will pass the Cube to the Cube Viewer, which will pop up on your screen. (This presumes you used the word “VIEW” in the spool name field on the request screen. You have other options, of course. We will not cover them here since they are the same options you may use for reports.)

The CUBE Viewer - Default

The system shows the results of your request in the AMS CUBE viewer. Its default display appears as described below.

(1) The file path and filename of the CUBE using the file extension .YAC. In this example, C:\Ams\Ams Client\Users\Ernie\THOR2012\DEMOCART\InBox\D538965–A.YAC.

When you close the Cube, the system gives you options to save or delete this file.

Saving the YAC cube file in any manner enables you to reopen the Cube quickly without having to resubmit your screen request.

Leave the file where it is in your InBox.
Or Save As using a name of your own choice to a known location outside AMS.
Delete and discard. You will need to resubmit a cube request if and when you need its data.
Move the file to a new File Page.

(2) CUBE Viewer Toolbar used to make a spreadsheet, send data to Excel, view a report, etc. Hover your mouse over each control to display its tooltip description.

(3) Dimension Bar containing your requested Cube dimensions, displayed as moveable data objects. It is the parking place for dimensions that we don’t want to be displayed as a row or column just now.

(4) The Column Dimension Bar. Drag a dimension into this area to define the dimension as a column.

(5) Row Dimension Bar. Drag a dimension into this area to define the dimension as a row. (Note, the word Rows shown in the image does not actually appear in the Cube viewer and is for purposes of this documentation only.)

(6) Default Measures, as defined, and turned off and on by the Measure Manager. Default measures will differ, depending upon the AMS module in which it is defined.

Functional areas of the Cube viewer

Below is a picture of the Cube Viewer with our orders cube displayed. In the picture, we have pointed out some areas in the Cube Viewer so we can describe them in detail.

The GUI - The CUBE Viewer’s Graphical User Interface

This document covers only the portions of the CUBE Viewer's graphical user interface that will be the most commonly used. We encourage you to experiment and explore how its built-in features may be of further value to you. Here are a few basics.

Dimension Controls

Hide or Show the dimensions contained in each area that holds them.
Open the dimension editor.

Total by ROWS in Toolbar

Self-explanatory. Toggle display of measures, totaled by row.

The Dimension Editor - Checkmarked Filters

The dimension editor allows you to filter the dimension’s data. Click on the checkmarks to cycle through each available dimension status and color, explained below:

Green-colored checkmarks indicate which data is included and displayed
Red-colored checkmarks indicate which data is excluded entirely.
Blue-colored checkmarks cause the most confusion. When used, the checked value is not displayed. However, the image below shows that its associated measures remain included in the system-calculated Total by ROW.

Example 1 - Filter on State. Below, only the states of ARIZONA and CALIFORNIA are checked in green and included in the Cube’s display.

Example 2 - Filter on Ship Year. Below, the Year 2022 is checked in red. Its measures will be excluded from the Cube’s display and system-calculated Totals by ROW.

Example 3 - Filter on Ship Year. Below, Year’s 2019 and 2022 are checked in blue.

Row and column measures for these years will not display in the Cube. However, their associated measures will be included in the system-calculated Total by ROW. Notice in the image below that column measures for Years 2019 and 2022 are missing (i.e., blue checkmarks in the editor), and only Years 2020 and 2021 are visible (i.e., green checkmarks in the editor).

Dimension Editor Toolbar

(1) Check all dimension values green - select all

(2) Check all dimension values blue - include in Total by ROWS but do not display column measures

(3) Check all dimension values red - exclude all

(4) Cycle all checkmarks from their current color to the next color in the green, blue, and red sequence

(5) Find searches for dimensions that match the entered value. Wildcards are supported. For example, entering “*O” would tell the system to find states containing the letter “O.” Or entering “C*” would tell the system to find states beginning with that letter.

6. Accept dimension editor settings and display the results in the Cube.

7. Discard changes made since opening the editor and return to the Cube.

The Measure Manager

Measures are values calculated by the system or defined by the user and displayed in the Cube.

Use the controls appearing in the bottom left corner of the Cube to launch the two versions of the Measure Manager.

Hide/Show Measure Toolbar
Show Measure Manager for all available measures
Show Calculated Measure Manager

Default Measures

By default, three measures appear in our example Wholesale Orders Cube.

StdCases or Standard Cases
NetPrice
NetAmt

Default measures will vary between different cubes, depending upon the AMS module in which they are defined.

Checkmarks - Green or Red

The image below shows all available measures. Green checkmarks indicate the default measures that the system selects for display. We’ll accept ctSumma as the Calculation type and save explaining the right-hand portion of the measure manager for another document.

Click the + sign before each measure to drill down into the details available for each measure. For example, for the StdCases measure, the Cube displays the measure Value, which is the sum of StdCases. Measures checked in red do not display in the Cube but may be turned on or off as required.

Clicking on the glyph that appears next to the measure’s name opens the Measure Manager for just that measure.

Calculated Measures Manager

To determine how the system calculates its measures, examine the calculation formula in the Calculated Measures Manager. In the example below, the Calculated measure “NetPrice” is derived from the formula “NetAmt / Quantity” that is, Net Amount divided by Quantity.

For purposes of this introduction, we’ll accept the calculations that come with the system and save explaining adding and editing measures or another document.

Browser not supported