PowerBi - Attaching a Common Data Model Folder to a DataFlow - Error: path doesnt contain model.json

If you get the "The path doesn't contain model.json. Please provide the full path to the CDM folder including its definition file (its Model.json file) and try again." error, there can be many reasons for it.

Here are some things to try:

1. Make sure your url doesnt contain blob.core.windows.net.


1. When I used the desktop app StorageExplorer and copied the path to the model.json file, then the path looked like: https://xxxxxx.blob.core.windows.net/cds-crmuat1/model.json
2. The same file, the path through Azure portal's storage explorer was: https://xxxxxx.dfs.core.windows.net/cds-crmuat1/model.json.
   The 2nd path worked, but the first one didnt.
2. Make sure your permissions are correct:
   
1. In the screen shot above, what I had missed were:
1. Adding myself to the owner role on the storage account directly. Its not enough to be in here via inheritance, you need to add yourself directly.
2. Also I had to add myself to the "Storage Blob Data Owner" role.
2. The ones in blue are what the documentation asks you to do: https://docs.microsoft.com/en-us/power-bi/service-dataflows-connect-azure-data-lake-storage-gen2#grant-permissions-to-power-bi-services

Some other gotchas:

1. The storage account used for CDM needs to be the exact same storage account used by PowerBi. As of writing this blog, this is a limitation of PowerBi that I hope they will remove.
2. The storage account needs to be in the same tenant and also hosted in the same region as PowerBi.
3. If you encounter an error when trying to save a DataFlow, make sure that there is a container/filesystem named PowerBi in that storage account.

Useful resources:

Informatica's documentation is helpful: https://docs.informatica.com/integration-cloud/cloud-data-integration-connectors/h2l/prerequisites-to-use-cdm-folders-connector/prerequisites-to-use-cdm-folders-connector.html

Dynamics CRM - Alternate key fails with message "The statement has been terminated."

If you get the unhelpful message that the "Statement has been terminated.":


Look to see if you have a duplicate value in your key. Unfortunately, if you have more than 50k records, you cannot use fetchXml and either would need to load the data into a database or filter the list by date-range (but the latter option is not guaranteed to catch all the errors and so the db option is your best bet).

Here is a query to find dups based on accountNumber on account entity

 
 
   
   
   
     
   
   
 

It used to be one time that CRM used to throw an error message stating the key creation failed because of a duplicate key. In the latest version, its a rather unhelpful message.


 

git@github.com: Permission denied (publickey).

If you get the error "git@github.com: Permission denied (publickey).", try using HTTPS:
Under clone or download, "use https" instead of "use SSH".

Restarting a web-app using Logic Apps

Unfortunately its not straight forward and the help wasnt very helpful. So here are the steps and the pages I had to look up for getting the data.

1. Create a logic app and add whatever trigger you need. (I used a recurrence based trigger, but you could use a HTTP request, etc).
2.  Add a new step and then look for Azure Resource Manager. Once you click on it, search for "Invoke" and pick the "Invoke Resource Operation" action.
   
3. You will need to fill in the following parameters and here is how to find them:
   
4. Subscription: This should be available in the drop-down. Else, its your subscription Id.
5. Resource Group: The resource group in which your web-app resides
6. Resource Provider: Microsoft.Web
   This I figured out from this page: Resource providers for Azure services
   Microsoft.Web because I needed to manage an App Service.
   
7. Short Resource Id: this I figured out by going to the "Properties" section of my App Service and its the in the "ResourceId".
   
   The value will end up being something like "sites/xxxxxxx"
8. Action Name: Lets look at this param before the ClientAPIVersion.
   The function we want to invoke is "Restart" which I found via the REST API documentation pages: https://docs.microsoft.com/en-us/rest/api/appservice/webapps
   Restart: https://docs.microsoft.com/en-us/rest/api/appservice/webapps/restart
   You can check out all the other parameters that one could provide. In this case, we dont need any additional parameters.
9. Client Api Version: This is date value and you can get it from the Restart API documenation page. Its called "API Version" and can be found at the top of the page. In my case, as of writing of this post the value was "2019-08-01".
10. Final result will look like this:
    
11. Test it out by clicking run.


1. If it ran successfully, you should see a screen that looks like this
   
2. You should also check the activity log of the web-app to see if it did restart.
   

Restarting a redis cache

To restart a redis cache, here are the parameters you need for the Invoke operation:

Subscription: subscription in which the resource resides

Resource Group: resource group in which the resource resides.

Resource Provider: Microsoft.Cache

Short Resource Id: Redis/NameOfCacheInstance (can be found under properties of your cache instance)

Client API Version: 2016-04-01 (as of the writing of this post)

Action Name: forceReboot

Request Body: {"rebootType":"AllNodes"}

Querying Dynamics CRM using PowerBi - Query Folding

When you use the Common Data Service connector, then PowerBi will attempt to fold your query into a single call.

As an example:

When you look at the query in the Advanced Editor, this is what it shows:

let
    Source = Cds.Entities("https://xxxx.api.crm.dynamics.com/api/data/v9.1/", [ReorderColumns=null, UseFormattedValue=null]),
    entities = Source{[Group="entities"]}[Data],
    territories = entities{[EntitySetName="territories"]}[Data],
    #"Filtered Rows" = Table.SelectRows(territories, each [createdon] > #datetimezone(2019, 10, 1, 0, 0, 0, -6, 0)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"createdon", "name"})
in
    #"Removed Other Columns"

And this is the query that goes out (removed url encoded characters for legibility):

/api/data/v8.2/territories?$filter=createdon gt 2019-10-01T00:00:00-06:00&$select=territoryid,createdon,name&$top=1000

As you can see, even though the query was broken out over 4 steps, there was only one call that went out to CRM, which is very cool!

Merge options provided by Visual Studio DevOps

I really like how Visual Studio (devOps) shows a visual hint for each type of merge that can be done via its interface. Here they are:

Merge (no fast forward) Nonlinear history preserving all commits git merge pr where “pr” is the name of the branch.
Squash Commit (in AWS CodeCommit this is called Squash and Merge) Linear history with only a single commit on the target Deleting the source branch when squashing is recommended git merge pr --squash
Rebase (in AWS CodeCommit this is called Fast Forward Merge) Linear history with all commits rebased onto the target git rebase master git merge pr --ff-only
Semi-linear merge (in AWS CodeCommit this is called 3-way Merge) Rebase source commits onto target and create a two-parent merge git rebase master git merge pr --no-ff

For more info: 

Azure: https://devblogs.microsoft.com/devops/pull-requests-with-rebase/

AWS: Merge a pull request in an AWS CodeCommit repository - AWS CodeCommit (amazon.com)

PowerBi DataFlows–Filtering by Date

The cool feature of incremental refresh is only available to PowerBi Premium instances. So, what do the poor folk do? Well, we fake it!

The following is based on Dynamics CRM data, which provides the modifiedon field for all entities, but it can be extended to use any date field. You simply use the Table.SelectRows function to filter based on the ModifiedOn field. (the example shows how I did it on the “SystemUser” entity).

let
   Source = Cds.Entities(https://xxxxx.crm.dynamics.com, null),
   #"Navigation 1" = Source{[Group = "entities"]}[Data],
   #"Navigation 2" = #"Navigation 1"{[EntitySetName = "systemusers"]}[Data],
  #"Changed column type" = Table.TransformColumnTypes(#"Navigation 2", {{"modifiedon", type datetime}}),
   #"Filtered By Date" = Table.SelectRows(#"Changed column type", each DateTime.From([modifiedon]) >= DateTime.From("2018-01-01"))
in
   #"Filtered By Date"

Dynamics Ribbons

Base ribbon definitions can be found at: https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/customize-dev/export-ribbon-definitions

Account limits for Application Users in Dynamics 365

There has been considerable confusion on whether an "application user" needs a license or not. In June 2018, Microsoft has clarified that Application Users do not count towards the limits and do not use up licenses:

All application users are created with a non-interactive user account, however they are not counted towards the five non-interactive user accounts limit. In addition, there is no limit on how many application users you can create in an instance.

https://docs.microsoft.com/en-us/dynamics365/customer-engagement/admin/create-users-assign-online-security-roles#create-an-application-user
https://github.com/MicrosoftDocs/dynamics-365-customer-engagement/commit/9068938aa7ca91f12b8744518bf5071ec4514530#diff-184905bb3786c535b8e1eedb5ba54cf2R258

And for the steps to create the Application user, see: https://blog.aggregatedintelligence.com/2017/02/headless-authentication-against-crm-365.html

Modelling Dynamics 365 SystemUser hierarchy in PowerBI

The SystemUser entity in Dynamics 365 is a self referencing entity, that defines its hierarchy based on the parentSystemUserId field.

So how do you go about showing the hierarchy and utilizing it in report?

Here is one way that I found, that utilizes the DAX Path function.

1. Go to the data view and select the “SystemUser” table.
2. Add a new column and define your first field using the following function.
   ManagerHierarchy = Path(SystemUser[systemuserid],SystemUser[parentsystemuserid])
   
   This will add a column that lists all the managers of a systemuser, delimited by the pipe character “|”
3. Add another new column to show the depth of the current user
   HierarchyDepth = PATHLENGTH ( SystemUser[ManagerHierarchy] )
   
   This will add a column with integer values starting at 1 and going up to the max-depth of the record found in your system
4. Next, add columns, for each of the following functions (I went up to 5, as that was the max I had in my system)
   
   

   [ManagerFirstLevel] = LOOKUPVALUE (      SystemUser[fullname],      SystemUser[systemuserid],      PATHITEMREVERSE ( SystemUser[ManagerHierarchy], 2, TEXT ) )

   ManagerSecondLevel = if (SystemUser[HierarchyDepth] >= 3,      LOOKUPVALUE (      SystemUser[fullname],      SystemUser[systemuserid],      PATHITEMREVERSE ( SystemUser[ManagerHierarchy], 3, TEXT ) ), SystemUser[ManagerFirstLevel])

   ManagerThirdLevel = if (SystemUser[HierarchyDepth] >= 4,      LOOKUPVALUE (      SystemUser[fullname],      SystemUser[systemuserid],      PATHITEMREVERSE ( SystemUser[ManagerHierarchy], 4, TEXT ) ), SystemUser[ManagerSecondLevel])

   ManagerFourthLevel = if (SystemUser[HierarchyDepth] >= 5,      LOOKUPVALUE (      SystemUser[fullname],      SystemUser[systemuserid],      PATHITEMREVERSE ( SystemUser[ManagerHierarchy], 5, TEXT ) ), SystemUser[ManagerThirdLevel])

   
   Things to note:
   PathItem is used to get the value at a particular depth from the Path generated field (in this case SystemUser[ManagerHierarchy]). LookUpValue is then used to lookup that value’s fullname, so that it cant make for a pretty display value).
   In my case I had to use PathItemReverse, so that a depth of 2 would give me the first manager’s value and so on. If I just used PathItem, I would have had to reverse my depth values.
5. You now will have a SystemUser table with the names of each manager at the different levels. You could rename the columns, to have actual title names (Manager, Vice President, etc).
6. The final thing I did was to setup the hierarchy on the fields:
   
   By doing this, you can get drill downs working. The hierarchy settings shown above will start at the bottom most level (employee) and as you click the drill down button, it will move up the hierarchy. If instead you wanted it to work the other way, you can just reorder it in reverse.

Things to know:

1. This works only if you know the number of levels you are dealing with. So, the same concept would theoretically break on Accounts, which could be nested many levels deep.
2. This was modeled after this power-pivot sample: https://www.daxpatterns.com/parent-child-hierarchies/