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 theExplorer
tab and hit theNew Data Report
button.Choose the
Edit Custom SQL
optionCreate a report titled
fiscal_cal_adl
with the following SQL query and hit thePublish
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 inRedshift
and ready to be integrated into ADL 2.0.To create the
fiscal_cal_adl
view inBigquery
, follow this documentation: Create BigQuery ViewCurrently, creating views in
Snowflake
throughAlli 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