Solved

Indexing on fact and dimension tables

Posted on 2014-11-11
6
93 Views
Last Modified: 2016-02-18
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
:
:
0
Comment
Question by:AXISHK
  • 3
  • 3
6 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40436728
Will there be any benefit if I add index for all surrogate key in Fact tables  ...?
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?
0
 

Author Comment

by:AXISHK
ID: 40439094
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 ?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40439100
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.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:AXISHK
ID: 40439217
Sorry, should create a single index with multiple fields, or create individual index for each surrogate (foreign key) ? Tks
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40439220
I do not know - with any certainty - through lack of knowledge - what your best options are.

Please discuss with the designer(s).
0
 

Author Closing Comment

by:AXISHK
ID: 40439332
Tks
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server - Sum Decimal Columns 11 33
SQL 2008 R2 syntax 11 29
Query group by data in SQL Server - cursor? 3 34
Why I am having this error in my query? 2 13
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question