Move data from Dynamics CRM via PowerShell to Power BI


Hi everyone,

Today, I introduce PowerShell scripts to move your Dynamics CRM data to Power BI via PowerShell so that you can see CRM data nicer way.

Prerequisites

Before starting, let’s setup all the prerequisites.

PowerShell

If you are running Windows 10, you are ready. If you are on prior versions, make sure your PowerShell is later than PowerShell V4.0. To confirm, open PowerShell, type “host” and run. There is Version column which shows you which version the PowerShell is. If it is not version 4 or later, please update your PowerShell.

Dynamics CRM PowerShell module

We use Microsoft.Xrm.Data.PowerShell module to get data from Dynamics CRM. Please follow this blog to install the module to your PowerShell environment. If you are on Windows 10, or you have installed WMF 5.0, you can simply run “Install-Module Microsoft.Xrm.Data.PowerShell” to install it from PowerShell gallery. Please see more details here.

Or you are also able to download from GitHub where we host releases. Please read how to install here.

Power BI PowerShell module

For Power BI interaction, we use Microsoft.PowerBI.PowerShell and Microsoft.ADAL.PowerShell. Please download these modules here and here.

Once you install all modules, please make sure you can find these modules by running following command in your PowerShell.

>Import-Module Microsoft.Xrm.Data.PowerShell
>Import-Module Microsoft.ADAL.PowerShell
>Import-Module Microsoft.PowerBI.PowerShell

Power BI provision

Next, setup or signup PowerBI.com as your Power BI destination. Go to http://www.powerbi.com and provision Power BI if you do not have any, If you already have one, you are able to use it. Free edition is fine for now. To signup, simple click “Sign In” button on the page and it will navigate you through.

Get ClientId and Authorizatin Name

The Microsoft.ADAL.PowerShell needs ClientId and Authorization Name to acquire access token. If you have no idea what they are, please do not mind for now and just follow the steps below to obtain them.

1. Go to https://dev.powerbi.com/apps?type=native

2. Click “Sign in with your existing account” and sign in by using the account you provisioned above.

3. In Step 2, enter like below.

image

4. In Step 3, check all checkboxes for now.

5. In Step 4, click “Register App” button which gives you “Client ID”. Please note it.

Create Power BI dataset

Now, you are ready to write scripts. First of all, let’s add a dataset to Power BI which stores CRM data.

1. Open PowerShell ISE.

2. Add following code. This lets you connect to PowerBI.

# Connect to your PowerBI
Connect-PowerBI –AuthorityName <yourdomain>.onmicrosoft.com `
-ClientId <obtained ClientID> `
-RedirectUri http://localhost/powershell  -ForcePromptSignIn

3. Add following code. This will create a dataset which includes table and columns.

# Define columns for Table
$col1 = New-PowerBIColumn -ColumnName Entity -ColumnType String
$col2 = New-PowerBIColumn -ColumnName Counts -ColumnType Int64
$col3 = New-PowerBIColumn –ColumnName Date –ColumnType DateTime
# Define table by using defined columns
$table1 = New-PowerBITable -TableName EntityCount -Columns $col1,$col2,$col3
# Define dataset by using defined table
$dataset = New-PowerBIDataSet -DataSetName CRMPowerBIDemo -Tables $table1

# Create Table
Add-PowerBIDataSet -DataSet $dataset

4. Run the script. Sign in by using Power BI account.

5. When completed, go to http://www.powerbi.com to see if the dataset created.

image

Insert Data from Dynamics CRM

Next, you retrieve Data from CRM.

1. Click New on PowerShell ISE to create new script.

2. Enter following code to get all Entity record counts from CRM Online.

# Connect to CRM Online
Connect-CrmOnlineDiscovery -InteractiveMode

# Connect to PowerBI

Connect-PowerBI –AuthorityName <yourdomain>.onmicrosoft.com `
-ClientId <obtained ClientID> `
-RedirectUri http://localhost/powershell  -ForcePromptSignIn

# Get PowerBI DataSet
$dataSet = Get-PowerBIDataSets | ? {$_.name -eq 'CRMPowerBIDemo'}
# Get All CRM entity definitions
$entities = Get-CrmEntityAllMetadata -conn $conn -EntityFilters Entity
$date = [System.DateTime]::Now.Date

# Count records only User/Team owned type
foreach($entity in $entities | ? {$_.OwnershipType -eq [Microsoft.Xrm.Sdk.Metadata.OwnershipTypes]::UserOwned})
{
$logicalName = $entity.LogicalName
$count = (Get-CrmRecordsCount -conn $conn -EntityLogicalName $logicalName -WarningAction SilentlyContinue)

if($count -eq 0)
{
continue
}
Write-Host $logicalName $count

# Create row and insert to PowerBI Table
$row = @{"Entity"=$logicalName;"Counts"=$count;"Date"=$date}
Add-PowerBIRows -DataSetId $dataSet.Id -TableName 'EntityCount' -Rows $row
}

3. Run the script.

Visualize data in Power BI

Lastly, create visuals in PowerBI.

1. Go to http://www.powerbi.com and login.

2. Select “CRMPowerBIDemo” dataset.

3. Click Table Visual from Visualizations.

image

4. Drag and drop fields from EntityCount table to Values.

image

5. Table will show the data.

image

6. Click any chart as you wish to change the visualization.

image

image

Summery

By using PowerShell, it is very easy to pull data from Dynamics CRM and import to Power BI.
In addition to importing data by using PowerShell, you can directly pull data from Dynamics CRM Online by using OData or Dynamics CRM connector from PowerBI.com, too! I will explain that in the future articles.

Ken

Comments (4)

  1. Azharuddin Mohammed says:

    What’s the benefit of using PS when I can do it from POWER BI with Odata?

    1. There might be several benefits but one of the benefit is to get snapshot of data. OData lets you query current latest data, but not historical data, as CRM doesn’t store all state.

  2. Silla says:

    Dear Nakamura,
    Great article, Can i achieve this on CRM on-premise?

    1. Yes you can, as PowerShell can retrieve data from CRM OnPrem and insert data to PowerBI. Please try and let me know if you have any issue.

Skip to main content