Welcome back, today we begin our module on analytical technologies.
In this module, we'll focus on the applications and
tools that are used to store, extract and analyze data.
In our first module, we learnt how events and characteristics in the real world,
are captured by source systems.
In this video, we'll definitely pick up where we left off, and
talk about the various ways in which data can be stored.
Given that we're potentially capturing massive amounts of data in our
source systems.
It's natural to ask where the heck does all the stuff go?
Well, it turns out that each source usually has it's own storage system to
hold data relevant to that system.
Unfortunately, that isn't necessarily ideal for us as analyst for a few reasons.
First, it's likely that the source's storage system is optimized for
functional performance, not for data extraction and analysis.
As an example, you may have seen the terms online transactional processing or OLTP.
An Online Analytical Processing OLAP or OLAP.
These terms refer to storage systems that are optimized for business operations and
transactions versus those that are optimized for analytics.
While it's possible to perform analytics on transactional systems,
it's often much easier to do it on analytical systems.
The second challenge with source store storage systems is that they
often contain a lot more information than we really need for analytics.
It's not uncommon for a source database to contain all sorts of internal working data
that really doesn't have a use outside the system operation.
We prefer not to have to carry all that extra data indoor analytical environment.
We also need to remember the source system can be critical to the day to
day operation of the business.
We may not want to risk slowing down or even crashing those systems by allowing
direct access to system data by analytical applications.
Finally, because source systems often deal with very high volumes of data,
they may not store data for
very long in order to optimize the overall performance of that system.
This means that if we want the data, or
some subset of the data, to be available for a longer period of time,
we need to grab it and put it in a longer term storage location.
As we mentioned earlier, we do sometimes connect to record resource systems
especially when we access to real time data.
We may even intercept data as it streams through a connection point.
However, a more common solution is to gather data
into a separate storage location.
This may be a central data repository, where data is physically colocated.
It could also be a virtual repository, where the data is physically located in
different places but appears to the user as though it's in a common location.
And it could be a combination of these two things or a semi-centralized repository.
So we've talked a bit about where we store information.
Let's change gears a little and talk about how we store information.
There are many ways to store data, but here we'll cover the two broad mechanisms
that you're most likely to see in the world of analytics, data files and
databases.
We'll cover a number of common data types in a few minutes but
it's useful the first talk about file systems.
A file system is basically just the digital equivalent of
an organized file cabinet.
I take pieces of information, I put them into a folder and
perhaps put that folder into a larger folder.
Think about your own computer,
this is how you probably store most things on your PC or Mac.
The nice thing about a file system is that I can put pretty much
anything I want there and just note it's name and location so I can find it later.
File systems are attractive in that they can handle all sorts of information,
including what we call unstructured data.
We can store documents, spreadsheets, pictures, music, video, you name it.
The file system doesn't really care what's in the file.
The downside of storing data in file systems is that it's not
as obvious how it accessed the data,
given that you are sending in a bunch of different file locations.
It's also unclear how I perform data analysis on something like a document,
photo or a video without some sort of intermediate processing to turn it into
something more structural.
One important example of a file system is the Hadoop Distributed File System or
HDFS, which is a big data manifestation of the file system concept.
HDFS uses massively parallel processing on relatively inexpensive
infrastructure to efficiently store very large amounts of information without
much regard to the data type.
We'll talk a bit more about big data technologies in a later video.
So, what about the data files themselves?
There are many types of data files.
Think of all the different extensions that files on your computer have but
there are few file types that come up most often in the world of data analytics.
The first is what's called a delimited text file.
Normally, a delimited text file contains data that represents
a two dimensional table with columns and rows.
That data itself is stored as text with breaks between the columns and
rows, identified using specific characters or formatting codes called delimiters.
The most common delimiters are commas, tabs and pipes.
The pipe is the vertical line character you see on your keyboard.
You'll often see comma delimited files with the extension CSV which stands for
comma separated values.
Tab and pipe delimited files usually just have the TXT text file extension.
The nice thing about text files is that they are understood by wide variety of
systems and analytical tools.
So it's pretty straight forward to move data from one environment to
another using this file format.
A second file type is an Extensible Markup Language or XML file.
XML is a flexible structure of encoding documents and data that was developed in
the late 90s, primarily to facilitate data sharing over the Internet.
However, it has a wide range of applications from web pages to
applications to messaging systems.
The nice thing about XML is that it is a common standard and it allows for
a more complex structuring of data that something like I'm doing the text file.
The downside is that it requires a more sophisticated interface
to interpret the data and structure for analysis.
A third type of file is a log file.
Log files are generally used to capture event data from a system and
are common in machine data, messaging and web analytics applications.
Log files may or may not follow a standard structure and
they generally require something called a parser to read and interpret the file.
The advantage of log files is that they are very flexible,
it can capture just about any data structure you want.
However, this comes at the expense of a much more complicated process for
reading and using the data.
In fact, there are specific software tools that specialize in parsing log files.
The last type of data file we'll discuss is really a class of
files that are specific to common data analysis tools.
Most tools have their own proprietary file formats for storing data,
along with other information called metadata.
Which describes calculations, operations, or other attributes of the data itself.
Far and away, the most common of these is the Microsoft Excel spreadsheet file.
Even though there are a lot of sophisticated analysis tools out there,
the reality is that a disproportionate amount of actual
analytical work is done in Excel.
And that's not necessarily a bad thing,
just about everyone knows how to do basic operations in Excel.
And there are certain things that are quite frankly just easier to do in Excel,
making it a very flexible tool for both manipulating and sharing data and
analytical results.
However, Excel is just one of many specialized file formats.
Tools like SaaS, SPSS, Tableau, and a whole host of other applications
each have their own specific formats for storing data in standalone files.
Broadly, this means you may need to use that specific application to open and
use those files.
But increasingly, applications are opening up a bit and
building in the ability to ingest and interpret other file types.
It really depends on the tool.
Okay, so we talked about data files.
Now let's move on to databases.
A database is simply an organize collection of data.
When we say database, we're usually referring to both the structure and
design of a data environment, as well as the data itself.
A database seeks to store data in a more complex way
than what could be achieved in a data file.
Specifically, a database usually stores a number of different date entities
with some unifying information about how those entities are arranged or related.
This enables access to a wider array of information in one common environment.
Versus storing that information in multiple data files that may or
may not be tied together.
Usually, a database is constructed using a Database Management System or DBMS.
A Database Management System is a software application use for
creating, maintaining and accessing databases.
There are variety of different types of databases but far and
away the most common is the Relational Database.
In fact, the relational database is so important that we'll spend a later video
in this module talking in detail about this type of database.
And we'll spend all of module three learning how to extract data
from relational databases.
The basic concept behind relational databases, is that we store information in
two dimensional tables, and then to find specific relationships among those tables.
It turns out that this can be a really efficient and effective way of storing
data that is pretty easy to understand which contributes to it's popularity.
The idea was developed by a computer scientist named EF Codd at IBM in 1969 and
1970.
Given that it's been nearly 50 years since then, it's pretty impressive that
the relational database remains the dominant paradigm in data storage.
That having been said, the relational database is not the only type of database.
In fact, there are a number of emerging types of databases that
are being used to handle special types of data, store unstructured data, or
improve performance in the era of big data.
Let's talk about four common Alternative Databases.
Graph Databases, Document Stores, Columnar Databases, and Key-Value Stores.
A graph database is based on graph theory, or
the study of para-wise relationships between objects.
These databases tend to work well with highly interconnected data,
like relationships between people or locations and
have applications in physical and social network analysis.
A document store, as its name suggests, is generally designed to store documents,
along with key pieces of metadata describing those documents.
It's useful for storing unstructured data or different data types in a way that's
a little more useful than a typical file system.
Columnar databases are storage mechanisms which seek to improve the performance of
data access.
By focusing on columns of data tables,
versus the row based approach of relational database systems.
It turns that when we write information into a database,
we usually do it row by row.
Which makes sense when each row of data represents something like a purchase
transaction or a new customer.
However, when we extract data from databases we're usually more
interested in summarizing some attribute across all rows.
Columnar databases tend to be much more efficient at
this type of data extraction operation.
Key-value stores are very simple but power ways of storing data.
They store information in very small pairs.
Typically, a key and a value.
This method of storing data is very flexible
as it doesn't require the extensive design and structure of other data base types.
Without getting into too much detail,
it turns out that storing data in this way is very efficient.
Uses less memory, and
can be used to achieve very high levels of speed in certain types of operation.
However, it also requires more sophisticated
programming to manipulate and extract data.
So let's recap.
We've learnt about two broad types of data storage, Data Files and Databases.
We talked about file systems and specific file formats like Delimited Text Files,
XML Files, Log Files and Application-specific Files.
We introduce the idea of databases and
covered different types of databases like Relational Databases, Graph Databases,
Document Stores, Columnar Databases and Key-value Stores.
As a data analyst, you almost certainly encounter a number of these data storage
mechanism both data files and databases.
And you need to understand how to work with them.
Unfortunately, we can't cover all of them in this course.
But as we mentioned earlier, we will go a bit deeper into relational databases.
And in subsequent courses, we'll use Excel and
more advanced tools to actually execute different types of analysis.