Database Reference
In-Depth Information
In the case where the table is partitioned by date, the primary key will be composed
of the date and the integer surrogate key, to be able to meet the needs of partitioning.
If a column is used to create a
DISTINCT COUNT
measure in a cube, then it might be
useful to have that column in the clustered index, because Analysis Services will
request an
ORDER BY
clause on that column during the process of the measure group.
It is clear that the creation of a clustered index is useful in large cubes where data is
added incrementally, so processing will benefit from the ordered data. If on the other
hand, we have a one-shot solution where all tables are reloaded from scratch and
the cube is fully processed, then it is better to avoid the creation of a clustered index
since the sorting of the data is performed only once, during cube processing.
Once the cube has been built, if MOLAP storage is being used, no other indexes are
useful. However if the data mart is queried by other tools such as Reporting Services,
or if ROLAP partitions are created in Analysis Services, then it might be necessary to
add more indexes to the tables. Remember, though, that indexes slow down update
and insert operations so they should be added with care. A deep analysis of the queries
sent to the relational database will help to determine the best indexes to create.
Usage of schemas
The data warehouse is normally divided into subject areas. The meaning of a subject
area really depends on the specific needs of the solution. Typical subject areas include:
•
Sales
•
Accounts
•
Warehouses
•
Suppliers
•
Personnel and staff management
Clearly, this list is far from complete and is different for every business. SQL Server
provides schemas to arrange tables and—in our experience—the usage of schemas to
assign database objects to subject areas leads to a very clear database structure.
Some tables will inevitably have no place at all in any subject area, but we can
always define a common subject area to hold all these tables.
Search WWH ::
Custom Search