Move data between mutually exclusive cubes (not linked) with different extensibility

RandyThompson
New Contributor III

I need to move data between two mutually exclusive cubes. Cube1 (FinPlan) U3 dimension base level is ProductType, Cube2(SalesFcst) UD3 base level is SKU which rollup up to ProductType. SKU also has a Text6 field containing its ProductType Code.

 

RandyThompson_0-1662735007846.png

 

 

I also have separate Account dimensions: Cube1 uses FinAccounts for Forecasting, while Cube2 uses the SalesFcstAccts dimension

 

RandyThompson_1-1662735007848.png

 

 

To move the data from Cube2 to Cube1, I tried using a Business Rule using the api.Data.ConvertDataBufferExtendedMembers function Cube is an option:

 

   Dim summarySalesFcstData As DataBuffer = api.Data.ConvertDataBufferExtendedMembers("SalesFcst", "Fcast1", dbSalesFcstData)

 

However, it didn’t replace the SKU with its ProductType. Does anyone know is this function will work between mutually exclusive cubes with different account dimensions.

 

The second approach I would like to try is to extract the data from Cube2 including the SKU Text6 value of ProductType, then I could use a transformation rule to convert the accounts and use the Text6 value to upload to the product dimension.

Does anyone know an approach I can use to export base data from a cube including a member Text field in the extract.

 

Any suggestions will be appreciated. Thanks.

1 ACCEPTED SOLUTION

chul
Contributor III

On your second approach, create a DM Export Sequence Data Source. Since you're creating a transformation rule for accounts, create one for your UD3 (you can use Excel to bring in the text6 value and upload the trx). The other dimensions would use your PassThru rules.

Not clear if this is a one-time exercise or you're moving data as an ongoing process.

cds

View solution in original post

2 REPLIES 2

chul
Contributor III

On your second approach, create a DM Export Sequence Data Source. Since you're creating a transformation rule for accounts, create one for your UD3 (you can use Excel to bring in the text6 value and upload the trx). The other dimensions would use your PassThru rules.

Not clear if this is a one-time exercise or you're moving data as an ongoing process.

cds

TonyToniTone
Contributor II

2nd approach is your option here.  To answer your 1st question, yes, you can have mutually exclusive Cubes with different Account Dimensions and use the ConvertDataBufferExtendedMembers.  You would need Cube 1 to use FinAccounts and Cube 2 to use FinDetAccounts.  Because FinDetAccounts extends from FinAccounts, the ConvertDataBufferExtendedMembers is a viable option.  In this case, Cube 1 and Cube 2 have no Cube References assigned so they are not linked.