Skip to main content

 

Eptura Knowledge Center

Historical Bookings API Power BI integration

  1. Last updated
  2. Save as PDF
Learn how to consume Condeco’s Historical Bookings API storage extension via Power BI. This step-by-step guide explains how to connect to Azure Table Storage (data mart) and create a sample report.

Prerequisites

The following items are required before deploying the Historical Bookings API:

  1. The historical booking storage extension should already be deployed.
  2. An active Power BI account or trial. Learn more at https://powerbi.microsoft.com/en-us/.
  3. The Azure Table Storage URL and account key.

Download and install Power BI desktop

To connect to the data mart, you need to download and install the Power BI desktop.

  1. Visit https://powerbi.microsoft.com/en-us/desktop/.
  2. Click download free.
  3. When the download has finished, double-click PBIDesktop.msi to open the installation wizard and follow the steps.
  4. Open Power BI desktop from the desktop shortcut or Start Menu.
  5. Log in to the Power BI account when prompted.

Connect to Azure table storage (data mart)

  1. Connect to the data mart.
  2. Click Get Data > More.
    output-onlinepngtools.png
  1. Click Azure > Azure Table Storage. Click Connect.
    output-onlinepngtools-1.png
  1. Enter the Azure Table storage URL and Access Key.
    output-onlinepngtools-2.png
    output-onlinepngtools-3.png
  1. In the Navigator select Booking, BookingAttendees and BookingItems tables then click Transform Data.
    output-onlinepngtools-4_v1.png
  1. Select the Booking table from the left sidebar.
  1. Click the Edit properties button in the Content column. Ensure all columns are selected then deselect (untick) Use original column name as prefix and click OK.
    output-onlinepngtools-5.png

For optimal use, provide the appropriate data type for each column.

  1. Select the desired column by clicking the column header, then click Data type drop-down menu to select the appropriate data type for the column (see table below for appropriate data types).
    output-onlinepngtools-6.png
    Booking Data type
    BookedByUserId Whole Number
    BookingCreateDate Date/Time
    BookingId Whole Number
    Capacity Whole Number
    FloorNo Whole Number
    HostUserId Whole Number
    IsDeleted True/False (logical)
    IsPrivateMeeting True/False
    LocationId Whole Number
    NumberOfAttendees Whole Number
    RecurrenceId Whole Number
    RequesterUserId Whole Number
    RoomId Whole Number
    ScheduledEndUtc Date/Time
    ScheduledStartUtc Date/Time
    WasBumped True/False
  1. Select the BookingAttendees table from the left sidebar.
  1. Repeat steps 7 and 8. The data types for BookingAttendees are shown in the table below.
    BookingAttendees Data type
    BookingId Whole Number
    IsInternal True/False
    UserId Whole Number
  1. Select the BookingItems table from the left sidebar.
  1. Repeat steps 7 and 8. The data types for BookingItems are shown in the table below.
    BookingItems Data type
    BookingId Whole Number
    Quantity Whole Number
    ScheduledEndUtc Date/Time
    ScheduledStartUtc Date/Time
  1. When finished, click Close & Apply button on the ribbon.
    output-onlinepngtools-7.png
  1. Your query changes will now be applied and data will be downloaded locally for reporting.

This could take some time depending on the number of records and the speed of the connection.

When loaded, check and edit the relationships between tables as described in the next section.

Manage relationships

The relationship between the tables BookingID is many-to-many. This is because recurring bookings share the same BookingID and fixed and flexible desk booking records do not have a BookingID, making the BookingID non-unique in the booking table.

Check and edit the relationships between tables.

  1. Click Relationships tab from left panel. Delete any existing relationship – right-click and click Delete.
    output-onlinepngtools-8.png
  1. Click Manage Relationships on the ribbon and click New.
  1. Select the tables and columns that are related – Booking.BookingId → BookingAttendees.BookingId – tick Make this relationship active then click OK.
    output-onlinepngtools-9_v2.png
  1. Click Close.
    output-onlinepngtools-11.png

Your final relationships should look like this:
output-onlinepngtools-12_v4.png

All three tables are now set up. Switch to the Report tab to create your first report.

Create sample report, save and publish

Multi-Row Card

  1. Select Multi-Row Card Icon from Visualizations Tab.
    1. Select Columns need to show in the report.
    2. Resize and move the Card as per need.
      output-onlinepngtools-13.png

Card

  1. Select Card Icon from Visualizations Tab.
    1. Select Column need to show in the report. For eg: BookingId.
    2. Click on the Down Arrow located on the right side of Fields.
    3. Rename to Bookings.
    4. Select Count (Distinct).
    5. Resize and move the Card as per need.
      output-onlinepngtools-14.png

Slicer

  1. Select the Slicer Icon from Visualizations Tab.
    1. Select the Column from which you want to filter the report. For eg: IsDeleted.
    2. Resize and move the Card as per need.
    3. Select True/False to see the report filter as per selection.

Learn more

Visit these websites to learn more about Power BI and how to design your reports:

Example of reports created with Power BI.

output-onlinepngtools-16.png

output-onlinepngtools-17.png

Scheduled refresh

Apply a scheduled refresh in Power BI portal to keep the report up-to-date.

Refreshing data can take a considerable amount of time and should be done as infrequently as it makes sense for your reporting needs.

More information is available on the following websites: