Building an Online Data Warehouse Part 3

Visualizing the Data

A picture is worth a thousand words.  No doubt, a well-crafted chart from sound data is similarly impactful.  An armful of those meaningful charts can lead to those a ha! “good to know!” moments that give your business that critical and much needed edge.

In our last blog, we explained that although marvin.'s own visualization engine is still in development, its analytical engine can currently be used for analysis by writing queries within the database. Soon, through marvin.’s user interface, or by leveraging other Google Technology Partners like Tableau or BIME, you will be able to create charts and visualizations on top of this database.  Doing so can help make sense of your data, identify patterns and trends, create reports, make measurements and ultimately help you learn and make decisions on how to improve your business- an ultimate cloud BI solution.

Currently, marvin. allows users to  configure databases in a way that is friendly to visualization engines available on the market. Databases can be set up to help data visualization engines recognize fields like text versus numbers versus dates and other options that help provide  meaningful insight. Ultimately, this visualized data can be combined to create dashboards where business users can see a tapestry of charts  rather than  one piece of data at a time.  Users will be able to define multiple dashboards, multiple charts, and multiple filters-- all based on their data.  

Visualizations conbimed with an online data warehouse open up a whole new world of business insight by allowing users to recognize and identify patterns that are almost impossible to see just by looking at numbers.  Dashboards with a whole host of these visualizations can provide you with critical information and rare insight about your business that you never had access to before.

Building an Online Data Warehouse Part 2

Google Cloud Platform is a great option for companies looking to build a Data Warehouse on the cloud.  As described in Part 1, you have two options: hire a developer to code and integrate on top of those great APIs, or use tools like marvin. to help you automate some of the processes that are required to build an online data warehouse. In Part 2 and 3,  we will discuss the most valuable sections of a Data Warehouse, Analyzing and Visualizing your Data.

Analyzing the Data

marvin.’s super powers come from Google BigQuery API, an analytical database on the cloud. marvin. automates the process of inserting and storing data uploaded in the previous step. Together, Google BigQuery and marvin. are the analytical engine for your data warehouse. Your new Data Warehouse is limitless in terms of size and analytical computing power. Even better, there is nothing to code, no servers to manage, and no hard drives to setup. To learn more check out our blog:  Google BigQuery: 7 fascinating facts.

With marvin., you create datasets, make tables that host data, and preview that data in a table format. The data is no longer raw. Instead, your precious data is stored like in a huge spreadsheet that can be analyzed very quickly. Think billions of records in seconds. Think Big Data. Think cloud bi.

What if your business only has a few thousand records? No worries. marvin. will manage small data just as well. After all, a small business does not need to analyze a billion records of its data to make informed decisions and leverage insight to compete with other businesses.

Your new analytical engine also allows users to interact with simple SQL statements. If your team does not know SQL, it is also compatible with multiple visualization engines like Tableau and BIME, popular programs that present your data graphically. Visualizing the data is the last piece of the 4 steps of the simplified process.  We will explore data visualization in Part 3.   

Building an Online Data Warehouse Part 1

Google Cloud Platform is a great option for companies looking to build a Data Warehouse on the cloud, with plenty of APIs from which to build.  Currently, the number of APIs which you can choose from to connect your applications to the cloud is growing by the day.  As of now, you have two options: hire a developer to code and integrate on top of those great Google and Applications APIs, or use tools like marvin. to help you automate some of the processes that are required to build a data warehouse.

marvin. is a tool from Bimotics that allows you to set up an online data warehouse without the need to code. Google has created the capability to host the biggest data warehouse in history, Google BigQuery, and marvin. is the bridge you need to make that process as easy as possible.  The steps below will help you get started.

What does it take to set up a Data Warehouse on the cloud? A simplified process for cloud bi would contain the following steps: 

1. Gathering the Data

2. Storing the Data

3. Analyzing the Data

4. Visualizing the Data

Gathering the Data

This first step has historically been the hardest, but advances in on-premise technology and Cloud applications like QuickBooks and SalesForce now provide methods to extract the data through APIs. These are often referred to as connectors. Most of these apps allow users to extract data in formats like CSV or JSON. Even e-commerce platforms allow users to extract their customer, product, order, inventory, and lead information through these connectors. Extracting these files to build a Cloud Data Warehouse is where it all begins. Without access to raw data, it is hard to design and build the right Data Warehouse architecture. Remember the old mantra “garbage in, garbage out”, bad data will always yield bad insight or analysis. Important note before going any further: if your data has exceptions, missing items, or other errors, fix them in the application itself before progressing with your warehouse.

Storing the Data

Storage is another big challenge.  You will need to answer questions such as:

• Where should I store this data and how?
• Is there enough space on my servers and hard drives?
• How much data needs to be stored?
• How often does information need to be added?

After setting marvin. up, pull data from your chosen application (e.g. e-commerce sample). marvin. then allows you to create buckets or folders where these files are to be stored- each relating to specific customers, products, orders, inventory etc.

Organizing data into buckets makes it much easier to refer to later. We suggest you create two types of buckets for each file: one to process into the next step and another for files that have already been processed. For example, we have a file called “Orders_New” and one called “Orders_History.” Not to go into great detail on file names, but naming them as order_YYYYMMDD.csv can help identify when the orders where extracted.

marvin. allows you to store data in any format, but the next step requires that the data be either  in CSV or JSON formats. marvin. will take an additional helpful step in compressing the files into GZIP. This minimizes the storage space used as well as the cost associated with storing the data. Storing your data this way allows you to keep a good archive of your data. marvin. also lets you to upload multiple files or folders into a bucket. Finally, you can preview files, as well as download or delete the ones that you do not need.

In the next part of this series, we will go into the next two steps: analyzing and visualizing data using marvin.