Follow Us:
If you’ve been paying any attention at all to the world of business intelligence lately, you’ve seen the shift of Microsoft Power BI from the service built-in to Office 365 to the standalone offering from http://powerbi.com. There are various data sources available sources but one I use frequently is Dynamics CRM Online. There have been various blogs that give tutorials and examples of getting your CRM online data into Power BI. I’ve written previous posts on gotchas when using Power Query with CRM Online in part 1 and part 2. In today’s post, I want to show you how to leverage the Power BI service and construct your data source so that you can have date tables and OData sources that you can configure scheduled refreshes on.
I had my Excel-based file with all my data models and Power View dashboards published to Power BI in Office 365, cool. But, I couldn’t refresh the Odata data source for CRM online.
Boo! Besides, if you’re seen the writing on the wall, they haven’t made any updates I can see to Power BI in Office 365. But they make continuous updates to the standalone Power BI service and are constantly adding new content packs for data sources. During its preview and initial release of this service, I don’t believe it was usable for my specific needs and complex DAX calculations I was doing. While the overall service was great, it didn’t support data labels and had many other limitations.
If you need more info on DAX, see these resources:
Over the past few months, all that has changed. There have been a ton of improvements that really make it very flexible and usable, and have features / chart types that aren’t in Excel (2013 but are coming to Excel 2016). So I wanted to leverage these new abilities with my dataset and see what I could do and if it would offer me. What you do is use the free download program Power BI Desktop, build your datasets and publish to Power BI for use.
The reason we care about this is because for those times when you need to use a date table and you want to use that date table in Power BI. What’s a data table? In Excel, you can create a quick pivot table and have built-in time intelligence for grouping by days, months, etc. and filter by last week, last year, and so on. When you use a Power Pivot data model or dataset in Power BI, you have to build your own time intelligence. You do this with a date table. It’s a flat table that contains a row record for every day for a given set of years (depending on the years you need for your dataset). There are columns that carve this date into different needed formats as desired like week ending, month, year, quarter, etc.
Originally, I created a date table in Power BI desktop using a query function. This worked great and auto-generated all the data I needed for my query. After uploading to Power BI, when I tried to schedule a refresh it said it required an on-premise gateway to connect to. What? Basically, because I generated my date table with a query, every time you refresh the data source it needed to refresh the date table. It saw this function query as something it needed to connect on-premise for, and therefore wouldn’t work.
In case you want the query to play with, here is the formula to use (this is not my own and I believe it was taken from here):
let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date)
in
InsertWeekEnding, #"Invoked FunctionCreateDateTable" = CreateDateTable(#date(2014, 1, 1), #date(2019, 1, 1), null) in #"Invoked FunctionCreateDateTable"
It looked like this in the editor:
After you publish this to Power BI, it loaded and I could see my reports but after going to the schedule refresh settings of the dataset, it wants to configure a gateway. This shouldn’t be necessary and is what we’re going to fix to allow us to refresh our CRM Online OData source automatically.
The idea here to make this work is that we need to EMBED the date table in the data model as a data source so Power BI doesn’t think we need to refresh it from an external source. You’ll actually see this if you look at the demo that Microsoft builds when you just load the default dashboards using your tenant’s org.
So how do we do that?
You can read more about importing Excel data into Power BI via these resources:
If you haven’t started using Power BI yet, I’d highly recommend you check it out. I’m still waiting for the Windows Phone app to release but the Windows 8 app works really nice. Please contact us if you’d like to learn more about how Power BI can help you analyze Dynamics CRM!
The complementary paper includes over 12 years of research, recent survey results, and CRM turnaround success stories.
Request Download
This 60-second assessment is designed to evaluate your organization's collaboration readiness.
Learn how you rank compared to organizations typically in years 1 to 5 of implementation - and which areas to focus on to improve.
This is a sandbox solution which can be activated per site collection to allow you to easily collect feedback from users into a custom Feedback list.
Whether you are upgrading to SharePoint Online, 2010, 2013 or the latest 2016, this checklist contains everything you need to know for a successful transition.