enrique_aeo
asked on
Dimension table indexes
hi experts
about Dimension table indexes
What is correct?
1. Clustered index on surrogate key column
or
2. Create a clustered business key index
about Dimension table indexes
What is correct?
1. Clustered index on surrogate key column
or
2. Create a clustered business key index
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
^^^ That doesn't tell me anything that wasn't stated very generically in your original question. Can you provide us some context, such as the table schemas of the dim and facts in question?
ASKER
in the offical course of microsoft sql server 2012 10777A - Designing and Implementing a Data Warehouse says:
Dimension table indexes: Create a clustered business key index
but
in the offical course of microsoft sql server 2016 20767A - Designing and Implementing a Data Warehouse says:
Dimension table indexes: Clustered index on surrogate key column
i am very confused
Dimension table indexes: Create a clustered business key index
but
in the offical course of microsoft sql server 2016 20767A - Designing and Implementing a Data Warehouse says:
Dimension table indexes: Clustered index on surrogate key column
i am very confused
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I think that
Dimension table indexes: Create a clustered business key index
ProductAltKey = clustered business key index
CREATE TABLE DimProduct
(ProductKey int identity NOT NULL PRIMARY KEY NONCLUSTERED,
ProductAltKey nvarchar(10) NOT NULL,
ProductName nvarchar(50) NULL,
ProductDescription nvarchar(100) NULL,
ProductCategoryName nvarchar(50))
GO
CREATE CLUSTERED INDEX IX_DimProduct_ProductAltKe
ON dbo.DimProduct (ProductAltKey);
GO
If I process a file with 5,000 products I think it will be faster if the business key is a cluster index, what do you think?
Dimension table indexes: Create a clustered business key index
ProductAltKey = clustered business key index
CREATE TABLE DimProduct
(ProductKey int identity NOT NULL PRIMARY KEY NONCLUSTERED,
ProductAltKey nvarchar(10) NOT NULL,
ProductName nvarchar(50) NULL,
ProductDescription nvarchar(100) NULL,
ProductCategoryName nvarchar(50))
GO
CREATE CLUSTERED INDEX IX_DimProduct_ProductAltKe
ON dbo.DimProduct (ProductAltKey);
GO
If I process a file with 5,000 products I think it will be faster if the business key is a cluster index, what do you think?
Explain to me what you believe the definition is of a clustered business index.
My definition is one or more columns, and possibly concatenated letters and numbers (e.g. MN00178949). These will always be larger in size then a four bit int column, so my answer would be the better relationship is with the surrogate column.
If that doesn't answer your question, then you need to explain in simple terms with a data example what exactly you are asking, as opposed to simply repeating the question.
My definition is one or more columns, and possibly concatenated letters and numbers (e.g. MN00178949). These will always be larger in size then a four bit int column, so my answer would be the better relationship is with the surrogate column.
If that doesn't answer your question, then you need to explain in simple terms with a data example what exactly you are asking, as opposed to simply repeating the question.
ASKER
ASKER
it is database OLAP - datawarehouse - The Dimensional Model