AXISHK
asked on
Indexing on fact and dimension tables
Will there be any benefit if I add index for all surrogate key in Fact tables and the primary key on all dimension tables ? What kinds of index key should be defined on each tables ? Please provide the TSQL that I need to run on dimenions and fact tables.
Currently, nothing is defined in the tables. Is there another way to increase the query for the SSAS ?
Tks
DIM_Customer (similar layout for other dimension tables)
---------------------
Customer_IDX
Customer
Name
FACT_SALES_INVOICE
-------------------------- ----
CURRENTCY_IDX
REGION_COUNTRY_IDX
TRANSDATE
CUSTOMER_IDX
SALESMAN_IDX
:
:
Currently, nothing is defined in the tables. Is there another way to increase the query for the SSAS ?
Tks
DIM_Customer (similar layout for other dimension tables)
---------------------
Customer_IDX
Customer
Name
FACT_SALES_INVOICE
--------------------------
CURRENTCY_IDX
REGION_COUNTRY_IDX
TRANSDATE
CUSTOMER_IDX
SALESMAN_IDX
:
:
ASKER
Will it affect the processing speed of the cube, or affect the querying performance on the cube ?
Do I need to specify clustered index for primary key ?
Do I need to group any surrogate key in a single index, say
Current schema
DIM_CURRENCY (CURRENCY_IDX, CURRENCYCODE, NAME, SYMBOL, RATE)
FACT_SALES_INVOICE (CURRENCY_IDX, REGION_COUNTRY, TRANSDATE, CUSTOMER_IDX, SALESMAN_IDX, BASEAMOUNT, QTY, INVOICENO, COMPANY)
CREATE UNIQUE CLUSTERED INDEX PIndex
ON DIM_CURRENCY (CURRRENCY_IDX)
CREATE INDEX PIndex
ON FACT_SALES_INVOICE (CURRENTCY_IDX, REGION_COUNTRY_IDX, TRANSDATE, CUSTOMER_IDX, SALESMAN_IDX)
It seems that Fact Invoice doesn't have a primary key.
Any suggestion ?
Do I need to specify clustered index for primary key ?
Do I need to group any surrogate key in a single index, say
Current schema
DIM_CURRENCY (CURRENCY_IDX, CURRENCYCODE, NAME, SYMBOL, RATE)
FACT_SALES_INVOICE (CURRENCY_IDX, REGION_COUNTRY, TRANSDATE, CUSTOMER_IDX, SALESMAN_IDX, BASEAMOUNT, QTY, INVOICENO, COMPANY)
CREATE UNIQUE CLUSTERED INDEX PIndex
ON DIM_CURRENCY (CURRRENCY_IDX)
CREATE INDEX PIndex
ON FACT_SALES_INVOICE (CURRENTCY_IDX, REGION_COUNTRY_IDX, TRANSDATE, CUSTOMER_IDX, SALESMAN_IDX)
It seems that Fact Invoice doesn't have a primary key.
Any suggestion ?
It is not mandatory in database design that every table has a unique integer on every row,
it is extremely common but not mandatory.
It appears that the fact table DOES have a primary key, it just happens to involve multiple fields
(CURRENTCY_IDX, REGION_COUNTRY_IDX, TRANSDATE, CUSTOMER_IDX, SALESMAN_IDX)
However I doubt you can have so many indexes defined as "PIndex"
To second guess a database design from virtually no information or background is not a worthwhile activity and could be misleading for you. I would pose these questions to the database designer(s) or look for documentation on the design.
it is extremely common but not mandatory.
It appears that the fact table DOES have a primary key, it just happens to involve multiple fields
(CURRENTCY_IDX, REGION_COUNTRY_IDX, TRANSDATE, CUSTOMER_IDX, SALESMAN_IDX)
However I doubt you can have so many indexes defined as "PIndex"
To second guess a database design from virtually no information or background is not a worthwhile activity and could be misleading for you. I would pose these questions to the database designer(s) or look for documentation on the design.
ASKER
Sorry, should create a single index with multiple fields, or create individual index for each surrogate (foreign key) ? Tks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Tks
YES
... and the primary key on all dimension tables ?
YES
I'm really rather surprised you have "dims & facts" without indexing.
In SQL Server the most important indexing decision is what will be the used for the clustered index as this shapes the physical means of storage on disk.
Are you absolutely certain there are no indexes on any of these tables?