Join us
@nataliapolomkina 㝠Mar 11,2025 㝠15 min read㝠779 views  㝠Originally posted on blog.skyvia.com
SQL Server Data warehouse? Sounds boring. Who would want to learn this? But what if I tell you that data analysis is one of the sought-after skills today?
If âdata warehouse SQLâ is a question you frequently type into Google, youâre not alone. As businesses generate and rely on vast amounts of information, understanding how to store, manage, and query it efficiently becomes crucial. Data warehouses are the backbone of modern analytics, and SQL is the universal language with which to interact. Whether youâre looking to optimize your queries, integrate information from multiple sources, or simply understand the basics, this article will guide you through the essentials of using SQL in a data warehouse environment.Â
This guide should be easy for everyone, no matter how much experience you have in SQL. You can follow the example described below on your PC. Simply download and install the SQL Server Community Edition for free.
A data warehouse is the central repository of information for analysis, artificial intelligence, and machine learning. To make the warehouse efficient, data is loaded from different sources, like transactional databases, flat files, or cloud storage, and should be regularly updated.
The illustration for a typical data warehouse environment is shown below.
The first part of the diagram is the sources of data. These are databases from transactional systems. It can be in SQL Server or another relational database. It can also be from flat files like CSVs, Excel, XML, and text.
Afterward, you consolidate all the necessary records from the source into a single format called the staging area. For simplicity, you can also implement the staging area in SQL Server.
Then comes the data warehouse SQL Server with a dimensional model. We will discuss how to make one with an example later.
The final part of the diagram is different data marts. A data mart focuses on one aspect of the business, like sales, purchasing, and more. In this article, we will make a data warehouse with one data mart for insurance policy sales.
SQL Server data warehouse needs to be modeled for efficient processing. Next, weâll review its components, focusing on the dimensional model and its role.
Here are some key terms used in the dimensional model.
A fact table contains all the facts about a business entity or process. Itâs at the center of the schema, surrounded by dimensions. A fact table may be about sales, ticket support, projects, etc. You can implement this as an SQL database table. Columns include the ID keys of dimensions and measures.
Each record in the fact table will determine how detailed a fact table is. There can be several fact tables in a data warehouse defining different business processes in one warehouse. Each of them can share dimensions about location, date, and more.
Dimension categorizes facts and measures in a fact table. For example, the city or region dimension describes a customerâs location in a sales transaction. Other examples of dimensions are customer and product in a sales business. Dimensions also enable users to answer a business question. For example, âHow much did we earn from Product X this month?â In this question, the Product is the dimension of a Sales fact.
Dimension is implemented as a table referenced by the fact table. It includes a primary key and the key description or name, such as a product ID and name. However, more can be defined within a dimension to categorize it and further build a hierarchy. For example, product category and subcategory describe a product.
The primary key of each dimension can be different from the primary key of the source table. This happens when a table of customers from one database is combined with a table of customers from another. Itâs also called a surrogate key.
The measure is a property of the fact table that allows calculation. This can be sum, average, count, minimum, or maximum. For example, you can sum sales amounts to form total sales.
Measures can be additive, non-additive, semi-additive, or calculated. The sales amount is an additive measure. You can sum or average it. But the unit price is non-additive. It may not make sense if you sum it up. Meanwhile, a calculated or computed measure is like its name. The total sales amount, for example, is calculated based on product unit price + tax.
The schema defines how information is structured in a data warehouse. Different schema types are designed to balance performance, storage efficiency, and query complexity. The choice of schema depends on the business requirements and data usage. Letâs review the most common types.
The simplest and the most widely used dimensional model is a star schema. It has the fact table at the center and the dimensions surrounding it. It can also be described as a parent-child table design. The fact table is the parent, while the dimensions are the children. But since itâs so simple, there are no grandchildren.
Common characteristics of star schema
Advantages of star schema
In a snowflake schema, dimension tables are normalized. The physical structure resembles a snowflake shape. Compared to a parent-child design, snowflake schemas can have grandchildren.
Common characteristics of snowflake schema
Advantages of snowflake schema
Choosing the platform is an essential step in building a data warehouse. SQL Server is an excellent option for many reasons:
Time to put the concepts above to practical use. In this example, we will use a fictitious company called ABC Insurance Co. The company sells fire insurance policies for residential houses, apartments, and business structures.
Our data warehouse example will have these simple characteristics:
To make informed decisions, analysts must be prepared to answer questions from stakeholders, such as business leaders or managers.
Output for this step:
Your stakeholders have questions in mind. Your role is to answer those questions so they can make informed decisions.
In our example, we only need to answer how many sales were made in a particular period. Of course, there are more. However, we will only answer this question to demonstrate the concepts we have learned.
To get the answers, pay attention to the current state of the system and the desired outcome. Ask for the report formats they need. Then, proceed to the next step, which is discussed next.
Output for this step:
The transactional database contains all the currently available information. For this example, we assume all the information we need can be found in the source database. If there is missing information, you must go back to your stakeholders. Then, resolve the matter separately. Then, go back to this step.
After seeing the source database, identify what tables and columns you need. You donât need everything. If you need to clean the data, identify the steps you need to do it. You may need to clarify some parts of the stakeholdersâ data.
Now, letâs assume that we already have what we need. Below, you can find a diagram of the database staging area.
At this point, you need to plan how to get the data to the staging area. After this, youâre ready for the next step. But before we do that, I think this question deserves to be answered. Why create a separate database for the staging area?
Good point. You may ask, whatâs wrong with getting the data straight from the transactional database? Our example uses only 1 database source. In the real world, you donât just deal with sales. You can have other systems for purchasing, petty cash, payroll, and more. If these have separate databases, and you want to analyze them, this staging area may be good for them, too.
How would you know? Ask yourself whether there is information that these systems can share. If yes, consolidating them into one staging area will be an advantage. One example of something that they can share is an employee list.
Another point is data cleansing. You donât want to touch a working transactional system. So, you clean the data in the staging area. And one more point is the precalculation of aggregates. Before reaching the data warehouse, do you need to do some complex calculations or summarization? You can also do that in the staging area.
Finally, we have reached the focal point of this article. Start by setting up a new SQL Server database specifically for the data warehouse.
Output for this step:
To create a new database for the data warehouse, launch SQL Server Management Studio. Then, right-click the Databases folder in the Object Explorer and select New Database. Name your database and set the database options. We named ours as fire_insurance_DW.
Now, letâs create new tables for the SQL Server. We will start with the fact table. For our fire insurance sales example, we have the structure as shown below.
The fact table above includes 3 additive measures: premium, other_charges, and total_amount_paid. Meantime, total_charges is a computed measure based on premium + other_charges.
Please also pay attention to the foreign keys client_id, building_city_id, product_id, and statement_date. They will reference dimension tables later.
Next, create the dimension tables. We have product, client, city, and date dimensions. Each serves a purpose in reporting. The table below shows all the dimensions in our data warehouse example.
See each dimension in more detail:
Check the final database diagram of our data warehouse below.
Analysis doesnât end in creating the database for the data warehouse. So, what are the next several steps?
Here, we will extract information from the source database, send it to the staging area, and send it to the data warehouse. Before you extract data, do not forget to create the field mappings from the source and target. You can find an example of fact table mappings below.
For the date dimension, you also need a script to generate data. The sample SQL code below will build a date table from 2020 to 2021. It uses the dimDate dimension table that we have in the data warehouse.
DECLARE @StartDate date = '01/01/2020';
DECLARE @EndDate date = '12/31/2021';
;WITH seq(n) AS
(
SELECT 0 UNION ALL SELECT n + 1 FROM seq
WHERE n < DATEDIFF(DAY, @StartDate, @EndDate)
),
d(d) AS
(
SELECT DATEADD(DAY, n, @StartDate) FROM seq
),
src AS
(
SELECT
[transaction_date] = CONVERT(date, d),
[year] = DATEPART(YEAR, d),
[month_number] = FORMAT(d,'MM'),
[year_month_number] = FORMAT(d,'yyyy-MM'),
[year_month_short] = FORMAT(d, 'yyyy-MMM'),
[month_name_short] = FORMAT(d,'MMM'),
[month_name_long] = FORMAT(d,'MMMM'),
[day_of_week_number]= DATEPART(WEEKDAY, d),
[day_of_week] = DATENAME(WEEKDAY, d),
[day_of_week_short] = FORMAT(d,'ddd'),
[quarter] = 'Q' + CAST(DATEPART(QUARTER,d) AS NCHAR(1)),
[year_quarter] = CAST(YEAR(d) AS NCHAR(4)) + '-Q' + CAST(DATEPART(QUARTER,d) AS NCHAR(1)),
[week_number] = DATEPART(WEEK, d)
FROM d
)
INSERT INTO dimDate
SELECT * FROM src
ORDER BY transaction_date
OPTION (MAXRECURSION 0);
If you need more years, simply change the start and end dates in the script.
Finally, you can build the reports and dashboards your stakeholders asked for. You may use Excel because they are probably familiar with it. You can also use Power BI or SQL Server Reporting Services (SSRS).
A possible report output for the data warehouse weâve built is shown below. It uses Power BI to show product sales per period. A few more reports are possible with the data warehouse, like client sales or sales based on location.
By itself, an SQL Server data warehouse is just a place for storing large volumes of information â it doesnât automatically solve business problems or generate insights. If you want it to have value, it needs to become a part of your data ecosystem. This ecosystem canât work without integrating your services and sources, and thatâs where ETL (Extract, Transform, Load) processes step in.
SQL Server Integration Services (SSIS) is a popular on-premises ETL tool that is tightly integrated with SQL Server. Itâs powerful but complex to set up and maintain, especially if youâre looking for a beginner-friendly or cloud-based solution. You can integrate SSIS with SQL Server Agent to schedule and automate this workflow.Â
While SSIS is a powerful tool, it has some drawbacks. SSIS is an on-premises solution, making setting up and running pricey. Plus, itâs not beginner-friendly â creating and managing workflows can be easier to set up in alternative solutions.Â
For those exploring alternatives, Skyvia provides a cloud, no-code solution. An intuitive tool that users love, backed by a 4.8/5 rating on G2 Crowd, and takes a spot among the top easiest ETL tools. Skyviaâs Data Integration product offers tools to help move data, combining ETL, ELT, and reverse ETL functionality.
Creating an SQL Server data warehouse is a straightforward way to transform your raw information into useful insights that can help you uncover trends, improve decision-making, and support business growth.
In this article, you learned how to build an SQL Server data warehouse from scratch. The example is simple; however, it covers most of the basic needs of the data warehouse. You can create a robust data warehouse in SQL by following the steps â gathering business requirements, designing and implementing the schema, extracting and transforming data, and creating insightful reports.Â
Letâs recap this topic with some key takeaways!
This article was originally published by Edwin Sanchez on the Skyvia Blog.
Share with your friends and followers
Join other developers and claim your FAUN.dev account now!
