Satalyst Brilliance on 07 Sep 2015

Open Source PowerBI REST API for Java

At Satalyst, we do a lot of work on managing an organisation’s data and integrating systems together. One of the challenges with managing this data is providing visualisations that give insight into how a business is performing.

One area of interest that we have been focusing on recently is in the area of real-time analytics and actionable insights; especially related to the growing field of the Internet of Things (IoT). PowerBI, a Microsoft visualisation tool, has the ability to create simple visualisations from real-time data and present them quickly through a web-browser using a simple RESTful API.

Whilst there is a REST API for interacting with PowerBI, there seems to be no simple API wrapper written for Java (there are wrappers for C# available). The prototype I’ve been working on is written in Java, so I needed to create a wrapper for the PowerBI REST API. After a lot of research on the internet for PowerBI REST API for Java, it appears that there are a large number of people crying out for a Java library to use to interact with the API, so I figured we should open source ours.

You can get the source code from GitHub here: https://github.com/satalyst/powerbi-rest-java

Connecting to PowerBI

Using the API is fairly simple. Firstly you need to configure your application to be able to authenticate with PowerBI by following these instructions: https://msdn.microsoft.com/en-US/library/dn877545.aspx

To get a connection to the PowerBI endpoint, you need some code similar to the below (replacing the values with your own of course):

ExecutorService executor = Executors.newFixedThreadPool(10);
Office365Authenticator auth = new Office365Authenticator("NATIVE-CLIENT-ID”, 
"YOUR OFFICE365 TENANT", " USER NAME", "PASSWORD”, executor);
PowerBiConnectionFactory factory = new DefaultPowerBiConnectionFactory(auth, executor);
PowerBiConnection connection = factory.create();

You now have a connection established to PowerBI which you can then perform operations against.

Operations

The following operations are currently supported (see the code in the com.satalyst.powerbi.operations package):

  1. Create Dataset
  2. List Datasets
  3. List Tables
  4. Update Table Schemas
  5. Add Row to Table
  6. Clear Rows from Table

All operations are performed by passing an instance of the operation to the execute method on the PowerBIConnection instance. This will return a Future which you can use to retrieve the result of the operation.

Creating a Dataset

Creating a dataset requires three parameters:

  1. The name of the dataset to create;
  2. The schema of the tables to include in the dataset (NOTE: must be specified at creation time, whilst table schemas can be modified later on, you cannot add or remove tables after this point); and
  3. The retention policy for the data (FIFO or None).
CreateDataset cd = new CreateDataset(“Example Dataset”, tables, RetentionPolicy.FIFO);

Creating a Table schema can either be done in code, or loaded from a resource using the TableSchemaLoader interface. For the moment, there is a simple JSON schema loader, but you can implement your own.

This is an example of creating a Table schema in code:

List<Table> tables = Arrays.asList(
new TableBuilder().setName("Test Table 1")
.addColumn("Test String 1", ColumnType.STRING)
.addColumn("Test Bool 1", ColumnType.BOOL)
.addColumn("Test Int 1", ColumnType.INT64)
.addColumn("Test Date 1", ColumnType.DATETIME).build(),
new TableBuilder().setName("Test Table 2")
.addColumn("Test String 2", ColumnType.STRING)
.addColumn("Test Bool 2", ColumnType.BOOL)
.addColumn("Test Int 2", ColumnType.INT64)
.addColumn("Test Date 2", ColumnType.DATETIME).build()
        );

 Listing Datasets

ListDatasets list = new ListDatasets();
List<Dataset> datasets = connection.execute(list).get()

Listing Tables

ListTables cmd = new ListTables(UUID.fromString(“67bab52f-f622-4e14-8a44-
02ebeda4a451”));
List<Table> tables = connection.execute(cmd).get();

Update Table Schemas

Table t = new TableBuilder().setName("Test Table 1")
.addColumn("Test String 1", ColumnType.STRING)
.addColumn("Test Bool 1", ColumnType.BOOL)
.addColumn("Test Int 1", ColumnType.INT64)
.addColumn("Test Date 1", ColumnType.DATETIME)
.build();

UpdateTableSchema schema = new UpdateTableSchema(UUID.fromString(“67bab52f-f622-4e14-
8a44-02ebeda4a451”), “Test Table 1”, t);
connection.execute(schema).get();

 Add Row to Table

AddRow add = new AddRow(
createdDataset.getId(),
"Test Table 1",
tables.get(0).getColumns()
);

connection.execute(add).get();

Clear Rows from Table

ClearRows clear = new ClearRows(UUID.fromString(“67bab52f-f622-4e14-8a4402ebeda4a451”), 
“Test Table 1”);
connection.execute(clear).get();
Categories:
Tags: