Demo data: Dim Hour

Description

A look-up dimension related to Fact Visitors through the hour_key column.

Usage

dim_hour

Format

A data frame with 24 rows and 2 columns:

hour_key

Primary key (unique identifier of hour).

hour

Hour as a name (character type).

Source

Anonymized data from google analytics.

Demo data: Fact Visitors

Description

A fact table showing individual visitors and their transactions on an e-commerce website.

Usage

fact_visitors

Format

A data frame with 10,033 rows and 5 columns:

visitor_id

Unique identifier of the visitor.

transaction_id

Unique identifier of the transactions.

revenue

The value of the transaction in USD.

timestamp

The time of visit in minutes since 1970-01-01.

hour_key

Foreign key referring to dim_hour.

Source

Anonymized data from google analytics.

Authenticate to Power BI

Description

This function authenticates your Power BI session using a service principal that represents an application registered in Azure Active Directory.

Usage

pbi_auth(
  tenant = Sys.getenv("PBI_TENANT"),
  app = Sys.getenv("PBI_APP"),
  password = Sys.getenv("PBI_PW")
)

Arguments

Details

The function returns an authentication token invisibly and makes it available to other functions in this package. The token is automatically refreshed upon expiration.

To auto-authenticate, you can specify credentials in environment variables via an .Renviron file or using Sys.setenv (see example below).

pbi_auth() is a wrapper for AzureAuth::get_azure_token(). Currently, only non-interactive authentication is supported. You therefore need to register an Azure Active Directory service-principal application and obtain tenant ID, app ID and app password (client secret).

For reasons of CRAN policy, the first time AzureAuth is loaded, it will prompt you for permission to create a user-specific directory in order to cache the token. The prompt only appears in an interactive session, not in a batch script. For more details, see AzureAuth.

Value

Returns a token invisibly.

Examples

## Not run: 

# Basic authentications
pbi_auth(
tenant = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx", # The tenant ID
app = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx",    # The app ID
password = "****"                                # The client secret
)

# Using environment variables
Sys.setenv(
  PBI_TENANT = "my_tenant_id",
  PBI_APP = "my_app_id",
  PBI_PW = "my_app_client_secret"
  )

pbi_auth()

## End(Not run)

Refresh dataset

Description

Triggers a refresh for the specified dataset from the specified workspace.

Usage

pbi_dataset_refresh(group_id, dataset_id)

Arguments

Value

If successful, the refresh request ID is returned.

See Also

pbi_dataset_refresh_hist

Examples

## Not run: 

group_id <- "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
dataset_id <- "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

pbi_dataset_refresh(group_id, dataset_id)
#> A refresh of dataset xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx was triggered.
#>
#> To check status, use pbi_dataset_refresh_hist() and the request ID returned
#> by this function.
#> [1] "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

## End(Not run)

Refresh history of a dataset

Description

Returns the refresh history for the specified dataset from the specified workspace.

Usage

pbi_dataset_refresh_hist(group_id, dataset_id, top = NULL, request_id = NULL)

Arguments

Details

By default the function will return all historical refreshes. You can reduce the list to the most recent refreshes using the top argument.

If request_id is provided the function will return a single refresh status, but will still query the Power BI API for all historical entries. If you query the top 5 most recent refreshes using the top argument, the function will only return a status if the provided request_id is in this list.

The status value return can be either 'Completed', 'Failed' or 'Unknown', which means that the refresh is still in progress.

Value

A data frame with status, start and end times of historical refreshes or a single refresh status message if request_id is used.

See Also

pbi_dataset_refresh

Examples

## Not run: 

group_id <- "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
dataset_id <- "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

pbi_dataset_refresh_hist(group_id, dataset_id)

## End(Not run)

Delete dataset

Description

Deletes the specified dataset from the specified workspace. Applicable to push datasets as well as imported datasets.

Usage

pbi_delete_dataset(group_id, dataset_id)

Arguments

Value

Deletes the entire dataset.

Examples

## Not run: 

group_id <- "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
dataset_id <- "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

pbi_delete_dataset(group_id, dataset_id)

## End(Not run)

Delete rows

Description

Deletes all rows from the specified table within the specified dataset from the specified workspace (group ID). Only applicable to push datasets.

Usage

pbi_delete_rows(group_id, dataset_id, table_name)

Arguments

Value

All rows will be deleted from the specified table.

Examples

## Not run: 

group_id <- "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
dataset_id <- "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
table_name <- "My Table"

pbi_delete_rows(group_id, dataset_id, table_name)

## End(Not run)

Get a list of datasets in a workspace

Description

Returns the IDs and meta data of all available datasets in the specified Power BI workspace (group ID).

Usage

pbi_list_datasets(group_id)

Arguments

Value

A data.table / data frame with dataset information.

Examples

## Not run: 

group_id <- "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

pbi_list_datasets(group_id)

## End(Not run)

Get a list of workspaces

Description

Returns the ids and meta data of all Power BI workspaces to which the service principal app has been granted access.

Usage

pbi_list_groups()

Value

A data frame with workspaces.

Examples

## Not run: 

pbi_list_groups()

## End(Not run)

Push a dataset schema to Power BI

Description

Pushes a dataset schema to the specified Power BI workspace. To add rows to the dataset, use pbi_push_rows().

Usage

pbi_push_dataset_schema(schema, group_id, retention = c("none", "basicFIFO"))

Arguments

Value

A dataset with tables will be created in the specified Power BI workspace.

Examples

## Not run: 

group_id <- "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

schema <- pbi_schema_create(
  dt_list = list(iris),
  dataset_name = "The iris dataset",
  table_name_list = list(iris)
)

pbi_push_dataset_schema(schema, group_id)

## End(Not run)

Push rows to a dataset table

Description

Adds new data rows to the specified table within the specified dataset from the specified Power BI workspace. Only applicable to push datasets.

Usage

pbi_push_rows(dt, group_id, dataset_id, table_name, overwrite = FALSE)

Arguments

Details

The Power BI REST has a limit of 10K rows per POST rows request. This limit is handled by splitting the data frame into chunks of 10K rows each and pushing these chunks one at a time. However, you should manually observe the other limitations of the API. See https://docs.microsoft.com/en-au/rest/api/power-bi/ for more details.

Value

A dataset with tables and optionally defined relationships will be created in the specified Power BI workspace.

Examples

## Not run: 

group_id <- "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
dataset_id <- "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

pbi_push_rows(group_id, dataset_id, "My table")

## End(Not run)

Create a Power BI dataset schema

Description

Creates a Power BI dataset schema from a set of data frames. Columns and data types will be inferred from the data frames. Only applicable to push datasets.

Usage

pbi_schema_create(
  dt_list,
  dataset_name = "My Power BI Dataset",
  table_name_list,
  relations_list = NULL,
  date_format = "yyyy-mm-dd",
  integer_format = "#,###0",
  double_format = "#,###.00",
  sort_by_col = NULL,
  hidden_col = NULL,
  default_mode = c("Push", "Streaming", "PushStreaming", "AsOnPrem", "AsAzure")
)

Arguments

Value

A list with schema properties.

Examples

# Load package
library(powerbiR)

# Use data from the powerbiR package
data(dim_hour)
data(fact_visitors)

# Define dataset and its tables
table_list <- list(fact_visitors, dim_hour)
table_names  <- c("visitors", "hour")
dataset_name <- c("Online Visitors")

# Define relations between tables
relation <- pbi_schema_relation_create(
  from_table = "visitors",
  from_column = "hour_key",
  to_table = "hour"
)

# Define sorting behavior of columns in the hour table
sortlist = list(
  table = c("hour"),
  sort = c("hour"),
  sort_by = c("hour_key")
)

# Hide hour_key in the hour and visitors tables
hidden <- list(
  list(
    table = c("hour"),
    hidden = c("hour_key")
  ),
  list(
    table = c("visitors"),
    hidden = c("hour_key", "visitor_id")
  )
)

# Create schema
schema <- pbi_schema_create(
  dt_list = table_list,
  dataset_name = dataset_name,
  table_name_list = table_names,
  relations_list = list(relation),
  sort_by_col = list(sortlist),
  hidden_col = hidden
)

Define table relationship

Description

Defines a relationship between tables in a Power BI push dataset. To add this definition to a Power BI dataset schema, use pbi_schema_add_relations().

Usage

pbi_schema_relation_create(
  from_table = NULL,
  from_column = NULL,
  to_table = NULL,
  to_column = from_column,
  direction = c("OneDirection", "BothDirections", "Automatic"),
  name = paste0(from_table, to_table, from_column)
)

Arguments

Value

A data.table

Examples

# An example