Dave's Adventures in Business Intelligence

While researching a Teradata syntax question earlier this morning, I discovered that I can download a virtual machine with Teradata 13 (or 12) that will run on my laptop.

The best part about this is I already have the VMware server up and running. Once I complete the download of the TD image (it’s about 4GB for the 1TB Teradata installation, or 2.3GB for a 40GB Teradata configuration) I can get up and running with a “play” Teradata system in just a few minutes. How cool is that? 😎

Related Links

• Download Teradata Virtual Machine Images
• Download VMware Server or Player

One of the reasons I have been slowing down on blog posts is my virtual machine / blog development environment was still running XI 3.0. Over the past week I have managed to get my VM upgraded to XI 3.1 SP2 and that gives me quite a few new goodies to play with. I am looking forward to seeing how I can use and abuse input controls among other things.

I did encounter one slight issue. My VM host operating system is Windows Server 2003. The XI 3.1 install went fine. The SP2 install never did anything. During the install process it was complaining about a server issue and said something like, “… you may encounter isssues and if you do please read Microsoft KB article blah blah blah…” After I finally decided to pay attention and look up that KB article (and apply the fix available) everything went fine. It had to do with the .msi file (installation file) being too large for my server operating system to validate.

I took a BIAR file from my XI 3.0 virtual machine and restored it on the 3.1 system. My reports and universes all moved over just fine.

I have to say that virtual machines are AWESOME. 🙂 I now have VM images for 6.5, XI R2, XI 3, and XI 3.1 SP2. I can launch any of these as I need them. Perhaps I need to confirm that a technique that I am using in XI 3.1 is backwards compatible… I can do that. Maybe I want to test the impact of an upgrade, I can start in 6.5 and roll it forward through various versions. All it takes is disk space, and disk space is cheap today. When I was a consultant it was a great way for me to create separate environments for each client. Now I just use it to maintain different environments without having to manage multiple installations on one hardware. It’s also a great way to set up a “demo system” for use in presentations.

Oh, and blog posts. 😎

We are using the OpenDocument() function to “drill” from one document to another. In order to make report maintenance easier I have created some objects in the universe that contain the proper syntax for the URL required to access this function, as well as one that contains the report path. This way even if we change our folder names or structure I can change the universe and do not have to update every report on the project. This has worked very well for us.

Until my current project.

On this project the primary distribution channel was PDF sent via email. Our users said that the links were not working. And of course every time I tested by logging in to Infoview the links worked just fine. After further investigation by another team member, it seems that our Report Path (in the format [Folder],[Sub Folder] was being truncated at the comma. As a result, the OpenDocument() function was looking for the reports in [Folder] and ignoring the full path. That was a bit of a problem. Continue reading “Fixing Report Path For Adobe PDF Viewers”

Jason Cao, one of the Business Objects Community Evangelists at the SAP Community Network (SCN) has an interesting challenge for Xcelsius developers. He has posted some very simple data and asked for folks to jazz it up with Xcelsius.

Xcelsius Challenge for Community Poll Results

If you’re interested, you have until April 30, 2010 to enter your submission.

I’m thinking of creating a pie chart. 😛

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…”

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.

Save the date. 😎

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”