How do you change the fiscal calendar in Metric Insights?

ISSUE

How do you change the fiscal calendar? Our company has a fiscal calendar that is different than what is defined in Metric Insights. I can't seem to find a Fiscal Calendar page in which to make these changes.

SOLUTION

1. Set Up a CSV with Fiscal Calendar Data

The CSV file must be in the following format:

fiscal_month_id: 202501
period_name: F25 P01 (MAY)
period_number: 1
first_day_of_period: 2024-04-25
last_day_of_period: 2024-05-29
fiscal_quarter_id: 202501
quarter_name: Q1 FY2025
quarter_number: 1
first_day_of_quarter: 2024-04-25
fiscal_year_id: 2025
year_name: FY2025
first_day_of_fiscal_year: 2024-04-25

NOTE: The sample entries above illustrate the required format. Some columns can be computed from others. This is not a minimal set, just a simple one that works.

2. Dump the Existing Fiscal Calendar Tables as a Backup

NOTE: Root access is needed for this action.

~# mysqldump dashboard fiscal_month > /tmp/fiscal_month.sql
~# mysqldump dashboard fiscal_quarter > /tmp/fiscal_quarter.sql
~# mysqldump dashboard fiscal_year > /tmp/fiscal_year.sql

3. Create a Dataset

  1. Populate a Dataset from the CSV created in the first step.
  2. Note the Dataset ID value, it is needed for SQL table update in the next steps.
    • The Dataset ID value can be obtained from the Dataset Editor URL, it is located after the /dataset/ portion of the URL,
    • Example: If the URL is <MI hostname>/editor/dataset/63#dataset_info, the Dataset ID is 63.

4. Update Fiscal Tables with CSV from Dataset

NOTE: Replace <dataset_id> in the commands below with the ID of the Dataset populated from the CSV in the previous step.

4.1. fiscal_year 

> use dashboard;


> truncate table fiscal_year;


> insert fiscal_year(fiscal_year_id, name, first_day_of_fiscal_year)
select distinct 
 fiscal_year_id, 
 year_name, 
 first_day_of_fiscal_year
from dashboard_dataset.dataset_<dataset_id>
order by fiscal_year_id asc;


update fiscal_year y 
set last_day_of_fiscal_year = (
 select max(m.last_day_of_period)
 from dashboard_dataset.dataset_<dataset_id> m
 where m.fiscal_year_id=y.fiscal_year_id
);

 

4.2. fiscal_quarter 

> truncate table fiscal_quarter;


> insert fiscal_quarter(fiscal_quarter_id, fiscal_year_id, quarter_number, name, first_day_of_quarter)
select distinct 
 fiscal_quarter_id,
 fiscal_year_id, 
 quarter_number, 
 quarter_name,
 first_day_of_quarter
from dashboard_dataset.dataset_<dataset_id>
order by fiscal_quarter_id asc;


> set @Row_ID=0;
update fiscal_quarter 
set fiscal_quarter_seq = (
 select @Row_ID := @Row_ID + 1
);


> update fiscal_quarter q
set last_day_of_quarter = (
 select max(m.last_day_of_period)
 from dashboard_dataset.dataset_<dataset_id> m
 where m.fiscal_quarter_id=q.fiscal_quarter_id
);

 

4.3. fiscal_month 

> truncate table fiscal_month;


> insert fiscal_month(fiscal_month_id, name, fiscal_year_id, period_number, fiscal_quarter_id, first_day_of_period, last_day_of_period)
select distinct 
 fiscal_month_id,
 period_name, 
 fiscal_year_id, 
 period_number,
 fiscal_quarter_id,
 first_day_of_period,
 last_day_of_period
from dashboard_dataset.dataset_<dataset_id>
order by fiscal_month_id asc;


> set @Row_ID=0;
update fiscal_month
set fiscal_month_seq = (
 select @Row_ID := @Row_ID + 1
);


> update fiscal_month mon
set duration_days = timestampdiff(day, first_day_of_period, last_day_of_period) + 1;


> update fiscal_month
set 
 last_year_fiscal_month_id = fiscal_month_id - 100,
 last_month_fiscal_month_id = fiscal_month_id - 1;


> update fiscal_month
set last_month_fiscal_month_id = last_month_fiscal_month_id - 100 + 12
where last_month_fiscal_month_id/100 - floor(last_month_fiscal_month_id/100) = 0;

4.4. calendar_day 

> update calendar_day set fiscal_month_id = null;


> update calendar_day set fiscal_quarter_id = null;


> update calendar_day set fiscal_year_id = null;


> update calendar_day d
set d.fiscal_month_id = (
 select m.fiscal_month_id
 from fiscal_month m
 where d.calendar_date between m.first_day_of_period and m.last_day_of_period
);


> update calendar_day d
join fiscal_month m 
on d.fiscal_month_id = m.fiscal_month_id
set 
 d.fiscal_quarter_id = m.fiscal_quarter_id,
 d.fiscal_year_id = m.fiscal_year_id;

You've now updated your fiscal calendar in Metric Insights!