I am looking to modify an existing cube that monitors stock movements.
I would like a 2nd opinion on my design options.
The base is a SQL query but I have 2 options on the design of the query which will effect the design of the cube:-
Create measures for each stock movement event e.g.
Sales Qty, Value etc
Stock Qty, Value etc
There would be at least 16 measures created with this design.
Or create a Type field containing each movement event e.g. sales, transfers, build a dimension of the values of the Type field and have two measures Quantity and Value.
The query would generate approx 40 million records, the existing cube takes approx 1½ hours to process. I would like to try and maintain the processing level as best as possible.
Which design method is the most appropriate?