How-To: Integrate a fiscal calendar

Overview

This guide explains how to add fiscal calendar data to Certified Datasources.

Instructions

• 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 your Certified Datasources.

• 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

  

Related articles