PowerBI Overview

From Data Islands

WORK IN PROGRESS

Power BI support Version 1

Power BI has been added as a "local server" connector in the Data Islands console app.

There are some limitations with this over other "local servers" in that you cannot retrive data from Power BI (at least in version 1).

But importantly you can pull/import data to Power BI (from the Data Islands cloud).

So you would connect the local server to say an MSSQL db, run your querydb, push the data in an island to the Data Islands cloud, change your local server to Power BI and pull/import the island(s) to that.

Requirements

  • Requires a Power BI "Premium Per User" license
  • XMLA Endpoint (1) should be set to "Read Write" (Found in Admin Portal)

Connecting to Power BI 

 >addserver powerbi "[alias]"

PowerBI Authentication is required at this point so your default browser will open and you connect using your Microsoft Credentials

You see what permissions are requested and if you agree click "Accept" - They must be accepted for the system to work.

You then see the approval message

You will receive an email confirming this

You then return to the console app

Using command 

 >listdb

you will see your workspaces. Workspaces are what we consider as Databases in the Data Islands console app.

To "pull" an island into Pwer BI we then run command 

 >pull arcustomers arcustomersdemo

This data is visible and can be used to build a report in Power BI.

Note that Datasets in workspaces we consider as Tables. The Command

>dbtables

will show any datasets in the workspace



Within Poweer BI these datasets can be used in a Report (Simple Pagination report here)



Push datasets limitations In Power BI

Limitations from Microsoft

  • 75 max columns
  • 75 max tables
  • 10,000 max rows per single POST rows request
  • 1,000,000 rows added per hour per dataset
  • 5 max pending POST rows requests per dataset
  • 120 POST rows requests per minute per dataset
  • If table has 250,000 or more rows, 120 POST rows requests per hour per dataset
  • 200,000 max rows stored per table in FIFO dataset
  • 5,000,000 max rows stored per table in 'none retention policy' dataset
  • 4,000 characters per value for string column in POST rows operation
  • 75 max relationships
  • Doesn't work with service principal profiles

Ref: https://learn.microsoft.com/en-us/power-bi/developer/embedded/push-datasets-limitations


Please note the following console commands not currently supported for the PowerBI connector:

  • DescribeTable
  • NativeSql
  • Push
  • QueryDB

Connector Limitations

Due to how Microsoft handle Tokens they only live for 1hr 5mins and so this really prevents the Power BI connector being used in an automated job.

We are looking to see if this can be resolved in future.

Retrieved from "https://dataislands.crmtogether.com/index.php?title=PowerBI_Overview&oldid=198"