Hi All,
well, it’s been many years since I have been here. Working on non BO projects. Now I am working on another BO project and I have a problem to solve. We have built data models and there are lots of joins. We used to put them in by hand but I am getting sick of that. I am writing some software to put BO joins into a universe programatically using vb.net. We will store the joins in an excel workbook and load them into the universe.
My question is. Has anyone done this and published any code about it?
(Obvious second question is…am I starting at the beginning?)
More ‘discsussion’ than anything…
In the end, I think we will write everything we need to maintain BO universes in spreadsheets…it’s too time consuming to use the designer.
This is at 6.5 level as the client is still on 6.5.
By the way…when talking to the client there still seems no good way to manage multi-language universes…this surprised me as we published a workbook to maintain multi-language universes ages ago (free) and got no interest so I just thought BO must have done something…the guys I am talking to at my new client are doing things the exact same way as they were done 3 years ago, the last time I used BO…
We have now moved to the idea that the entire ETL subsystem and report translations should be possible to store in Excel…probably heresy here but last year we wrote a translator for Report Services reports and we are able to translate the base report from English to any of 10 languages by editting the .rdl files directly through code that get’s it’s translations from an excel workbook…funnily enough RS people still want to maintain all their language versions by hand…LOL!!
Ok…I have got this working with one universe and the simple insert of a few joins in vb…
However, when I try to create a new universe and then I try to add my first joins using Univ.Joins.Add(JoinExpressionToAdd) I get the following message.
Unhandled Exception…
Additional Information: Cannot set non regular expression.
Passed is the joinExpressionToAdd is simply table1.col1=table2.col2.
I don’t see how this works for one universe and a few joins on that one and not another universe…can anyone point me to how I might find out more information about this or find more detailed error messages is a trace or something like that?
Hi DataHog,
thanks for that…I’ve put a post over there…the code that is published I understand…and mine does the same…what I can’t figure out is the error and why I get it on one universe and not another…sigh…
Also, what we are doing is creating ‘one workbook to rule them all’…LOL! I am a lord of the rings reader from a young age…our workbook already does massive amounts of ‘stuff’ and we want to be able to tie the object on the screen to the source field…full data linearage at last…so we want to get this to work in our workbook…I just can’t see why it works in some cases and not others…it’s the same damn code and the expression is correct…sigh…oh for more detailed error messages!! LOL!
Thanks for the pointer though…some of the tools there will get us over our initial needs…
Hi All,
to let others who may try this know…
I found two areas of ‘sensitivity’…firstly…if the tables have prefixes then the prefix needs to be included in the join to the addition of the join work properly. Further, and this was a surprise to me, the case of the table/columns specified in the join need to be in the same case as the table and columns themselves otherwise it seems to throw an error. This one surprised me a bit…but the error is thrown…though this is an old version…
So, now, we have a nice little utility to add joins to a BO Universe like the one in the downloads only we are also using it to load the joins into a metadata table and we are also using the joins to generate the RI constraints between tables so that we can import the model into data modeling tool…