I would like to design database structure on which could you please give your greater thoughts as per below requirements?
I have a report which will be scheduled nightly and when end user will come up in the morning then program needs to fetch data only from calculated stored tables; there will not be any calculation from other transaction tables.
For e.g., I've a two dropdown list / combo box i.e. Department and Salary with multi select option. (Note: Department and Salary from other master tables which are also having internal ID in database like HR - 1, Sales -2 and similar 0 to 10000 - 101 on so on.
Now, user can select one/multiple item(s) from Department and Salary as well.
HR 0 to 10000
Sales 10000 to 15000
Account 15000 to 20000
Market 20000 Above
1. What should be the best structure design like we have in OLAP?
2. We need to store calculated result nightly based on all permutation & combination, do you have any thoughts?
3. How we can generate a key to fetch calculated data?