How to create a Dynamics GP Date Dimension Table for Power BI and Power Pivot

Creating a Dynamics GP Date Dimension Table for Power BI and Power Pivot

Posted By

Imagine we are curious about the number of Green Phones that Aaron Fitz purchased yesterday. Determining this information with Microsoft Dynamics GP will not pose much of a challenge to a report developer or savvy end-user with a fondness for SmartList.

But what if we need to know the number of Green Phones purchased by Aaron Fitz over the last five years, on Tuesdays that fell between the 40th and 50th weeks of each year? This one requires more thought and introduces the challenge of date intelligence in reporting.

(Incidentally, those familiar with Green Phones and Aaron Fitz will recognize both from the Dynamics GP sample company, TWO Inc. In my development environment, Aaron Fitz actually purchased more than 12,000 Green Phones on April 12, 2017. That was a bit of Dynamics GP developer humour – cut me some slack, my iPhone told me it was bedtime more than an hour ago!)

All entities in Dynamics GP have specific properties. For example, customers have names and addresses. Dates are no different: today is January 10. Today is also a Tuesday, a weekday, and falls in the second week of 2017. Unfortunately, Dynamics GP does not have a standard “calendar” table with which to associate these properties. I am a developer and I’m writing this after midnight, so I figured, “why not create one?”

create procedure dbo.sp_CreateCalendar
@dBeginDate date,
@dEndDate date
declare @d date

declare @aDateDim table
[DateKey] Date,
[DateDescription] varchar(20),
[CalendarYear] smallint,
[CalendarQuarter] smallint,
[CalendarQuarterString] varchar(5),
[CalendarMonth] smallint,
[CalendarMonthString] varchar(10),
[CalendarDay] smallint,
[CalendarDayString] varchar(10),
[DayOfYear] smallint,
[WeekOfYear] smallint,
[ISOWeekOfYear] smallint,
[IsWeekend] tinyint

The stored procedure above will return a result set in a simplified date dimension table (calendar). This is accomplished by providing a range of dates and utilizing SQL’s CONVERT and DATEPART functions.

set @d = @dBeginDate
while @d <= @dEndDate
insert into @aDateDim
datepart(quarter, @d),
'Q' + convert(varchar(5),datepart(quarter, @d)),
              when datepart(month,@d) = 1 then 'January'
              when datepart(month,@d) = 2 then 'February'
              when datepart(month,@d) = 3 then 'March'
              when datepart(month,@d) = 4 then 'April'
              when datepart(month,@d) = 5 then 'May'
              when datepart(month,@d) = 6 then 'June'
              when datepart(month,@d) = 7 then 'July'
              when datepart(month,@d) = 8 then 'August'
              when datepart(month,@d) = 9 then 'September'
              when datepart(month,@d) = 10 then 'October'
              when datepart(month,@d) = 11 then 'November'
              when datepart(month,@d) = 12 then 'December'
              else '' end,
              when datepart(weekday,@d) = 1 then 'Sunday'
              when datepart(weekday,@d) = 2 then 'Monday'
              when datepart(weekday,@d) = 3 then 'Tuesday'
              when datepart(weekday,@d) = 4 then 'Wednesday'
              when datepart(weekday,@d) = 5 then 'Thursday'
              when datepart(weekday,@d) = 6 then 'Friday'
              when datepart(weekday,@d) = 7 then 'Saturday'
              else '' end,
when datepart(weekday,@d) = 1 then 1
when datepart(day,@d) = 7 then 1
else 0 end
set @d = dateadd(day,1,@d)

The result set returned by the stored procedure can be incorporated in a Power BI or Power Pivot data model using Power Query’s ability to retrieve data from SQL Server. As a starting point, dates are provided for the “begin” and “end” parameters. The same result set can be exposed through an OData feed – we’ll leave that for a future article.

If Power BI is the tool of choice, the stored procedure call is made more flexible with the introduction of parameters.

Since no article involving Power Query is complete without at least one line of “M” code, the following change is made using the Advanced Editor.

Power Query prefers to append a time component to date columns. Before the date dimension table can be used with Dynamics GP data, the format of the Date Key column has to be transformed to reflect the “Date” option. This step must is applied to the associated date column in the imported Dynamics GP tables as well.

The date dimension table can now be associated with any Dynamics GP table with a date column. This is not as odd as it might first appear. Using the example of a sales transaction, each transaction is associated with a single customer. Along these lines, each transaction is associated with a single document date.

The date dimension table is now incorporated in the data model. Use it to group, sort, and analyze the associated Dynamics GP data. In the example below, it provides data for a slicer that defines the filter context for a map (everyone loves data on a map!).

With the application of some date intelligence, we can gain new insights into our data. We can spot date-based trends, make predictions, and adjust strategies accordingly.

Microsoft Dynamics GP webinar
Unlock more capabilities in Microsoft Dynamics GP! Watch our on-demand webinar on automated procurement in Dynamics GP.

BDO eBook - The Cloud Changes the Game

There are times in the course of your business when you have the opportunity to dramatically accelerate growth and improve day-to-day efficiencies. Recognizing