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.

2 Responses to “Updated Strategy Renders Schema Change SDK Tool Obsolete”

  1. Comment by Phil Morris

    Bumped into this post looking for something loosely related!

    We overcome a similar issue by using specific user accounts for BO to connect to Teradata. Each user will have default_database setting equal to required database area.
    All tables are then ‘unqualified’ in IDT so there is no schema/owner information.
    The query is then resolved using the default_database.

    This isn’t 100% and there are some weird things that happen occasionally, but generally speaking this seems to work OK.

    So, back to my issue – why is IDT ‘refreshing’ every table in every database it can see! Very likely because it can ’see’ them even though they are outside the default database.

  2. Comment by Dave Rathbun

    Phil, we looked at setting a default database but the problem is that some users have more than one database (schema) they need to connect to. We use pass-through usernames in order to enforce row security on the Teradata side, so we’re not able to use this technique.

    As to why IDT refreshes every table… that matches the behavior of Universe Designer as well. We used to get around this by altering the strategy SQL file so that it included a where clause on the list of tables…

Leave a Reply

If you want to include formulas or code in your comment, please read my Tips for formatting comments first. Tags you can use are listed below.

XHTML: You can use these tags: <a href="" title=""> <acronym title=""> <blockquote cite=""> <code> <em> <strike> <strong> <sup> <sub> <u>

Confirm submission by clicking only the marked checkbox:

 []                 

Please remember that comments that are not related to this blog post may be ignored or deleted without notice. If you're looking for help on a topic you have already posted on BOB then please do not repost your question here.