Setup of Time Dimesion in Custom Schema Definition

Questions & Answers
1

Hello,

I’m currently evaluating eazyBI private for the use in a clients BI environment and I was wondering how to set up the time dimension in a way that it works with relative date ranges (e.g. “30 days ago and today”).

The time dimension is already working, but when trying to enter a date range its returning no rows.

Here is my current setup for the time dimension:
dimension name: ‘Date by month’, type: ‘TimeDimension’, foreignKey: ‘day_fk’ do
hierarchy name: ‘Date by month’, primaryKey: ‘day_id’, hasAll: ‘true’, allMemberName: ‘All dates’ do
table schema: ‘os_dim’, name: ‘day’
level column: ‘year_id’, name: ‘Year’, type: ‘Integer’, uniqueMembers: ‘false’, levelType: ‘TimeYears’
level levelType: ‘TimeQuarters’, type: ‘Integer’, nameColumn: ‘quarter_name’, column: ‘quarter_id’, name: ‘Quarter’, uniqueMembers: ‘false’
level levelType: ‘TimeMonths’, type: ‘Integer’, nameColumn: ‘month_name’, column: ‘month_id’, name: ‘Month’, uniqueMembers: ‘false’
level levelType: ‘TimeDays’, type: ‘Integer’, nameColumn: ‘day_name’, column: ‘day_id’, name: ‘Day’, uniqueMembers: ‘false’
end
end

And here how the data of the time dimension table looks like:
day_id,day_name,month_id,month_name,quarter_id,quarter_name,year_id
---------------------------------------------------------------------------------------------------
20171024;“2017-10-24”;201710;“2017-10”;20174;“2017-Q4”;2017
20171023;“2017-10-23”;201710;“2017-10”;20174;“2017-Q4”;2017

Do I need to change the data structure in order to get it work? Or is something wrong with my dimension definition?

Cheers,
Henry

2

Hello,

eazyBI requires numeric values of year, month, day to work properly in the time dimension. I would suggest changing time dimension table structure and data.

eazyBI private installation includes foodmart schema as an example. You can also download MySQL, PostgreSQL, or MS SQL database example for this schema as well: https://github.com/OSBI/foodmart-data
Here is some information about custom schema definition in eazyBI:
https://docs.eazybi.com/display/EAZYBIPRIVATE/Custom+schema+definition

Here is time dimension table structrue with some example data:
time_id, the_date, the_day, the_month, the_year, day_of_month, week_of_year, month_of_year, quarter
367,01.01.97 00:00,Wednesday,January,1997,1,2,1,Q1
368,02.01.97 00:00,Thursday,January,1997,2,2,1,Q1
369,03.01.97 00:00,Friday,January,1997,3,2,1,Q1

With this time dimension table setup, here is an example schema definition for Time dimension:

  dimension 'Time', type: 'TimeDimension' do
    hierarchy has_all: true, all_member_name: 'All Time', primary_key: 'time_id' do
      table 'time_by_day'
      level 'Year', column: 'the_year', type: 'Numeric', unique_members: true, level_type: 'TimeYears'
      level 'Quarter', column: 'quarter', unique_members: false, level_type: 'TimeQuarters'
      level 'Month', column: 'month_of_year', type: 'Numeric', unique_members: false, level_type: 'TimeMonths'
      level 'Day', column: 'day_of_month', type: 'Numeric', unique_members: false, level_type: 'TimeDays'
    end
    hierarchy 'Weekly', has_all: true, all_member_name: 'All Time (Weekly)', primary_key: 'time_id' do
      table 'time_by_day'
      level 'Year', column: 'the_year', type: 'Numeric', unique_members: true, level_type: 'TimeYears'
      level 'Week', column: 'week_of_year', type: 'Numeric', unique_members: false, level_type: 'TimeWeeks'
    end
  end