Jul 30 2013

Pivot UserResponse() Values Into Rows

Categories: Report Techniques, Variables! Dave Rathbun @ 6:15 am

Several years ago I wrote a post that has generated a fair number of follow-up questions and comments. The post was related to splitting user response values onto separate rows and it used some basic string operations to do that. The important distinction is that the values were on different rows but remained in a single cell, which meant the output was suitable for a header cell.

Recently I got a comment that posed this question:

In one of my reports there is prompt to select the Fiscal Year and the user can select multiple LOVs. Prompt Name is “Year”. Say for example the user enters 2011,2012 and 2013. On using the function userresponse the report will show 2011;2012;2013

My requirement is to identify minimum value from the LOVs that the user has entered. In this case the report should show the mininum value as 2011. Can you please guide me on how to achieve this?

Continue reading “Pivot UserResponse() Values Into Rows”


Jul 25 2013

Updated Strategy Renders Schema Change SDK Tool Obsolete

Categories: Universe Design, VBA Tools Dave Rathbun @ 10:23 am

Many years ago when I first started working with Teradata we had a challenge. The DBA team had defined a standard where each table was tagged to indicate the purpose of the table. Specifically, and development table would be called PROJ_D.TABLE_NAME and the equivalent table in production was called PROJ_P.TABLE_NAME. Why do this? In a Teradata connection I connect to a server, not to a schema (or instance as Oracle would call it). One of the DBA strategies was to use the QA or “system test” hardware as a preliminary DR (disaster recovery) platform. That means they keep a copy of the tables in PROJ_S and the same table exists as PROJ_P on the same server. In order to have specific queries sent to the database I had to include the schema name (or prefix) on every table name in my universe. During DR testing I could reset my schema from PROJ_S to PROJ_P without moving the universe (it’s still on the QA server) and now I would be using the production tables.

While this did provide the advantage of being specific, it also presented a problem during migrations because I had to change my code. I first wrote about this back in 2008 when I shared a utility that I wrote to make this process easier. (Read the details in the Using the Designer SDK to Ease Migrations post.)

With the advent of BI4 the new challenge is that we don’t (yet) have an SDK. At the same time we have become a much more mature Teradata shop. Our DBA team recently introduced a new strategy that eliminates the issue altogether, meaning I don’t have to worry about either of the issues listed above.

New View Layer

Our standard for reporting says that our tools (Business Objects included) never access the source tables. As a result, the naming convention I described above was used on all of the views that I used in my universe. Assume we have views called PROJ_D.TABLE1, PROJ_D.TABLE2, and PROJ_D.TABLE3. In the “old days” I would have to update each of these tables when I want to migrate my universe out of the development environment. Our new strategy seems simple on the surface, probably because it is :) , but it solves both issues.

For each and every table that I am using we have now created a “semantic layer” view on top of the base view (which is on top of the base table). So for each of the above tables I know have this:

create view SEM_PROJ.TABLE1
as
select * from PROJ_D.TABLE1

The “PROJ” portion of the schema related to the project, so it remains as part of the view name. The “SEM” prefix is of course short for “semantic layer” and indicates that this view is used by some sort of external tool. What is missing from the new view name is the location tag (either _D or _S or _P for production). This seems like a very simple solution (and it is) but it took a while for us to get here. We have created the semantic layer views for one project so far, and it’s a real pleasure to be able to migrate a universe without touching it anymore. 8-) I anticipate we’ll be using this strategy for all of our Teradata projects from this point forward. Obviously the select clause changes in each environment, but finally I have a consistent view name in all environments.

When I have to reset my universe due to a disaster recovery exercise, it’s now up to the DBA team to re-point the semantic layer views to the proper schema. When I migrate the universe I no longer have to touch anything, except to perhaps change a connection. It’s a much cleaner process, and no longer requires me to be concerned about waiting for the full SDK to become available in BI4.


Jul 23 2013

Really Cool Stock Market Visualization

Categories: General Dave Rathbun @ 3:08 pm

I was looking over some financial sites the other day and ran across this visualization that is based on the stock market. The overall presentation is divided into blocks of companies grouped by sector. The size of the company within their sector is based on their market capitalization, and the color indicates the current stock price trend. Clicking on a sector allows the viewer to “drill” down into the sector data. At the bottom of the frame is a drop-down that allows you to specify the time range used to identify the up or downward trend, with options for since last close, 26 weeks, 52 weeks, or year to date. While the color (green or red) shows the direction of the stock price move, the intensity of the color shows the percentage of the move. A company like Barrick Gold which has dropped substantially during 2013 (down ~48%) is fairly bright red, while PepsiCo (up ~25% YTD) is a muted green.

I think this is one of the best uses of this visualization style that I have seen, and decided to share it.