Date Tables

The Business Edge data model includes several date dimension tables:

  • CalendarDate is the date dimension table that has an active relationship between the date column in the table and the OpenDate column in the EdgeEntity table. It also has 3 inactive relationships between CloseDate, CreateDate and ModifiedDate in the EdgeEntity table to the date column. In addition, all the date fields in the opportunity, pursuit and initiative tables are mapped to the date column as inactive relationships. By doing so, we can create DAX measures to invoke the inactive relationship explicitly to report different measures against one universal calendar date dimension in one report.  All our measures that involve date dimensions are calculated based on this date table.

The data model also provides three other date dimensions, EdgeEntityOpenDate, EdgeEntityCloseDate, and EdgeEntityCreateDate, for different reporting/filtering needs.

  • EdgeEntityOpenDate has an active relationship with OpenDate in the EdgeEntity table, and inactive relationships with the OpenDate in the opportunity and pursuit tables. It is essentially a duplicate of the CalendarDate table. The calendar date is used by measures provided out-of-the-box; it should always be used if you use out-of-the- box measures for reporting.
  • EdgeEntityCreateDate has an active relationship with the CreateDate in the EdgeEntity table.
  • EdgeEntityCloseDate has an active relationship with the CloseDate in the EdgeEntity table.

Remember that EdgeEntity contains all Opportunities, Pursuits and Initiatives records (unique IDs) and the OpenDate, CloseDate, CreateDate and ModifiedDate in this table are consistent with those dates in the Opportunity,  Pursuit and Initiative tables. So the EdgeEntityOpenDate date dimension can be used for reporting based on OpenDate in EdgeEntity, Opportunity, Pursuit and Initiative tables.  EdgeCreateDate and EdgeCloseDate behave similarly.

For the Task table, we provide four different date dimensions: TaskCompletionDate, TaskCreateDate, TaskDueDate, and TaskStartDate. Each date is mapped to CompleteDate, CreateDate, DueDate and StartDate as active relationships respectively.

 

Related topics