Design question - Foreign key indexes

SQL Server Analysis Services

John Hennesey


A rather elementary design question - is it a good idea to index foreign keys in fact tables I understand this will slow down inserts/deletes/updates, but when the cube is processing will it speed up processing time Does the cube generate all dimension members then query fact tables for them Does it query fact tables with group by's on participating dimensions I see example cubes that have this, but am unable to find documentation saying this is a good idea. I would hate to miss out on a big performance boost, but don't want to take the chance to slow things down... Any suggestions / supporting documentation is very much welcomed.

Thanks in advance,

John Hennesey

Top

Re: Design question - Foreign key indexes

Edward Melomed


It is an interesting question.

General assumption is Analysis Services cubes are being built based on the relational data warehouse which data is coming from OLTP system. The relationa data warehouse is not the place where you expecting lots of individual atomic updates. That is in theory. In practice depending on the size I have seen implmentations where cubes are built based on raw OLTP shemas.

It is dependant on your case and it is really tradeoff you will be making. If you would like to speed up processind of cubes , you should go and run, for example index tuning wizard and build your relational database indexes that match the queries Analysis Server sends during processing. I wouldnt go and try analyzing every query by yourself. Index tuning wizard makes work easier for you.

But if you see indexed built slowing down your ETL update process, you can always drop these indexes.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.






Top

Re: Design question - Foreign key indexes

Alejandro Leguizamo MVP

Hi:

I think there is another consideration and it is performance -not on insertion of data - but at ETL time, when you want to determine if a given row is already present in a dimension (locating a row).

Most of the times, I just index for ETL speed. OLAP Processing , because of the amount of rows read, may easily go to be a table/partition scan, making indexes almost useless.However, that also depends on the cardinality of your attributes, and what kind of processing you use in your OLAP structures

Alejandro Leguizamo

SQL Server MVP

Colombia






Top

Re: Design question - Foreign key indexes

JohnHennesey

Alejandro / Edward -

Thank you very much for your quick responses - both very good points. Edward - to clarify, we are using service broker which receives xml messages from our source system, which uses stored procs to update our data warehouse. During peak times we receive hundreds of thousands of messages each day, and each one will cause updates / inserts / deletes in our dimension and fact tables. Hence my consideration about index changes slowing things down. When we process, we are doing a full process, so Alejandro's response about doing full table scans is a very realistic possibility. The only way to truly tell what is going on and if there can be some performance boost is to run the index tuning wizard - a very good suggestion. I will check it out!

Thanks again,

John

p.s. Alejandro - you would be suprised what the Enterprise Reporting solution has evolved into - I believe Robert Skoglund gave you guys a nice presentation a few months back...


Top