Showing posts with label fiddler. Show all posts
Showing posts with label fiddler. Show all posts

Friday, February 2, 2018

Use reference tables for previews in Power BI

In my quest to provide customers with awesome reports I’ve been looking at why the performance in Power BI can be so slow at times. As I pointed out in earlier posts the OData.Feed can be quite the resource hog depending on the endpoint, so I’ve started to fall back on Json.Document to get much better preview times (but not without it’s drawbacks, like record count, connected tables and option set labels).

Just because I’m kinda new to this I like to perform a lot of the logic using M in the query editor, then I sew it all together with relationships and measures inside the reports I build. I noticed that the previews kept getting slower the more I logic I added, so get the ultimate magic tool and looked at what was happening.

First step: Json.Document(Web.Contents(“https://<org>.api.crm4.dynamics.com/api/data/v8.2/opportunities”))

image

OK, so it makes a request to Dynamics and retrieves the data in 3.435 seconds. That’s not fantastic, but it’s not bad.

Second step: = Source[value]

image

OK, no requests made. That’s what I would expect from it

Third step: = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

image

Still no requests made, that’s great

Fourth step: = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"actualclosedate", "actualvalue", "budgetamount", "estimatedclosedate", "estimatedvalue", "name", "opportunityid", "pzl_countryregion", "statuscode"}, {"Column1.actualclosedate", "Column1.actualvalue", "Column1.budgetamount", "Column1.estimatedclosedate", "Column1.estimatedvalue", "Column1.name", "Column1.opportunityid", "Column1.pzl_countryregion", "Column1.statuscode"})

Guess what? Still no requests.

Fifth step: = Table.TransformColumnTypes(#"Expanded Column1",{{"Column1.estimatedclosedate", type date}})

No requests. Great, so we only make 1 request per step, as long as we don’t merge in more columns? Not quite.

Refresh preview

image

Wait what? 5 requests, that’s 1 request per step. That makes the loading time increase from the original 3+ seconds into well over 15 seconds. I don’t have the answer for why it makes a new request for each and every step, but let me show you the consequence of this when you use OData.Feed instead of Json.Document

These are the steps taken:

  1. = OData.Feed(https://<org>.api.crm4.dynamics.com/api/data/v8.2/opportunities)
  2. = Table.SelectColumns(Source,{"actualclosedate", "actualvalue", "budgetamount", "createdon", "estimatedclosedate", "estimatedvalue", "name", "new_calculatedrevenue", "new_product", "opportunityid", "pzl_countryregion"})
  3. = Table.TransformColumnTypes(#"Removed Other Columns",{{"createdon", type datetime}})

image

Now that should be enough to make you think twice before reporting on Dynamics data in Power BI. As you can see it’s the metadata documents which takes the longest, but for some reason there are 4 requests to the “all opportunities” endpoint and the metadata endpoint, while there are 3 requests to the opportunity endpoint with reduced number of columns (fields). Also, there is one top=1000 request which completes before the last two. This tells me that when you refresh the data, the requests are made in sequence for each step, but the steps are performed in parallel. It almost smells like premature optimization.

So let’s break this down and do it step by step.

Step 1

image

OK, great, one request for the opportunities and one for the metadata, finally one to get the top 1000 records for the preview.

Step 2

image

Now we see that it makes the same three requests as it did in the previous step, but it also performs another set of requests against “all opportunities” and metadata before it performs a request against opportunities with reduced number of columns (fields).

Step 3

image

Finally, it does all the requests from previous steps, but it also adds another duplicate for this current step (which only converts the type, and doesn’t require collecting more data from the source system).

The magic workaround

Just by pure luck, when I tested this originally I got 4 duplicates of the “all opportunities”+select requests, and I couldn’t figure out where the 4th duplicate came in. Turns out, because I made a reference from this opportunity query into a new query, and the data is refreshed in all referenced queries as well as the parent(s).

That made me test one final thing. I created a new reference query after I had done all my logic in the main query.

image

Then I refreshed the data while in the reference query.

Step 1: = Query1

image

VoilĂ ! Only one set of requests, even though it’s technically the fourth step.

This means I just saved myself many duplicate requests, and the data loads much faster than for each subsequent step. This was only for 3 steps, imagine how much this will matter if you have 10, or 20 steps in your queries.

The wrap up

So in summary, when you refresh the preview for a query in a Power BI, all the requests are are repeated and added on for each step you have in that query. It’s an exponential growth of data gathering, and can make everything slow as nothing else.

Creating new queries with references removes the need to duplicate all the requests, and you can keep working with and massaging the data without having to worry about minutes of data reloading.

Thursday, August 17, 2017

Using Dynamics365 Customer Engagement admin API with PowerShell, part2

In my first post in this series we looked at scaffolding a new commandlet project. In this part we're taking a deep dive into how to do OAuth authentication against the new admin API. I'm going to document the process like I did it (without all the mistakes) to explain how I work when I try to figure things out. If you're just interested in the code then feel free to scroll down to the bottom or check out the last post in this series.

Also, check out part3 here.

Authenticating with the admin API using OAuth

The tools I've used for this module is Visual Studio (I'm using enterprise edition, community should suffice), PowerShell and Fiddler.

We're going to start where we left yesterday with fleshing out our AuthenticationHelper class with some more content. We are not going to play hackers from the 90's, so we're starting out with the documentation from Microsoft on how to authenticate against the new admin API.
This sample is pretty good, it works excellent for the admin services, especially in a web project or native app with background processing. We, on the other hand, are making a commandlet, so we are going to do things synchronously.
The first thing to do is to specify some connection details first. To be able to authenticate using OAuth we need to register an application in Azure AD first, and then we need the Application Id and a reply url in our application.
You don't need Azure AD premium for this, so feel free to create a free subscription to do this.

Registering an application in Azure AD

Navigate to the azure portal, and then go to Azure Active Directory, and the App Registrations blade. Click the + New application registration button to register a new app.
Enter a descriptive name for your app, and then select Native as the application type.
For the redirect Uri, specify the following (this has become a standard for multi-tenanted oauth applications):
urn:ietf:wg:oauth:2.0:oob



Next we go into the app settings, choose Required permissions, and then click the + Add button to add a new permission to the application.
From the Application Permissions choose Dynamics CRM Online (yes, they should update this name), and for permissions select Access CRM Online as organization users.
As you can see, this permission does not require admin approval, which means that for multi-tenant apps, a user can choose to use this app without requiring the approval of an AAD admin.


Finally, click the Grant Permission button to actually grant the permissions specified. If you don't do this then the permissions will not go into effect.

Adding configuration values to the project

Now that we've created an AAD App we can add the configuration variables to the AuthenticationHelper class. Add two static strings at the top of your class, one for App Id (clientid) and one for reply url (redirectUrl). In addition, we're adding a variable for the service resource and the authentication authority. The attributes should now look like this

private static string _clientId = "b954ae2b-8130-4b0e-a45a-d91ef9faec59";
private static string _redirectUrl = "urn:ietf:wg:oauth:2.0:oob";

private string _endpoint = null;
private string _resource = null;
private string _authority = null;

Next up we are going to create a method to identify the authentication Authority and the resource address. We could add these statically since we know which addresses we are going to use, but in part3 we will look at how to reuse this helper for the MSDYN365 Customer Engagement data api. Most of the method is a copy of the sample provided by Microsoft, but I've done a couple of tweaks to it to make it work better with our commandlet.

private string DiscoverAuthority(Uri discoveryUrl)
{
    try
    {
        Task.Run(async () =>
        {
            AuthenticationParameters ap = await AuthenticationParameters.CreateFromResourceUrlAsync(discoveryUrl);
            _resource = ap.Resource;
            _authority = ap.Authority;
        }).Wait();
        return _authority;
    }
    catch (HttpRequestException e)
    {
        throw new Exception("An HTTP request exception occurred during authority discovery.", e);
    }
    catch (Exception e)
    {
        throw e;
    }
}
What we're doing here is sending a web request to a discovery URL, which will return a 401 challenge which includes WWW-Authenticate headers (more information on Microsoft docs). These headers include the resource URL as well as the authority URL. The authority URL tells us which service URL we have to query to authenticate, and the resource is simply which resource we're requesting a valid token for.
Add the following line to the end of the ProcessRecord method in your cmdlet class to instantiate a new AuthenticationHelper object, which we're going to use for debugging.

var auth = new AuthenticationHelper(serverUrl);

Querying the discovery URL

If you're like me then you might wonder why we can't use the same URL as we're using to query the admin API, so I did a little debugging to figure it out. If you're not interesting skip right to the next part which will continue on our quest to authenticate.
First of all, if you want to replicate what I'm doing then make sure you've installed Fiddler, and enable HTTPS decryption.
To debug what's happening when I query the discovery URL I've added the following line to the public constructor, just beneath setting the endpoint (duh).

DiscoverAuthority(new Uri(_endpoint + "/api/aad/challenge"));
The appended path to the URL is specified in the Microsoft docs on authenticating against the admin services API, I'm going to elaborate on why I think this was a bad choice in the next part (please don't punish me).
Then instantiate a new auth class
Now add a breakpoint to the line added so we will get the chance to actually get some data. Now, to debug a cmdlet we have to add some parameters to the project. Right click the project in the right hand navigation, and select properties.
Go to the debug section, select the "Start an external program" option, and paste in the following:
C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

Inside the command line arguments add the following:

-NoLogo -Command "Import-Module '.\MSDYN365AdminApiAndMore.dll'; Get-DynamicsInstances -Location EMEA;"
This will launch a PowerShell window which runs our commandlet when we start debugging it, and we will be able to step through it as the code executes.

Now start up fiddler (you've remembered to enable HTTPS decryption I hope), and hit [F12] to stop capturing. Delete the requests that might have popped up already by marking them and hitting [Delete]. Resize the fiddler window so you can see additional windows at the same time.
Now start debugging your code in Visual Studio, and wait for it to hit the break point.
When the breakpoint hits and the execution pauses, find the PowerShell window that was started and bring it into the foreground. Then find the fiddler window and bring that to the front.
From the toolbar, left click and hold on the bulls eye which says "any process", and drag your mouse over to the PowerShell window. The PowerShell window should get highlighted, and you can release the mouse button. If you've done it correctly it should look something like this:
Press [F12] in your Fiddler window to resume capturing, and then go into Visual Studio and press [F5] to resume execution. If you've done everything correctly it should look like this in your fiddler window once the execution is finished.
The value of the WWW-Authenticate header is as follows:
Bearer authorization_uri=https://login.windows.net/common/oauth2/authorize,resource_id=https://adminapi.crm4.dynamics.com/

Now, breaking down the header values we can see the following:
uri=https://login.windows.net/common/oauth2/authorize
This one tells us that the uri for authenticating against the service is

resource_id=https://adminapi.crm4.dynamics.com/
This one tells us what the resoure id is. What we can see here is that the resource ID is different from the admin service URL specified in the Microsoft docs.

Q: Why is this important?
A: The resource id needs to be specified when you're requesting an OAuth token because it will issue a token which is valid for a service with the given resource id. If you're using the service URL then you will still get a token when you authenticate, but if you try to call the admin service API you will get a 401 unauthenticated error because the bearer token has a resource id which doesn't match the resource id of the API.

Now that we're done debugging, remove the DiscoveryAuthentication-line we added in the constructor so it doesn't interfere with what we're doing next.

Adding an authentication context and result

Again, we're utilizing the idea Microsoft had for the sample code. It's a good piece of code so there's no reason to reinvent it. I've simply made a few, small changes in the process of understanding how it works.
First of all we're going to add an authentication context. The constructor for AuthenticationContext takes an authority URL as a string input, which means that we have to run the DiscoverAuthority method before we can add a context.
To do this we're adding a public property which will return the authority URL or run the DiscoverAuthority method and return the result.


public string Authority
{
    get
    {
        if (_authority == null)
        {
            DiscoverAuthority(new Uri(_endpoint + "/api/aad/challenge"));
        }
        return _authority;
    }
}

Next, for generating an authentication context we're adding a new private AuthenticationContext
private AuthenticationContext _authContext = null;
Then add a public property to retrieve it. Notice that if we now try to get the authentication context, and the context is null, then it will instantiate a new authentication context and return that. Upon instantiation, if the Authority is null, it will also run the DiscoveryAuthority method which will propagate the private string values for resource and authority.

public AuthenticationContext AuthContext
{
    get
    {
        if (_authContext == null)
        {
            _authContext = new AuthenticationContext(Authority, false);
        }
        return _authContext;
    }
}

Now that that is out of the way, it's time for the actual authentication.
As earlier, we first create a private AuthenticationResult set to null.
private AuthenticationResult _authResult = null;

Then we create a method used to authenticate against the API.

private void Authorize()
{
    if (_authResult == null || _authResult.ExpiresOn.AddMinutes(-30) < DateTime.Now)
    {
        Task.Run(async () =>
        {
            _authResult = await AuthContext.AcquireTokenAsync(_resource, _clientId, new Uri(_redirectUrl),
            new PlatformParameters(PromptBehavior.Always));
        }).Wait();
    }
}
As we can see from this code we first check whether _authResult has a value, and then we see if the expiration date is less than thirty minutes. If either of those conditions are true we perform a new authentication against the resource we got earlier and assign it to _authresult. To acquire the token we also have to submit the clientId (application id) and redirecturl (reply URL) we got from registering the app in Azure AD in the previous post. If we don't specify these, or use invalid values, we will get a response with a very good description of what went wrong.

Finally we add a public property which calls the Authorize() method before returning _authresult. We don't need an if-clause in this because we're already checking inside the Authorize() method.

public AuthenticationResult AuthResult
{
    get
    {
        Authorize();
        return _authResult;
    }
}
Because we're using the public property of AuthContext here we can actually call retrieve the public AuthResult object without instantiating anything first, as all the objects used are propagated through their public properties.

Testing authorization

We'll now hook up fiddler and look at the authorization results. If you're not interested in this part you can jump straight into the next blog post to see how we can create our own custom HTTP message handler and send requests to the adminapi.

To test this part we'll add the following line to the end of the ProcessRecord() method in our commandlet class to trigger all the methods we've added.

var authResult = auth.AuthResult;
Now just add a break point to the new line, make sure you have fiddler started and ready, and then start debugging your code.
Hook fiddler to the new process, start capturing and continue the execution. If you've done everything right you will be presented with the following, hopefully familiar, window to authenticate:

Just fill out your credentials, and approve sign-in with Azure MFA (if applicable).
Next you will be presented with a window which says that the Azure App you registered earlier needs permission to access CRM Online as you, as well as sign you in and read your profile.
There is nothing scary about these permissions, as they don't get access to any of your data. If you already trust the application enough to log in with it you're using then these permissions only says that it will use your authenticated credentials to perform actions. Basically, it looks worse than it is.

Accept these terms, and wait for the execution to stop. Then, head back to fiddler and stop capturing traffic (hit [F12]). Look for one of the latest request (might be the latest, depending on how much you're capturing), host should be "login.windows.net" and the URL should be "/common/oauth2/token". The response here should be 200 OK, and if you check the headers it will look like this.
This response is then transformed into an AuthenticationResult object in our code, and we can use that to set a bearer token in our actual API request.
Finally, go back into the cmdlet class and remove the line which assigned AuthResult to clean up after debugging.

Wrap-up

In this post we've seen how we can build an authentication helper which queries a service for the correct resources, and finally authenticates against the authority to get a valid OAuth2 token. In the next part we'll finally perform the request against the MSDYN365 Customer Engagement admin API, and take a look at the response. Finally, we'll look at how we can extend this code to also work with the normal MSDYN365 data api.