Relation between WorkSpace, DataFlow , Dataset and Report of Power BI

[amsrivas]

Hi Folks!!

I want to create a report that will display relation ship between  WorkSpace, DataFlow , Dataset and Report of Power BI.

In order to acheive this, I need to create Dataset which having all such detail.

I have used PowerShell (Power BI CMDLETS) and used Get-PowerBIActivityEvent and other commands to fetch the detail.

Though i am able to acheive the data but doen't have relationship between them.

I.e. No relationship found between WorkSpace and dataflow.

Is there any command which detailed out these detail and reslationship as well.

Or Any other way to acheive this.

Thanks

Amit 

[avatorl]

>How we connect Dataflow -> Dataset?
This https://docs.microsoft.com/en-us/rest/api/power-bi/admin/datasets_getdatasettodataflowslinksingroupa... 

[amsrivas]

HI @avatorl 

Will this provide detail of all the dataset and dataflow.

OR

It will take one Datset as input and provide one output.

Also, is there a way to do this using Power shell?

Thanks

Amit Srivastava

[avatorl]

It will return links to dataflows for all datasets in a workspace

use Invoke-PowerBIRestMethod to call REST API methotds using cmdlets, for example

$url="https://api.powerbi.com/v1.0/myorg/admin/groups/{groupId}/datasets/upstreamDataflows"
$result = Invoke-PowerBIRestMethod -Url $url -Method Get

[amsrivas]

Hi @avatorl 

Thanks for reponse, but unfortunately i did get the resolution yet.

 While using this command 

$url="https://api.powerbi.com/v1.0/myorg/admin/groups/{groupId}/datasets/upstreamDataflows"
$result = Invoke-PowerBIRestMethod -Url $url -Method Get

where replaced groupid with Id of my group, it throws error as below.

Error

Invoke-PowerBIRestMethod : One or more errors occurred.
At line:1 char:1
+ Invoke-PowerBIRestMethod -Url $url
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : WriteError: (Microsoft.Power...werBIRestMethod:InvokePowerBIRestMethod) [Invoke-PowerBIRestMethod], AggregateException
+ FullyQualifiedErrorId : One or more errors occurred.,Microsoft.PowerBI.Commands.Profile.InvokePowerBIRestMethod

Please suggest!!

Thanks

Amit

[avatorl]

Did you replace {groupid} with your workspace ID? And are you logeed in (using Connect-PowerBIServiceAccount)? Not sure why there is 'Invoke-PowerBIRestMethod -Url $url' in your error description instead of '$result = Invoke-PowerBIRestMethod -Url $url -Method Get' and why it says line 1...

[avatorl]

Are you an admin (Office 365 Global Administrator or Power BI Service Administrator)?

[amsrivas]

Hi @avatorl 

I am just a normal Power BI user however running PowerShell with admin account.

Is this could be the issue?

Thanks

Amit 

[avatorl]

Did you read the documentation of this API function?
https://docs.microsoft.com/en-us/rest/api/power-bi/admin/datasets_getdatasettodataflowslinksingroupa...

Note: The user must have administrator rights (such as Office 365 Global Administrator or Power BI Service Administrator) to call this API or authenticate via service principal.

[amsrivas]

Hi @avatorl 

Now i have got data using Admin account but still have some query.

DataFlow->DataSet : To fetch detail, Invoke-PowerBIRestMethod will be used and need to pass workspaceid to it, can understand, if i want to retreive detail of all the dataflow, i need to pass workspaceid one by one, is there any way of writing script whic take all the workspaceid one by one and consolidate detail of all the dataflow into CSV. 

$url="https://api.powerbi.com/v1.0/myorg/admin/groups/{groupId}/datasets/upstreamDataflows"
$result = Invoke-PowerBIRestMethod -Url $url -Method Get

For e.g, if i have 10 workspace in organization, i need to pass workspaceid (groupid) again and again and its manual too, Is there any way of writing Powershell script via which i can pass all 10 workspaceid one by one at once programatically. 

Workspace -> Dataflow (the way you suggested)  : Again, how to write script which can iterate based on workspaceid.

Thanks

Amit

[avatorl]

$Workspaces = Get-PowerBIWorkspace
foreach ($workspace in $Workspaces) {
Write-Host $workspace.id Write-Host $workspace.name
}

[amsrivas]

Hi @avatorl  Thanks for all your inputs !!!

Any help here would be really appreciable.

1. Relation ship between Workspace and Dataflow 

Script Used : 
$Workspaces = Get-PowerBIWorkspace
$res = @( foreach ($workspace in $Workspaces) {Get-PowerBIDataFlow -Workspace $workspace }) | Export-Csv -NoTypeInformation -Path C:\Azure\Dataflow.csv

Result : Its giving me detail of Dataflow only such Dataflow Id, Dataflow Name etc.

-  Will it be possible to include the workspace ID which i have passed above in script in the Output.

2. WorkSpace - Dataset 

Script Used : Get-PowerBIDataset

Output - No relationship between Workspace ,dataflow and reports are there.

3. WorkSpace - Report 

 Script Used : Get-PowerBIReport

This gives relation ship with Dataset, any way to have relation between workspace, dataflow as well.

4. Any scipt that says relation ship between Report to Workspace, Dataflows , dataset , users etc will be great help!!

THanks

Amit 

[amsrivas]

Hi @avatorl 

Yes, i have replaced it with Workspace id and still getting error as shown

Thanks

Amit 

[avatorl]

You're right, for some reason dataflow object has no workspace property. You can loop through all workspaces with Get-PowerBIDataflow -WorkspaceId <workspaceid>. And this is how you'll get the relationships.

[amsrivas]

Hello @avatorl ,

Yes, we can iterate workspace id with Dataflow but how will we manage relationship Workspace -> Dataflow -> Dataset.

As we have relationship as below

: Workspace -> Dataflow (the way you suggested)

: Workspace -> Dataset (Get-PowerBIActivityEvents.).

How we connect Dataflow -> Dataset?

Alsom when i executing this Get-PowerBIDataflow command in PowerShell (admin account), it does not return anything, however i do have Dataflows in workspace apart from My Workspace.

Any suggestion!!

Thanks

Amit