Hi xfgethelp
Yes this is possible with Dynamic Cube Services (DCS). At a high level you could import your SKU level detail via SQL, use that SQL to derive (a) the Dynamic Dimension and (b) the Dynamic Data that goes in your Dynamic Cube. Once it is in a Cube you can use Cube Views for input like normal and you can write Finance Rules to get/set values e.g. your allocations use case, just like you would in a real cube. All of this is reasonably straightforward. It gets a bit more complicated when you try and write back from the Cube to your source data (which it doesn't sound like you are doing, but that can get trickier depending on the complexity of your original SQL, for example)
There is a DCS block for Genesis coming out soon (in development currently) and there is also content on Navigator for DCS that is currently being developed and will be released soon. For now, you should be able to work with the code snippets you get when you create the boiler plate Dynamic Dimension and Data service assembly files. The key is to approach this like a real Cube in terms of design and implementation. Just because this is dynamic does not mean that you can disregard the Data Unit for example and be mindful of sparsity with your allocations data. So I would treat this Data Model design the same whether it be Dynamic or Physical
Best way to start is see if you can create a Dimension with your SKU level data (not bound to a Cube). Remember to add Root as a Parent in your SQL (if it not there already, see simple example below); otherwise your members will be created as orphans (assuming your dynamic dimension is inherited from Root Dimension). You can then use api.ReadDynamicDimensionInfo() to do the heavy lifting.
SELECT LastName As Child, CONCAT(FirstName, ' ', LastName) As Description, 'TotalTitle' As Parent
FROM XFW_PLP20_Register
UNION ALL
SELECT 'TotalTitle' As Child, 'TotalTitle' As Description, 'Root' As Parent
Hope this helps
Sam
