Chapter 10: Stratification

Chapter 10: Stratification Transcript

Stratification is a tool for profiling your data by breaking it up into bands. In this exercise, we’re going to look at the data to get a sense of the number and value of customer accounts.

Begin by ensuring the Summarized Transactions database is active and that data is selected in the properties window.

Before we start the stratification process, we need to have a sense of the range of values in our data in order to determine what an appropriate size for each bands is. We can get this information from the field statistics.

In the properties pane, click field statistics to create them and have a look at the values which show in the AMOUNT column. Return to the data view by clicking data in the properties window.

If you only want to see a subset of the field statistics while viewing the data you can right click anywhere in the data, and select “Show Field Stats” then pick and choose which values you want to see. I’m going to select Net Value, absolute value, average, minimum and maximum and click OK. These values are now shown at the top of the file.

I can see that the total value of the amount field is 6.4 million but more than half of that is contained in one transaction, shown in the maximum value. The average value is only 21 thousand. This means that I don’t need to stratify my data all the way to 6 million, I can stop well before that and have that one transaction remain an outlier.

So to perform a Stratification, go to the Analysis Tab, and click Stratification in the Categorize group.

The Stratification dialog appears. The first box asks which field is being stratified, or broken up into bands. That’s AMOUNT_SUM.

Next, indicate which field is being totaled. Select the Number of records. The third part is you must indicate what stratification interval is to be used. The default value is 10,000 but this can be changed. So I’m going to change this to 5000.

Now to build the strata. The first value of 0 is determined by the minimum value in your database. This can be changed if you wish but for this example, we’ll leave it. Next click in the Upper Limit column to write in the value of the strata. Click in the upper strata of the second row. Click in the third row and drag it to the fifth. You should now have from 0 to 25K filled with increments of 5000.

Now we’re going to change that increment to be 25K.

Go back to the bands and click in the sixth row and drag until the 12th. You should have values up to 200K. Ensure the Create Result check box is selected and provide a name for your result set, such as Numeric Stratification, and click OK. The output this time is not a database, but is called a result set. Here you can see the distribution of records. There are 219 records in the first band which represents 72% of all the records in the database.

There is one upper limit exception. If we click the link, we can see that its that one large transaction mentioned earlier so this exception is not unexpected.

As mentioned, this is a result set, not a database so there are some things you need to know. First, this is part of the database which was used to create it, not a child so a new database is not generated. Instead an entry is visible under Results in the Properties window. Additionally, there are graphing capabilities here that are not present in a regular database. Click the graph button in the result set toolbar

Each bar is a strata, the number of records is indicated on the y axis, with the strata spread across the x axis. If you hover over any of these bars, you can see details about the strata such as the number of records and which stratum it is. If you click a bar you will get a menu to extract data to a new database, to display the records or to display the field statistics of that particular stratum. You can change the chart type by click the Gallery button and selecting the chart you want. You can also add a 3D effect and change color palettes as well.

Experiment here: when you are finished you can return to the result set data by clicking the “Alternate” button again or you can return to the data in the Summarized Transactions database by clicking data in the properties window.

When you’re finished, close any open databases.