PowerBI Overview
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