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…”
Five easy steps to crash your Teradata system:
- Step 1: Upgrade to Teradata version 13
- 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…”
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”
When working with the reporting suite from Business Objects there are many different calculation engines. A report developer can create custom formulas or variables in Desktop Intelligence, Web Intelligence, and of course Crystal. A universe designer can build custom objects using database functions in the universe. An ETL architect can design special query transformations. So where do you do the work?
This post covers slides 6 through 9 from my 2009 GBN presentation titled “Return of the Variables” which can be downloaded from my conference page. Continue reading “Calculation Options”
All I can say is “wow, I can hardly wait” for some of the features discussed in a private strategy session for the semantic layer. I need to find out how much I can say (and when) before I say much more on a public blog though.
I haven’t had a good rant in a while. I’m overdue.
Here are some real comments that were posted on BOB.
I already have around 18 contexts in Universe and hence I dont want to add any extra
The last two companies I have worked at, forbade the use of contexts
I only create contexts when I’ve got no other choice.
And this one wins the prize:
Here’s a list of what I cannot do (client requirements)
1. Cannot use contexts in the universe.
…
If you recognize one of your comments, please understand I am not ranting about you as a person, but about the sentiment or opinions expressed in the comment itself.
As a consultant, my job was sometimes to deliver a solution. At other times my job was to provide advice. My advice was based on my assessment of two things: what the client told me they wanted, and what I felt like they really needed. Continue reading “Use Contexts, Dammit”
In the first post in this series I defined what time-sliced measures are and why they can be useful in a universe. I also shared the design requirements for a particular project I was on. The requirements included:
- Each report is expected to have multiple time-sliced measures
- Users must be able to select the type of calendar during the refresh process; calendar types include Monthly and Fiscal
- Users can provide any possible date as the “to date” for the time slices
- The process of splitting each time-slice time period into its own SQL statement should be completely transparent
- The resulting SQL should be as efficient as possible
In this post I am going to cover the design of a special table that we built in order to support our solution for these requirements. After I talk about the table design I will cover how I use it in the universe, as well as provide a few pros and cons about this solution as I have outlined it so far. Just to prepare you, this post is a bit longer than most that I write, and gets fairly detailed. This post will cover slides 23 through 25 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 II: Time Slice Calendar Table”
During the 2008 GBN Conference in Dallas I delivered a presentation related to universe design. The last third of the presentation demonstrated a solution for time-sliced measures that I have used on a couple of different projects now. After the presentation I had two different people make suggestions that were proposed as being easier to implement than what I showed.
In each case I was able to tell the person that we had considered and perhaps even tested their suggestion and found it lacking in some way. I didn’t have time to present all of the different options during the one hour slot that I had at the conference, but I have unlimited time to explain options here on my blog. But before I go back and detail things that we tried that did not work out so well, I am going to have a few posts (it’s too long for just one) about the solution we did implement.
This blog post will cover slides 19 through 22 from the 2008 GBN Conference presentation. Continue reading “Time-Sliced Measures Part I: Defining the Problem”
I did a fun presentation (at least I had fun) at the New York user group a few years back. I found the original download package and have posted it on the presentations page on this site. The download package includes the presentation in Adobe PDF format, several universes, some Microsoft Access database files, some sample reports… basically everything you need to go through the slides and review the examples.
There is a “readme” file that contains notes on how to get everything working. The reports and universes were originally built in version 5, but should be usable in any current version (up through XI 3.1 as I type this). The demonstration reports were all built in Desktop Intelligence.
