Simulate Multiple Clustered Indexes on a Fact Table

You can use the following trick  if you have two widely used date values in fact table.

We will use the example of Revenue Fact from Hospital system to illustrate the concept.

The  RevenueFact table has service date (ServiceDateID) and Payment post date (PostedDateID )fields.  It is clustered on PostedDateId. Some reports are driven by service date and other by posted date. Since the fact table is clustered on posted date, the reports driven off of service date would be slow.

To fix the problem

• Create an indexed view on the RevenueFact with ServiceDateID being the first column of the clustered index.
• Limit the data returned by the view (e.g. last 12 months). This way most of the operational reports/queries which hit recent data would end up using this indexed view.
• Limit the columns in the view to the ones that your reports/queries would be using.

By following the last two guidelines, you wouldn't end up creating a copy of the fact table, and you wouldn't be slowing your data loads too much.

You don't have to make any changes to the reports, since the optimizer would use the indexed view whenever applicable.