How to Get the Most out of Your Analytics by Using a Data Warehouse
Periscope Data offers integrations with more than a dozen databases. What if you also want to report on data from a SaaS marketing, support, CRM or billing platform? How do you combine data from cloud applications with your internal databases to gain insight into your business?
Your best tactic is to create a data warehouse that consolidates all of your data in a single location. Most businesses nowadays use cloud data warehouses for that purpose.
To populate the data warehouse, you can extract the data you have in SaaS applications and on-premises databases and load it using an ETL (extract, transform, load) tool. Once the data is available, analysts can use it to create reports.
In this post, we'll walk through the process of replicating data to a data warehouse and connecting Periscope Data to the data warehouse to create reports.
Three tiers of the data analytics architecture
Data analytics stacks comprise three tiers: software, data warehouse and business intelligence (BI) software.
Periscope Data partner Stitch provides a simple, powerful ETL service for businesses of all sizes. Signup is simple — you can be moving data from one or more sources to a data warehouse in five minutes.
The last few years have seen the emergence of cloud-native data warehouses like Amazon Redshift, Google BigQuery and Snowflake. Because they run on cloud infrastructure that scales quickly and cost-effectively to meet performance demands, they can handle transformation using the same hardware on which the data warehouse runs.
Periscope Data serves as the third layer of our data analytics stack, useful for building visualizations and running analyses on the data. Periscope can connect with more than a dozen databases, including Amazon Redshift, Google BigQuery and Snowflake.
Setting up a data warehouse
Let's set up a three-tiered data analytics stack, starting with the data warehouse. If you don't already have a data warehouse, choose one that meets your needs. If you choose Redshift, BigQuery, Snowflake or one of the other destinations Stitch supports, you can also follow the setup steps in the Stitch documentation.
Setting up Stitch for ETL
The next step is setting up an ETL pipeline to move data from your sources to the data warehouse. Stitch makes extracting data from a source and loading it into a data warehouse easy. To get started, visit the signup page, enter your email address then enter your name and a password.
Add an integration
Next, add an integration.
For instance, suppose you want to pipe data from Google AdWords into your data warehouse. Click on the AdWords icon to configure the integration:
The documentation linked at the top of the page walks users through the process. Part of the process involves validating your credentials — username and password. You can then select the AdWords accounts you want to replicate:
Each account has multiple tables, so the next step is to choose the ones with data that you want to include in your reporting:
When you click the checkbox next to a table name, you'll bring up another screen that lets you select the fields you want to replicate from that table:
Continue the process for additional tables and fields.
Add a destination
Once you've selected all the tables and fields you want, it's time to select a destination:
Suppose you've chosen an Amazon Redshift data warehouse. Clicking on the Redshift icon brings you to a screen where you can enter your credentials. It asks for the same fields as the corresponding screen in Periscope Data:
Now all the pieces are in place and the data is ready to flow.
When you visit your Stitch dashboard, you'll see that your integration is marked Pending. It takes a little time for Stitch to queue up your replication. If you refresh the screen, you'll see the status change to In Progress.
From the dashboard you can also do things like change the replication frequency and add fields, tables and integrations.
Connecting to your data warehouse from Periscope Data
The final stage of the process is connecting Periscope Data to your data warehouse. A data warehouse is just another database to Periscope Data, so you can click on Settings > Database > Add Database and enter all the necessary information. Be sure to select the proper database type — in our case, Redshift.
Once you're connected, you're good to go! You can click on New Chart, select your newly connected database and use all of Periscope's tools and features just as you would with any data source.
That's all there is to it. Using an ETL tool like Stitch to move data into a data warehouse lets you leverage the power of Periscope Data to correlate and report on data from multiple sources.