Microsoft SQL Server and INFORMATION_SCHEMA

Looking for Microsoft SQL Server metadata?

Most of my database experience is with Oracle; however, I’m currently helping a client that uses SQL Server 2005. I’m used to obtaining metadata by querying ALL_TABLES and ALL_TAB_COLUMNS (or USER_TABLES/USER_TAB_COLUMNS or DBA_TABLES/DBA_TAB_COLUMNS). How can similar information be obtained from SQL Server using similar techniques to ALL_TABLES/ALL_TAB_COLUMNS in Oracle?

 

Microsoft SQL Server Information SchemaMetadata can be queried in SQL Server using the INFORMATION_SCHEMA. For example, the following query identifies columns in one table that do not exist in another (actually, a view based on the table).

— INFORMATION_SCHEMA query to identify columns missing in database view but present in source table.SELECT source.TABLE_CATALOG, source.TABLE_SCHEMA, source.TABLE_NAME, source.COLUMN_NAME, source.ORDINAL_POSITION, SOURCE.data_type
FROM
INFORMATION_SCHEMA.COLUMNS source
WHERE
source.TABLE_CATALOG =‘FACT’
AND
source.TABLE_SCHEMA =‘dbo’
AND
source.TABLE_NAME =‘FCT_FREIGHT’
AND
NOTEXISTS
(
SELECT
‘X’
FROM
INFORMATION_SCHEMA.COLUMNS missing
WHERE
missing.TABLE_CATALOG =‘FACT’
AND
missing.TABLE_SCHEMA =‘dbo’
AND
missing.TABLE_NAME =‘FCT_FREGHT_EV’
AND
missing.COLUMN_NAME = source.COLUMN_NAME
)
ORDERBY source.ORDINAL_POSITION

This is just one application of using database metadata. Visit MSDN, the Microsoft Developer’s Network, for more information about the INFORMATION_SCHEMA.

Business Objects, an SAP company

And so it begins…

“On January 16, 2008 SAP announced next steps in the successful acquisition of Business Objects, uniting two of the technology industry’s biggest brands with a shared vision to transform the way the world works by connecting people, information and businesses.” Although the acquisition became effective on January 16, today is the first day the Business Objects logo now sports the “an SAP company” tagline.

Customers are invited to join a live online event on February 12, 2008 to learn how SAP and Business Objects will transform the way the world works by connecting people, information, and businesses…

2008 Business Objects Partner Summit

Thoughts from the 2008 BusinessObjects Partner Summit

I’ve arrived at the Bellagio hotel in Las Vegas, site of the Business Objects Partner Summit 2008. This is the first Partner Summit held in conjunction with the Business Objects Sales Kickoff, so it will be an opportunity to network extensively with the Business Objects sales force.

Today was the first day of the conference. Unfortunately, the juicy product stuff is under NDA. But I attended a great presentation called How To Give Compelling Demos by Peter Cohan. You can order his book, Great Demo!: How To Create And Execute Stunning Software Demonstrations, from Amazon.com.

During one of the general sessions, Bernard Liautaud (Business Objects founder and chief strategy officer) spoke live via satellite phone with Pete Collette, a sales consultant for Business Objects in Australia. Pete is rowing the Atlantic solo – a pretty awesome feat. You can read more at Atlantic Solo Challenge.

Can’t view above slide show?  Try this link instead.

Queries to identify universes and their universe connections

Query Builder connections for troubleshooting universe connections.

The Query Builder can be used to analyze universe connections. I recently used the Query Builder to assist in how many universes (and which ones) were using a specific connection. Using the information, I was able to update and republish universes to use the “preferred” universe connection. Once all universes were updated, the Query Builder clearly showed that the “non preferred” universe connections were no longer in use and could be safely deleted using the Central Management Console (CMC).

First, determine the SI_ID of the connection.

SELECT SI_NAME, SI_ID, SI_DESCRIPTION, SI_CONNUNIVERSE
FROM CI_APPOBJECTS
WHERE SI_KIND=’MetaData.DataConnection’
— AND SI_NAME = ‘DW’

ORDER BY SI_NAME

Then, use the list of IDs in the SI_CONNUNIVERSE collection to build a second query to inquire about the universes that use that connection.

SELECT SI_NAME, SI_SHORTNAME, SI_DESCRIPTION, SI_ID, SI_PARENT_FOLDER, SI_OWNER, SI_CREATION_TIME
FROM CI_APPOBJECTS
WHERE SI_KIND=’Universe’
AND SI_ID IN (692,670,633,634,699)

The Query Builder can also identify unused universe connections. Using the first query above, look for connections where the SI_CONNUNIVERSE has a single attribute of SI_TOTAL with a value of 0. For example, no universes are using the test only connection.

SELECT SI_NAME, SI_ID, SI_DESCRIPTION, SI_CONNUNIVERSE
FROM CI_APPOBJECTS
WHERE SI_KIND=’MetaData.DataConnection’
AND SI_NAME = ‘test only’

ORDER BY SI_NAME

Accordingly, the test only connection can be safely deleted from the Central Management Console (CMC).

Resources

If you’ve found a useful resource regarding the Query Builder, feel free to post a comment to share with others.

Going to 2008 Business Objects Partner Summit

I’m headed to my first BusinessObjects Partner Summit!

Does what happens in Vegas really stay in Vegas? The upcoming Partner Summit 2008 will be my first trip to Sin City.

Join us for Partner Summit 08 where for the first time it will be held in conjunction with our Worldwide Sales Kickoff conference to allow our partners access to our internal sales tools and training.

Looking forward to seeing my friends in the sales, sales consulting, and partner communities. See you at the Bellagio!

Dallas Marks on MySpace?

One would think that a name like “Dallas Marks” would be unique. Maybe even Google-able. Sigh – not so. In addition to being my dad’s name (although we have different middle names), it is also the name of a country western singer in Pennsylvania. You can check out his MySpace page. He’s not me. Just in case you were wondering…

My Important Moments with Business Objects

Key events at the start of my SAP BusinessObjects career

I’ve been cleaning up my home office as part of getting ready for the new year. I stumbled across some of my old planners. Some nostalgia…

February 13, 2003: After nearly 10 years working with Unix, C & C++, and Oracle, began first Business Objects consulting engagement
September 24-26, 2003: Attended Universe Design training

December 1-3, 2004:
Taught my first Business Objects classes (v6.5 Core, Intermediate, and Advanced Reporting)

May 24-27, 2005: Business Objects XI R1 Fast Track Training, Atlanta, Georgia
June 8, 2005: Passed SAE201 Certification Exam
June 13, 2005: Passed SAE301 Certification Exam
June 20, 2005: Passed SAE401 Certification Exam – I’m a Business Objects Certified Professional!
July 14, 2005: Passed Accredited Report Designer v6.5 Exam
July 28, 2005: Organized steering committee and relaunched SONKBOUG (Southern Ohio/Northern Kentucky Business Objects User Group). Presented Getting To Know Business Objects XI
November 6-9, 2005: Business Objects Insight Americas 2005, Orlando, Florida
December 1-2, 2005: Train-the-Trainer training at Xavier University
December 12, 2005: Taught my first Web Intelligence XI R1 class

February 26-28, 2006: Business Objects Partner Summit 2006, Austin, Texas
July 28, 2006: Accepted as breakout speaker for Business Objects Insight Americas 2006
October 26, 2006: Presented my Insight 2006 presentation, Getting Personal with Publications and Profiles, at SONKBOUG (Southern Ohio/Northern Kentucky BusinessObjects User Group)
November 5-8, 2006: Breakout speaker at Business Objects Insight Americas 2006, San Francisco, California

February 13, 2007: Speaker at Northern Ohio BusinessObjects User Group (NOBOUG) in Cleveland, Ohio
May 14-16, 2007: Customer Centric Selling training for Business Objects partners
July 28, 2007: Accepted as breakout speaker for Business Objects Insight Americas 2007
September 2007: Business Objects Accredited Specialist: Crystal Decisions/Edge Series
September 2007: Business Objects 6.5 Accredited System Administrator
October 7, 2007: SAP AG and Business Objects S.A. announced that the companies reached an agreement that has brought together two of the information technology industry leaders.
October 15-28, 2007: Breakout speaker at Business Objects Insight Americas 2007, Orlando, Florida – presented Secure Universes Using Restriction Sets
December 7, 2007: Business Objects XI R2 Migration Specialist

And in 2008, the adventure continues…
January 13-15, 2008: Business Objects Partner Summit 2008, Las Vegas, Nevada

Business Objects Insight 2007 – Part 2

Learn how to secure classic UNV universes

My Insight 2007 presentation, Secure Universes Using Restriction Sets, is now available for your viewing pleasure (see related article, Business Objects Insight 2007 – Part 1). Restriction sets are most commonly used to create row and column restrictions to secure data for an individual user or group. However, there are some other cool things that you can use restriction sets for, and they are all covered in the presentation. There’s also a supplemental document that provides step-by-step instructions to create my demos. Sample universes and reports, derived from the eFashion universe, are available in a BIAR file.

A quick Google search revealed that my presentation has also been uploaded to an Israeli web site. I can’t read Hebrew and couldn’t find a web site to translate. But if you can, I’d love to find out who found my presentation useful.

Top Ten Mistakes to Avoid in Universe Design – INTRO

Avoiding the most popular mistakes in universe design.

Editor’s Note: This article ultimately became the inspiration for 2009 presentation, Universe Design: Evolution, Intelligent Design, Or Just A Big Mess?

I began my career in Business Objects with very little training. My first assignment was helping a new Business Objects customer replace MS Access reports from their SQL Server sales data mart with Business Objects 5.1. Fortunately, I was working under a very good mentor with many years of Business Objects experience.

Nine months later, I finally got to take Business Objects’ Universe Design class, as my employer was a Business Objects Authorized Education Partner. Attending the class made me appreciate how much more productive I could have been by having the training from the outset.

My initial experiences came flashing back to me at my current project, a migration from Business Objects 6.5 to XI Release 2. As often happens, the staff performing the migration is not the staff that created the universes and reports. The actual migration project can be challenging by itself, but the quality of the work being migrated can introduce additional complexity to the project. The client is a manufacturing firm that adheres to the “Lean Sigma” methodology. I’m still learning about what it means, but apparently part of being lean is not sending staff to training.

Over the next few weeks, I’ll be writing about the top ten mistakes to avoid in universe design. The knowledge to avoid these mistakes is covered in DM310R2 Universe Design, part of Business Objects’ official training curriculum. However, I’ll address these topics from a “what not to do” standpoint rather than the “what to do” standpoint taken by the class.

Stay tuned…