Knowledge BaseMetric InsightsConfiguration Fiscal CalendarHow do you change the fiscal calendar in Metric Insights?

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

The fiscal calendar in Metric Insights is not yet available through the UI (to be surfaced in a future release). However, we can still make changes to the calendar directly in the database. To proceed, please follow the instructions below.


1.) Set up a Fiscal Calendar:

You can use the SQL code below if you obtain or create a csv file with the following columns saved as fiscal_master.csv (csv file must be in the following format):

fiscal_month_id:201601

period_name:F16 P01 (MAY)

period_number: 1

first_day_of_period: 2015-04-25

last_day_of_period: 2015-05-29

fiscal_quarter_id: 201601

quarter_name: Q1 FY2016

quarter_number: 1

first_day_of_quarter: 2015-04-25

fiscal_year_id: 2016

year_name: FY2016

first_day_of_fiscal_year: 2015-04-25


Note the sample entry for each column above. Some of the columns can be computed from other columns. This is not a minimal set, just a simple one that works.


2.) Dump the existing fiscal calendar tables as a backup (you'll need root acces):

~# 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.) Now, create a fiscal master table to import the csv:

**fiscal_master**

>create database temp;

>use temp;


>create table fiscal_master(

fiscal_month_id int,

period_name varchar(20),

period_number int,

first_day_of_period date,

last_day_of_period date,

fiscal_quarter_id int,

quarter_name varchar(9),

quarter_number int,

first_day_of_quarter date,

fiscal_year_id int,

year_name varchar(6),

first_day_of_fiscal_year date

);


4.) Load the fiscal_master table from your csv file to the various fiscal tables:

 

MySQL command to load the csv file into the temp.fiscal_master table:

mysql> load data local infile '/path/to/fiscal_master.csv' into table temp.fiscal_master fields terminated by ',' ignore 1 lines;


**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 temp.fiscal_master

order by fiscal_year_id asc;


update fiscal_year y

set last_day_of_fiscal_year = (

select max(m.last_day_of_period)

from temp.fiscal_master m

where m.fiscal_year_id=y.fiscal_year_id

);


**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 temp.fiscal_master

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 temp.fiscal_master m

where m.fiscal_quarter_id=q.fiscal_quarter_id

);


**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 temp.fiscal_master

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;



**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 caledar in Metric Insights!