[SQL Server Data Warehousing] Data warehouse

No, your approach is not right.

SQL Server is RDBMS (Realational database managment system) and it should have keys (natural primary keys and surrogate keys for DWH model). Data warehouse model is more simple then normalized OLTP model, and it is often called dimensional model. It can be denormalized to snowflaked or star schema to gain better join performance for reports - BUT IT SHOULD ALLWAYS have keys defined.

Problem that arrives if table has no primary (clustered index) key defined is with working of sql server. Without keys defined MSSQL stores data internaly as heap structure, as oposite of B-Tree structure when you have clustered index defined.Heap is pretty simple structure and in this perspective you should know that SQL Server always do full table scan on heap structures (so to find any row MSSQL usualy read whole table), and this is not desirable in most scenarios.

You should post your DDL at least if you are expecting some constructive and better help, please do that

Thnx

---

if (helpful) then Vote();

.

social.technet.microsoft.com/Forums

[SQL Server Data Warehousing] Data warehouse

No, your approach is not right.

SQL Server is RDBMS (Realational database managment system) and it should have keys (natural primary keys and surrogate keys for DWH model). Data warehouse model is more simple then normalized OLTP model, and it is often called dimensional model. It can be denormalized to snowflaked or star schema to gain better join performance for reports - BUT IT SHOULD ALLWAYS have keys defined.

Problem that arrives if table has no primary (clustered index) key defined is with working of sql server. Without keys defined MSSQL stores data internaly as heap structure, as oposite of B-Tree structure when you have clustered index defined.Heap is pretty simple structure and in this perspective you should know that SQL Server always do full table scan on heap structures (so to find any row MSSQL usualy read whole table), and this is not desirable in most scenarios.

You should post your DDL at least if you are expecting some constructive and better help, please do that

Thnx

---

if (helpful) then Vote();

.

social.technet.microsoft.com/Forums

[SQL Server Data Warehousing] Data warehouse

We have constructed the data warehouse  in SQL Server -> creating dimensions, fact tables. Its not relational database(have not added primary key or surrogate key) in the data warehouse.

Our functional area is Terminal & Ports, so is this right approach to have the non relation database or not?

Should we go with Normalization approach or dimensional?

.

social.technet.microsoft.com/Forums

[SQL Server Data Warehousing] Data warehouse

We have constructed the data warehouse  in SQL Server -> creating dimensions, fact tables. Its not relational database(have not added primary key or surrogate key) in the data warehouse.

Our functional area is Terminal & Ports, so is this right approach to have the non relation database or not?

Should we go with Normalization approach or dimensional?

.

social.technet.microsoft.com/Forums