Levels and groups are ways to organize and analyze your data. Oftentimes, we want to create groups within the data based on common characteristics. Maybe you want to see all the new sign-ups in a day or all of the merchandise in a department. Groups and levels are a way to accomplish that. To visualize how they work, let’s go back to the basics.
Think of a collection of blocks, all different colors and shapes, all piled together. This represents our data.
In Sigma, each individual block is represented by a row. Properties of the block, like color and shape, are listed in the columns. All of the column names in Sigma are listed on the right-hand side, where we show you how the data is being organized. This is also where you can create groups and levels. But why would you want to? Levels and groups allow you to very quickly surface information about groups that is hard to see otherwise. Let’s look at an example.
Let’s say we want to know how many blocks are blue. When there are only a few shapes, you may be able to just count them. When there are more, it’s much easier to group the blocks together by color first. With blocks, you can put them in piles, with each pile having one color.
In Sigma, you create a new level, grouped by color. This is very similar to sorting out the blocks into different piles. You can see on the right-hand side of the screen, there is a new level. Underneath Level 1, you see the column name COLOR, with a blue background. The blue background indicated that the column is acting as a grouping key. This means when separating the individual rows (representing our blocks) into groups we are using color to decide which group each row belongs to. The result is all of the rows grouped by color.
Once you have the rows grouped by color, you can add a calculation to find out how many rows are in each group. These numbers will be the number of blocks of each color. The Count() calculation will count all the rows in each group on the level it is placed on. With only 23 rows, you could count manually, but with 23 million (or more!) that isn’t an option, making count a very useful function. By adding a new column on the level grouped by color, and using the formula Count(), we learn that there are 7 blue blocks.
Once we answer our initial question, there are often more questions that arise. Sigma makes that easy too! Now that we have our blocks grouped by color, what if we want to know how many blue squares there are? Or how many green triangles? We can group by shape, and apply a count to the rows to answer our questions.
Creating a level underneath our existing level will create new groups, within the existing level. It’s like organizing blocks by color, then sorting by shape within those color groups.
The color groups still exist, we’ve just added groups by shape as well. It’s another level of groups.
In Sigma, it works similarly. You can see the level grouped by COLOR, and then see the next level with the rows grouped by SHAPE. We’ve added a formula to count the rows under both levels so you can see the total number of rows in each color group and the number of rows for each shape and color. The number of rows is the number of blocks because each block is represented by one row. we can see that there are 2 blue squares.
Looking at the right-hand Column Panel, you can see that the group keys for each level are light blue. The Count by Color column, and the base columns, have white backgrounds. How many squares are there? How many are blue?
Let’s say that after looking at our data, we’ve decided we no longer need to know how many blocks of each color we have, but the number of each shape is important. We want to know the total number of squares, as well as the number of blue squares. How can we find that information?
The quickest way is to change how we have our rows (or blocks) grouped. Rather than grouping the blocks together by color and then organizing them by shape, we can group the blocks by shape, and then by color.
There are still two levels and the same two grouping keys, but the order changes the information we can get. Unlike with blocks, in Sigma changing how we have our rows sorted takes just a few seconds. Rearranging the levels quickly gives us the results we want. We can see that there are 7 squares and 2 blue squares.
Count is just one of many functions you can use to analyze your data. You can quickly Sum all of the values in a group, or take the Average. Learn more about all the the Aggregates that you can apply. Explore the other functions you can use to create complex analysis at all of the levels of your data.
Understanding the power of groups is the first step in unlocking the power of Sigma. Organizing blocks by hand is fun and playful. With Sigma, organizing millions of rows of live data from your data warehouse can be just as playful. Unleash your curiosity and start asking more questions.