Case-1 I have a fact table "fact1" which contains data for around 3 yrs. PI of the fact table is store number and reporting date column. Case-2 Another fact table which contains data for 3 yrs and PI is on store number only.
Most of my database queries join these fact table with dimension table to find out the aggregated data for different timeframe like daily,weekly,monthly,quarterly,yeraly for last 3 yrs(current year,previous year,previous tp previous year).
For these two cases whether i will Create a value ordered NUSI on reporting date column or is it wise to have partitioning(PPI) on a interval of DAY on reporting date column. Which will give the best performance and why?
So do u mean to say for case-1 it is PPI and for case-2 it is VOSI.
Or is it like create a VOSI on reporting date column for case-1 and case-2.
Lets say there is a billing table. As i think most queries will use to see data according to reporting date or for a particualr timeframe like weekly,monthly etc data and reporting date column is in UPI along with some other columns, So is it good to use PPI or VOSI. I have chosen VOSI and feel it is fine to go with VOSI. Please suggest.