Passing values - no CSV in here please

Paul Wyatt

Hello BOARD community. 

I wanted to contribute before I asked a question but this issue is perplexing and I really want to know if it can be achieved.  I want to disseminate values of a cube to other cubes which have the exact same members but use differently named entities and live as parents in other trees.  

 

I do appreciate that a solution which uses a csv file, re-loaded into the target cube, would achieve what I want, however, I want to know if it is achievable without using a CSV file - OLAP only.

 

I have attached an illustration that I hope will make this easy to understand.

Brendan Broughton

Hi Paul Wyatt - Avison Young UK, I don't know the exact structures of your data/cubes, or how likely/frequently new members will be added to these entities (these are things I'd consider in deciding which option to use), but there are a few options.

 

Using a procedure you could select the first client and relevant secpropclient, then dataflow between your cubes.  Then select the second client/secpropclient combination and flow and so on.  Another procedure based option would be a dataflow with a mapping cube. The mapping cube would be dimensioned by client and secproprclient, and you would populate a '1' for the client/secpropclient combinations you need.  In a dataflow you then multiply your source cube by the mapping cube and populate this into your target cube (or depending what is already in the cube you could use an IF algorithm instead if(map=1,source,target))

There might also be the possibility to use replicated entities in this case so maybe have a look at that too.

Cheers

Brendan

Daniel Zillmann

Hi Paul,

 

I would probably follow Brendan's approach with the mapping cube. I am quite certain that you can automate this mapping as well, my first idea would be Nexel. You "just" need to compare the two entities with each other and when they are the same, then you have a 1 in your mapping cube. Or you could even create this mapping cube with a data reader by using the same data for both entities and enter a 1 into the mapping cube during the load.

 

Cheers,

Daniel

Unknown

I'm not sure if the entities are replicated already, but using replicated entities, may help with the matching as you move from one allocation to another.

 

BOARD Replicated [R] Entities - How to use them by Gabriele Gallo does a good job explaining when, where and how replicated entities work. It sounds like this may help you out.

Björn Reuber

Hi

 

a different approach might be the BOARD rolap Technology. You can create a Rolap cube on Entity Client and set a second Rolap cube (structured on SecPropClient) using the same relational Table.

So if the value in the first cube is change, second cube is automatically updated (without the need of an procedure) cause both cubes share the same Rolap Table.

 

Regards

Björn

Etienne CAUSSE

Hi,

The mapping with a Diagonal Matrix seems the better option to me.

Regarding Replicated Entities, we have had a very bad experience when the cube are structured with a [R] entity that is not at the lowest leve, as in your case is SecPropClient.

Basically we had to restart with new cube structures because the drill down did not work properly.

Cheers,
Etienne

Paul Wyatt

Thank you all for your replies.  It's great to see several solutions to one problem as this really opens up so many more possibilities to different problems.  To summerise, there are 4 possible solutions:

  1.   CSV - save to text and use file to populate anything - creates independent artefacts which could be lost and must be tracked on promotion trough Dev > UAT & Production.

  2.   BOARD Replicated [R] Entities - How to use them - Exciting as I've not used this functionality yet but I do heed the warning from Bob Gill.  I will now be playing with [R] Entities on a dev model so thank you very much for pointing it out to me.

  3.   ROLAP - Really clean, simple and fits fits with BI Best Practice.

  4.   Mapping Cube - This is what I had originally set out to do but encountered issues.

 

I will go with the ROLAP approach - Thanks Björn Reuber.  However, Etienne CAUSSE, would you have a model I could look at (cpsx + hbmp) as I want to understand this approach and learn where I was going wrong?

 

Thanks all for your excellent advice.

Etienne CAUSSE

Hi Paul Wyatt - Avison Young UK

Sorry I don't have anything that can be shared easily (everything is too big over here), but here are the steps I would follow:

1. populate a diagonal matrix (let's call it DM), basically a cube structured by Client,SecPropClient, with 1 whenever the two values are equal. to do this I would use a data reader which reads the Client master data from source, and maps both entities and the DM cube to the client source master data.

In the ETL, just replace the cube value by "=1". Make sure you clear the cube before the data reader to make sure it's always up to date.

2. if you just want to manage selection in your procedure, just do the following:

- select Client based on previous logic

- then use "select based on cube" for entity SecPropClient using the DM cube. After that your selection will be applied to SecPropClient exactly as it was on client.

3. if you want to copy a value from cube X (by client) to cube Y (by SecPropClient), first select your Client then make a dataflow with 

- a=cube X

- b=cube DM

- c= cube Y

 

with dataflow setup as c=b*a

 

I hope it's clear.

 

Etienne

Paul Wyatt

Thank you very much Etienne CAUSSE.  That is very clear and I look forward to having some time to play. 

 

To Brendan Broughton, my apologies for not acknowledging you as the first to respond to my query and also for proposing the mapping cube approach.  I was departing on holiday when I posted the question and then came back to six responses, acting on the last posts first.

Björn Reuber

Hi,

Etienne CAUSSE there is some minor room for improvement.

If you set it up like this

 if you want to copy all value from cube X (by client) to cube Y (by SecPropClient) you can do this for all Clients (in one step) if you have an cube temp_Y (by client and by SecPropClient)

DF1:

- a=cube X

- b=cube DM

- c= cube temp_Y

with dataflow setup as c=b*a

DF2

- a= cube temp_Y

- b= cube Y

with dataflow setup as b=a

 

Regards

Björn

Brendan Broughton

No worries Paul Wyatt - Avison Young UK, as long as you found the solution you needed all is good.