A data warehouse is a database built for questions rather than transactions. Your order system is designed so a thousand people can place orders at once without stepping on each other. A warehouse is designed so one person can ask what happened last quarter and get an answer before losing interest. Those are different jobs, and they want different shapes.
Why Not Just Query the Real Database
People try, and it fails in three ways.
It is shaped wrong. An order system is normalized so nothing is stored twice. Answering one business question then means joining eleven tables, and that gets slow.
It fights the live system. A report that scans two years of orders holds locks and eats memory while customers are trying to buy things.
It forgets. A customer moves from Mumbai to Pune and the row is updated. Last year’s sales just moved cities too, and your history is now quietly wrong.
A warehouse is a separate copy, restructured, loaded on a schedule, and allowed to remember things the live system overwrites.
Facts and Dimensions
Two kinds of table, and this is the whole idea.
A fact table holds the things that happened and the numbers you add up. One row per sale, per shipment, per meter reading. It is long and narrow and it is where all the rows are.
A dimension table holds the things you slice by. Customer, product, date, store, salesperson. It is short and wide and full of descriptions.
The sample warehouse Microsoft ships makes the split obvious:
fact_tables dim_tables
10 16largest facts rows largest dimensions rows
FactProductInventory 776286 DimCustomer 18484
FactInternetSalesReason 64515 DimDate 3652
FactResellerSales 60855 DimReseller 701
FactInternetSales 60398 DimGeography 655Notice the naming. Every table announces which kind it is, and that convention is doing real work when somebody new opens the database.
The Star Schema
Put the fact table in the middle and the dimensions around it, each joined to the centre by one key. Drawn on a whiteboard it looks like a star, which is where the name came from.
The point is that every dimension is one join away from the facts. No chains, no hunting. Here is a real question answered against that shape:
SELECT TOP 5 d.CalendarYear, g.EnglishCountryRegionName AS country,
COUNT(*) AS orders, CAST(SUM(f.SalesAmount) AS decimal(18,0)) AS sales
FROM dbo.FactInternetSales AS f
JOIN dbo.DimDate AS d ON d.DateKey = f.OrderDateKey
JOIN dbo.DimCustomer AS c ON c.CustomerKey = f.CustomerKey
JOIN dbo.DimGeography AS g ON g.GeographyKey = c.GeographyKey
GROUP BY d.CalendarYear, g.EnglishCountryRegionName
ORDER BY sales DESC;CalendarYear country orders sales
2013 United States 18932 5462079
2013 Australia 11054 4339443
2011 Australia 786 2563732
2011 United States 770 2458285
2012 Australia 1167 2128407Sales by year and country, and it reads almost like the sentence you would say out loud. That readability is not decoration. It is why people who are not developers can be handed a warehouse and get somewhere.
The Date Dimension
A table of 3,652 rows, one per day for ten years, holding every way you might want to describe a date. Year, quarter, month name, day of week, whether it was a working day, which fiscal period it fell in.
It looks redundant next to a date column and it earns its place immediately. Fiscal years that start in April, week numbers, public holidays and comparisons against the same week last year all become a join instead of an argument. Every warehouse has one.
Slowly Changing Dimensions
Back to the customer who moved from Mumbai to Pune. The warehouse has to choose what to do, and the choices have names.
Type 1 overwrites. Simple, and history changes underneath you.
Type 2 adds a new row with a date range and marks the old one as no longer current. Last year’s sales stay in Mumbai, this year’s are in Pune, and both are correct. This is why dimension tables have those surrogate keys instead of using the real customer number.
Type 2 is the usual answer when history matters, which in a warehouse is most of the time.
Getting the Data In
Extract from the source systems, transform it into the warehouse shape, load it. That is ETL, and it is the part that takes the time. Most of a warehouse project is spent on the loading, not the schema.
The names have moved around over the years. Warehouse, data mart, data lake, lakehouse. The vocabulary changes faster than the idea does, and the idea is still facts in the middle with dimensions around them.
When You Need One
When reporting is slowing down the live system. When answers need data from more than one system. When you need to know what was true last March and not just what is true now. When the same question gets three different answers depending on who runs it.
And when you do not, say so. Plenty of organisations run good reporting off a read-only replica and a handful of views. A warehouse is real work to build and real work to keep loaded, and the smallest thing that answers the question is usually the right thing.
A data warehouse is not a bigger database, it is the same facts arranged for asking instead of for recording.
This post was rewritten from scratch in September 2026. The original, published on 2011-09-05, was a short announcement about something that no longer exists. The address is the same, the subject is now a basic idea worth keeping.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
19 Comments. Leave new
Hi Pinal,
It is a good whitepaper on how to use implement SQL Server using Hyper-V and it covers the following strategies.
Hyper-V Dynamic Memory treats memory as a shared resource that can be reallocated automatically among running virtual machines.
Using a single physical machine to host multiple virtual machines running the Microsoft SQL Server database software
Using a single machine to host multiple SQL Server instances
Using a single instance of SQL Server to host multiple databases
Gopalakrishnan Arthanarisamy
Unisys, Bangalore, India.
Dear sir i hve a problem in sql server 2005 .
I wants to get all the record from the table in which user defined record/rows (one row) should be placed on the top of the record and rest should be below and condition is that we have only use one select statement.
Exp: suppose i hve table name test(id,name,class) which contain 4 rows .when user pass id=1 then that record should be placed on top and resr are below of that bt we hve use only one select statement.pls reply me
Exp: table_name test
ID Name Class
2 A MCa
4 B MBA
5 C MCa
1 K MCA
when user wants to get record of ID=4 at top position then
results are like
ID Name Class
4 B MBA
2 A MCa
5 C MCa
1 K MCA
in such scenario we hve to use only one select statement
select columns from table
order by case when id=4 then 0 else 1,id
Dear madhivanan
thanks for reply ,bt that query in not working for above scenario.I think u hve not understood the problem.when user wants to get the record those id=4 that record should be at top position of results and rest r below of that record.
What order do you want to expect for the rest of the values?
Num1 num2 n um3
3 1 2
2 2 3
1 3 1
Please sir tell how the query that would display
o/p
Num1 num2 n um3
1 1 1
2 2 2
3 3 3
Dear madhivanan
there is no need to change the order of the values,except the values which is selected by user should be at the top position other wise if rest values sequence change ,it does’t matter but user selected values should be at the top and we hve to use only one select statement.
Dear Zubair khan
execute this query ::
select num1,num2,num3
from uy_table_name
order by num1,num2,num3 asc
Dear Ashish,
I tried but this query is not working. please suggest me another query.
thankx
The query that I posted should work. Did you try it?
Dear Zubair khan u hve to use self join for this out put
try this query::
select t1.num1,t2.num2,t3 .num3
from table1 t1,table1 t2,table1 t3
where t2.num2=t1.num1 and t2.num2=t3.num3 order by num1 asc
Dear Ashish,
Special thankx to u….it works…thnkx again
What is the logic behind this output?
Dear madhivanan
the query which u hve posted is not working.I hve already told that there is no logic behind this output but i hve a requirement in my project that if the usere if passes their ID so the information/record associated with that ID should be at the top position and rest record should be below of that record.
Dear All,
Please suggest if Sql Server installed in server and other systems are unable to acces the SQl Server. Then what could be the main problem in the connectivity.
Thaxn
Hi Sir,
Sometimes I have noticed that sql server does not release memory it is using.For example i am running some heavy(but optimized) queries and the memory usages goes up.Now after execution of all process it should release those extry mbs of memory and set its position back to normal. But it is not the situation most of the time. Is it a problem either in sql server or server installed on my system. What should be the first step(steps) I have to take.
Thanks
Please, Is it a good idea to virtualize a SQL Server for Dynamics AX 2012? on a HP blade using VMware? Thanks a lot
Had a question on MS SQL licenses and Virtualization. I tried to find the answers from MS SQL guide but it did not help.
We have around 20 SQL Servers ( Standard edition/Per processor license Model) Most of them SQL 2005 and SQL 2000. We do not have SA and we are not planning to upgrade from existing version. My question is if I do a P2V and virtualize the servers to a 4 node ESXi 5.0 cluster and allocate the same number of processors , do i need to buy any additional licenses? Can I do vmotion for the SQL Servers between nodes? Please let me know.