Andre Software Development

Intelligent Solutions for the Future


Power BI Desktop - Fiscal Comparison Part 3 - The all important measures

In Part 1 we covered the setup for the Fiscal Comparison and is pre-requisite to this post.

In Part 2 focuses on the creation of the Date Range tables for our measures.

 

And here we are in Part 3, the final part in this series, here we create the measures that will use what we have created in the first two parts.

Overview

In this post we will create measures that will use the Date Range tables filtering information dynamically as time progresses and create some labels to indicate the days that have working days that have passed with regards to your sales.

So...

Lets get started...


Calendar Measures:

Previously we created the 'Today" and "Start of..." measures, now we will use these as the basis for additional measures that allow the creation of labels and additional filtering capabilities.

First up, "Days Gone" measures:

Without having the date tables we created in Part 2, attempting the calculation of working days in a given month is difficult and tedious and then on top of that trying to do it for the current month is even worse.  They are possible of course, but having to do it for every measure is just messy and when you need to update anything, we, like anyone else, prefer to do it once and have the change apply everywhere where necessary.

The normal process for calculating these measures would be to calculate the number of working days in a given period or between a set of dates, which can result in some complex queries, but you will see the simple calculations we make below.

  • Month Days Gone
          .MonthDaysGone = CALCULATE(COUNTROWS(DATES_MTD), 'Calendar'[.IsWorkingDay] = 1)
    Here we simply count the number of rows in the "DATES_MTD" table with a filter against the ".IsWorkingDay" column within the Calendar table.  The same applies to the rest of the periods.
      
  • Quarter Days Gone
          .QuarterDaysGone = CALCULATE(COUNTROWS(DATES_QTD), 'Calendar'[.IsWorkingDay] = 1)
  • Year Days Gone
          .YearDaysGone = CALCULATE(COUNTROWS(DATES_YTD), 'Calendar'[.IsWorkingDay] = 1)

   
Second, "Days Gone %" measures"

Many users like the visual of percentages, so why not give it to them?

  • Month Days Gone %
          .MonthDaysGone% = DIVIDE(CALCULATE(COUNTROWS(DATES_MTD), 'Calendar'[.IsWorkingDay] = 1), CALCULATE(COUNTROWS(DATES_MTH), 'Calendar'[.IsWorkingDay] = 1))
  • Quarter Days Gone %
          .QuarterDaysGone% = DIVIDE(CALCULATE(COUNTROWS(DATES_QTD), 'Calendar'[.IsWorkingDay] = 1), CALCULATE(COUNTROWS(DATES_QTR), 'Calendar'[.IsWorkingDay] = 1))
  • Year Days Gone %
          .YearDaysGone% = DIVIDE(CALCULATE(COUNTROWS(DATES_YTD), 'Calendar'[.IsWorkingDay] = 1), CALCULATE(COUNTROWS(DATES_YR), 'Calendar'[.IsWorkingDay] = 1))

  
Here we divide the counts of the "To Date" and "Full" periods and get a percentage.
REMEMBER: To set the data types of measures you create, particularly percentage values.

Again this is dynamic and simply... set and forget :)

  

Finally, top of the measures to the Calendar table, lets and a few user friendly labels based on the above:

  • Month Days Gone label:
          .Label.MTD.DaysGone = "MTD - Days Gone: " & [.MonthDaysGone] & " (" & FORMAT([.MonthDaysGone%], "0%") & ")"
  • Quarter Days Gone label:
          .Label.QTD.DaysGone = "QTD - Days Gone: " & [.QuarterDaysGone] & " (" & FORMAT([.QuarterDaysGone%], "0%") & ")"
  • Year Days Gone label:
          .Label.YTD.DaysGone = "YTD - Days Gone: " & [.YearDaysGone] & " (" & FORMAT([.YearDaysGone%], "0%") & ")"

  
Result of above should look something like this:

 

Sales Data Measures and Filters

 

By know you should have a really good understand of the capabilities and the benefits of the Dates tables, but as a brief example of how to filter your sales data for example, we have included a few examples of what you can do.

Please note that these are crude examples and you will need to update accordingly.

  • Sales for the Month To Data:
          Sales: MTD = CALCULATE(SUM('SalesTable'[ValueColumm]), DATES_MTD[Date])
  • Percentage of Sales against the Budget for the Quarter 
          Sales: Fiscal Quarter % to Budget = 
    	CALCULATE(DIVIDE(SUM('SalesTable'[value]), SUM('SalesTable'[budgetvalue])), DATES_QTR[Date])
  • Sales for current month period vs the sales achieved in the same month Last Year.
          Sales: Full Month % to Last Year = 
    	DIVIDE(CALCULATE(SUM('SalesTable'[value]), DATES_MTD[Date]), CALCULATE(SUM('SalesTable'[value]), DATES_MTH_LY[Date]))


There are many more that you can create and by using those dynamic tables.

 

And that's a wrap...

 

This was a short post but it does provide a big launch pad to generate some detailed reporting which will no doubt impress those that need to be in the know about the status of the business and its performance.

We hope that the information provided in this series helps many who followed it to identify additional measures and capabilities of Power BI.

 

Please comment and let us know what you would like to see next.

Till the next post/series, have fun with Power BI!

Power BI Desktop - Fiscal Comparison Part 2 - Configuring the Date Range tables

In Part 1 we covered the setup for the Fiscal Comparison and is pre-requisite to this post.

In Part 3, the final part in this series, we create the measures that will use what we have created.

Part 2 focuses on the creation of the Date Range tables for our measures.

Some Background info...

  
Selecting data based on date ranges can result in some very complex DAX expressions that while do work, can also cause some headaches in the long run.  This is where we came up with the idea of dynamics tables that update on refresh based on the simple measures we have already defined that to retrieve the relevant dates without us having to worry about it.  Set and Forget.  A bonus for anyone having to deal with those archaic spreadsheets and macros that require constant copy/paste.

These tables are in effect, rolling Current Month, Current Quarter and Current Year (Fiscal or Calendar - based on what you have configured) tables that will, upon refresh, update and any measure using them will update and report accordingly.

This is great solution especially once you publish to the Power BI service and enable the regular refresh through your personal or enterprise gateway, users will then see the latest information for the up-to-date period without worry that expressions on your measures need to be updated.

Agreed, you could merge these expressions into each of the relevant measures, but, that would mean you would be duplicating that long expression into every measure that needs the filter... which means if we need to make a change, like the fiscal period of a company changes because of... say... a takeover or merger and the new parent company has a different fiscal period (poor example, but you get the point) you will have to have update EVERY measure, so why not limit the locations that require this change?

Beyond that, we can get started...
   

How to... Configure the Dynamic Date Range Tables...
   

Summary of points we will cover:

  • Generation of "To Date" tables for the Month, Quarter and Year of the Current and Last Year periods
  • Generation of "Full" tables for the Month, Quarter and Year of the Current and Last Year periods

When adding any of the below, use "Modeling" then "New Table"

 

Month

  • "Month To Date" current year:
          DATES_MTD = DATESBETWEEN('Calendar'[Date], [.StartOfMonth], [.Today])
  • "Month To Date" last year:
          DATES_MTD_LY = DATESBETWEEN('Calendar'[Date], [.StartOfMonth LY], [.Today LY])
  • "Full Month" current year:
          DATES_MTH = DATESINPERIOD('Calendar'[Date], [.StartOfMonth], 1, MONTH)
  • "Full Month" last year:
          DATES_MTH_LY = DATESINPERIOD('Calendar'[Date], [.StartOfMonth LY], 1, MONTH)

Quarter

  • "Quarter To Date" current year:
          DATES_QTD = DATESBETWEEN('Calendar'[Date], [.StartOfQuarter], [.Today])
  • "Quarter To Date" last year:
          DATES_QTD_LY = DATESBETWEEN('Calendar'[Date], [.StartOfQuarter LY], [.Today LY])
  • "Full Quarter" current year:
          DATES_QTR = DATESINPERIOD('Calendar'[Date], [.StartOfQuarter], 3, MONTH)
  • "Full Quarter" last year:
          DATES_QTR_LY = DATESINPERIOD('Calendar'[Date], [.StartOfQuarter LY], 3, MONTH)

Year

  • "Year To Date" current year:
          DATES_YTD = DATESBETWEEN('Calendar'[Date], [.StartOfYear], [.Today])
  • "Year To Date" last year:
          DATES_YTD_LY = DATESBETWEEN('Calendar'[Date], [.StartOfYear LY], [.Today LY])
  • "Full Year" current year:
          DATES_YR = DATESINPERIOD('Calendar'[Date], [.StartOfYear], 1, YEAR)
  • "Full Year" last year:
          DATES_YR_LY = DATESINPERIOD('Calendar'[Date], [.StartOfYear LY], 1, YEAR)

 

So... What happens?

  • Each of the "...To Date" expressions look at the "Date" column in our Calendar table, and using the relevant "Start of..." and ".Today" measures generate the range of dates for that period.
  • Each of the "Full..." expressions again look at the "Date" column in our Calendar table, and beginning with the relevant "Start of..." measure and stipulating a MONTH or YEAR value to generate the full period in which the date range should be generated.

Using the "Start of..." measures we created, ensures that no matter what MONTH, QUARTER or YEAR we are actually in, those ranges will never be incorrect.  Due to the fact that when you move over into a new month, quarter or year, the "Start of..." measures are updated on the refresh and hey presto your ranges are updated.

And of course with your Calendar dates based on your imported query, everything ties in together nicely

 

Note:  Remember to hide these tables from the Report View as you wont need their data in the report view, but you will still be able to reference them in your measures you create.

 

And finally... the relationships...

 

  • Connect the "Date" column from each of your new date range tables to the "Date" column in your calendar.
  • There is one issue with the relationships though, as "One to one (1:1)" relationships they skew the measures we create later on, and here comes the manual labour part, you need to update each relationship manually to a "Many to one (*:1).  You can leave everything else as the same.
  • This occurs due to there been only one column and a single, unique value for each date in the tables and as such Power BI Desktop automatically sets the relationship to one we don't want.

  • Ultimately once complete, you should have something similar to the following:

 

Here we come to the conclusion of Part 2 of this series.

Next stop, the Measures, see you there.

Power BI Desktop - Fiscal Comparison Part 1 - Setting up for the comparisons...

In Part 1 of this series we will cover the basic measures we will require for the rest of the series to enable or intelligent/dynamic filtering for the relevant fiscal periods.

The fiscal period referenced in this series as well as previous posts is from July to June.  We will highlight what you will need to change if your fiscal period is different.  While we are based in Australia and the most common fiscal period is the Tax year, there are differences between organisations and the expressions are easily adjusted.

Shall we begin...

 

Assumptions:

  • You have an existing imported query (live connections are not supported when creating additional tables)
  • You are familiar with Power BI Desktop interface

Prerequisites:

  • You have generated a Calendar table based on your Fiscal Calendar - We walkthrough this process in the post: Power BI Desktop - Calendar with Fiscal Information
  • You have a calculated column in your calendar table which identifies your working/business days, if not you can use the following expression to do so, assuming you have a "DayOfWeek" column in your "Calendar" table:
           .IsWorkingDay = IF (NOT('Calendar'[DayOfWeek] = "Saturday" || ('Calendar'[DayOfWeek] = "Sunday")),1,0)
    You can view the full post here: Power BI Desktop - Working Days and Holiday Calendars

Notes:

  • You do not need the Holiday Calendars for this series, although it is useful for reporting if you need it.
  • If everyday is a working day for your business, this series does not cater for it, but you can make the changes to the queries as required.

 

Setting up for the comparisons...


To perform the Year-on-Year comparisons we need setup some base measures that will govern the dynamic generation of Date Range tables we will build in part 2.

NOTE:  We are only working with our Calendar table in this post, we have hidden all these measures from the Report view and you can choose to do so.

  • First, we need to know what the current date is, easy, create the following measure
          .Today = DATE(YEAR(NOW()), MONTH(NOW()), DAY(NOW()))
  • And then the same time last year
          .Today LY = DATE(YEAR(NOW())-1, MONTH(NOW()), DAY(NOW()))


These setup the foundation of the rest of the work to be done, as without these the dynamic tables... well... just aren't "dynamic" :)

Now for the period start dates:

  • Start of the Month, current and Last Year:
          .StartOfMonth = DATE(YEAR([.Today]),MONTH([.Today]),1)
    
          .StartOfMonth LY = DATE(YEAR([.Today LY]),MONTH([.Today LY]),1)
  • Start of the Quarter current and Last Year:
          .StartOfQuarter = DATE(YEAR([.Today]), SWITCH(INT((MONTH([.Today])+2)/3), 1, 1, 2, 4, 3, 7, 4, 10, 0), 1)
    
          .StartOfQuarter LY = DATE(YEAR([.Today LY]), SWITCH(INT((MONTH([.Today LY])+2)/3), 1, 1, 2, 4, 3, 7, 4, 10, 0), 1)
    
    The SWITCH is rather ugly, but basically it gets the current quarter and then based on that sets the first month of the quarter accordingly.
    You will need to tweak this if your fiscal quarters do not follow the typical quarters.  i.e.: if you quarter starts in November instead of October.
    (If there is a better solution, please send your comments.)

  • Start of the Year, current and Last Year:
          .StartOfYear = DATE(IF(MONTH([.Today])<=6,YEAR([.Today])-1,YEAR([.Today])),7,1)
    
          .StartOfYear LY = DATE(IF(MONTH([.Today LY])<=6,YEAR([.Today LY])-1,YEAR([.Today LY])),7,1)
      

You can perform a sanity check of the dates by adding the measures to Multi-Row Card:

                

As you can see we built this this month and thus all the dates are pretty much the same as our Month, Quarter and Fiscal Year all start at the same point of the year.  
Remember this is still going to show a correct calendar date, even though it is FY2018, at the time of writing, it is not going to change the year, that will just mess with everything.
Its just our filtering for the "Year" is based on the FISCAL YEAR column you would have created in the calendar prior to this process which would allow you to select "FY2018" while the date range is from 1 July 2017 to 30 June 2018.

In financial terms, it is FYE or Financial/Fiscal Year End 2018, meaning the financial or fiscal year ends in 2018.

  
Bonus... Simple Measures that are Useful:

  • Current (Fiscal) Month
    "Fiscal" is relative, it is still the same month, but at least in reporting is looks better.
          CurrentFiscalMonth = LOOKUPVALUE('Calendar'[FiscalMonth], 'Calendar'[Date].[Date], [.Today])
  • Current Fiscal Quarter
          CurrentFiscalQuarter = LOOKUPVALUE('Calendar'[FiscalQuarter], 'Calendar'[Date].[Date], [.Today])
  • Current Fiscal Year
          CurrentFiscalYear = LOOKUPVALUE('Calendar'[FiscalYear], 'Calendar'[Date].[Date], [.Today])

   

A final sanity check should produce the following:

 

 

This concludes Part 1.

In Part 2, we generate the Dynamic date range tables all based on these measures we have just created and thus eliminating the need to have complex IF and DATE range expressions in our measures in our data sets knowing that that measure will always be current and valid.

See you then.

Power BI Desktop - Working Days and Holiday Calendars

In the previous post Power BI Desktop - Calendar with Fiscal Information we covered the generation of a good calendar within Power BI desktop to enable some decent filtering of data in a imported queries.

This post will cover the implementation of some Working or Business Day calculations for the calendar which we have found is extremely useful when sales data comes in to the picture and a business does not operate over weekends or public holidays.

Having the ability to filter out anomalous data or you are just wanting to know your daily/weekly/monthly/quarterly/yearly averages are without some complex calculations... and there some very complex calculations out there... and maybe as to how many business days have gone by in the particular period.  All very pertinent reasons for this.

Well, let's get down to it.

Prerequisites:

How To... Working Day Identification:

  • Firstly, lets add two new columns
    • The first on is to identify if the date is in the current fiscal year, while not used in any of our calculations in this and any future posts, it can be a useful filtering option 
        
          .IsInCurrentFiscalYear = IF(YEAR(NOW())= [FiscalYear],1,0)
         
    • The second is to identity if the day is a working day or not, basically excluding Weekends from the selection: 
        
         .IsWorkingDay = IF (NOT('Calendar'[DayOfWeek] = "Saturday" || ('Calendar'[DayOfWeek] = "Sunday")),1,0)

        
    • NOTE: the "." dot prefix is to have the fields show at the top of the Fields list - more for ease of access while building your reports but not required.
    • Once created, right-click on each in the Fields navigation and select "hide in report view" if you do not want them visible to end users.

    • These changes should result in the something similar to this:

           

How to... Australian Public Holidays:

  • While this section is customised to Australian Public Holidays, it is pulling from an Xml based source and thus can offer the similar results.  Check out your local government resources for public holiday information.
  • NOTE: Xml data sources, at the time of writing, are currently NOT supported in the Power BI Online Service and will not refresh.
    To cater for this issue, simply right-click the applicable query in the query editor and untick "Include in report refresh"

  • Within the Query Editor, click on "Home" then "Enter Data":


       
  • In the "Create Table" window, give your table a name, in this example we are using "StateHolidays" and click "OK".

    and the result:
     

      
  • Instead of adding each step manually, you can overwrite the entire query for this table.
  • Go to "Home" then "Advanced Editor"


        
  • Replace the query

      
    With this:
       
        let
            Source = Xml.Tables(Web.Contents("http://www.australia.gov.au/about-australia/special-dates-and-events/public-holidays/xml")),
            Table0 = Source{0}[Table],
            #"Expanded events" = Table.ExpandTableColumn(Table0, "events", {"event"}, {"event"}),
            #"Expanded event" = Table.ExpandTableColumn(#"Expanded events", "event", {"date", "holidayTitle", "Attribute:year"}, {"date", "holidayTitle", "Attribute:year"}),
            #"Changed Type" = Table.TransformColumnTypes(#"Expanded event",{{"date", type date}, {"dateUpdated", type date}})
        in
            #"Changed Type"
    Like this:
     

      
    And click "Done"
      
    Ultimately, it stipulates the xml source for the public holidays, fetches the data, expands and select the relevant columns and updates some data types
      
    and the result:
      

      
  • Remember, while this includes holidays for ALL states in Australia, you can easily limit it down to a specific state if your business operates only within that particular state or states.
  • NOTE: This post does not cater for state specific calculations, but with the State information been available in the "jurisidictionName" column, this is easily attained.
     
  • And finally, create your relationship between the State Holidays table and Calendar table, not the data query as it doesn't need to know about the holidays, just your calendar.
       

How To... Working Day Modification:

  • Now you just have to modify the ".IsWorkingDay" column to exclude the public holidays.
  • A small addition to the if statement:
      
        && COUNTX(RELATEDTABLE(StateHolidays),1)<1
    Provides the required result. Here is the full expression:
      
        .IsWorkingDay = IF (NOT('Calendar'[DayOfWeek] = "Saturday" || ('Calendar'[DayOfWeek] = "Sunday")) && COUNTX(RELATEDTABLE(StateHolidays),1)<1,1,0)
     
  • You can achieve the same when custom options like business closure dates such as over the holiday period as well as religious holidays if these affect your particular business.

And that concludes this post.

In the next multi-part post, we will discuss structures to allow the dynamic filtering based on Month, Quarter and Year periods, for the current and previous years, all to remove the need of adding complex DATEADD, DATESBETWEEN or DATESINPERIOD functions to every measure you create... less pain more gain. :)

See you then.

 

Power BI Desktop - Calendar with Fiscal Information

Soo much searching through hundreds of posts and dozens of forums/blogs has resulted in weeks of work to cobble together on how to create a calendar with some... well... basic calendar information...  duh :)

This "cobbled' information is now brought to you in a format on a concise post for all to use and distribute.

This post will be one of several which will focus on the creation of Calendars in Power BI Desktop.  From Fiscal information to handling Public Holidays (Specific to Australia).  Additional building Measures that can use dynamic data ranges... more on that later.

So for now, lets get a Calendar setup with Fiscal Info.

Prerequisite:

  • You have a pre-existing query in Power BI Desktop with a Date column with the Data Type of Date
    This will not be covered in this post and assumes you know how to add a Data Source.
  • ONLY imported data will allow you to edit the queries and create tables - At the time of writing, this is still a limitation of Live Connections.
  • The Date column in your query is a "Calendar" year date, and has not been formatted to be a "Fiscal Date".  This may not make sense, but we have encountered some cases that a fiscal date is generated based on calendar dates.  Stranger things have been done I suppose.  It gets complicated and bit... weird... trying to explain it, but simply put, if it isn't Calendar dates - don't use it!

How to:

  1. Within the Power BI desktop main window, click "Modeling" and then "New Table"



    You will see something like this:



    Now lets have some fun :)
      
  2. Setup your basic table by using the following expression
      
    Calendar = CALENDARAUTO(6)
    "Calendar" is the name of our table (you can change this if you wish)

    "CALENDARAUTO(6)" generates a "Date" column in our table based upon the dates that are within the queries with in your Power BI project.  The "6" stipulates your FISCAL YEAR END MONTH.  In this case, that is June which is 6.  This is ultimately defines the START POINT of your Calendar, in this case July.
      
    While stipulating the fiscal value here is NOT critical, since all our reporting is based on Fiscal periods, there is no point in having our calendar start on any other month.

    Once done you should see a result similar to this:
    Note: The dates will format based on your system settings, i.e.: if you are in the US, they will be 07/01/2015 00:00:00 etc.


     
  3. Let's create our first column... "Year"
    We create both the standard Calendar columns as well as the Fiscal, as you may need them in the future.  You can always remove them later is you decide you do not need them, but I recommend leaving them as you never know when you might need them as well as they use soo little space there is little or no impact, useless you somehow have hundreds of years to deal with... :)

    Click "Modeling" and then "New Column"


      
    And use the following expression:
      
    Year = Year([Date])
    And the result - simple :)


     
  4. Now lets creates the rest of the columns the same way, here are the expressions:
      
    MonthNo = MONTH([Date])
    QuarterNo = INT(([MonthNo] + 2)/3)
    Quarter = "Q"&[QuarterNo]
    Month = FORMAT([Date], "MMMM")
    DayOfWeekNo = WEEKDAY([Date], 2)
    DayOfWeek = FORMAT([Date],"DDDD")
    Day = DAY([Date])
    MonthShort = FORMAT([Date], "MMM")
    MMM-YYYY = FORMAT([Date],"MMM-YYYY")
    
    FiscalYear = IF([MonthNo]<=6,[Year],[Year]+1)
    FiscalMonthNo = IF(MOD([MonthNo]+6,12)>0, MOD([MonthNo]+6,12),12)
    FiscalQuarterNo = INT(([FiscalMonthNo]+2)/3)
    FiscalQuarter = "Q"&[FiscalQuarterNo]
    FiscalMonth = FORMAT([Date],"MMMM")
    FiscalYYYY-MM = FORMAT([FiscalYear],"0000-") & FORMAT([FiscalMonthNo],"00")
    FiscalYYYY-QQ = FORMAT([FiscalYear],"0000")&"-Q"&FORMAT([FiscalQuarterNo],"0")
    FiscalMonthShort = FORMAT([Date],"MMM")
    FiscalYYYY = FORMAT([FiscalYear],"0000")
     
    Most of the these are self-explanatory and will give you 99% of what you would need for standard Fiscal filtering.

    Within the "FiscalYear" and "FiscalMonthNo" expressions you will notice the 6 been used, this is again related to our Fiscal Year End Month, if yours is different, make the necessary change.

    The end result should look something like this:


       
  5. Now you must sort your columns with "Month" information by the related "MonthNo" columns:
    Select your column, Click "Modeling" then "Sort by Column" and finally the relevant "MonthNo" column.  i.e.: Month -> MonthNo; FiscalMonth -> FiscalMonthNo; etc
    This is so that when you use these columns in your reports and dashboards that the are ordered correctly and not the default alphabetical ordering.  


      
  6. All that's left to do now is create a relationship between your date column in your query or queries to your new Calendar table and you will able to use the columns in the filtering of your queries.

    
This should be great start for greater things.

In the next post we talk about identifying working days and the usage of holiday calendars... see you then :)

Dynamics 365 / CRM 2016 On-Premise with IFD, ADFS & WAP

Dynamics 365 / CRM 2016 On-Premise with IFD, ADFS & WAP is something that has been a... conundrum for many.  Something that is seemly very easy by the way of the Microsoft documentation, which is detailed enough for an implementation, should work every time... but as many of us have discovered, it does not.

This post hopes to help that situation by addressing some of the common mistakes made in configuring IFD for CRM 2016 / 365 with ADFS & WAP.

This post does not cover a step-by-step process of implementation from start to finish, purely this has already been done many times over and the latest documentation from Microsoft covers everything you typically need.  We may refer directly to sections of the documentation as needed.

It does cover that most important parts with configuration suggestions and advice in the areas most commonly presenting problems.

So without further discussion...

Lets configure Dynamics 365 / CRM 2016 On-Premise with IFD, ADFS & WAP...


Microsoft Documentation can be found here for the step-by-step: https://technet.microsoft.com/en-us/library/dn609803.aspx

As previously stated, we may refer directly to sections of this documentation.

Please Note:  While the Microsoft Documentation link above for DOES NOT cover the implementation of Server 2016 with ADFS and WAP, the concepts are the same.  Plenty of documentation can be found for this in separate TechNet posts.

Things to Consider:

  • URLs
    • You will be using multiple URLs in your configuration, so DNS is obviously required.  How you have configured your DNS and with you use CNAMEs or ZONES is purely up to you.  We used CNAMEs as they are easier and typically you will have no need for sublevels within this URLs.  So keep it simple.
    • DO NOT USE your server name in the URLs, why?
      • for security,
      • for simplicity,
      • ask yourself:
        • What if my server dies?
        • What if my company is bought by someone else?
        • What if my server is physically relocated and I need to rename it?

          Having a CNAME means users only remember on URL and they are none the wiser when the backend changes.  

          Happy User... Happy Life...
    • Name your CRM organisations which is EASY for users to remember.
      Users WILL NOT remember ASD-CLD-CRMORG01, try CRM or CRMPROD or ASDCRM, for example.  Remember these are NOT server names, they are CRM ORGANISATION names and should reflect that.
    • Finally with regards to the WEB ADDRESSES in CRM, DO NOT USE anything that is an existing CRM Organisation name or an intended name.  The documentation covers this, and yet soo many make this very mistake.  DO NOT DO IT. It WILL cause you major HEADACHES!
  • SSL Certificate
    • We highly recommend Wildcard SSL Certificates, while they are more expensive, they are worth it, One certificate for all your first level URLs on your domain!
    • Yes, you can get single domain certificates on the cheap, but the more servers you add over time, those cheap options become costly and a royal pain to administrator.
    • Again, this is your choice, as we will be using at least 6 subdomains, the cost of wildcard starts becoming more attractive.  Yes, you can use the same URL for all the web addresses, but you still need one cert for the Web Addresses and another for the ONE organisation... Oh and not to mention a Certificate for the WAP server... and the ADFS server... and potentially the SSRS server if you choose to expose this as well.
        

Assumptions

This post assumes the required knowledge of ADFS, WAP and CRM configuration.

This post assumes the following is configured and working:

  • Server 2016 on all servers concerned
  • ADFS on Server 2016
  • WAP on Server 2016
  • Dynamics 365 (Dynamics CRM 2016) On-Premises
    • Preferably updated to at least 8.2


Configurations

  • Configure Claims-Based Authentication
    https://technet.microsoft.com/en-us/library/gg188575.aspx
    • Define server bindings
      • On the "Web Addresses" tab configure your web address(s)
        NOTE:  Ensure your Binding Type is HTTPS
        You can stipulate a single URL across all options or like we have done separate addresses for each:
        • Web Application Server:
               crmweb.domain.com
        • Organisation Web Service:
               crmorg.domain.com
        • Discovery Web Service:
               crmdisc.domain.com
        • Deployment Web Service:
               crmdep.domain.com
      • The separate URLs allow for us to scale our environment later on when we want to separate our CRM roles across different servers.
         
  • DNS Entries required Internally and Externally
    • Internal DNS entries point to your CRM Server(s)
    • External DNS entries point to your WAP server(s)
      crmweb.domain.com
      crmorg.domain.com
      crmdisc.domain.com
      crmdep.domain.com
    • Any Organisation based URLs
      • org1.domain.com
      • org2.domain.com
      • etc.
         
  • ADFS and WAP configurations
    • Two ADFS relying parties are required, for Internal and External IFD configurations. Based on the examples above, URLs used to configure the Relying Parties as below:

      • Internal 
        https://crmweb.domain.com/FederationMetadata/2007-06/FederationMetadata.xml
      • External:
        https://crmauth.domain.com/FederationMetadata/2007-06/FederationMetadata.xml

         

      The Internal URL will populate only the CRMWEB address while the External URL will populate the CRMAUTH , CRMDISC and organization URLs in the identifiers tabs.

    • You are required to configure WAP pass-through entries for each entry configured in the DNS.
      Perform the following steps to achieve this:
      ( Taken from https://support.microsoft.com/en-us/help/2988534/http-404-error-received-in-microsoft-dynamics-crm-2013-when-using-an-a )

      1. Log in as an Administrator on the Windows Server that hosts the WAP role
        
      2. Obtain the IDs of the WAP applications for CRM. In a Windows PowerShell window, type the following command:
                  Get-WebApplicationProxyApplication | Format-Table ID, Name, ExternalURL
        
      3. Note the IDs of the following CRM endpoints that were specified during the configuration of CRM Internet Facing Deployment
                  Web Application Server Domain
                  Discovery Web Service Domain
                  External Domain URL
                  Any Organisation URLs
        
      4. Run the following command via PowerShell, using the IDs obtained in the previous steps, to disable URL Translation in Response Headers
                  Set-WebApplicationProxyApplication -ID <WebApplicationServerDomainID> -DisableTranslateUrlInResponseHeaders
                  Set-WebApplicationProxyApplication -ID <DiscoveryWebServiceDomainID> -DisableTranslateUrlInResponseHeaders
                  Set-WebApplicationProxyApplication -ID <ExternalDomainURLID> -DisableTranslateUrlInResponseHeaders
                  Set-WebApplicationProxyApplication -ID <OrganisationURLID> -DisableTranslateUrlInResponseHeaders
       5. Restart ADFS Services on both the ADFS Server and Web Application Proxy Server
        

Updates to this Post

Over time we will elaborate on this post and provide more detailed information.  For now it is a quick guide with the key information and consideration points that supports the Microsoft Documentation.

 

Disclaimer

While we know our particular situation is specific to ours and much of the configuration is directly transferrable.  We make no assumption that any information provided herein will ultimately resolve any issues you may be or may not yet be experiencing.  This information is provided as a guide and based on experiences both within our business and with configurations for our clients.

If you require assistance or guidance in your configuration please comment on the post or contact us regarding services and support work here.

Welcome to the Andre Software Development Team Blog

Welcome...

 

...to the Blog for the ASD Team.

Here we will post our teams experiences... professional and personal... in the development world.

We look forward to providing new views and useful information across, Microsoft Dynamics 365, SharePoint Online, Office 365, Azure, UWP and much, much more!

 

We welcome you to stay and enjoy... favourite us, link to us - check out the bottom of the page.