2020.10 is the latest release from Pyramid Analytics. It extends the functionality introduced in our major 2020 release. This is one in a series of posts that highlights some of the major new features.

The smart Calendar Slicer in Pyramid is designed to make date-time operations fast and simple for both novice and advanced users alike. It is especially useful when more date-time structures in the original data source do not exist and users do not want to use complex formulas to filter data with dates.

Problem

Many data sources do not have extra date-time groupings and values like “year,” “month,” “week,” and “quarter.” This is especially true on relational databases that only have a simple date-time field. Usually, to get the extra “time intelligence,” the data needs to be manipulated via ETL operations or the user needs to build and deploy calculations. Sometimes these options don’t exist (because there is no write back), or the calculations are too complex for a non-technical user.

Many BI tools have real issues in these scenarios because they cannot deliver the time-intelligence in a direct query model. Often, they resolve this by duplicating the data into their own proprietary databases first. There are two problems with this solution: it doesn’t scale and it breaks governance.

Pyramid’s approach

The “Calendar Slicer” tool in Pyramid represents date selection options in a graphical, non-technical interface. It works directly on any SQL data source (including Oracle, SAP HANA, Exasol, Snowflake, Redshift, etc.)  without needing to add time intelligence, change the source, or add any special date-time calculation logic (which is still possible for more advanced time requirements). It handles selections with different granularity settings (dates, weeks, month, quarters, semesters, years); range selections  (from, to); formulaic selections (YTD, MTD, last “n” periods); and even lets the user define non-calendar years. It does all this, all while making the process incredibly easy.

Business case

Mary, a business analyst at XYZ Component Manufacturers Inc., is using Pyramid to query her Amazon Redshift data warehouse (which contains 10B records). The sales data is stored in a classic snowflake schema, with all date-time values stored as the transactional dates of each order in the system.

Mary needs to analyze sales and quantity values by the manufacturer by looking at data for a given month or quarter. However, she doesn’t have month and quarter groupings in the database and she is not technical enough to create these time intelligence columns. Worse, the DBA’s are reluctant to make adjustments to their data warehouse—“it’s too big,” they say.

Luckily, the Calendar Slicer tool helps Mary resolve the problem instantly.

In the diagram below, we see a Sales Report by product category, gender, and commute distance with no dates selected.

Simple select of date

Mary simply drags the date columns to “filters” and it automatically builds the Calendar Slicer. Mary now uses the slicer to select the 17th March 2018, from a classic calendar UI that is easy to traverse from dates to months or years.

The resultant report has now been filtered for 17th March, 2018. The selection and breadcrumb at the top of the report clearly indicates the selected date.

Date range

Mary now uses the calendar slicer to select all records from the 17th March 2018, until 15th November 2018. She selects the range option, and then selects “from” and “to” dates from the two calendar slicers.

The results are displayed immediately as per the selection, and can be seen in the breadcrumb at the top of the report.

Simple month select

Mary now uses the calendar slicer to select all records for March and May 2018. She selects the “simple” option, and then selects the “months” granularity, picking the two relevant months from the calendar slicer. Despite the fact that the year, quarter, month, and week columns do not exist in the database, all of these options are available in the calendar slicer as if they were.

(Note that many sequential or non-sequential months can be selected with the “Multi Select” option checked.)

The results are displayed immediately as per her selection, as can be seen in the breadcrumb at the top of the report.

Simple quarter select

Mary now uses the calendar slicer to select two quarters, Q2 and Q3 for 2019. She selects the “simple” option, the “Quarters” granularity, and then the two quarters from the calendar slicer.

Once again, the results are displayed as per the selection of the second and third quarters of 2019, as can be seen in the breadcrumb at the top of the report.

Year-to-date calculation

For this selection, Mary wants to view year-to-date sales until the 15th May 2020. She selects the “Formula” option from the left-hand selection options, selects the YTD function from the function option at the bottom of the slicer, and selects the desired date: the 15th May, 2020.

The results are displayed as per the selection of the year-to-date until 15th May, 2020, as can be seen in the breadcrumb at the top of the report.

Selecting previous periods using the “last” function.

The formula calculation can powerfully select the last “n” number of days, weeks, months, or years using simple point-and-click functionality. In this example, Mary selects the Formula option, then selects the “Last” function. Within the “Last” function she then selects the weeks option, entering “4” as the number of weeks to display. Mary uses the 29th May as her “to date.”

The results are displayed as per the selection of the last four weeks ending 29th May, 2020, as can be seen in the selection at the top of the report.

Non-calendar periods option

Pyramid provides users with the capability to define non-calendar periods that begin on dates other than January 1st.  Users may also define the week start day. This flexibility enables users to customize the date function to suit their own country or company’s standards.

Summary

Pyramid’s smart Calendar Slicer is designed to make date-time operations fast and simple—on any data technology. Users without date-time structures in the original data source have to manipulate data in ETL operations or build and deploy complex calculations and competitive tools can’t deliver time-intelligence in a direct query model. Pyramid allow users to use smart date-time selections through an instantaneous point-and-click interface without changing the data source or ingesting the data into an intermediate database. It provides selections with different granularity settings, using simple, range, and formulaic selections, truly delivering sophisticated analytics simply.

This post originally appeared at https://www.pyramidanalytics.com/blog/details/blog-2020.10-feature---calendar-slicer

Other posts in this series:

Pyramid 2020.10 is chock full of powerful features like the content migration tools described above. For more detail about some of the other new headline features contained in the 2020.10, please see these additional write-ups:

Post 2 – Ragged Queries: The ragged query feature in Pyramid helps users create highly complex views of data, allowing them to easily “laser” out content items they do not want to see in a report—effectively building an “asymmetric” query.

Post 3 - Self-Building Data Catalogs: Pyramid provides users with a self-building and self-maintaining documentation mechanism for data and analytics, which helps analysts and administrators to find the data or analytical assets that they need.

Post 4 - Data Catalog Toolkit: Learn about Pyramid’s Structure Analyzer, Data Source Changer and Lineage tool, all of which provides a graphical, intuitive set of mechanisms to all the elements in the system, fully exploiting the power of centralized data and asset cataloging.

Post 5 - Flow Grids and Flow Charts: The flow grid and flow chart tools in Pyramid are designed to dramatically extend the grid and chart capability for publications.

Post 6 – Present Lite: The new Present Lite tool is designed to create quick and easy dashboards for non-technical users. Users will experience a simplified UI with fewer advanced features, and they will get all the essential tools required for creating self-service, self-built dashboards.

Post 7 – Audit Logs: Pyramid’s Audit Trail provides users with a framework to track all changes made to key objects throughout the system - providing administrators with a clear view and history of when an object was created, changed or deleted, by date and by user.

Post 8 – Datavard Glue: Pyramid now integrates seamlessly with Datavard’s “Glue” – a third-party tool that allows users to trigger and extract raw SAP ERP data. The data can then be ingested into Pyramid by end users for further analysis and reporting.

Post 9 – Content Migration: Pyramid users can easily migrate content from one Pyramid instance to another with an easy, intuitive wizard assisting them through the process. This facilitates re-use of content items with efficient processes to ensure governance and security.