SQL SERVER – SQL Basics: Database Careers – Day 7 of 10

This is the 7th post out of my 10 post series of my 10th book – SQL Basics. Today will show the importance of data and information.

You can get that in Paperback (USA) and Kindle (Worldwide).

The Live System

The live system is the one that interacts with our customers and must stay up during all business hours which is often 24-7 in today’s global business world. These databases should be designed to collect the data in transactions that are needed to do business.

This is often called the Online Transaction Processing database (or OLTP database). This OLTP system must respond immediately to user requests through a proper interface (such as an ATM). The ATM is accessing a database based on the customer’s request. The OLTP System is the SQL Server that is holding our OLTP database.

During peak business hours the OLTP system can slow down and cause customer delays. If we were to run a query for a business meeting during this peak time we could slow the live system even further. For this reason any testing or analysis is often done on a copy of the database. This way company work and research can get done without interfering with the customer’s usage of the same database.

Database Developers and Database Administrators

Both the Database Developer and the Database Administrator have their career revolving around live OLTP systems. To build the database that works with ATMs the developer needed to know all the information that would be collected by and shown to the ATM. Based on this specification they can build the correct tables and write the correct queries. Once that is up and running then these tables and queries get used over and over again by each customer who uses an ATM.

If the SQL Server goes down then this can cause the ATM to shut down. We need alerts to let us know when the system is running slowly or going down. When this happens we need to react quickly to restore the system. It is the job of the Database Administrator to monitor and protect live database systems.

The Analytical System

It’s very common for the business to want to see a query about how things are going. Running a query on the live server would only cause to slow down the OLTP system. There is a need to copy the database to a new SQL Server so it can be analyzed. Once it is analyzed by a SQL professional it will need to become a report that other people in the company can view. The data from this database is often turned into an Excel spreadsheet or an internal web page.

The OLTP system may be taking millions of transactions per day but you might only copy the latest data over once per day. This new system will not need to specialize in transactions but instead need to be very fast at analyzing queries. This new offline system is known as the Online Analytical Processing Database (OLAP). This is where data turns into information.

So there are three major steps to turning data from your OLTP system into information via the OLAP system.

1. Copy the live data to an OLAP system.

2. Run the analysis on the data that is needed.

3. Deliver the data in reports to the right people.

The BI Developer

The process of copying the live OLTP database into an OLAP database to deliver information is known as a “Business Intelligence” Solution (Often called BI).

The BI developer does not work on live data but instead analyzes data that came from the live system. The BI developer uses a suite of tools that is part of SQL Server to move data, analyze it into information, and build company reports.

To tool used to move data from one system to another is called SQL Server Integration Services (SSIS). The tool used to analyze the data is called SQL Server Analysis Service (SSAS). The tool that turns this information into human readable reports that can be posted or e-mailed is called SQL Server Reporting Services (SSRS). These three tools make up the BI suite for the BI Developer to use. An Administrator or Developer can move on to BI once they are certified or have significant experience. Joes 2 Pros will have a BI certification series ready for you by December 2013.

Action Item

Get the book for yourself and your friend. This is just a reference everyone must have it.

Available in Paperback (USA), Kindle (Worldwide) 

Reference: Pinal Dave (https://blog.sqlauthority.com)