Universe changes

system · March 21, 2000, 2:19pm

I have a universe that contains 12 tables. A field name was changed in one of the tables. When I now view the universe in the designer module, I can see the table with the corrected field name. However, when I look at the table in the left section of the screen I see the old field name still appears. I went to review/refresh structure and the table on the left section still shows the old field name.

How do I update the table with the new name? I was going to delete the table from the left side and put the corrected table back in but thought it might affect the reports that access it.

Any help is greatly apreciated.

Thanks,
Dave

Listserv Archives (BOB member since 2002-06-25)

system · March 21, 2000, 2:23pm

2 things,

You can change the name of the object and your reports will reflect the updated name. As long as you just change the object, (not add a new one and delete the old). You just need to make sure it keeps the same id as it already has so you can do any modifications.
edit the object and see if the object is pointing to the correct table and field - if not, you can change that as well.

Hope it helps!

Julie

I have a universe that contains 12 tables. A field name was changed in one of the tables. When I now view the universe in the designer module, I can see the table with the corrected field name. However, when I look at the table in the left section of the screen I see the old field name still appears. I went to review/refresh structure and the table on the left section still shows the old field name.

How do I update the table with the new name? I was going to delete the table from the left side and put the corrected table back in but thought it might affect the reports that access it.

Any help is greatly apreciated.

Listserv Archives (BOB member since 2002-06-25)

system · March 21, 2000, 2:25pm

Dave

You answered your own question. Delete the table on the left,rename it the same way as before, change that object name the same as it was before and that should work. If this does not work for the report, do everything and remove the objects from the report that came from the old table, then add the objects from the new table and run it again.

This will work for you.

Aris

Listserv Archives (BOB member since 2002-06-25)

system · March 21, 2000, 3:23pm

SOGRIZ@QGRAPH.COM

This is not the correct way to do this. If you delete the class/object on the left, and create a new class/object, you get a new ID for that object and your reports become invalid.

To do this the right way, you need to go into the Object, and alter the SELECT statement to show the correct table and fieldname.

Snezana Ogrizovic

Listserv Archives (BOB member since 2002-06-25)

system · March 21, 2000, 3:53pm

SOGRIZ@QGRAPH.COM

Hi

The SQL is regenerated the 1st time that you open & refresh the report. I’ve been doing this for quite a while and did a simple test. Give this a try:

Create a report off of a universe.
Save the report after you note the SQL generated. 3. Exit BusinessObjects.
Change the table source or field source for one of the objects you used
in your report.
Save your universe.
Start Business Objects and Open your previously saved report. 7. Refresh the report.
Note the differences in your SQL.

Snezana Ogrizovic

=================================================================

O.K. but how do you ensure that your reports will automatically regenerate the new SQL?
Remember that the reports cache the previous SQL statement.

Listserv Archives (BOB member since 2002-06-25)

system · March 21, 2000, 4:32pm

O.K. but how do you ensure that your reports will automatically regenerate the new SQL?
Remember that the reports cache the previous SQL statement.

Listserv Archives (BOB member since 2002-06-25)

system · March 21, 2000, 4:48pm

SOGRIZ@QGRAPH.COM

Hi,

I hit the refresh button and that has always done the trick for me. I am on version 5.0.2. Which version are you in and what’s your back end database?

Snezana Ogrizovic

Listserv Archives (BOB member since 2002-06-25)

system · March 21, 2000, 5:44pm

Specifically how did you refresh your report? If you simply hit the refresh icon it does not always regenerate the SQL for me. -----Original Message-----
Hi

The SQL is regenerated the 1st time that you open & refresh the report. I’ve been doing this for quite a while and did a simple test. Give this a try:

Create a report off of a universe.
Save the report after you note the SQL generated. 3. Exit BusinessObjects.
Change the table source or field source for one of the objects you used
in your report.
Save your universe.
Start Business Objects and Open your previously saved report. 7. Refresh the report.
Note the differences in your SQL.

Snezana Ogrizovic

=================================================================

O.K. but how do you ensure that your reports will automatically regenerate the new SQL?
Remember that the reports cache the previous SQL statement.

Listserv Archives (BOB member since 2002-06-25)

system · March 21, 2000, 5:58pm

SOGRIZ@QGRAPH.COM

Ah, I am running on Sybase System 11. As I’m not a SQL7 expert, I can’t say what the exact way is that the query/SQL is stored. Any SQL7 experts to comment?

S. Ogrizovic

Listserv Archives (BOB member since 2002-06-25)

system · March 21, 2000, 6:08pm

Version 4.1.5 and the database is MS SQL 7 I wonder if this has anything to do with how queries are sent to the server. In the case of SQL 7 the queries are precompiled in a stored procedure and the stored procedure is run against the database. SQL may cache these stored procedures not knowing that the SQL has changed. Hmmm…

Listserv Archives (BOB member since 2002-06-25)

system · March 21, 2000, 7:04pm

Or…could be B.O. version 4.x vs 5.x?

Listserv Archives (BOB member since 2002-06-25)

system · March 21, 2000, 7:35pm

SOGRIZ@QGRAPH.COM

This is where my oversight was! In Supervisor, I have set the SQL to always regenerate. It’s been a while and I forgot that setting.

Thanks for reminding me.

Snezana Ogrizovic

======================================================================== One of the problems with particle physics is that it is difficult to run experiments on a sub-atomic level. There is no way to monitor the experiment without altering the outcome. In other words, if you weren’t looking, then a different result would occur! So how can you have experiments that you can never watch? I guess that’s a question for the particle physics list.

What does that have to do with BusinessObjects? And more specifically, the idea of regenerating the SQL? See notes below…

In a message dated 00-03-21 12:46:32 EST, you write:

Specifically how did you refresh your report? If you simply hit the refresh
icon it does not always regenerate the SQL for me.

My understanding is that clicking on the “Refresh” button on the toolbar does NOT regenerate the SQL. Even if a universe has changed. You can validate this quite easily if your universe has contexts. If you select certain objects (in Island Resorts select only the Resort and the Customer objects) then you are prompted for a context. Only after selecting a context is the SQL generated.

Once you run the query, you can use the Refresh button on the toolbar and you will never be prompted for a context selection. It simply re-uses the SQL that was already generated by the Query Panel.

Now, on to the subject of “looking”…

Hi

The SQL is regenerated the 1st time that you open & refresh the report. I’ ve
been doing this for quite a while and did a simple test. Give this a try:

Create a report off of a universe.

Save the report after you note the SQL generated. 3. Exit BusinessObjects.

Change the table source or field source for one of the objects you used
in your report.

Save your universe.

Start Business Objects and Open your previously saved report. 7. Refresh the report.

Note the differences in your SQL.

The only way you can note the difference in your SQL is to look at it. And the process of looking at the SQL (via the SQL button on the query panel) causes BusObj to regenerate the SQL.

In other words:

You can’t verify the SQL changes without looking Looking does regenerate the SQL, and so shows the universe changes Not looking does not regenerate the SQL

So you will only see universe changes if you look, which makes it seem as if it is working.

Here’s an experiment to run (and this one you CAN watch ):

Create a new variable and place it on your report. The variable can be called “DP SQL”, and the formula is something like (for my Island Resorts example):

=DataProviderSQL(DataProvider())

This formula will retrieve the SQL from the query and display it on the document. After doing this, change an object definition in your universe, save, and export it. Open the document and click on the “Refresh” button on the toolbar. You will not see the SQL changes.

Interestingly enough, in 4.1 I even removed an object from the universe that was used in the document. The query ran as is, without recognizing that the object was no longer available. When I invoked the query panel, then and only then did I get the message about “obsolete” objects being removed.

In version 5 you will see this same behavior.

If you need to have the SQL regenerated even by using the Refresh button on the toolbar (as opposed to running from the Query Panel) then you can alter this behavior.

In Supervisor, right-click on BusinessObjects and select Properties.

In v5, select the “Query Technique” folder from the left hand side. In v4, select the “Query Technique” setting from the pull-down menu. There is a setting for “Do not always regenerate SQL” that is on (enabled) by default. Disable this setting, and your documents will always regenerated the SQL.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

system · March 21, 2000, 8:21pm

One of the problems with particle physics is that it is difficult to run experiments on a sub-atomic level. There is no way to monitor the experiment without altering the outcome. In other words, if you weren’t looking, then a different result would occur! So how can you have experiments that you can never watch? I guess that’s a question for the particle physics list.

What does that have to do with BusinessObjects? And more specifically, the idea of regenerating the SQL? See notes below…

In a message dated 00-03-21 12:46:32 EST, you write:

Specifically how did you refresh your report? If you simply hit the refresh
icon it does not always regenerate the SQL for me.

My understanding is that clicking on the “Refresh” button on the toolbar does NOT regenerate the SQL. Even if a universe has changed. You can validate this quite easily if your universe has contexts. If you select certain objects (in Island Resorts select only the Resort and the Customer objects) then you are prompted for a context. Only after selecting a context is the SQL generated.

Once you run the query, you can use the Refresh button on the toolbar and you will never be prompted for a context selection. It simply re-uses the SQL that was already generated by the Query Panel.

Now, on to the subject of “looking”…

Hi

The SQL is regenerated the 1st time that you open & refresh the report. I’ ve
been doing this for quite a while and did a simple test. Give this a try:

Create a report off of a universe.

Save the report after you note the SQL generated. 3. Exit BusinessObjects.

Change the table source or field source for one of the objects you used
in your report.

Save your universe.

Start Business Objects and Open your previously saved report. 7. Refresh the report.

Note the differences in your SQL.

The only way you can note the difference in your SQL is to look at it. And the process of looking at the SQL (via the SQL button on the query panel) causes BusObj to regenerate the SQL.

In other words:

You can’t verify the SQL changes without looking Looking does regenerate the SQL, and so shows the universe changes Not looking does not regenerate the SQL

So you will only see universe changes if you look, which makes it seem as if it is working.

Here’s an experiment to run (and this one you CAN watch ):

Create a new variable and place it on your report. The variable can be called “DP SQL”, and the formula is something like (for my Island Resorts example):

=DataProviderSQL(DataProvider())

This formula will retrieve the SQL from the query and display it on the document. After doing this, change an object definition in your universe, save, and export it. Open the document and click on the “Refresh” button on the toolbar. You will not see the SQL changes.

Interestingly enough, in 4.1 I even removed an object from the universe that was used in the document. The query ran as is, without recognizing that the object was no longer available. When I invoked the query panel, then and only then did I get the message about “obsolete” objects being removed.

In version 5 you will see this same behavior.

If you need to have the SQL regenerated even by using the Refresh button on the toolbar (as opposed to running from the Query Panel) then you can alter this behavior.

In Supervisor, right-click on BusinessObjects and select Properties.

In v5, select the “Query Technique” folder from the left hand side. In v4, select the “Query Technique” setting from the pull-down menu. There is a setting for “Do not always regenerate SQL” that is on (enabled) by default. Disable this setting, and your documents will always regenerated the SQL.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

system · March 21, 2000, 9:22pm

Do you have to disable it?
Can you hide it instead?
Is this an undocumented thing?
Simon

Listserv Archives (BOB member since 2002-06-25)

system · March 21, 2000, 9:31pm

In a message dated 00-03-21 16:23:57 EST, you write:

Do you have to disable it?
Can you hide it instead?
Is this an undocumented thing?
Simon

I imagine that you have to disable it. There is no option that you can view in the client software that would be “hidden”.

In v5, select the “Query Technique” folder from the left hand side. In v4,
select the “Query Technique” setting from the pull-down menu. There is a setting for “Do not always regenerate SQL” that is on (enabled) by default. Disable this setting, and your documents will always regenerated the SQL.

As far as being undocumented… I didn’t know about it until I started digging. But I have never read the Supervisor manual (or help file for that matter) so I don’t know if there is an explanation of this or not.

One interesting note: in v5 you can disable and it works even for a general supervisor. In v4, nothing can be revoked from general supervisor. It appears that you must log on with a “normal” user ID to see the effects.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

system · March 21, 2000, 9:53pm

If you look at the generated SQL statement dialogue box there is a check box on the lower left hand corner. (In the Business Objects Query Panel: click on the “SQL” button) I thought that the Supervisor option “Query Technique” enabled, disabled or hid this check box. I have used it in the past to override my SQL statements. How is this check box related to the forcing of the SQL to be regenerated under all circumstances?
Not that I don’t believe you, it just seems a bit strange. Simon

Listserv Archives (BOB member since 2002-06-25)

system · March 21, 2000, 10:06pm

If you have “Edit Query SQL” enabled, you will be able to manually modify the SQL of your query. In order to keep the changes when you click “Run”, (i.e. to prevent BusinessObjects from regenerating the SQL) it is necessary to check the box you mention.

On the other hand, you can force BO to regenerate the SQL every time you refresh a query, by disabling “Do not always regenerate SQL” (you negate a negation, which means “DO regenerate …”.

These two features are distinct and have different purposes.

Jean-Francois Cayron
InfoSol Inc.
http://www.infosol-inc.com

Listserv Archives (BOB member since 2002-06-25)

system · March 21, 2000, 10:30pm

Which brings me back to the original point: You have to (as Dave put it) “look” at the query for B.O. to decide whether or not to regenerate the SQL. Just because this option is disabled does not mean that you are guaranteed that your report will regenerate the SQL regardless of what you do or do not do.
I hope that I am wrong!!!
Simon

Listserv Archives (BOB member since 2002-06-25)

system · March 21, 2000, 10:34pm

In a message dated 00-03-21 17:31:15 EST, you write:

Which brings me back to the original point:
You have to (as Dave put it) “look” at the query for B.O. to decide
whether
or
not to regenerate the SQL. Just because this option is disabled does not mean
that you are guaranteed that your report will regenerate the SQL
regardless
of
what you do or do not do.
I hope that I am wrong!!!

If you reset the setting I mentioned before, then the SQL is regenerated every time. It doesn’t matter if you “look”, it doesn’t matter if you go to the query panel or simply use the Refresh button.

Disabling the “Do not regenerate SQL” becomes - as someone said - a double negetive. Do not do not regenerate SQL or DO regenerate SQL.

If you use the Island Resorts example using the context, or use the DataProviderSQL function, then you can test and convince yourself that the SQL will be regenerated every time if you disable this setting.

Which begs the question: why is the setting disabled in the first place?

I suspect the answer is performance. On a large universe it takes time to regenerate the SQL. BusObj is assuming that once the SQL is generated it is “good”, so for performance reasons they don’t load the universe and regenerate it each time.

I think.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

system · March 22, 2000, 4:54pm

X-cc: “Koch, Rick” Rick.Koch@diverseylever.com

Hi Dave,

a) I went into Supervisor (version 5.0.1) and disabled the option “do not always regenerate SQL”. I then changed an object name in Designer (version 4.1.7 and the Universe directory is shared between version 4 and 5). I opened up a report in the full client (version 4.1.7) and refreshed the report. I then looked at my variables. The report has multiple queries, and only the first one had the SQL regenerated.

b) I then logged out of Business Objects. I changed the option “do not always regenerate SQL” back to enabled. I then logged back into Business Objects and re-opened the above report and refreshed again (report was not saved from the above experiment). Same results: only the first query picked up the change to the object name.

c) I repeated the same experiment as above but I changed the underlying select statement instead of the object name. Same results: the Supervisor settings did not make a difference and in all cases only the first query was changed.

d) There is a check box called “do not regenerate SQL when running”. I can not figure out how to hide or disable it when the user has the rights to edit their own SQL. It does not seem to be related to the “do not always regenerate SQL” in Supervisor. I even tried disabling the “edit query SQL” in combination with the “do not always regenerate SQL” being disabled but this did not make a difference.

e) Whenever an object name is changed or the underlying SQL is changed then only the first data provider picks up this change. If there is a second, third, … tenth data provider then these do not automatically pick up the changes. I have always been led to believe that the first data provider would not automatically be regenerated and now I am wondering if this is a random thing. I have concluded that it is a shot in the dark as to what triggers your report to regenerate the SQL.

f) This is not a “versionitis” thing. I tried this with all version 5 products and received the same results. I was logged into Supervisor as the General Supervisor and was using Designer and the full client logged in as a separate user profile.

g) As a last resort (and out of desperation) I tried the same experiments with logging in as the General Supervisor into the full client. In this case ALL the data providers were updated with the Universe Changes upon pressing the refresh button.

Conclusions:

a) Only the General Supervisor profile will ensure that all your data providers automatically have the SQL regenerated for ALL the data providers when you open an existing report and click on the refresh button. b) There is no way to directly disable or hide the check box called “do not regenerate SQL when running” in the full client. c) The Supervisor option “do not always regenerate SQL” does not do squat. d) Clicking the refresh button is like throwing the dice as to whether your reports will pick up the Universe changes. Even if it does only your first data provider will be updated.

Simon

Listserv Archives (BOB member since 2002-06-25)