I have two cubes.
One composed of stored measures. The other composed of calculated measures. The measures share the same names. Both cubes have the same dimensions.
I'd like to do a union of these two cubes in a virtual like cube. Basically looking for the equivalent of a UNION ALL in SQL.
How can I accomplish this?
Can you provide some insight into why you are designing this as a two cube solution? If the cubes share the same dimensions why are all the measure not in the same cube? (In most cases, two or more cubes would be created if the stored measures in each cube have different sparsity characteristics, are loaded from different tables on different schedules, have different aggregation operators, etc. This doesn't seem too be the case in your application).
I have a dimension "symbol" where my facts for some symbols are loaded with a pre-adjustment, while my facts for other symbols are loaded without an adjustment.
For the facts which have been loaded with an adjustment a calculation must be applied to them before they can be viewed by the user. The input parameter for the calculation changes every few months i.e. the calculation is dynamic.
Only a certain subset of symbols will actually require this calculation to be applied to them. So the idea was to have two separate cubes and bring them together via a union.
I suppose the alternative here is to convert each measure in a single cube to a calculated measure and put in a simple condition statement such as:
if symbol_attribute eq 'gets_calc'
then return perform_calc(cube_value)
else return cube_value
I would than have to modify my fact table view to union the sources of the data (adjusted symbols and non adjusted symbols)
Possibly there is a cleaner way to this?