This has to be the parameter with the worst. Name. Ever. But let me start at the beginning.
Some databases require you to use actual column names in an ORDER BY clause. Like this:
select first_name, last_name, phone
from employee
order by last_name, first_name
Other databases let you take a shorter approach and sort by the position of the column in the select clause, Like this:
select first_name, last_name, phone
from employee
order by 2, 1
To be honest, I don’t like the shortcut. I would rather see explicit column names in my order by because that way I know exactly what is being sorted without having to refer back to the select clause. Another advantage is that if the objects in my select ever change, my order by is not affected.
There is a parameter found in the .PRM file for each database named SORT_BY_NO. When you see that name, what do you think it is? Every time I see it I assume that it is used to determine whether the SQL will contain numbers in the ORDER BY clause like order by 2, 1 instead of order by last_name, first_name. But that’s not what it does at all. Instead of doing what I described above, this parameter is used to determine if a query can be sorted by a column that does not appear in the select clause. That makes sense, doesn’t it? It should be called SORT_BY_IN_SELECT or something. But it’s not, and here’s how it works. Continue reading “SORT_BY_NO=NO? Very Confusing…”
Step 2: Recognize that with this version a “distinct” query no longer returns sorted results
Step 3: On the advice of Teradata, reconfigure your box with the “regression” parameter that makes distinct queries behave the way they did in 6.2
Step 4: Send a Business Objects LOV query to the database that includes a DISTINCT keyword and a where clause with a couple of constant values
Step 5: Watch the system reboot
That’s about what happened to us a few days ago. It wasn’t pretty. It took a long time to get our production box upgraded (and this after seeing development and Q/A roll through the upgrades with flying colors). Once the upgrade was finally completed, we had catch-up work as far as batch processing to do. Once that was complete the users got back into the system… only to see it sporadically reboot.
With a personal computer or laptop, a sporadic reboot is often a loose connection or faulty piece of hardware. We had not experienced anything like this on our database servers. Ultimately someone figured out that the following query was at fault:
select DISTINCT table.column FROM table WHERE table.column in ('A','B')
That’s a fairly innocuous query, isn’t it? At first someone thought the table was corrupt. Nope, it checks out fine. Next someone suggested that the data in the table was bad. Nope, I can query it just fine. Then we thought maybe the fact that there were some special characters in the where clause was the problem. Nope, they work fine too. Finally it was narrowed all the way down to the fact that we had a DISTINCT clause with the where clause and the regression parameter set on our database. Continue reading “Want To Crash Teradata? Give It Some LOV…”
I spent last Sunday and Monday in Atlanta. I didn’t see much of the city as I was holed up in a downtown hotel with 250 of my closest friends working on plans for ASUG’s coming year. There were a number of interesting things that came out of the meeting. I already posted about the location and dates for the fall conference. There are a few other tidbits that I wanted to share as well. In no particular order… Continue reading “ASUG Volunteer Summit”
I’m in Atlanta for the 2010 volunteer summit meeting for ASUG. One of the early announcements was the location and dates for the 2010 fall Business Objects conference. It will run from October 4 – 7 and will be hosted at the Dolphin Resort (near Walt Disney World) in Orlando, Florida.
Hi, folks, just wanted to let everyone know that I realize there are a number of comments or questions that have been piling up without responses. I’m a bit busy with work and preparing for the ASUG volunteer summit being held this weekend. I will catch up on comments and have some news about the GBN merger into ASUG and I hope some news about the fall Business Objects conference after this weekend.
One of the hardest concepts to explain when introducing new folks to Business Objects is context.
I now imagine blog readers who have been working with the tools for a while have started nodding their heads in agreement, only to stop and say, “Hey, wait a second, is Dave talking about universe contexts or report calculation contexts?”
And the answer is, of course, yes.
Both concepts can be quite complicated and take a while to fully understand. Since I have already written a number of blog posts about universe contexts I thought it was time to turn my attention to the report technique of calculation contexts. Note that this concept is used in Desktop Intelligence and Web Intelligence but not in Crystal. This post is intended to be an overview only. I have plans for a detailed post on each of the various context operators where I will go into much more depth. For this post my goal is to introduce the concept and provide some basic definitions. Continue reading “Calculation Context Part I: Overview”
It has been an interesting year. I got to attend more conferences this year than any other year before. I left one company and joined another. The GBN grew and then ultimately merged into ASUG. BOB grows ever closer to the next milestone (600,000 posts and 50,000 users). My BI blog (this site) has been running for eighteen months now, which some say is the average lifespan of a personal site or blog.
I’m not done yet. I’m done for this year, but I’ll see you again shortly.
Best wishes to you and yours during this holiday season.
Earlier this year I attended SAP TechEd 2009. Many of their sessions were lecture only, but they also provided a number of two or four-hour hands-on sessions. I selected one specific session in order to learn about improvements in the process used to build universes against SAP data sources like BEx queries. But of course I could not leave it at that. I got to the session a bit early and started poking around on the laptop to see if I could get some hints as to what we were going to cover. While poking around I found a universe named “Foodmart” so I opened it. It was… interesting. Continue reading “Foodmart 2000 Universe Review – Part I: Introduction”
In the first post in this series I defined what time-sliced measures are and why they can be useful in a universe. In the second post I described a special calendar table that was designed and built to support the requirements for this solution. I also showed how the join logic worked in conjunction with the table design. This post completes the implementation. I am finally going to work on the measure objects that a user will see.
In any universe design project I strive for the following goals:
Deliver the correct result
In my opinion, this is always the number one goal in any universe design.
User friendly
This is quite important but secondary to correctness
Easy to maintain
Universe maintenance is always allowed to suffer in order to provide the first two attributes on this list, but it is a worthwhile goal to strive for nonetheless
In this post I will show how all three of these goals are ultimately met by this implementation. When I am done I will have a completed universe. This post will cover slides 26 through 30 from my 2008 GBN Conference presentation. There is a link to download the file at the end of this post.Continue reading “Time Sliced Measures Part III: Making Measures”
Other items on the good news front… I have been continuing to challenge the ASUG/GBN folks about the location and time for the fall Business Objects conference. The inside rumor is that the initial round of location proposals has been processed and some final decisions are being made. I suggested Maui as a location, but it seems that’s not going to be an option.
It should be called SORT_BY_IN_SELECT or something. But it’s not, and here’s how it works. Continue reading “SORT_BY_NO=NO? Very Confusing…”
working on plans for ASUG’s coming year. There were a number of interesting things that came out of the meeting. I already posted about the 
