I am on BOE XI 3.1 FP 3.7, Update ver. 12.3.7.1198.
I am having an issue where columns have been moved from one table to another in the SQL database, the Universe table reflects this after doing “Refresh Structure”, but in the Objects List on the left in Designer, the lists do not match the tables changes. I can not simply remove and re-add the objects, because it breaks links in existing reports and causes “Unresolvable” errors. I can not ask the users to recreate all of their reports, so I am stuck. Appreciate any help with this!
Business Objects won’t do this automatically because there is no concept of moving a column from table to table - it would be dropped from one and added to another.
You could change your object select statement from TABLE_A.COLUMN_NAME to TABLE_B.COLUMN_NAME and the object ID wouldn’t be affected.
So are you basically saying that there isn’t any way I can handle this on the Designer side, and that the users must edit their reports? Or am I not following, which I apologize for. I’m trying to understand why the Refresh Structure function would not allow for this, because if you give users a one-to-one object list, if Refresh Structure updates the tables correctly, why would it not also update the objects in the list? That makes the object list out of sync with the tables in the Universe and does not make sense.
Very confused,
Carl
UPDATE: Additionally, to correct a statement I made above, we did an ADD/DROP function to facilitate the columns “moving” to the new table. It was not a true “move”. So to me, there should be a way for the Objects to reflect the tables changes to get them in sync. I’m just not seeing it.
Let’s give an example. Say you move OrderNumber from F_OrderHeaders to F_OrderLines.
Forget about BO for this paragraph; at a database level, as a DBA you wouldn’t move a column from one table to another. You’d drop a column from one table and you’d create a column in another table; they would have different internal IDs at the database level.
BO mode back on At the Designer level, you open your universe and refresh structure. You’ve had a column removed from F_OrderHeaders and one of the same name (but that’s irrelevant from a technical point of view) appears in F_OrderLines. You know already that any object containing F_OrderHeaders.OrderNumber is now broke. All you need to do at the Universe level is find the object and change the table name in the select section (assuming and hoping you’re not using the Where section ) from F_OrderHeaders to F_OrderLines. The underlying object ID has not changed because you have not removed the object, you’ve simply repaired a broken object and re-exported the universe.
At the report level, the reports that use this object would pick up the changed object’s SQL the next time they are opened/refreshed, just as they would for any object you change, e.g. if you change a Y/N flag to use a case statement to display Yes or No or any other standard universe change. The only things that wouldn’t be “fixed” are reports that are using custom SQL, but using custom SQL always carries a caveat that you run the risk of going it alone.
If it’s not it’s because I am not explaining this as simply as I should. Let’s leave the topic of existing reports for a minute, and address new reports. When a user logs into InfoView, and wants to create a new report based upon the columns that are now in table 2 that were in table 1 previously, they simply can’t, because in their view of the object list, the columns still display in table 1 when, from the database and Universe table view, they exist in table 2 now. Let’s also factor in that other columns were added to table 2 that did not exist before.
Now, users have created many reports against table 2 in the past, and need them to function. I on the other hand, want the object list to be current and reflect what columns now exist in what tables. I also no longer want columns in table 1 to show, that do not exist anymore. The issue is complicated by the fact that their reports are bound to the object IDs, and if I were to simply remove table 2 and re-add it, although it then displays correctly, I have now broken any reports that were calling that table.
Does that help? If not, could I post some screen shots as an example? Short end of the story, I have got to have the Object list reflect the current refreshed structure of the tables, and because previous reports exist, I can not correct this without breaking those reports, and if I leave it as is, although the previous reports work, new reports will not be able to utilize the new columns at all. I don’t think it’s as easy as changing the select statements, because NONE of the column changes are reflected in the Object list without me deleting and recreating those objects.
I really hope this makes more sense and appreciate your patience!!
If a column changes in a database it will not be reflected in the object list (and nor should it).
Object definitions are not just column names - you have aggregates, case statements, data type conversions, functions called and so on.
Don’t delete the object, you’ll break existing reports - as you appear to have discovered.
You have change the object definition, save and export the universe before you will see any changes for old or new reports.
The object list just can’t update based on table structure changes. For example a database environment such as an ERP db has many columns with the same name in many different tables. How is it to know how to change to point to the table/column you want it to. Thats one reason why it has to be a manual process. Seems like you should be able to change the objects to point to the desired table manually though.
And what about the new columns added to other tables? I have to say I’ve not had this issue in past updates to the database, so I was surprised I do now.
The way we use the BO platform has always been one-to-one , much in the way the older Crystal Reports clients worked where it connected straight to the database without a Universe to pass through. What exists in tables in the Universe on the right, has always been a direct mirror in the Object list on the left. It has to be that way, because our users know the database, the tables and the columns and expect to build reports in InfoView based off of the same. If I can’t provide that to them, then I have a break in functionality that won’t go over well <we are talking state government here :-/ >.
From what I am understanding based upon these replies, I have to delete the Objects columns that no longer match the tables they were removed from, and then add objects to the table/s new columns were added to? This is very much a manual process and seemingly a flaw in the Designer for those that are simply using the Universe as a “window” to the database.
I have attached a word doc showing the obvious. Again, how has this not been an issue for me in the past?? If the Object List on the left was never meant to be a reflection of the tables on the right, why is it when we drag the table over to the left to display in the list, it has objects for each column in the table? Just doesn’t add up, but I am certainly not disagrees with either of the replies. SAP_UNI.doc (90.0 KB)
The best action I can think of is to drag the existing object from table 1 class in your case and move it to table2 class and then refernce that object to the new column in table2.
Designer won’t automatically create or drop class or object when you refresh a data structure, but it will if you drop and add the table back in the designer window. But if you do this it will create new id for the objects and break the existing reports as Mark mentioned.
Thank you all. It seems to me that the inability to preserve the Object ID when re-adding a table in order to update it, or at the least be able to update the reports to allow for the change in the Object ID is the crux of the entire problem. It’s a shame SAP did not have any foresight to allow for this, and it now becomes another manual process I will have to do, deleting old objects and adding new objects to the correct classes every time the database changes.
Thank you all for your time and information, it’s much appreciated!
I mean re-adding Classes to the Object List in order for them to reflect the object changes, which was causing the Object IDs in existing reports to break.
Good steps but sad to have to lose the one-to-one naming scheme. Silly SAP…
What would be nice is a way to manually edit the Object ID that the reports reference, or instead of a huge nasty error in the report side, give the option to refresh the report with the new Object ID and keep going instead of being hard locked to the old Object ID. Now there’s innovation!!
Long story short, the Object List in the left pane of Designer does not update with Refresh Structure, nor is there any way to manually keep the classes/objects in sync with each other without it becoming a manual process once people have started creating reports.
Unlike many businesses I’m sure, I have no need for the two halves of the Designer to ever differ from each other. So when Table A has columns added/dropped from it, or columns are moved from one table to another, while Refresh Structure will pick up those changes for the right side structure view, it will not do anything for the left Object List view, which is what I was lacking. I did not realize it was a manual process of adding/deleting/editing objects to the classes, since deleting a class loses it’s “Object ID” that reports that have been created already could be bound to.
In what way? If all you have done is drag tables over to the object pane to auto-create classes, you’ve failed. Your universe design looks cluttered and you should spread your tables out to identify potential chasm and fan traps within the structure.
No failures here, just a different usage of the platform from what is probably the “norm” with most businesses. Again, I WANT the Object List to be a direct reflection of each and every class/object in the Universe structure. The users see a dictionary of the database structure, and know what tables/columns to select when building reports. Thus, my database view, and that in InfoView HAVE to match! I have NO reason to have it be otherwise.
The only “fail” is with SAP not allowing ease of use in this manner. Honestly, if I could drag Objects to the object list in the same way I can drag Classes as a whole, my issue would be less complex. But I do not see where I can, at the object level.
I appreciate the help, but I don’t see our methods as a “Failure”. Again, it’s just a different usage of the platform and our customers requirements are way less than other larger businesses with a bigger need/complexity.
I suggested the workflow / best practice of how to deal with this to stop your reports breaking.
Having it working on auto method, as you described would not be what most developers would want, as it provides little control.
If you really want to have this working, as you decribed, you will have to investigate the SDK route.
At the Designer level, you open your universe and refresh structure. You’ve had a column removed from F_OrderHeaders and one of the same name (but that’s irrelevant from a technical point of view) appears in F_OrderLines. You know already that any object containing F_OrderHeaders.OrderNumber is now broke. All you need to do at the Universe level is find the object and change the table name in the select section (assuming and hoping you’re not using the Where section 
) from F_OrderHeaders to F_OrderLines. The underlying object ID has not changed because you have not removed the object, you’ve simply repaired a broken object and re-exported the universe.
Refresh structure.
(BOB member since 2005-01-06)
(BOB member since 2005-01-06)