PBI Best Practices - Date Dimension Table

PowerBI offers to create a very useful Date (dimension) table via the DAX function CALENDAR. However, in terms of performance, Indexima recommends creating this table in Indexima.

The Date dimension table could be created into 2 steps:

Step1: Get the list of the required dates (Table Calendar0)
- a script to insert Dates from the year 2003 to 2030 is here

Insert Only the useful years. Sometimes PowerBI is asking the whole date table, resulting in transferring many values. In order to increase performance and reduce network consumption, insert only the useful years

Step2: Generating the dates attributes (you will below a list of common attributes, feel free to add yours)

SQL

DROP schema if exists tmp_yqmTD CASCADE;
create schema tmp_yqmTD;

-- Step 1
CREATE DIMENSION TABLE IF NOT EXISTS  tmp_yqmTD.Calendar0 (DateX TIMESTAMP(DAY));
INSERT INTO TABLE tmp_yqmTD.Calendar0 VALUES ('2003-01-01 00:00:00'),('2003-01-02 00:00:00') ;

-- Step2
CREATE DIMENSION TABLE IF NOT EXISTS  tmp_yqmTD.Calendar AS 
SELECT 
	DateX as Date_for_Join,
	DateX as Date,
    year(DateX) as Calendar_Year,

    day(DateX) as Calendar_Day,
	cast(add_months(DateX,-12) as TIMESTAMP) Same_dayLastyear,
	cast(add_months(DateX,-24) as TIMESTAMP) Same_dayLast2year,

	month(DateX) as Calendar_Month,
	date_format(DateX,'MMMMM') as Calendar_MonthName,
	year(DateX)*100 + month(DateX)  as Calendar_YearMonth,
	
    date_format(DateX, 'u') as Calendar_day_of_week,
	date_format(DateX, 'EEE') as Calendar_day_of_week_Name, 

	date_format(DateX, 'w') as Calendar_week_of_year,
	year(DateX)*100 + cast(date_format(DateX, 'w') as int) as Calendar_YearWeek,
	
	date_add(DateX,-7) as SameDayPreviousWeek,
    year(DateX)*100 + cast(date_format(date_add(DateX,-7), 'w') as int) as Calendar_YearPreviousWeek,

	cast(month(DateX)/4 + 1 AS BIGINT) as Calendar_quarter,
	year(DateX)*100 + Calendar_quarter as Calendar_YearQuarter,
	
    date_format(DateX, 'D') as Calendar_day_of_year, 
    if(Calendar_day_of_week BETWEEN 6 AND 7, true, false) as Calendar_Is_weekend,
	
	CAST(concat(year(DateX),"-" ,lpad(month(DateX),2,"0"),"-",Last_day( DateX),"", substring(DateX, 11,11) ) AS DATE) as Calendar_LastDayofMonth ,
	 if(cast(DateX as DATE)=Calendar_LastDayofMonth , 1 , 0) as Calendar_Is_LastDayofMonth
FROM tmp_yqmTD.Calendar0;