What is Data Warehousing

Top 15 Data Warehousing Tools

JULY 18, 2019

What is a Data Warehouse?

Many people don't know what a data warehouse is or why they would even need one. A data warehouse is a headquarters for all your company data. This might include marketing data, customer data, finance data, IT data-- any type of data you can think of. In order to get one single point of truth for all your data, you need up upload it to a data warehouse so that it can all live together in one place.

A data warehouse makes it much easier to work with very large data sets and put them together and take them apart. Think of a data warehouse like Excel, times a million.

A data warehouse is where you keep your "data lake."

What is a Data Lake?

Think of it like this, a data warehouse is the hole in the ground and your company data is the water that fills the hole in the ground. Your company data is your data lake.

Right now your company data likely lives all over the place, but you can create a unified data lake by slurping all the water from all the different platforms, into a data warehouse, creating a data lake. Improvado is the tool that pushes all your water [data] into that lake-- but we can talk more about that later.

‍

At what point does a company need a Data Warehouse?

Usually a company is ready for a data warehouse once they have so much data that they've outgrown Excel and they have someone on staff with a basic understanding of data science analytics or someone who knows how to use SQL and can manage a data warehouse.

How do you know if you've outgrown Excel? Since the tool has a row limit and a loading limit, updating sheets usually starts to get slow. Usually small agencies or businesses use Excel or Google Spreadsheets until they get to that point, and then it's time to upgrade to a Data Warehouse.

If different teams within one company are looking to match up their data together to get important insight, for example the marketing wanting to map it's data with data from the finance team, a data warehouse would be helpful.

If a business is looking to build customized models and algorithms through business intelligence data-- that is something that might not be possible through out of the box reporting platforms.

An example of a customized model would be, let’s say Company A has a Google Bitquery Data Warehouse and they found out that due to specific customer behavior data, they are able to build their own formula that can tell them exactly what to do to optimize their marketing ad spend. This is a formula that is proprietary to them and their business model and flow. Other examples of customized models or algorithms would include operational inventory checks, customer data, and accounting.

‍

How do you pull reports from a Data Warehouse?

Historically, in order to pull any meaningful data from your data warehouse, someone on your team would need to know SQL and could query the data warehouse by writing a question in the language of SQL. This process is not automated and so this means people are often asking lots of one-off requests to the one guy who knows SQL on the team.

However, now Visualization tool have made this process easier. Your Visualization tool, (whether it's Looker, Tableau, Google Data Studio) sits on-top of your data warehouse and makes it possible for anyone to query the data and create reports and charts and visualizations and dashboards.

‍

How do you get your data into a Data Warehouse?

Historically, this part has been pretty tricky. That's because your company data lives in dozens of different tools. Marketing data is particularly difficult to wrangle. For example Company A might have data in Salesforce, Google Analytics, Facebook Ads Manager, Google Ads, Mailchimp, Adroll, and more.

‍

There are 3 ways to get data into your Data Warehouse:

1- Manually Uploading Data‍

Exporting data from each platform separately into spreadsheets on a daily, weekly or monthly basis and uploading it into your data warehouse

Pros:

There are no benefits to performing this process manually

Cons:

• This process is time consuming.
• It's also expensive from an opportunity cost standpoint.
• Instead of focusing on high value strategic decisions and gathering insights, most marketers are focusing on low value data cleansing and spreadsheeting.
• Greater probability for human error when copy and pasting data.
• The manual process takes up 10-40 hours per week and leaves little time for actual analysis. Marketers need access to data in real-time.

2- Code your own API integrations

Utilize your in-house developers to build and manage APIs that sync with each platform in order to aggregate data into your data warehouse.

Pros: This route will save marketers a lot of time, by shifting the manual workload to be automated by developers.

Cons: There are quite a few issues with this route:

• It will require a lot of time for developers to build various API integrations for the data warehouse.
• Any new API integration requests will compete for development time vs. other projects at the agency. This can often lead to a half automated, half manual process. Defeating the purpose altogether.
• Supporting integrations will be time consuming due to stability issues and new feature releases. It is a maintenance nightmare to take on this task.
• There are many other nuances that constantly arise like platform limitations, data clearance and normalization, dealing with errors, and more.

3- Use a Data Aggregation Tool

Instead of building all your own API integrations internally, use a tool that automates this for you in minutes. It's a plug & play way to get all your data into your data warehouse in a matter of clicks.

For example, by simply logging onto Improvado you can sync with over 100 different marketing platforms, where data is instantly pulled into your data warehouse or the one Improvado provides for you. That way you can use the tool to map metrics, pull reports, and visualize data faster than ever before possible.

Pros:

• No need to ask developers for help. It's easy for any marketer to aggregate data from multiple platforms into BI, visualization, or spreadsheets.
• High-touch customer service to help you customize your reports and dashboards.
• 100+ integrations that include everything from Facebook to Snapchat to Linkedin and so much more.
• Perfect for Marketing Leaders & Analytics Leaders looking to build a clean flow of data that puts an end to one-off marketing/developer requests, once and for all!

Cons:

• ‍‍Marketers may still want to use a visualization or BI tool like Looker, Tableau or Data Studio, but they also have the option to view their data in Improvado’s custom visualization tool.

‍

Key takeaway: Improvado is a great data aggregation tool that can send all of your data to any data warehouse you choose with ease, saving you time and effort.

Harvard Business Review and Alteryx ran a study that found that while we all agree that marketing analytics is critically important, executives are not getting what they need out of the tools they’re using.

Our mission is to help marketers find the marketing analytics tools they need to make reporting and optimization a breeze.

‍

These top 15 Data Warehousing tools all have their own benefits when it comes to storing and analyzing data. Once you have your data warehouse tool, having a tool like Improvado that can aggregate your data from all of the platforms you use and send this data to your data warehouse can be extremely useful.

‍

Each of the following data warehousing tools vary. Read through them carefully to determine which tool is the right fit for your marketing needs.

‍

Top Data Warehousing Tools

‍

1. BigQuery

What is BigQuery?

BigQuery is offered by Google and is an enterprise-level, cloud-native data warehouse tool. The platform is designed to save time with storing and querying large datasets by enabling super-fast SQL queries against multi-terabyte datasets in seconds, giving users real-time data insights.

Google BigQuery offers automatic data transfer and full control over the access to stored data. It also makes its easy for users to read and write data in BigQuery via Cloud Dataflow, Spark, and Hadoop.

Pros:

• Low cost. Pay as you go.
• Robust query language
• Large datasets can be analyzed in seconds

Cons:

• Learning curve for non-IT users
• BigQuery API requires coding skills

Pricing

Google BigQuery offers a “pay-as-you-go” pricing system for its data warehousing platform.

Link

https://cloud.google.com/bigquery/

Video

https://youtu.be/eyBK9nj-7AA

‍

2. Redshift

What is Redshift?

Amazon Redshift is part of the popular Amazon Web Services. The platform is an analytical, fully-managed data warehouse that is capable of handling petabyte-scale data and enables analysts to run queries from it within seconds. Redshift offers limitless scalability on Amazon’s architecture, with no up-front costs.

Redshift is a cost-effective tool for data warehousing, analyzing nearly any data type using standard SQL. The Amazon Web Services platform allows you to automate the majority of common administrative tasks to monitor, manage, and scale your data warehouse. Redshift continuously monitors the health of the cluster, and automatically re-replicates data from failed drives and replaces nodes when needed.

Pros:

• Redshift keeps all of our data together for analytics
• Allows users to store our large datasets in easily accessible databases.
• Redshift clusters are quick and easy to deploy.

Cons:

• Some of the data analytics are difficult based on the size and clarity of data.
• Optimizing databases can be difficult at times.
• Because it is its own database with it's own syntax you can't 100% use postgres syntax or plug-ins directly.
• Doesn't have many options for desktop clients

Pricing

Pricing for Amazon Redshift starts from $0.25 per hour for one Redshift instance, up to $1,000 per terabyte per year for larger deployments.

Link

https://aws.amazon.com/redshift/

Video

https://youtu.be/AUvn49gey8Y

‍

3. Microsoft SQL Server Integration Services

What is MS SSIS?

Microsoft’s SQL Server Integration Services is a data warehouse tool that performs ETL operations, such as extracting, transforming, and loading data. SQL Server Integration also includes a rich set of built-in tasks. The tool also tightly integrates with SQL Server and Microsoft Visual Studio, handles data from various sources in the same package, and makes it easier to maintain and package configuration. Microsoft SSIS can also handle more difficult data, including MSMQ, HTTP, and FTP.

Pros:

• Source system connectivity (API's, SQL DB's, Olap Cubes, etc.)
• UI which allows less technical people to quickly and easily complete tasks
• Visual programming makes configuration easy and accessible
• The ability to code also allows users to implement complex logic for data manipulation

Cons:

• Version control sometimes seems to be an issue when many different sources are coming into play
• Cannot debug properly inside of Script component
• Different components could have different syntax or data type definition
• Not enough scripting learning materials

Pricing

Microsoft SQL Server pricing depends on the edition.

Link

https://docs.microsoft.com/en-us/sql/integration-services/sql-server-integration-services?view=sql-server-2017

Video

https://youtu.be/T9ngvTpUj3w

‍

‍

4. PostgreSQL

What is PostgreSQL?

PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.

Pros:

• PostgreSQL comes with many features aimed to help developers build applications, administrators to protect data integrity and build fault-tolerant environments, and help you manage your data no matter how big or small the dataset.
• In addition to being free and open source, PostgreSQL is highly extensible. For example, you can define your own data types, build out custom functions, even write code from different programming languages without recompiling your database!

Cons:

• It can be a bit harder to get community support
• Reported issues with scale.
• Replication is not yet as well implemented.

Pricing

• Not provided by vendor

Link

https://www.postgresql.org/

‍

‍

5. MySQL

What is MySQL?

Oracle MySQL Cloud Service delivers a secure, cost-effective and enterprise-grade MySQL database service. Built on MySQL Enterprise Edition and powered by the Oracle Cloud, it provides a simple, automated, integrated and enterprise ready MySQL cloud service, enabling organizations to increase business agility and reduce costs.

Pros:

• Easy to Use - MySQL Cloud Service provides a simple and user-friendly, web-based console to manage your MySQL Cloud instances.
• Self-service Provisioning - Create preconfigured MySQL databases optimized for performance, and cloud tooling automates database instance lifecycle management.
• Elastic Scalability - Automated scaling enables users to elastically scale compute and storage resources as well as MySQL replicas.
• High Availability - MySQL Replication and MySQL Replication monitoring improve application uptime and minimize service disruptions.
• Multi-layered Security - MySQL Cloud Service offers enterprise-grade security features including network access control, MySQL Enterprise Firewall, MySQL Enterprise Authentication, MySQL Enterprise Encryption & Transparent Data Encryption and MySQL Enterprise Audit.
• Backup and Recovery - MySQL Cloud Service supports hot, online backup, point-in-time recovery and on-demand snapshots.
  ‍

Cons:

• Hard to scale
• Performance degrade

Pricing

• Not provided by vendor

Link

https://www.mysql.com/cloud/

‍

‍

‍

6. SAP HANA

What is SAP HANA?

SAP is an integrated data management suite for open client/server systems. Features of SAP include a simplified data warehouse architecture, integration with any system, and support for both cloud and on-site deployment. The data tool provides transparent and highly flexible business solutions and follows a modular concept for space utilization and easy setup.

Pros:

• Integrates, stores, and manages company data from all sources
• Pre-configured information models are better than average
• Security authorization protocols and flexibility
• Integration capabilities

Cons:

• Certain pruning can only take place on metadata rather than posted data
• High runtimes for query execution
• It is not very user-friendly for beginners
• Not the best for larger datasets

Pricing

You will need to contact the company for pricing details.

Link

https://www.sap.com/products/hana.html

Video

https://youtu.be/PFj377TowAo

‍

7. Microsoft Azure

What is Microsoft Azure?

Migrate your SQL Server databases without changing your apps. Azure SQL Database is the intelligent, fully managed relational cloud database service that provides the broadest SQL Server engine compatibility. Accelerate app development and simplify maintenance using the SQL tools you love to use. Take advantage of built-in intelligence that learns app patterns and adapts to maximize performance, reliability, and data protection.

Pros:

• Migrate your SQL Server databases without changing your apps
• Migrate massive databases and scale up to 100 TB
• Maximize ROI by migrating to the cloud
• Security
• Scalability
• Cost-effectiveness

Cons:

• Requires management
• Requires platform expertise

Pricing

You will need to contact the company for pricing details.

Link

https://azure.microsoft.com/en-us/services/sql-database/

‍

‍

‍

8. SAS

What is SAS?

SAS is one of the leading data warehouse tools, allowing users to access data across many different sources. SAS Data Management can perform complex analyses and deliver information across organizations

With SAS, activities are managed from central locations, providing users with the ability to  access the tool remotely, from wherever they are, as long as they are connected to the internet. Raw data files can be viewed in external databases, and data can be managed using various data tools, as well as data displayed in statistical graphics and reports.

Pros:

• GUI is easy to understand
• Training provided
• Ability to easily create complex models
• Built-in quality knowledge base and ability to create customized QKBs

Cons:

• Sometimes errors are not easy to understand
• SAS is too pricey for some companies, and doesn't make itself accessible
• Learning curve for those with no SAS experience

Pricing

No pricing information is available on the website. You can contact the company for a quotes, demos, and free trials.

Link

https://www.sas.com/en_in/home.html

Video

https://youtu.be/yGGS4lNCxJc

‍

9. Oracle

What is Oracle?

The Oracle autonomous data warehouse software is a collection of data that is treated as a unit. The idea behind the database is to store and retrieve related data. Oracle manages large amounts of data and allows multiple users to access the same information.

The “autonomous” data warehouse tool offers real application testing, working for both real application and single-instance clusters. Another features is a Hi-Speed Connection, which allows for huge amounts of data to be moved quicker. Oracle works seamlessly with Windows and Linux platforms, and offers virtualization support, allowing connection to a remote database, view, or table.

Pros:

• Ease of set up and computing power
• Numerous features and possible customizations
• Intuitive graphic tools

Cons:

• Complexity. Learning curve for some users.
• For DBA without experience, a configuration is complicated by the amount of details that must be considered.
• A bit expensive, so it is not accessible to everyone.

Pricing

No pricing information is readily available on the website. You can contact the company to request a quote.

Link

https://www.oracle.com/database/data-warehouse.html

Video

https://youtu.be/tZMZODoi2xw

‍

10. Panoply

What is Panoply?

Panoply boasts that it is the first “Smart Cloud Data Warehouse” in the world and that it delivers the fastest time to insights in the industry by eliminating the development and coding that is typically used with managing, integrating, and transforming data. The tool’s AI technology automatically enriches, transforms and optimizes complex data, making it easy to gain actionable insights.

Panoply provides a cloud-based data warehouse with scalability, high availability and fast querying built in. It also provides end-to-end data management, automating all data preparation tasks.

Pros:

• Great query optimization
• Views automatic materialization
• Great customer support
• Easy to setup and get started

Cons:

• Some issues occasionally with materialization processes
• Lack of built-in visualization tools.
• Web UI can sometimes be slow

Pricing

Pricing for Panoply starts at $185 per month for 25 million rows, up to 12.5 GB of storage and unlimited queries.

Link

https://panoply.io/

Video

https://youtu.be/MKAwbtcmf6E

11. Teradata

What is Teradata?

Teradata is a data warehouse tool for viewing and managing huge amounts of data. The company boasts that its Vantage solution is “the platform for Pervasive Data Intelligence” and the “only software of its kind, capable of managing all of the data, all of the time.” Vantage offers full integration with many tools and languages and leading analytic engines.

Teradata offers simple solutions designed for organizations of varying sizes, providing fast and insightful analytics. Users can get answers to complex questions about their data on a platform built on a parallel architecture.

Pros:

• Highly scalable
• Processes large amounts of data with ease
• Vast pre-built functionality
• Easy to navigate

Cons:

• Can be a bit of expensive for smaller businesses
• Cloud option costlier than other providers
• Installation can take quite a bit of time

Pricing

No pricing information is available for Teradata.

Link

https://www.teradata.com/

Video

https://youtu.be/FvSgENWIvTI

‍

12. IBM-DataStage

What is DataStage?

IBM InfoSphere DataStage is a business intelligence and data warehouse tool that allows users to integrate data across a variety of enterprise systems, leveraging a parallel framework either on-site or in the cloud. DataStage supports universal business connectivity and extended metadata management. The BI tool is capable of solving complex big data challenges, provides real-time data integration, and supports Hadoop and Big Data.

Pros:

• Excellent performance for integrating data
• 100% visual development, operational and monitoring environment
• Complex transformation capabilities without writing code

Cons:

• Pricing
• Lack of a strong web development environment
• Metadata propagation in Jobs is somewhat complex.

Pricing

You will need to contact the company for pricing details.

Link

https://www.ibm.com/us-en/marketplace/datastage

Video

https://youtu.be/wH5XGYLxBv4

‍

13. Ab Initio

What is Ab Initio?

Ab Initio is a data warehouse tool for data analysis, GUI-based parallel processing, and batch processing. Features of Ab Initio include management of metadata, as well as the ability to manage graphs and control ETL processes, run and debug jobs, and trace execution logs. Components can also execute simultaneously on various branches of a graph.

Pros:

• Very effective for high volume data loads
• Wide range of components that help in extracting data from any source
• Transformations are easy to write and have the drag-n-drop facility making it more intuitive.

Cons:

• Knowledge of UNIX is needed to extract maximum output
• Every product comes under a license per user.
• Requires equal invest in hardware to make optimum use of the product.
• Very expensive compared to other options

Pricing

Pricing details aren’t available for Ab Initio. You can contact the company for pricing information.

Link

https://www.abinitio.com/en/

Video

https://youtu.be/YRU1YXOapbY

‍

14. Pentaho

What is Pentaho?

Pentaho is an open source data warehouse and business analytics tool that is strives for continuous innovation with its modern, integrated, embeddable platform built for the future of analytics, including diverse and big data requirements. Pentaho features an interactive and simplified approach that allows users to access, discover, and merge data of any size or type. The tool features integration with big data without any need for coding, visualization of data with custom dashboards, and a platform for accelerating the data pipeline.

Pros:

• Automates the steps for execution
• Easy to use
• Wide range of tools
• Able to analyze data from multiple sources quickly and efficiently
• Versatile and effective tool

Cons:

• High basic requirements for execution.
• Lack of detailed comments in error messages
• Although it has a large community, there is not much reliable documentation

Pricing

No pricing information is available for Pentaho.

Link

http://www.pentaho.com/

Video

https://youtu.be/VXYqK8V8rwQ

‍

15. Solver BI360

What is Solver BI360?

Solver BI360 is a comprehensive business intelligence platform, providing 360º insights into data, using reporting, data warehousing, and interactive dashboards. BI360 drives effective, data-based productivity and includes features like user-friendly forecasting and budgeting, Excel-based reporting with predefined templates, and easy configuration. Users can view data in the Data Explorer and easily add dimensions and modules.

Pros:

• Easy cross-platform reporting
• Flexibility
• Great customer support
• Ability to streamline reports

Cons:

• Lack of version and source control for reports on the Web portal
• Can be time consuming switching between design mode and run mode
• Error messaging is not always clear and concise.

Pricing

You will need to contact the company to request pricing details.

Link

https://www.solverglobal.com/

Video

https://youtu.be/nrpcXKWcvc8