How to incorporate the Fiscal Calendar into ADL 2.0 models

Overview

ADL 2.0 now supports the integration of the fiscal calendar with four columns: fiscal_year, fiscal_quarter, fiscal_month and fiscal_week. 🎉

This document provides guidance on incorporating the fiscal calendar to the ADL 2.0 models for a client without dependence on the Data Engineering team.

Steps:

Fiscal Calendar datasource setup

• Find and access the client’s fiscal calendar datasource within Alli Data. Most of the client’s employ this naming convention for the datasource: fiscal_calendar

  

• Once we have identified that the client has a fiscal_calendar datasource, click into the Explorer tab and hit the New Data Report button.

  

• Choose the Edit Custom SQL option
  

  

• Create a report titled fiscal_cal_adl with the following SQL query and hit the Publish button when done

Remember to cast the columns in this format:

fiscal_year

fiscal_quarter

fiscal_month

fiscal_week

In the above example, the fiscal_calendar datasource for Best Western did not have columns for fiscal_quarter and fiscal_week so they were casted to null. The client that you are trying to onboard may have these columns in their datasource so check the schema before writing the query

• The above step will ensure that the fiscal_cal_adl view is created in Redshift and ready to be integrated into ADL 2.0.

• To create the fiscal_cal_adl view in Bigquery, follow this documentation: Create BigQuery View

• Currently, creating views in Snowflake through Alli Marketplace isn’t supported but an app to be able to do so is currently being worked on.

• Once the view has been created across both data warehouses, run the ADL 2.0 Refresh apps for the client and you should be able to see the fiscal calendar columns in any of the {report}_performance models like below