Introduction to Data warehousing and Microsoft Business Intelligence

This article is for the Data warehousing and Microsoft Business Intelligence enthusiasts, ETL, OLAP developers, business analysts, IT professionals etc.

Before we look into the insights of crux on Microsoft Business Intelligence stack, let us think of traditional Data warehousing and Business Intelligence systems first:

Data Warehousing:

Every organization in the world must require some form of data store for storing their own data – be it everyday transactions, organization’s metadata or their own employee’s data etc.

Generally speaking, a data store is nothing but a Database (Relational database).

There are limitations for relational databases w.r.t the size.

As data gets increased over time for organizations, relational database cannot be suitable for managing huge data requirements – Performance, scalability issues arises over time.
So, organizations require a platform what is called as data warehouse for storing their huge data.

A Data warehouse is a relational database that is designed for query and analysis rather than for transaction processing. It usually contains historical data derived from transaction data, but it can include data from other sources. It separates analysis workload from transaction workload and enables an organization to consolidate data from several sources.

In addition to a relational database, a data warehouse environment includes an extraction, transportation, transformation, and loading (ETL) solution, an online analytical processing (OLAP) engine, client analysis tools, and other applications that manage the process of gathering data and delivering it to business users.

The picture below gives high level structure of elements in a general Data warehouse.

Operational data base is nothing but the organizational relation database – which contains everyday transactional data. This data needs to be read (Extract), cleaned and standardized (Transform) and loaded (Load) into data warehouse databases (OLAP database).

From the above perspective, a component called as ETL (Extract, Transform and Load) is inferred.

Generally speaking the destination for ETL may not be OLAP system (Data warehouse database), it can be any medium of data store.

Business Intelligence:

Let’s say, we have built the Data warehouse for our organization. Now, how do the organizations get profited with it?

We require a system which should give the insights out of their data warehouse, this system can be referred to as Business Intelligence.

It is simply the intelligence framework which gives the insights out of their business data and gives the flexibility to the organization to take managerial decisions.

Example: Top management executive may require to take a decision like this about their own product which is there in the market - used by several thousands of customers.

Should we continue to sell our product ‘XX’ in United States in current fiscal year 2015?

To answer this question, this top executive should have basic information like – How is the product XX sales in last few years (let’s say since 2011) – Are the sales getting increased – How is the customer count etc.

All these information can be easily provided by the well-designed Business Intelligence system like Microsoft Business Intelligence stack.

Microsoft Business Intelligence:

MSBI Stack is comprised of three components

i.         SQL Server Integration Services

ii.       SQL Server Analysis Services and

iii.      SQL Server Reporting Services

SQL Server Integration Services:

SQL Service Integration Services is also called as SSIS.

SSIS is the ETL tool from Microsoft.

SSIS is one of the most powerful features in SQL Server 2005/2008/2012/2014. It is technically classified as a business intelligence feature and is a robust way to load data and perform tasks in a workflow. Even though it's mainly used for data loads, you can use it to do other tasks in a workflow like executing a program or a script, or it can be extended.

The best thing about SSIS is its price tag: free with your SQL Server purchase. Many ETL vendors charge hundreds of thousands of dollars for providing similar functionality of what SSIS gives to customers. SSIS is also a great platform for you to expand and integrate into, which many ETL vendors do not offer. Once you get past the initial learning curve, you'll be amazed with the power of the tool, and it can take weeks off your time to market.

Since this article is to give a basic high level understanding on MSBI, we are not focusing on the SSIS features. We would want to exhibit the features in online SSIS video tutorials.

Typical ETL tool is represented using below diagram

Please note that we are not discussing about SSIS features – tasks in it etc.

SQL Server Analysis Services:

Microsoft SQL Server Analysis Services (SSAS) is a tool to help you implement business intelligence

(BI) in your organization. BI makes use of a data warehouse, often taking advantage of

Online analytical processing (OLAP) tool – in this case it is SSAS.

Below diagram represents the usage of SQL Server Analysis services for building OLAP system for an organization in their BI System.

OLAP is nothing but Online Analytical Processing system – which is simply a well-designed Data Warehouse.

Relational database is called as OLTP system – which is Online Transactional Processing system.

OLTP is named as it contains everyday transactional data.

Whereas OLAP contains the historical data – huge data J

SSAS is the tool from Microsoft which is used to build OLAP system – Data warehouse for small/medium/enterprise organization.

OLAP system contains data stored in entities called as Cube – Cube represents data in different dimensions. So, data in OLAP is stored in multi-dimensional manner.

Below diagram represents SSAS Cube – its source and destination entities on high level.

SQL Server Reporting Services:

SSRS is a tool for designing cool, presentation ready professional reports.

Imagine that you are sitting in a presentation meeting at the corporate office of a key customer. You are a senior sales representative for a company that sells high-volume data backup systems, and the solution they decide on will be implemented in several regional data centers around the world. Your team has been preparing for this meeting for months. Your success depends on your ability to demonstrate your competence to the customer and a clear understanding of their needs. Your team has done their homework, and you know the customer has a history of scanning printed medical records and storing them as image files. Based on this information, you are certain that a particular product will adequately provide the file backup facilities for their moderate volume of image files. You have made it a point to familiarize yourself with the capabilities of the system that appears to be the best fit.

During your customer’s opening presentation, they tell you that they have recently made a huge investment into full-motion video-imaging equipment. Now they need a backup system that can handle large file capacities. They are prepared to make an investment that is substantially larger than what you had anticipated for a capable backup solution. Your company began to offer a large-scale solution just a couple of weeks ago, but you aren’t very familiar with its capabilities. You’ve spent so much time preparing to sell the smaller system that you haven’t had time to learn more about this new product. Your associate is doing introductions, and it will be your turn in about 15 minutes.

Discretely, you open your Pocket PC Phone and access the World Wide Web. You log in to your company’s secure report server, select the product catalog report, choose the product category, and then drill down to the new product. The report has a drill-through option that lets you quickly view a detailed specification report for the new, high-volume backup system. After noting the pertinent specifications, you save this report to a PDF file and then choose the customer sales inquiry history report. Looking up this customer, you learn that someone named Julie made an inquiry about two months ago regarding video media backups from this very company.

Using SSRS, we can design best possible reports like the below:

And many more J

Please note that SSIS Packages, SSAS OLAP Cubes and SSRS reports are designed in Microsoft Visual Studio IDE.

This IDE in SQL Server 2005, 2008, 2008 R2 is called as Business Intelligence Development Studio (BIDS).

From SQL Server 2012, Microsoft has renamed it with SQL Server Data Tools (SSDT).

Tutorial 1

Hello Mate,

This is one test case.. will post tutorials here..

Blog redesign is in progress..

Cheers,
Satheesh Panduga