Hi everyone,
I would like to build something like a "star schema" on PowerBI for my econometric system.
I want to explain the current S&P500 value and predict the next month value using many variables.
Table number 1 (the center of star) is the monthly series of S&P500 values (Column A = date, column B = closing price of the index).
Let's suppose I have 3 variables right now, each one linked to the center in a star schema. Each variable's table has 2 columns, date and variable's value of the previous month (the value is normalized into a decile, so it can be 1, 2, ..., 9, 10).
In the central table I want to add a column with the predicted value of the price deriving from the variable's tables. This prediction is made like this:
(S&P500 average historical value when variable A is in the current decile + S&P500 average historical value when variable B is in the current decile + S&P500 average historical value when variable C is in the current decile) / number of variables, 3 in this case
Then I want to add or remove variables by simply linking new tables to the center without writing or editing formulas in order to neutralize potential omissions (I'm currently using more then 50 variables and making errors is easy and difficult to correct).
Is it feasible in PowerBI or do you suggest other software? Right now the model is made in Excel but with a single table including all the variables, so adding or removing one is not immediate.
My goal is to have a "plug and play" system that pretty much does not need manutention and updates.
Thanks a lot!
