Stripe Power BI Integration: 3 Simple Methods

on BI Tool, Data Integration, ETL, Tutorials • January 18th, 2021 • Write for Hevo

Stripe Power BI Feature Image

Do you use Stripe for financial services? Are you a marketer, who uses Stripe and wants to analyze your churn rate? Do you want to load data from Stripe to Power BI? If yes, then this blog will answer all your queries. Stripe is an American company that provides financial services to various other companies. The accumulated data of Stripe can help you to perform various analyses of your clients. Stripe Power BI Integration can help you analyze your data in real-time to obtain data-driven decisions. Through this article, you will get a deep understanding of the tools & techniques with a step-by-step solution, and thus, it will help you hone your skills further.

Table of Contents

Introduction to Stripe

StripeLogo-Stripe Power BI - Transfer Data for Free from Stripe to your target destination
Image Source: Wikipedia

Stripe is an American Payment Platform. It provides financial services. The company mainly offers payment processing software and APIs for your E-Commerce websites and mobile applications. Stripe APIs can be used by the developers to integrate payment processing with your website and applications. It also acts as a gateway and a third-party payment tool. Stripe offers scalability and security with its cloud-based infrastructure.

Key Features of Stripe

  1. Stripe requires no additional fees for installation, setup, cancellation, etc. 
  2. Stripe offers smooth integration with multiple applications. It is considered highly customizable. 
  3. If your organization has some international customers, then Stripe is a great option as it has support for 135+ currencies, allowing you to charge customers in their native currency while receiving funds in yours.
  4. Stripe offers an easy-to-use platform, where you can easily sign up and integrate it for the payment process. The Stripe checkout form is simple and allows the customer to complete their purchase easily.
  5. Stripe allows exporting data of different types generated by the users, such as financial data, transaction data, etc. in CSV format.

In addition, if you would like to learn more about exporting Stripe Data, check out our amazing article here.

Introduction to Power BI

PowerBILogo-Stripe Power BI - Transfer Data for Free from Stripe to your target destination
Image Source: Free Logo Vectors

Power BI is a cloud-based business intelligence tool used to analyze and visualize data in real-time. It converts data from multiple sources into interactive dashboards and reports, managing your business needs. Power BI consists of various formats namely, Power BI Desktop, an online Software as a Service (SaaS) called Power BI Service, and a range of mobile apps for Windows, iOS, and Android devices. It also has Power BI Report Server which allows you to publish Power BI reports to an on-premises report server, after creating them in Power BI Desktop.

These tools enable you to analyze your data and in turn, helps businesses and organizations to ask the right questions and get appropriate insights. Power BI takes data from different sources and turns it into custom visuals designed to not only help you understand the information derived from it but also get a clear idea of actions to be taken.

Key Features of Microsoft Power BI

  1. Easy Set-up and Implementation: You can set it up for free and start your visualization within minutes.
  2. Real-Time Visualization: Data is available for visualization in real-time. You are capable of capturing opportunities and solving problems.
  3. Customized Visualization: Power BI offers customized visualization by custom visual SDK. 
  4. Multiple Data Sources: Power BI supports a range of data sources such as Excel, Oracle, SQL Server, etc.

For a more powerful understanding of Microsoft Power BI Visualisation

3 Methods to Set Up Stripe Power BI Integration

Method 1: Stripe Power BI Integration using Manual Extraction of Data

Using the inbuilt functionality of Stripe to manually download the required data in CSV form and then importing it in Power BI.

Method 2: Stripe Power BI Integration via Cloud Data Warehouse Integration

Using API integration to connect Power BI to connect to a cloud data warehouse and then connecting the data source in the cloud data warehouse to import data into Power BI.

Method 3: Stripe Power BI Integration using Hevo

Hevo is a No-code Data Pipeline. It will automatically load your Stripe data into Power BI without writing any line of code.

Transfer Data from sources like Stripe to your target destination for Free!

Get Started with Hevo for Free
Download the Guide on How to Set Up a Data Analytics Stack
Download the Guide on How to Set Up a Data Analytics Stack
Download the Guide on How to Set Up a Data Analytics Stack
Learn how to build a self-service data analytics stack for your use case.

Pre-requisites 

  • Working knowledge of Power BI.
  • Working knowledge of Stripe.
  • An active Stripe account with either administrator or developer role.
  • Power BI installed on the host workstation.
  • A Power BI service account.

3 Methods to set up Stripe Power BI Integration

Method 1: Stripe Power BI Integration using Manual Extraction of Data

What Data can you Export from Stripe?

Some of the data that you can export from Stripe is of the following type:

  1. Payout data
  2. Dispute data
  3. Succeed payment
  4. Detailed payments and refund data
  5. Detailed balance transactions
  6. Monthly summary reports
  7. Monthly activity summaries
  8. QuickBooks data

Stripe uses REST API principles for accessing, storing, and retrieving customer and payment data from their accounts. It further provides support for many programming languages such as Python, Node.js, Perl, .NET, etc. to access its APIs. They support JSON for the response. You can learn more about Stripe APIs from here. Under this method, you would be using Windows Powershell and its CMDlets to make web-based API requests.  This request will help you fetch your data in the JSON format and then transform it into CSV format. This data in CSV format can then be imported into Power BI for analyses and visualization.

This method can be implemented using the following steps:

Step 1: Retrieve your API keys from Stripe

To load data using Stripe APIs, first, there is a need to obtain your API keys, from the Stripe dashboard. To do this, go to Stripe’s official website, and then log in using your credentials such as username and password.

To get started with Stripe Dashboard and learn more about it, you may check our other article here.

Stripe API Key-Stripe Power BI - Transfer Data for Free from Stripe to your target destination
Image Source: Stripe

Once the login process is complete, your Stripe dashboard will now open up on your screen. Then click on the developer’s option, found in the panel on the left, and select the API keys option.

Stripe API Key -Stripe Power BI
Image Source: Stripe

Next, for being able to see the API keys, you must ensure that the account being used for this process has either developer access or administrator access. Further, ensure that you are not viewing the test data by toggling off the test mode and going to the live data mode.

Stripe API Key-Stripe Power BI
Image Source: Stripe

After enabling the live data view, you will be able to see the publishable and secret keys option on the screen. You need to click on both of these options to access the full API key-values. The “Publishable” and “Secret” APIs keys use the following syntax respectively:

  • Publishable Key: pk_live_somelongrandomvaluelike0Rx3jvetc
  • Secret Key: sk_live_somelongrandomvaluelike0Rx3jvetc

Copy “Publishable” and “Secret” API keys and keep them safe as you’ll not be able to see the “Secret” key again.   

Stripe API Key -Stripe Power BI
Image Source: Stripe

And now you have retrieved the Stripe API keys to use them for fetching data via Stripe’s REST API.

Step 2: Retrieve Stripe Records using the Stripe API

After the Stripe API key has been retrieved, Windows Powershell can be used to make a web request using the Invoke-WebRequest cmdlet (which is available as a part of the Microsoft.PowerShell.Utility module). If you do not have Windows PowerShell installed on your system, it can be downloaded from here.

Stripe API Windows Powershell-Stripe Power BI

To fetch the customer data from Stripe, the following commands need to be entered in the Windows PowerShell running on your system. You would be required to enter the correct URI for your account and your Stripe Private API key that you just retrieved from the official website.

$uri = 'https://api.stripe.com/' #Base URI
$key = '<stripe API private key>'
$authVal = 'Basic ' + `
[System.Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes($key))
$response = Invoke-WebRequest -Uri $uri -Headers @{'AUTHORIZATION'=$authVal}

The invoked web request will enable Stripe API to start sending the JSON-based responses which contain the customer and payment data. The received data can be checked using the following line of code, by entering in the command line and printing output from the JSON response.

echo $response.Content

You can also make use of .NET objects such as System.NET.WebRequest to make an API-based web request to pull your data as follows:

$webRequest = [System.Net.WebRequest]::Create($uri)
$webRequest.ContentType = 'application/json'
$webRequest.Headers.Add('AUTHORIZATION', $authVal)
$webRequest.Method = 'GET'
$responseStream = ($webRequest.GetResponse()).GetResponseStream()
$streamReader = New-Object System.IO.StreamReader -ArgumentList $responseStream

Now, this was the step required to start pulling data from Stripe’s REST API in JSON format.

Step 3: Transform JSON Responses to a Flat Structure for CSV Files

As the Stripe API is used to fetch data in JSON format using invoked web requests, you will need to transform the hierarchical JSON data into the format of a flat file, to export that data in CSV format.

For achieving this functionality, you will be required to create a function that will accept the Stripe APIs JSON object as a parameter and map it into a flat data structure and then return a collection of data objects in the correct format. The following code snippet provides an example to execute this functionality.

function flattenObject ($jsonCustomers) {
	$customers = @()
	foreach($jsonCustomer in $jsonCustomers) {
$customerProperties = @{}
$customerProperties.id = $jsonCustomer.id
$customerProperties.Description = $jsonCustomer.description
$customerProperties.Email = $jsonCustomer.email
$customerProperties.Created = $jsonCustomer.created
$customerProperties.Delinquent = $jsonCustomer.delinquent
$customerProperties["Account Balance"] = $jsonCustomer.account_balance
		
         foreach($paymentSource in $jsonCustomer.sources.data) {
			if ($jsonCustomer.default_source -eq $paymentSource.id) {	
$customerProperties['Card ID'] = $paymentSource.id
$customerProperties['Card Last4'] = $paymentSource.last4
$customerProperties['Card Brand'] = $paymentSource.brand
$customerProperties['Card Funding'] = $paymentSource.funding
$customerProperties['Card Exp Month'] = $paymentSource.exp_month
$customerProperties['Card Exp Year'] = $paymentSource.exp_year
$customerProperties['Card Name'] = $paymentSource.name
$customerProperties['Card Name'] = $paymentSource.name
$customerProperties['Card Address Line1'] = $paymentSource.address_line1
$customerProperties['Card Address Line2'] = $paymentSource.address_line2
$customerProperties['Card Address City'] = `$paymentSource.address_city
$customerProperties['Card Address Country'] = $paymentSource.address_country
$customerProperties['Card Address State'] = `$paymentSource.address_state
$customerProperties['Card Address Zip'] = $paymentSource.address_zip
$customerProperties['Card Issue Country'] = $paymentSource.country
$customerProperties['Card Fingerprint'] = $paymentSource.fingerprint
$customerProperties['Card CVC Status'] = $paymentSource.cvc_check }
		}

	foreach($subscription in $jsonCustomer.subscriptions.data) {
			if ($subscription.plan.id -eq 1) {
$customerProperties.Plan = $subscription.plan.id
$customerProperties.Status = $subscription.status
$customerProperties['Cancel At Period End'] = $subscription.cancel_at_period_end}		}
$customers += New-Object PSObject -Prop $customerProperties
	}
	return $customers
}

During this process, you need to ensure that the correct column names are used while creating the function that will help for mapping the JSON objects into the flat file structure. Also, every time your code generated a new customer object, ensure that you are adding it to the customer properties list, that your function will return.

Following this step will help you transform the JSON response data from Stripe API to a flat-file structure which will be used for CSV conversion.

Step 4: Export Data from Stripe as CSV

To export the Stripe data as a CSV file on the local system, you can use Windows PowerShell’s Export-CSV cmdlet. It will take an object as the input parameter and map the properties into a CSV file and store it in the file location of the path you would have entered.

You can use the following lines of code, that will fetch data from your Stripe APIs using the “System.NET.WebRequest module” .NET-based object. It will export the retrieved data as a CSV file by specifying a file path of your choice with the “Export-CSV” parameter.

$baseUri = 'https://api.stripe.com/v1/customers?limit=100'
$uri = $baseUri
$key = '<stripe API private key>'
$authVal = 'Basic ' + `
    [System.Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes($key))
do {
	$webRequest = [System.Net.WebRequest]::Create($uri)
	$webRequest.ContentType = 'application/json'
	$webRequest.Headers.Add('AUTHORIZATION', $authVal)
	$webRequest.Method = 'GET'
	$responseStream = ($webRequest.GetResponse()).GetResponseStream()
	$streamReader = New-Object System.IO.StreamReader -ArgumentList $responseStream
	
	$jsonCustomers = $streamReader.ReadToEnd() | ConvertFrom-Json
	$customersObject += flattenObject($jsonCustomers.data)
	$lastId = $jsonCustomers.data[-1].id
	$uri = $baseUri + '&starting_after=' + $lastId
} while ($jsonCustomers.has_more)

$customersObject | Export-Csv -Path 'C:TestStripe-Customers.CSV' -NoTypeinformation
rv customersObject

In this code snippet, the has_more parameter is used to check whether any more customer records are present in Stripe. You can use the limit parameter in the base URI to specify the number of customer records that you want to pull (Stripe API allows pulling up to 100 records at max). Similarly, the -No TypeInformation parameter is used to ensure that your CSV file export does not have the object type information in the first record/row.

Below is the combined code script from all the above-mentioned steps that you can use to execute at once.

function flattenObject ($jsonCustomers) {
	$customers = @()
	foreach($jsonCustomer in $jsonCustomers) {
		$customerProperties = @{}
		$customerProperties.id = $jsonCustomer.id
		$customerProperties.Description = $jsonCustomer.description
		$customerProperties.Email = $jsonCustomer.email
		$customerProperties.Created = $jsonCustomer.created
		$customerProperties.Delinquent = $jsonCustomer.delinquent
		$customerProperties["Account Balance"] = $jsonCustomer.account_balance
		foreach($paymentSource in $jsonCustomer.sources.data) {
			if ($jsonCustomer.default_source -eq $paymentSource.id) {
	$customerProperties['Card ID'] = $paymentSource.id
	$customerProperties['Card Last4'] = $paymentSource.last4
	$customerProperties['Card Brand'] = $paymentSource.brand
	$customerProperties['Card Funding'] = $paymentSource.funding
	$customerProperties['Card Exp Month'] = $paymentSource.exp_month
	$customerProperties['Card Exp Year'] = $paymentSource.exp_year
	$customerProperties['Card Name'] = $paymentSource.name
	$customerProperties['Card Name'] = $paymentSource.name
	$customerProperties['Card Address Line1'] = `
                    $paymentSource.address_line1
	$customerProperties['Card Address Line2'] = `
                    $paymentSource.address_line2
	$customerProperties['Card Address City'] = `
                    $paymentSource.address_city
	$customerProperties['Card Address Country'] = `
                    $paymentSource.address_country
	$customerProperties['Card Address State'] = `
                    $paymentSource.address_state
	$customerProperties['Card Address Zip'] = $paymentSource.address_zip
	$customerProperties['Card Issue Country'] = $paymentSource.country
	$customerProperties['Card Fingerprint'] = $paymentSource.fingerprint
	$customerProperties['Card CVC Status'] = $paymentSource.cvc_check}		}
		foreach($subscription in $jsonCustomer.subscriptions.data) {
			if ($subscription.plan.id -eq 1) {
	$customerProperties.Plan = $subscription.plan.id
	$customerProperties.Status = $subscription.status
	$customerProperties['Cancel At Period End'] = `
                    $subscription.cancel_at_period_end}	}
		$customers += New-Object PSObject -Prop $customerProperties} return $customers }

$baseUri = 'https://api.stripe.com/v1/customers?limit=100'
$uri = $baseUri
$key = '<stripe API private key>'
$authVal = 'Basic ' + ` [System.Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes($key))
do {
	$webRequest = [System.Net.WebRequest]::Create($uri)
	$webRequest.ContentType = 'application/json'
	$webRequest.Headers.Add('AUTHORIZATION', $authVal)
	$webRequest.Method = 'GET'
	$responseStream = ($webRequest.GetResponse()).GetResponseStream()
	$streamReader = New-Object System.IO.StreamReader -ArgumentList $responseStream
	$jsonCustomers = $streamReader.ReadToEnd() | ConvertFrom-Json
	$customersObject += flattenObject($jsonCustomers.data)
	$lastId = $jsonCustomers.data[-1].id
	$uri = $baseUri + '&starting_after=' + $lastId
} while ($jsonCustomers.has_more)
$customersObject | Export-Csv -Path 'C:TestStripeCustomers.csv' -NoTypeinformation
rv customersObject

These steps will enable you to export data from Stripe as a CSV file using Stripe APIs, to further import those files into Power BI.

Step 5: Import CSV-Based Data from Stripe to Power BI

You can very easily import your CSV data into Power BI to complete your Stripe Power BI integration and visualize and analyze your data. 

In case you would like to integrate your Stripe Data to Google Analytics, check out our other article on stripe and google analytics integration.

Firstly, Open up the Power BI Desktop and select Get Data > CSV from the home ribbon.

CSV import Power BI -Stripe Power BI - Transfer Data for Free from Stripe to your target destination
Image Source: Microsoft Power BI

Then, you need to select the CSV file from the dialogue box that appears.

File Selection CSV -Stripe Power BI
Image Source: Microsoft Power BI

After you have made the selection and selected open from the dialogue box, the Power BI desktop accesses the file to determine the file attributed (headers), which may include file origin, delimiter type, etc. As shown in the image below the file attributes and other options are shown in the drop-down selections at the top of the CSV import dialogue window. You can make your changes to these attributes by selecting the options from drop-down selectors.

Attribute Selection -Stripe Power BI
Image Source: Microsoft Power BI

After you are done making your selections and corresponding changes, you can proceed to select load to import the file into Power BI Desktop. Alternatively, you can select edit to open Query Editor and change the shape or transform the data according to your preferences.

After the loading is complete into the Power BI Desktop, you will be able to see the table and its columns. It would be presented as fields in the “Fields Pane”.

Field view Power BI -Stripe Power BI
Image Source: Microsoft Power BI

Congratulations!, your Stripe Power BI integration is now complete and now you can proceed to visualize and analyze your data with various functionalities of Power BI.

Limitations for Stripe Power BI Integration using Manual Extraction of Data:

Since this method requires manual extraction of data it has the following limitations:

  1. No real-time replication of data, since every time the user would have to manually pull down data to import from the Stripe API. 
  2. Requires manual conversion of JSON response to a flat structure.
  3. Requires manual conversion of flat structure file to CSV, which could lead to conversion issues if the attributes are not provided accurately.
  4. Requires working knowledge of command-line codes and .NET objects to implement properly. 
  5. The upper limit on retrieval of only 100 records.

Method 2: Stripe to Power BI via Cloud Data Warehouse Integration

Via this method, your Stripe Power BI integration can be set-up using the functionality offered by various cloud data warehouses. Under this, the data would first be transferred from Stripe to a centralized cloud data warehouse and further would be retrieved and imported into Power BI for visualization and analyses according to the requirement.

To set up your Stripe Power BI integration using cloud data warehouse, You would need to follow these steps:

Step 1: Retrieving Stripe API keys

To load data using Stripe APIs, first, there is a need to obtain your API keys, from the Stripe dashboard. To do this, go to Stripe’s official website, and then log in using your credentials such as username and password.

Strip login Page-Stripe Power BI
Image Source: Stripe

Once the login process is complete, your Stripe dashboard will now open up on your screen. Then click on the Developers option, found in the panel on the left, and select the API Keys option.

Stripe API Key -Stripe Power BI
Image Source: Stripe

Next, for being able to see the API keys, you must ensure that the account being used for this process has either developer access or administrator access. Further, ensure that you are not viewing the test data by toggling off the test mode and going to the live data mode.

Stripe API Key -Stripe Power BI - Transfer Data for Free from Stripe to your target destination
Image Source: Stripe

After enabling the live data view, you will be able to see the publishable and secret keys option on the screen. You need to click on both of these options to access the full API Key values. The “Publishable” and “Secret” APIs Keys use the following syntax respectively:

  • Publishable Key: pk_live_somelongrandomvaluelike0Rx3jvetc
  • Secret Key: sk_live_somelongrandomvaluelike0Rx3jvetc

Copy “Publishable” and “Secret” API keys and keep them safe as you’ll not be able to see the “Secret” key again.

Stripe API Key -Stripe Power BI
Image Source: Stripe

And now you have retrieved the Stripe API keys to use them for fetching data via Stripe’s REST API.

Step 2: Extracting Data from Stripe

You can use the REST API principles for accessing, storing, and retrieving your data from Stripe. To know more about their API, refer to this Stripe documentation. They support JSON for their response. They also offer two different types of keys for authentication – testing mode and live mode. With testing mode, you can test every aspect of your API without messing with your actual data. Calls to Stripe API is only over HTTPS because of security reasons.

Stripe API supports various core resources about information about – charges, balance, refunds, customers, token, events, transfer, dispute, etc. All these resources support CRUD operations using HTTP verbs on their associated endpoints. In this section, you will use CURL, but you can also work with Postman, Hyper, etc.

Extensive information about the updated list of core resources and their function can be accessed here.

Some of the core resources around which the Stripe API is built around are:
  1. Balance: This object is used to show the current balance at your end.
  2. File uploads: There may be various occasions when you want to upload files in Stripe like when uploading a receipt.
  3. Charges: To charge you the amount as per the order through debit or credit card.
  4. Dispute: This object deals with the data regarding the dispute which may occur when a customer questions your payment with their bank. 
  5. Refunds: Refund objects let you make a refund of a charge that has previously been created but not yet refunded.
  6. Events: Events are the way to let clients know if something interesting is there for them to see. 
  7. Transfers: When you initiate a transfer to a bank account or when Stripe sends you money.  
  8. Transfer reversals: A transfer created previously can be reversed if it has not been paid out at that time.
  9. Tokens: Tokens can be created with a publishable API key.
  10. Customers: Customer objects allow you to track multiple transactions with the same customers by assigning them unique IDs.

All the above-mentioned resources support CRUD operations by using HTTP verbs on their associated endpoints. Also as a web API, it can be accessed by using tools like CURL or Postman or any other technology stack i.e. language or framework.

Some of the options available are:

For the extraction of data from the Stripe API, you can use the following operations:

curl https://api.stripe.com/v1/charges?limit=3 
   -u sk_test_BQokikJOvBiI2HlWgH4olfQ2:

The response you will receive will look as follows:

{
  "object": "list",
  "url": "/v1/charges",
  "has_more": false,
  "data": [
    {
      "id": "ch_17SY5f2eZvKYlo2CiPfbfz4a",
      "object": "charge",
      "amount": 500,
      "amount_refunded": 0,
      "application_fee": null,
      "balance_transaction": "txn_17KGyT2eZvKYlo2CoIQ1KPB1",
      "captured": true,
      "created": 1452627963,
      "currency": "usd",
      "customer": null,
      "description": "thedude@grepinnovation.com Account Credit",
      "destination": null,
      "dispute": null,
      "failure_code": null,
      "failure_message": null,
      "fraud_details": {
      }, …….

Your customer object will look as follows:

{
  "id": "sub_7hy2fgATDfYnJS",
  "object": "subscription",
  "application_fee_percent": xxxx,
  "cancel_at_period_end": false,
  "canceled_at": xxxx,
  "current_period_end": 1455306419,
  "current_period_start": 1452628019,
  "customer": "cus_7hy0yQ55razJrh",
  "discount": xxxx,
  "ended_at": xxxx,
  "metadata": {
  },
  "plan": {
    "id": "gold2132",
    "object": "plan",
    "amount": 2000,
    "created": 1386249594,
    "currency": "usd",
    "interval": "month",
    "interval_count": 1,
    "livemode": false,
    "metadata": {
    },
    "name": "Gold ",
    "statement_descriptor": null,
    "trial_period_days": null
  },
  "quantity": 1,
  "start": 1452628019,
  "status": "active",
  "tax_percent": null,
  "trial_end": null,
  "trial_start": null
}

To stream your Stripe data, you will use Webhooks. You can register some events with Webhooks, and every time any event happens, Stripe will push the data in Webhooks. Webhooks play an important role in providing the functionality that Stripe supports. You have to register some events to it and every time there is any trigger, Stripe will push a message to your Webhook. You can know more about Stripe Webhooks from here.

Step 3: Prepare Stripe Data for Cloud Data Warehouse (Using Snowflake as an Example here)

Depending upon your choice of the cloud data warehouse, the process of preparation of Stripe Data can vary as the supported formats and the steps required can vary with the cloud data warehouse your organization is using. In this method, we will be describing the general steps to achieve the same and taking an example with the Snowflake Cloud Data Warehouse for your Stripe Power BI integration.

Firstly, You will need to create a well-defined schema in Snowflake for ingesting data into it. All the data in Snowflake is organized in tables with a set of columns. Each column has a specific data type.

Snowflake supports a variety of data types. You can read more about their data types here. Snowflake also supports multiple types of semi-structured data types – JSON, Avro, XML, ORC, etc.

You can load data from Stripe to Snowflake by firstly creating a schema in Snowflake, where you can map each API endpoint to the table. Each key inside the Stripe API endpoint response needs to be mapped to a column in the table. These keys need to have the right conversion to a Snowflake data type.

If you are using other cloud data warehouse services, you can check out the steps for the same in a step-by-step guide here for Google BigQuery, Amazon Redshift.

Step 4: Load Data into Cloud Data Warehouse (Using Snowflake as an Example here)

The steps of loading the Data can vary depending on the cloud data warehouse service of your choice for the Stripe Power BI integration. Here in this example, we will be using Snowflake as a medium to explain the generalized steps. If you are using other cloud data warehouse services, you can check out the steps for the same in a step-by-step guide here for Google BigQuery, Amazon Redshift.

Snowflake supports data loading in a bulk using the ‘COPY INTO’ command. The file contains data usually in JSON format, which is stored in a local file system or Amazon S3 bucket. It is followed by a COPY INTO command on the Snowflake instance.

Your files can also be pushed in Snowflake using the PUT command, before the COPY INTO command.

Another approach to load data in Snowflake is by uploading your data directly into Snowflake using the services of Amazon S3. You can use the following command to copy data from Amazon S3 to Snowflake. This command uses pattern matching and includes source, destination, and a set of parameters to specify your copy operation.

copy into table_name   
from s3://snowflakebucket/data/abc_files 
credentials=(aws_key_id='$KEY_ID' aws_secret_key='$SECRET_KEY')   pattern='filename.JSON';

You have successfully loaded data from Stripe to a cloud data warehouse. Further steps need to be carried out to implement the Power BI connector that is offered by the cloud data warehouse provider.

Step 5: Connecting Cloud Data Warehouse to Power BI using the Connector

The steps to connect Power BI to the cloud data warehouse of your choice can be found here for Google BigQuery, Amazon Redshift.

Here is an example we have used Snowflake to demonstrate the easy way of implementing the connector for your Stripe Power BI integration.

You can use the following steps to connect Snowflake to Power BI using the built-in connector:

In Power BI, select “Get Data” from the home ribbon. From the drop-down menu, select “More…”.

Then, in the left-side panel, select “Database”. Click “Snowflake” from the right-side panel, and then select “Connect”.

Cloud Data warehouse Selection Stripe Power BI
Image Source: Microsoft Power BI

In the Snowflake window, state your server name and warehouse name. Select “DirectQuery” in data connectivity mode and click “OK”.
Note: You can obtain your server name from your Snowflake URL. Use the following format to identify the server name in the URL.

https://servername/console#/internal/worksheet
Server Credentials - Stripe Power BI
Image Source: Microsoft Power BI

Then, provide your Snowflake username and password to connect to your Snowflake account. Click “Connect”.

User Credentials - Stripe Power BI
Image Source: Microsoft Power BI

Alternatively, You can choose the Microsoft Account option. In this case, the AAD (Azure Active Directory) integration needs to be done from the Snowflake side. You can get more information regarding this information in this Snowflake documentation.

Microsoft Azure AAD Login - Stripe Power BI - Transfer Data for Free from Stripe to your target destination
Image Source: Microsoft Power BI

After the completion of this step, in the navigator panel, select your data table and click “Load”.

Table view Power BI - Stripe Power BI
Image Source: Microsoft Power BI

Now you can visualize your data conveniently on the Power BI platform.

Congratulations!, your Stripe Power BI integration is successful using the integrations of the cloud data warehouse.

Limitations for Stripe Power BI Integration via Cloud Data Warehouse Integration

  1. The methodology is too technical to carry out for an employee with no technical background. 
  2. Requires a properly set-up cloud data warehouse, which may not be feasible depending on the context of scale and data pipeline of the company.
  3. As the scale of Stripe data increases, it would become difficult to constantly and manually insert and update the data in the cloud data warehouse.
  4. Using the manual method will make it difficult to identify and remove duplicate records in the database.

The above-mentioned issues can be solved using a fully automated ETL tool like Hevo. Hevo will ease the process of data loading from Stripe to Power BI and this process has been explained in the next method.

Method 3: Stripe Power BI Integration using Hevo

Hevo Logo. - Transfer Data for Free from Stripe to your target destination

Hevo is a No-code Data Pipeline. It supports pre-built data integrations from 100+ data sources, including Stripe. Hevo offers a fully managed solution for your fully automated Stripe Power BI integration process and will let you directly load data to Power BI from Stripe. It will automate your data flow in minutes without writing any line of code. Its fault-tolerant architecture makes sure that your data is secure and consistent. Hevo provides you with a truly efficient and fully automated solution to manage data in real-time and always have analysis-ready data at Power BI.

Now you can transfer data from Stripe to your target Destination for Free using Hevo!

Hevo focuses on three simple steps to get you started:

  • Connect: Connect Hevo with Stripe and various other payments, sales & marketing data sources by simply logging in with your credentials.
  • Integrate: Consolidate your payments & customer data from several sources in Hevo’s Managed Data Integration Platform and automatically transform it into an analysis-ready form.
  • Visualize: Connect Hevo with your desired BI tool such as Power BI and visualize your unified payments and sales data easily to gain better insights.

As can be seen, you are simply required to enter the corresponding credentials to implement this fully automated data pipeline without using any code.

The detailed documentation of the functionality offered can be found here.

Sign up here for a 14-Day Free Trial!

Let’s look at some salient features of Hevo:

  • Fully Managed: It requires no management and maintenance as Hevo is a fully automated platform.
  • Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer. 
  • Real-Time: Hevo offers real-time data migration. So, your data is always ready for analysis.
  • Schema Management: Hevo can automatically detect the schema of the incoming data and maps it to the destination schema.
  • Live Monitoring: Advanced monitoring gives you a one-stop view to watch all the activities that occur within pipelines.
  • Live Support: Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

Explore more about Hevo by signing up for a 14-day free trial today.

Conclusion

In this article, you learned about Stripe, Power BI, and three different methods to implement a Stripe Power BI integration to load your Stripe customer and payment data to Power BI for analysis. You can use the manual methods to import that data into Power BI and visualize it to gather important insights. But if you want to automate your data flow, you can use Hevo.

Hevo provides access to the most accurate and real-time data without any coding. You will have the flexibility to choose the data warehouse and BI tools such as Power BI, Looker, Tableau, etc. Hevo offers data integrations from 100+ data sources.

Visit our Website to Explore Hevo

Give Hevo a try and Sign Up for a 14-day free trial today! You can now transfer data from sources like Stripe to your target destination for Free using Hevo.

Free No-Code Data Pipeline for Stripe