SQL Server Data Warehouse Project

The estimated reading time for this post is 3 minutes

Data warehousing is a technique and concept for gathering and centralizing data for reporting and analysis purposes. Although the data warehouse project is subjective case by case, there are some common elements in most data warehousing implementations. Knowing these components will make you more clear to have better plan and build an effective data warehousing solution.

Running a business effectively can present a significant challenge, particularly as it grows or is affected by trends in its target market or the global economy. To be successful, a business must be able to adapt to changing conditions. That requires individuals within the organization to make good strategic and tactical decisions. However, the following problems can often make effective business decision-making difficult:

  • Key business data is distributed across multiple systems.
  • Finding the information required for business decision-making is time-consuming and error-prone.
  • Most business decisions require the answer to fundamental questions.

What is Data Warehouse?

A data warehouse centralizes distributed data that prevents effective business decision-making. There are many definitions of “data warehouse” .Data warehouse is a centralized store of business data that can be used for reporting and analysis to inform key business decisions.

Data warehouse has the following characteristics:-

  • Contains Large Volume of Data.
  • Optimized for Read and Analytical Operations.
  • Incremental Data Loading.
  • Provides basis for Enterprise BI

Architectures

There are many ways that you can implement a data warehouse solution in an organization. The followings are the common architecture approaches:-

  • Central Data Warehouse
  • Departmental Data Marts
  • Hub and Spoke

Components

dw components
image-1696

A data warehouse consists of few integrated components to fetch data from operational systems/Data Sources and then transform, finally load it into the data warehouse. commonly, data warehouse solutions have the following components:-

  • Data Sources
  • ETL and Data Cleansing Process
  • Staging Area
  • Data Warehouse
  • Data Models

The following components are additional and are rarely deployed in Data Warehouse Solutions:-

  • Master Data Management
  • Reference Data Management
  • Metadata Management
  • Operational Data Store (ODS)

Project Roles

There are many roles involved in Data Warehouse project, the following roles are the basis roles, One person can be in more than one role.

  1. Project Manager
  2. Solution Architect
  3. Data Modeler
  4. Database Administrator
  5. Infrastructure Specialist
  6. ETL Developer
  7. Business Users
  8. Testers
  9. Data Stewards

In addition to ensuring the appropriate assignment of these roles, you should consider the importance of executive-level sponsorship of the data warehousing project. It is significantly more likely to succeed if a high-profile executive sponsor is seen to actively support the creation of the data warehousing solution and its associated costs.

Data Warehousing Platform

SQL Server includes components and features that you can use to implement various architectural elements of a data warehousing solution as following:-

Database Engine:

The database engine component includes many features that are directly relevant to a data warehouse project, the main feature being a robust relational database.

  • In-Memory OLTP and in-memory optimizations, which enable businesses to have a high-performance, real-time transactional database, while also layering column-based query processing on top to achieve up to 10 times the query performance of traditional row-based storage.
  • Temporal Tables that provide businesses with time context to data being captured by maintaining a full history of all data changes.

SQL Server Integration Services (SSIS)

A component for building data integration and transformation solutions, commonly referred to as Extract, Transform, and Load (ETL). Improvements in SQL Server 2016 include:

  • The ability to connect to Hadoop (Big Data) and Teradata sources.
  • Incremental package deployments, which enable individual packages to be upgraded without having to deploy the project as a whole.

Data Quality Services (DQS)

  • Consisting of Data Quality Server and Data Quality Client, businesses can use DQS to build a knowledge base around their data. It is then possible to standardize, de-duplicate, and enrich this data.

Master Data Services (MDS)

  • A component that gives businesses the tools to discover and define non-transactional lists of data. The goal is to compile a maintainable master list of business rules and data types.

SQL Server Analysis Services (SSAS)

  • An online analytical data engine that provides analytical data upon which business reports can be built (see Reporting Services below). SSAS is able to create two different kinds of semantic models—Tabular or Multidimensional.

SQL Server Reporting Services (SSRS)

  • The main goal of developing a data warehouse is to provide answers to business critical questions. These answers normally take the form of reports. SSRS is a server-based reporting platform that includes a comprehensive set of tools to create, manage, and deliver business reports.

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

Be the First to Comment!

Notify of
avatar
1000
wpDiscuz