Drill down to where it matters
Edit: Note that this article is based on Atoti version 0.4.3 and some features may no longer be relevant. Check out the Atoti documentation for the latest features.
If you are reading this article because of the title, you probably already know what a pivot table is. Otherwise, a pivot table presents summarized data in a table format that allows you to perform your analysis with minimal effort.
Without further ado, let’s set some assumptions and see the content that will be covered in this article. Feel free to skip to the section you are interested in:
Assumptions
We are going to assume:
- you have some basic knowledge of Python and Jupyter Notebooks
- A multidimensional data cube has been created using Atoti library for this article
- Some measures have been created with respect to the use case
Creating Visuals
To create pivot tables in a notebook with Atoti, we need to visualize the cube by running the following:
cube.visualize("Title for the visualization")
Title is optional but it gives insight into the objective of the pivot table when you review the notebook. It will also be the name of the saved widget when you publish it to Atoti’s UI. This will generate the below output for the cell:
As you probably guessed, Atoti library supports 4 types of data visualizations:
- Chart (Uses Plotly as its underlying component)
- Featured values
- Pivot table
- Tabular view
Click on the Pivot table tile to start.
Take note of the 4 points marked on the screen above:
- Click on to open up the Atoti Editor (point 3)
- Alternatively, click on to expand the Atoti Editor (point 3)
- The expanded left hand panel is the Atoti Editor
- Drag the to adjust the height of the visualization, or what we call widget
Adding data to a pivot table
There are 3 ways to add data to the visualizations. However, depending on the type of visualization selected, you may have to populate the data differently. For instance, depending on the type of charts selected, you may only have to provide the measure and x-axis members. For Featured values, you have the option to add a comparison. In our case, pivot tables only have Rows, Columns and Measures that may or may not have to be populated.
Below are the ways to add data to the visualizations:
Direct selection from the Data Explorer
Expand the tree nodes to access the hierarchies, levels or measures that you required.
You can click on the intended hierarchy, level or measure to add it to the pivot table. You can also use the Search data function to access the data field faster.
By default, measures will be added to Columns, and hierarchies or levels will be added to the Rows. You will be able to see the selected measures under the Measures section.
Alternatively, you can drag and drop the field from the data explorer into the intended sections.
Adding from sections
Click on the + icon to access the Data Explorer popup. Similar to the previous step, click on the field to add it to the sections.
Manual manipulation via MDX
If you are an advanced user and you are familiar with MDX, you can toggle over to the MDX Editor view.
This is how the MDX Editor will look:
Tip: use “Ctrl/Cmd +space” for auto-completion of the member coordinates when you edit the MDX.
Once your data is added to the visualization, you can drag the data field across sections or re-arrange its order. Data is aggregated on the fly as you switch the hierarchies between rows and columns.
Tip: The widget will re-render each time you perform editing. Although it is usually relatively fast, you might not want it to re-render multiple times when you have huge datasets. A workaround for this is to select the Deferred Update checkbox at the bottom of Atoti Editor.
Rendering of the pivot table will only happen when you click on the Submit button. Remember to uncheck it when you are done, otherwise, you may receive some warnings as you move away from the cell.
Filters
Filters are an important aspect of a pivot table as it allows you to slice and dice the data. Let’s explore the filtering capabilities of the Atoti library.
Switch to the Filters tab and you will find the Data explorer, similar to the Fields tab.
Underneath the Data explorer, you will be able to access the widget filters:
Widget: Pokemon Stats
Applying a filter at this level impacts only the selected widget which in this case is the “Pokemon Stats” widget.
If you publish the widget to a dashboard, you will also see that there are additional layers of filters on the Dashboard and Page level.
In the widget section, you can see the 
icon. Clicking on this icon will disconnect it from higher-level filters such as user, dashboard or page filters. Only filters at the widget level will be effective.
After you understand the difference between the 2 levels of filters, you can add filters just like how you add fields to the pivot table. However, you will need to select the Filter type and the values to filter on.
The default filter type is Members, where you choose the members to include or exclude from the dataset.
Explore other filter types such as Top/Bottom count. It returns you the top/bottom number of the selected level objects that have the highest value in the selected measure.
If you are familiar with MDX, you can opt to write your own custom filter.
Exploring Atoti pivot tables
We will be using a notebook based on pokemon to demonstrate the various features of Atoti pivot tables. The pokemon cube is setup as follows:
Expand by…
Imagine you have a pivot table displayed and you would like to do some quick investigation against another level for a particular row’s member. Expand by… would be handy in this case. It is a feature of Atoti’s pivot tables that allows you to expand an existing row of data by a newly selected hierarchy.
Let’s take the below pivot table as an example. It shows the highest Total Stats across the pokemon in each generation.
It would be interesting to know which are the pokemon accounting for these values. Now, suppose you add Name of pokemon to the rows, all the generations will be expanded and you get a very long vertical scroll.
You can collapse the levels that you are not interested in.
Alternatively, if you are only interested in Generation 2, just click on the 
on the row that you wish to expand by. Choose the hierarchy you want to view from the popup.
From the gif above, you can see that only Generation 2 is expanded by Name hierarchy. Notice that if a row has been expanded, the rest of the rows are able to expand by the same hierarchy too. However, if all the rows are collapsed, you will need to re-select the hierarchy to expand by again.
Sorting
There are still a lot of rows to scroll through to find the pokemons with the highest Total Stats. One way to identify them would be to perform a sorting on the Total Stats columns.
Right-click on the column header and you will see a list of features available to the pivot table.
In this case, we will choose the “Descending” order since we want to view the highest value on top.
If you toggle to the MDX editor in Atoti Editor, you should be able to see the resulting MDX uses DESC instead of BDESC. This means that we do not break the hierarchy during sorting.
You can see in the resultant table, generation is first sorted based on the highest Total Stats. Then the Pokemons under generation 2 are sorted within the hierarchy.
In the event you used non-hierarchical sort (BDESC), you can see that all the rows are sorted purely based on the Total Stats, regardless of hierarchy.
You can right-click on the column header to change or remove the sorting.
Calculated Measures
During modelling, visualization helps to inspire the type of data you want to view. For instance, in the above table, we do not know how strong a pokemon is compared to its generation.
To enable quick prototyping, you can create calculated measures on the fly. Go to Atoti Editor’s Field tab. Click on the + icon of Measures as you would add a Measure.
Select “Calculation” as you see in the gif above. Atoti’s calculation wizard supports:
- Distinct Count
- Percentage of Parent
- Percentage of Total
- Custom
In the table below, you can see the values generated by using the above options (other than custom). Refer to the documentation for the calculation logic behind each option.
Did you notice the 
icons beside the calculated measures that you created? You can edit the calculated measure using the 
icon and publish the measure using the 
icon.
When publishing a measure, you will see the below popup. In the community edition, the security feature is turned off, hence you only have an anonymous user. Hence you will need to take the default value for the read and edit access right. You can update the measure name and select a folder to publish the measure under.
Why do we publish measures? The newly created calculated measures are only available in the current visualization. If you wish to use this measure in other visualizations, you have to publish it.
Once published, you can select the measure from the data explorer. You can delete the measure but you will not be able to update the measure anymore.
Do remember to persist your session in a database if you are publishing the calculated measures. As we are looking at an in-memory data cube here, your published measure will be lost on restart of the kernel if you do not persist it.
Alternatively, you could create the measure using python:
m["Highest Strength"] = tt.parent_value(
m["Total Stats"], on_hierarchies=h["Generation"]
)
m["Relative Strength"] = m["Total Stats"] / m["Highest Strength"]
m["Relative Strength"].formatter = "DOUBLE[#,###.00%]"
Formatting
Formatting cells is an important feature that is expected for any pivot table. Just right-click on the column header and select “Formatting” just as you did for sorting. You will access the below popup.
- You can set a default format for the numeric column. Refer to Format String MDX Cell Properties at Microsoft.com for the formatting syntax.
- Click on to expand the styling section. This section is rather intuitive in terms of how you can change the type, size, color and weight of the font, as well as the color of the cell. You can preview the change in the cell below, as you modify the various style options. This section defines the default style of the column.
- Click on “Add a style rule” to add conditional formatting. The types of conditions supported are:
You are not limited to these options. Behind the scenes, these styles are translated into MDX. You can modify or formulate your own conditional formatting by updating the MDX.
With the color styling below, we can have a rough gauge of a Pokemon’s strength compared to the rest without looking at the exact values..
Freezing columns
During data analysis, more often than not, we will compare the values of different members of a hierarchy. Comparison gets tough when you have a huge table and you need to scroll left and right.
Atoti’s pivot table allows you to freeze and pin columns to the left to facilitate comparison. Right-click on the column’s header to access the “Freeze” option in the context menu as shown in the GIF below.
Keep Only
“Keep Only” allows you to quickly filter on a row and/or column. You can do so by right-clicking on:
- Cell – to filter on the selected cell
- First cell of the row – to filter by the selected row member value
- Column header – to filter by the selected column member value
If you need to keep more than one value, use the Atoti Editor to add your filter instead.
Export to CSV
After performing all the aggregation, you could export the pivot table to a CSV. Right-click anywhere on the pivot table and you can access the “Export this data to a CSV file” function.
In the below popup, you have the options to:
- Update the CSV file name
- Change the default delimiter
- Selecting whether to apply formatting on the cell.
You can see in the Excel below, the difference with and without selecting formatting for numeric cells.
Publish widget in App
As you can see, you will lose the fonts and color formatting once you export the pivot table to CSV. Alternatively, you could publish the widget to Atoti UI’s and share the table in a dashboard with your peers.
Right-click anywhere in the table and you can find “Publish widget in app” in the context menu.
Select “Open App” to access Atoti’s UI. Alternatively, you can run the command below to get the URL of Atoti UI for your session:
session.url
Publishing the widget to Atoti’s UI gives you access to the full range of functionalities such as Quick Page Filters, dashboard building etc.
If you are hosting your notebook on the cloud, you could share the URL with your colleague and they will be able to view the same dashboard. Any change in the cube’s structure or measures will be reflected in Atoti’s UI automatically.
Summary
Hopefully, the article provides you with more insight into how you can create dynamic pivot tables with Atoti. Atoti is constantly evolving. Look out for more updates on Atoti.io.
