I have a fact table in UAT Phase. Objective to load 5 yrs of data in Prod (expected size 400 Mn records). Currently it has only 2 years of data in Test.
- No of Dimensions ~ 45
- Measures ~ 30
- Non-additive measures and other columns~ 25
- Current data size ~ 200 Million (2 years data)
- Time View: 3 different Month views: Fiscal/Calendar/Adjusted (i.e same row can fall in different months based on which view one is looking for)
- Only One view will be required at a time by a user. (ie. only one Month Column will be used in the query, it's stopping us to do partitioning on time view)
- Indexes: 1 Clustered Index on the Natural Keys (8 columns).Created 3 covering Non Clustered Indexes one on the each Month column including few Dimension SKs (FKs) and all the measures).
- Indexes are huge(total 190 GB) because of this.
- Space is not constraint (1 TB allocated)
- 64 GB of RAM available in server.
- Table compression also done.
Queries on this Fact table should give result within 30 seconds (General queries select sum(measure) joining few Dims group by Dim Values). Reports are directly done on top of this Fact table.
Any query which includes columns available in the Index works fine, but if we include any other columns which are not in the include..It sucks. It takes more than 5-10 minutes. Can any one suggest some solution where it works fine for any dimension/column we select. Can Index view help in this situation?