How:Slice & Dice Library Data: Difference between revisions

From Memento Database Wiki
Jump to navigation Jump to search
mNo edit summary
mNo edit summary
Line 102: Line 102:
Once the libraries are set up, and data is entered, the job of viewing the data and making sense of it is at hand. While viewing '''[[Libraries List screen|the list of library entries]]''', the following activities will give you the best possible look at the data and any wisdom that may be contained within it.
Once the libraries are set up, and data is entered, the job of viewing the data and making sense of it is at hand. While viewing '''[[Libraries List screen|the list of library entries]]''', the following activities will give you the best possible look at the data and any wisdom that may be contained within it.


=== Set-up ===
=== A final bit of setup ===


; Filtering
; Filtering
: If you find yourself frequently focusing on one customer, on customers from one zip code, or on certain products or product categories, define & store a filter for each such circumstance ahead of time. Then, during ad hoc data analysis, you can quickly set the filter you want.<br/>In this case, let's define filters for each product category. On the '''[[Library Entries List screen]]''', define the following filters:
: If you find yourself frequently focusing on one customer, on customers from one zip code, or on certain products or product categories, define & store a filter for each such circumstance ahead of time. Then, during ad hoc data analysis, you can quickly pick the filter you want.<br/>In this case, let's define filters for each product category. On the '''[[Library Entries List screen]]''', define the following filters:
* '''Lumber''', with field '''Product category''' having the value '''Lumber'''
* '''Lumber''', with field '''Product category''' having the value '''Lumber'''
* '''Tools''', with field '''Product category''' having the value '''Tools'''
* '''Tools''', with field '''Product category''' having the value '''Tools'''

Revision as of 14:46, 28 October 2016

« Page as of 2016-10-26, editions Mobile 3.9.7, Desktop 1.0.3 »

This page is incomplete and under development.

To describe how to perform the functions involved in slicing & dicing, we will use as an example a library Orders of orders on a date of a product by a customer, so there are related libraries Customers and Products.

Preparing the example
If you'd like to play along (recommended), add the libraries:
  • Customers with unique entry name and the following fields:
  • Products with unique entry name and the following fields:
  • Orders with unique entry name and the following fields:

Note that we already have some good hooks for subsequent analysis:

  • Customers have types and zip codes. This will enable orders to be summarized by customer type and by location.
  • Products have categories, cost, and a standard price. We can summarize by product category, and cost & standard price give us the basis for calculation of margins.

Identify the library to center on for data analysis

Among these libraries, the Orders library has the most potential for fruitful analysis, though there could also be useful analysis done within Customers or Products. We will focus on the Orders library.

Preparing for maximal slicing & dicing benefit

To prepare for subsequent analysis of the data, add related data and calculations to the data and then define aggregations of the data.

Bring related data into the entry

First, bring the "good hooks for subsequent analysis", mentioned above, into the Orders library to enable them to be used during analysis.

Add these fields to Orders
  • Press the +Field button, choose customer.type from the list
  • Press the +Field button, choose customer.zip code from the list
  • Press the +Field button, choose product.category from the list
  • Press the +Field button, choose product.cost from the list
  • Press the +Field button, choose product.std price from the list

Calculate with related data

Then, add any calculations within the newly-expanded Orders library that will assist in data analysis.

Add these fields to Orders
Either using the +Field button or by just typing, enter this script:
	#{product.std price} - #{product.cost}
Enter this script:
	( #{product.std price} - #{product.cost} ) * 100 / #{product.cost}
Enter this script:
	#{price} - #{product.cost}
Enter this script:
	( #{price} - #{product.cost} ) * 100 / #{product.cost}

Aggregation

There is nothing to aggregate in Customers. In the Products Library Edit screen, under the Aggregation tab, add the following aggregations;

  • Sum Std price on the left
  • Sum Cost on the right on the same line

In the Orders Library Edit screen, under the Aggregation tab, add the following aggregations:

  • Sum Product cost on the left
  • Sum Quantity on the right on the same line
  • Sum Product std price on the left
  • Sum Product margin on the right on the same line
  • Sum Price on the left
  • Sum Order margin on the right on the same line
  • Average Price on the left
  • Average Order margin on the right on the same line

Placement of the aggregations is entirely up to you, of course. I try to group like things together as much as possible, to make the analysis as meaningful as possible.

At this point, we've maximized the included fields as much as possible and then maximized the meaningful aggregations — all valuable tools for subsequent data analysis. Fell free to add more, as you see fit.

Enter test data

Enter as much data now as you can stand, as more data makes more meaningful analysis. Specifically, enter at least 2 entries for each of the following:

Customer types
Enter at least 2 entries for Consumer-type customers and 2 for Institution-type customers.
Customer zips
Enter at least 2 entries for each of at least 2 zip codes.
Product categories
Enter at least 2 entries for Lumber products and at least 2 for Tools products.
Orders
Enter as many orders as you can stand, making sure to reference as many customers of different types and zips as possible and products of as many categories as possible; then try some slicing & dicing, while reading the following sections. Then enter some more, until you are satisfied with what you can get out of slicing & dicing.

Slicing & Dicing

Once the libraries are set up, and data is entered, the job of viewing the data and making sense of it is at hand. While viewing the list of library entries, the following activities will give you the best possible look at the data and any wisdom that may be contained within it.

A final bit of setup

Filtering
If you find yourself frequently focusing on one customer, on customers from one zip code, or on certain products or product categories, define & store a filter for each such circumstance ahead of time. Then, during ad hoc data analysis, you can quickly pick the filter you want.
In this case, let's define filters for each product category. On the Library Entries List screen, define the following filters:
  • Lumber, with field Product category having the value Lumber
  • Tools, with field Product category having the value Tools
Charting
Another tool Memento provides for data analysis is charting the data. Memento charting is very flexible, providing visual views of the data.

Ad hoc analysis

Grouping
Then, within the filtered data, you may want to see certain data sparsely from others, such as orders of certain products or fuel purchases from certain gas stations. If you group your order entries by product or your fuel purchase entries by gas station, you can then not only see them separately and quickly switch between them, but aggregations will be displayed for each, in addition to aggregation of the entire set.
Sorting
With sorting, you can determine the order of entries, and if grouped, the order of the groups and the order of entries within groups.

Putting it all together

Use sorting carefully to enhance the effect of grouping.

If you don't already have an appropriate filter to use, consider grouping to accomplish the same thing. If that is insufficient, if you know you'll want to do that repeatedly in the future, or if you want to filter on one field while grouping on another, then you can proceed to defining & storing a filter.

It is not always clear how clarity and wisdom unfolds from within the data, but with artful use of the activities described in this page, one can often detect patterns in the data that would otherwise not be apparent.