Different options for copying items in a Power BI Workspace.

Copy_PowerBI_WorkspaceItems.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "powershell",
            "display_name": "PowerShell"
        },
        "language_info": {
            "name": "powershell",
            "codemirror_mode": "shell",
            "mimetype": "text/x-sh",
            "file_extension": ".ps1"
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "<img src=\"https://app.powerbi.com/13.0.10537.111/images/PowerBI96x96.png?raw=true\" width=\"10%\"> ➕\r\n",
                "<img src=\"https://github.com/Microsoft/azuredatastudio/blob/master/samples/notebookSamples/Graphics/AzureDataStudioLogo.png?raw=true\" width=\"10%\"> = ❤\r\n",
                "\r\n",
                "# Copying Workspaces with PowerShell\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "4c4ef8d1-f127-4267-b80c-470a85825855",
                "tags": [
                    "hide_input"
                ]
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "If you don't already have the Power BI PowerShell module installed, you can do that now by running the `Install-Module` command below."
            ],
            "metadata": {
                "azdata_cell_guid": "a60e8cfa-0259-4ea3-b2ee-5a1f57c51bf6"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Install-Module -Name MicrosoftPowerBIMgmt -Scope CurrentUser"
            ],
            "metadata": {
                "azdata_cell_guid": "b2142fb1-b0ea-4765-a7c8-55d16519de61",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                "Before you can get any of the Power BI cmdlets to work, you first need to login to your tenant.  \r\n",
                "\r\n",
                "Once you login, your credentials will be used for all subsequent Power BI commands in this Notebook.  After running the command below, you should not have to run `Login-PowerBI` again until you either: A) Close & re-open this Notebook.  B) run the `Logout-PowerBI` command."
            ],
            "metadata": {
                "azdata_cell_guid": "fddc1b59-9116-48be-a27b-2e6beb3b00e9",
                "tags": []
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Login-PowerBI"
            ],
            "metadata": {
                "azdata_cell_guid": "139c0111-976b-4bd1-a4a4-97dfe4d13b41",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": 4
        },
        {
            "cell_type": "markdown",
            "source": [
                "Here's a list of all the PowerShell cmdlets available for Power BI."
            ],
            "metadata": {
                "azdata_cell_guid": "5b8cdd56-c570-403e-ada2-7cae4073c14f",
                "tags": [
                    "hide_input"
                ]
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-Command -Module MicrosoftPowerBI* -CommandType Cmdlet"
            ],
            "metadata": {
                "azdata_cell_guid": "3cd36849-2948-4e53-ae58-9f0977811220",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "\nCommandType     Name                                               Version    Source                                   \n-----------     ----                                               -------    ------                                   \nCmdlet          Add-PowerBIDataset                                 1.0.802    MicrosoftPowerBIMgmt.Data                \nCmdlet          Add-PowerBIEncryptionKey                           1.0.802    MicrosoftPowerBIMgmt.Admin               \nCmdlet          Add-PowerBIRow                                     1.0.802    MicrosoftPowerBIMgmt.Data                \nCmdlet          Add-PowerBIWorkspaceUser                           1.0.802    MicrosoftPowerBIMgmt.Workspaces          \nCmdlet          Connect-PowerBIServiceAccount                      1.0.802    MicrosoftPowerBIMgmt.Profile             \nCmdlet          Copy-PowerBIReport                                 1.0.802    MicrosoftPowerBIMgmt.Reports             \nCmdlet          Copy-PowerBITile                                   1.0.802    MicrosoftPowerBIMgmt.Reports             \nCmdlet          Disconnect-PowerBIServiceAccount                   1.0.802    MicrosoftPowerBIMgmt.Profile             \nCmdlet          Export-PowerBIDataflow                             1.0.802    MicrosoftPowerBIMgmt.Data                \nCmdlet          Export-PowerBIReport                               1.0.802    MicrosoftPowerBIMgmt.Reports             \nCmdlet          Get-PowerBIAccessToken                             1.0.802    MicrosoftPowerBIMgmt.Profile             \nCmdlet          Get-PowerBIActivityEvent                           1.0.802    MicrosoftPowerBIMgmt.Admin               \nCmdlet          Get-PowerBIActivityEvents                          1.0.788    MicrosoftPowerBIMgmt.Admin               \nCmdlet          Get-PowerBICapacity                                1.0.802    MicrosoftPowerBIMgmt.Capacities          \nCmdlet          Get-PowerBIDashboard                               1.0.802    MicrosoftPowerBIMgmt.Reports             \nCmdlet          Get-PowerBIDataflow                                1.0.802    MicrosoftPowerBIMgmt.Data                \nCmdlet          Get-PowerBIDataflowDatasource                      1.0.802    MicrosoftPowerBIMgmt.Data                \nCmdlet          Get-PowerBIDataset                                 1.0.802    MicrosoftPowerBIMgmt.Data                \nCmdlet          Get-PowerBIDatasource                              1.0.802    MicrosoftPowerBIMgmt.Data                \nCmdlet          Get-PowerBIEncryptionKey                           1.0.802    MicrosoftPowerBIMgmt.Admin               \nCmdlet          Get-PowerBIImport                                  1.0.802    MicrosoftPowerBIMgmt.Reports             \nCmdlet          Get-PowerBIReport                                  1.0.802    MicrosoftPowerBIMgmt.Reports             \nCmdlet          Get-PowerBITable                                   1.0.802    MicrosoftPowerBIMgmt.Data                \nCmdlet          Get-PowerBITile                                    1.0.802    MicrosoftPowerBIMgmt.Reports             \nCmdlet          Get-PowerBIWorkspace                               1.0.802    MicrosoftPowerBIMgmt.Workspaces          \nCmdlet          Get-PowerBIWorkspaceEncryptionStatus               1.0.802    MicrosoftPowerBIMgmt.Admin               \nCmdlet          Get-PowerBIWorkspaceMigrationStatus                1.0.802    MicrosoftPowerBIMgmt.Workspaces          \nCmdlet          Invoke-PowerBIRestMethod                           1.0.802    MicrosoftPowerBIMgmt.Profile             \nCmdlet          New-PowerBIColumn                                  1.0.802    MicrosoftPowerBIMgmt.Data                \nCmdlet          New-PowerBIDashboard                               1.0.802    MicrosoftPowerBIMgmt.Reports             \nCmdlet          New-PowerBIDataset                                 1.0.802    MicrosoftPowerBIMgmt.Data                \nCmdlet          New-PowerBIReport                                  1.0.802    MicrosoftPowerBIMgmt.Reports             \nCmdlet          New-PowerBITable                                   1.0.802    MicrosoftPowerBIMgmt.Data                \nCmdlet          New-PowerBIWorkspace                               1.0.802    MicrosoftPowerBIMgmt.Workspaces          \nCmdlet          Remove-PowerBIReport                               1.0.802    MicrosoftPowerBIMgmt.Reports             \nCmdlet          Remove-PowerBIRow                                  1.0.802    MicrosoftPowerBIMgmt.Data                \nCmdlet          Remove-PowerBIWorkspaceUser                        1.0.802    MicrosoftPowerBIMgmt.Workspaces          \nCmdlet          Resolve-PowerBIError                               1.0.802    MicrosoftPowerBIMgmt.Profile             \nCmdlet          Restore-PowerBIWorkspace                           1.0.802    MicrosoftPowerBIMgmt.Workspaces          \nCmdlet          Set-PowerBICapacityEncryptionKey                   1.0.802    MicrosoftPowerBIMgmt.Admin               \nCmdlet          Set-PowerBIDataset                                 1.0.802    MicrosoftPowerBIMgmt.Data                \nCmdlet          Set-PowerBITable                                   1.0.802    MicrosoftPowerBIMgmt.Data                \nCmdlet          Set-PowerBIWorkspace                               1.0.802    MicrosoftPowerBIMgmt.Workspaces          \nCmdlet          Switch-PowerBIEncryptionKey                        1.0.802    MicrosoftPowerBIMgmt.Admin               \n\n\n"
                }
            ],
            "execution_count": 1
        },
        {
            "cell_type": "markdown",
            "source": [
                "Here are the cmdlets available for working with Workspaces"
            ],
            "metadata": {
                "azdata_cell_guid": "baf21b55-0c44-4707-b862-031c23056523",
                "tags": [
                    "hide_input"
                ]
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-Command -Module MicrosoftPowerBI* -Noun *Workspace*"
            ],
            "metadata": {
                "azdata_cell_guid": "965ecfac-644c-4dd7-a2ae-0d20e64d0119",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": "\nCommandType     Name                                               Version    Source                                   \n-----------     ----                                               -------    ------                                   \nCmdlet          Add-PowerBIWorkspaceUser                           1.0.802    MicrosoftPowerBIMgmt.Workspaces          \nCmdlet          Get-PowerBIWorkspace                               1.0.802    MicrosoftPowerBIMgmt.Workspaces          \nCmdlet          Get-PowerBIWorkspaceEncryptionStatus               1.0.802    MicrosoftPowerBIMgmt.Admin               \nCmdlet          Get-PowerBIWorkspaceMigrationStatus                1.0.802    MicrosoftPowerBIMgmt.Workspaces          \nCmdlet          New-PowerBIWorkspace                               1.0.802    MicrosoftPowerBIMgmt.Workspaces          \nCmdlet          Remove-PowerBIWorkspaceUser                        1.0.802    MicrosoftPowerBIMgmt.Workspaces          \nCmdlet          Restore-PowerBIWorkspace                           1.0.802    MicrosoftPowerBIMgmt.Workspaces          \nCmdlet          Set-PowerBIWorkspace                               1.0.802    MicrosoftPowerBIMgmt.Workspaces          \n\n\n"
                }
            ],
            "execution_count": 2
        },
        {
            "cell_type": "markdown",
            "source": [
                "As you can see, there's no cmdlet for **copying** the Workspace, it's actually a separate demo script.\r\n",
                "Use the code below to download & open it in another window."
            ],
            "metadata": {
                "azdata_cell_guid": "4e4cd5fa-955d-48bc-8f56-0e0ee42975af",
                "tags": [
                    "hide_input"
                ]
            }
        },
        {
            "cell_type": "code",
            "source": [
                "(Invoke-WebRequest https://raw.githubusercontent.com/microsoft/powerbi-powershell/master/examples/CopyWorkspaceSampleScript.ps1).Content |\r\n",
                "Out-File c:\\temp\\CopyWorkspaceSampleScript.ps1"
            ],
            "metadata": {
                "azdata_cell_guid": "59c2ec06-11a5-410e-9b55-bae89124bfc6",
                "tags": []
            },
            "outputs": [
                {
                    "output_type": "stream",
                    "name": "stdout",
                    "text": ""
                }
            ],
            "execution_count": 3
        },
        {
            "cell_type": "markdown",
            "source": [
                "### PART 3: Backup reports and datasets via Export of reports"
            ],
            "metadata": {
                "azdata_cell_guid": "6921924d-15c9-4db0-9640-708666bc6cdc",
                "tags": [
                    "hide_input"
                ]
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "\r\n",
                "Have a look at all the Power BI Workspaces you have access to."
            ],
            "metadata": {
                "azdata_cell_guid": "f73e2971-0e1a-4b93-a461-120f76fe7a80"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-PowerBIWorkspace | Format-Table -AutoSize"
            ],
            "metadata": {
                "azdata_cell_guid": "b81b7fd3-3697-4589-8db0-517ef04196d3",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": 4
        },
        {
            "cell_type": "markdown",
            "source": [
                "If you want to simply export all of the Power BI Reports from a Workspace, you could use something along the lines of the code below:"
            ],
            "metadata": {
                "azdata_cell_guid": "d574cb37-ba25-42e4-aaf0-1d8d9c3e277f"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-PowerBIReport -WorkspaceId (Get-PowerBIWorkspace -Name 'Prod').Id |\r\n",
                "Export-PowerBIReport"
            ],
            "metadata": {
                "azdata_cell_guid": "4e000e57-36d8-4635-a2ff-eb1e29b12450"
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                "Run the PowerShell script you donwloaded and tell it which Workspace you want to copy."
            ],
            "metadata": {
                "azdata_cell_guid": "81b9f643-3c6c-4339-b1fd-4709f8120347"
            }
        },
        {
            "cell_type": "code",
            "source": [
                ".\\CopyWorkspaceSampleScript.ps1 -SourceWorkspaceName 'Test' -TargetWorkspaceName 'Prod'\r\n",
                "$temp_path_root = \"$PSScriptRoot\\pbi-copy-workspace-temp-storage\"\r\n",
                "$temp_dir = New-Item -Path $temp_path_root -ItemType Directory -ErrorAction SilentlyContinue\r\n",
                "\r\n",
                "# STEP 3.2: Get the reports from the source workspace\r\n",
                "$report = Get-PowerBIWorkspace | WHERE { $_.Name -eq 'BTC Testing 2' } | Get-PowerBIReport\r\n",
                "\r\n",
                "# STEP 3.3: Export the PBIX files from the source and then import them into the target workspace\r\n",
                "Foreach ($report in $reports) {\r\n",
                "   \r\n",
                "    $report_id = [guid]$report.id\r\n",
                "    $dataset_id = [guid]$report.datasetId\r\n",
                "    $report_name = $report.name+\"_\"+(Get-Date -UFormat %Y%m%d_%H%M)\r\n",
                "    $temp_path = \"$temp_path_root\\$report_name.pbix\"\r\n",
                "    \r\n",
                "    try {\r\n",
                "        Export-PowerBIReport -Id $report_id -OutFile \"$temp_path\" -ErrorAction Stop\r\n",
                "    }\r\n",
                "    catch {\r\n",
                "        Write-Warning \"= This report and dataset cannot be copied, skipping. This is expected for most workspaces.\"\r\n",
                "        continue\r\n",
                "    }\r\n",
                "}"
            ],
            "metadata": {
                "azdata_cell_guid": "a953cca9-b933-4305-9839-6f7830ccd63e",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": 3
        },
        {
            "cell_type": "markdown",
            "source": [
                "After the script is finished running, check to make sure all the **Reports** are showing up in the _target_ **Workspace**."
            ],
            "metadata": {
                "azdata_cell_guid": "0cb7243a-e3dc-4602-be85-9a7444a843c4"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-PowerBIReport -WorkspaceId (Get-PowerBIWorkspace -Name 'Prod').Id |\r\n",
                "Format-Table -AutoSize"
            ],
            "metadata": {
                "azdata_cell_guid": "755ef837-f843-4e3b-a599-f5355f632b7a",
                "tags": []
            },
            "outputs": [],
            "execution_count": 6
        }
    ]
}