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)
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;