In this article, we'll show you how to export Power BI datasets to a connected SFTP site. From SFTP, you can import Kallidus data to mix with your organisational data using your organisation's Power BI.
In this article, we'll cover:
- What is SFTP?
- Overview of the process to extract data from Kallidus
- Steps to grant the Data Connector permission to a User
- How to create an SFTP connection
- Steps to review the delivery history of an SFTP connection
- Steps to rerun a one-time SFTP connection
- How to whitelist Kallidus' IP address for SFTP connections?
What is SFTP?
An SFTP data connection is an export of prebuilt Kallidus datasets to your own SFTP location. Once exported, you can extract and transform the data, as needed, before loading it into your external reporting tools.
A few things to know about SFTP connections:
- Only Users with permission to connect data will be able to create SFTP connections
- Each SFTP connection can export one or more datasets simultaneously
- You can create as many SFTP connections as required
- You must have your own non-Kallidus SFTP location to successfully create a connection
- The dataset mapping guide will help you establish the relationships between various Dimension and Fact tables
- The first Power BI dataset refresh of each day is completed by 7:30am UTC, so scheduling your SFTP connection for 8am UTC onwards will ensure the freshest data to start the day
Overview of the process to extract data from Kallidus
Overview of bulk data extraction
In this video, we'll provide a high-level overview of the process, and show how SFTP is used to extract your Power BI data to mix with your own organisational data:
View extracted data in your organisations' Power BI
In this video, Gary examines how your Kallidus data will appear when imported to your organisation's Power BI tool.
Steps to grant the Data Connector permission to a User
- Navigate to Manage users
- Search for the desired User
- Select Actions
- Select Edit:
- Scroll down to the Roles section
- Enable the Data connector permission. Note that this permission can be enabled independently of other reporting permissions. You don't have to enable the Reporting or Edit reports permissions for the User to successfully create data connections; however, you can enable them additionally if the User needs to view/edit reports:
Steps to create an SFTP connection
- Navigate to Reports
- Select Connect data
- Select New connection:
- Give the connection a name
- Add a description (optional):
- Select Next
- Select one or more datasets:
- Select Next
- Enter SFTP credentials:
- Select Next
- Select a frequency:
- The first Power BI dataset refresh of each day is completed by 7:30am UTC, so scheduling your SFTP connection for 8am UTC onwards will ensure the freshest data to start the day
- Select Save to complete the connection
Steps to review the delivery history for an SFTP connection
If you need to audit the delivery of your SFTP connections, or if you see an issue with any of your SFTP connections, you can easily review the delivery history. Here's how:
- Select Actions on any SFTP connection
- Select Delivery log:
- Find the relevant Timestamp, and review the Status column:
Possible statuses are:
- Successful: All datasets were successfully generated and delivered to your SFTP location
- Failed: One or more datasets were not successfully generated, or all datasets were successfully generated, but not successfully delivered to your SFTP location
- For 'Failed; SFTP deliveries, click into the Status column to review the Data Connection Job ID Provide this ID to our Support team and we'll help you troubleshoot
Steps to rerun a one-time SFTP connection
For any one-time SFTP connections (those where the Schedule column shows "Now" or "Once"), you can rerun them at any time after their initial runs by selecting Actions and Run now:
How to whitelist Kallidus' IP address for SFTP connections?
Kallidus doesn't use a static IP address for SFTP connections. Therefore, we don't have an IP address (or range of IP addresses) that you can easily whitelist. Our SFTP connection service draws from a pool of IP addresses available in Azure UK South, and that pool continually evolves as Microsoft adds/removes IP addresses on a weekly basis.
As a workaround, you can download the specification file (provided by Microsoft) that lists Azure IP addresses used across all regions and services. You can then filter for the UK South region and whitelist all the IP addresses listed. You'll need to repeat this process on a weekly basis as Microsoft publishes new specification files.
Discover more about reporting with Power BI
Looking for quick answers? Check out FAQ, Reporting and Analytics with Power BI, packed with over 100 common questions and links to in-depth resources.
If you're new to reporting with Power BI, Reporting and analytics 101 introduces the fundamentals to help you get started. For a deeper dive, the short Academy Course Five must-know features explores these key topics in more detail and serves as the starting point for the Get Started: Reporting and analytics with Power BI course.
You can also join our regular Ask Me Anything: Reporting live events - come along and ask your question directly to our panel of experts.
Don't have your Academy account yet? Contact your Customer Experience Manager or the Support Team to sign up today.