SQL Server Reference Guide

Mar 28, 2003

␡

⎙ Print

< Back Page 324 of 365 Next >

One of the most sensitive areas that you will have to tackle in creating your Business Intelligence landscape is extracting data from source systems. The main difficulties you'll have in this process are with the security aspects and politics. Not to fear though; in this tutorial I'll explain how to begin to put everyone's mind at ease and show you how to create a comprehensive plan for getting data from their system to yours. I'll focus first on creating the plan and the meta-data, and in the next couple of articles I'll explain the mechanics of that process.

You might be fortunate enough to own all the source systems in question, in which case the primary danger is that you won't be thorough enough when you design your extracts. If you do have control of all the source systems, pretend that you don't and treat each system as I'll explain here. You'll have a safer, more supportable infrastructure that way, and if you ever include a foreign system in your schema you'll have all the documentation set up, ready to go.

I'll show you the documentation you need to create, but we should take a moment and discuss the distribution methods for that documentation. In several cases you'll need to have various people sign-off that they agree with the plan, to make sure they know about it and just for a little protection for yourself. Trust me, corporate memories can be fraught with amnesia from time to time, and you don't want to be the only one that remembers that someone agreed to your scheme.

I personally like portals for distributing the plan. It's a central location, securable, and you can even architect it so that it provides a web service or e-mail capabilities. It's ultimately portable as well, and works across all platforms. I've found it to be the best place to store all the documentation for my Business Intelligence landscape.

You can also use a plain old drive share as well. This can also be secured, but it's a little more closed from the distribution standpoint. There's no easy mechanism to publish the fact that changes have been made to a document.

You can even use plain old paper documents in a binder. This is my least favorite choice of all, since you're killing trees, it's a single-use format, and can be physically destroyed or lost. Not to mention that's SO twentieth-century! But if it's paper or nothing, do the paper.

Plan for the fact that these documents will be living, that is, changing quite frequently. That's not only OK, it's to be expected. In a Business Intelligence landscape, several source systems will evolve as time goes by. New features will be added, upgrades will occur, and you'll even add new sources to the mix. Your document storage and distribution system (portal, if you will) that you'll use for your Business Intelligence landscape should account for that.

Up to now we've only been discussing the planning stages. When you begin to extract data from source systems the final planning documents need to be made public. This is called the meta-data of the system, the data about the data. This meta-data includes where the data comes from, what it means, how it is changed, and how it is moved. Finally, the meta-data explains where that data ends up.

You can create the meta-data for the system automatically, or you can purchase third-party tools and products. Even if it is created for you, you should consider putting it out on a portal for all to see. The reason for this is that most systems (Extract, Transform and Load programs or entire BI suites) don't include all the documentation I'll describe below. Even if they do, they usually aren't linked so that you can search them from several angles.

If you're designing your own BI tools using only database and OLAP engines, then you'll definitely want to spend a great deal of time creating a system that will manage the meta-data for you. I'll return to this theme throughout this series.

The first item in your meta-data schema is the source data locations and descriptions. What you need here is the name of the location of the source data, with all pertinent information such as data type (character, numeric, date, etc.), the name and type of the source system table, how you extract the data (flat file, direct-connect, B2B, etc.) and the name of the contact for that system, with the administrator. The first contact is the business owner of the system, and the second contact is the name, e-mail, IM address and phone number of the technical representative. You'll need all of these at one time or another.

The next part of the source meta-data information is the data dictionary. This term goes all the way back to the age of the dinosaurs (mainframes) when you actually had time to document what you were doing. A data dictionary is simply a grid that shows a field's name, its structure, its transformations, its meaning and its location. That is exactly what you'll need to base your entire search schema on in your meta-data product. Most of the users I've dealt with want to point to a screen in their source application and say to you "where is that field located in your BI system, and what did you do to it?"

You'll want to develop a chronology document showing when each extract happens and how long it takes from the source, to the staging area (if applicable), and how long it takes to make it into your system. Understand that the entire length of the time it takes before a purchase order is updated into the BI system. That's because you may need to wait on all the PO systems to send their data so that you can maintain data integrity. This timeline will also help you design your maintenance and down-time windows, and what kind of slack you have when an extract is late or missed. It will, by the way, do both.

Next, develop a security matrix. This shows who has rights to the data in the source system and ho vulnerable it is along the way. If you extract salaries into a flat file on a share, you should know about who has access to that share. Here in the US, there are laws about that sort of thing. This also helps you develop your security chain in your BI system.

You'll also want an ownership chain on the data. This helps everyone understand who's got the data at which stage.

No matter how you intend to transfer the data from the source system to your BI system, you are still transferring it across the network. Make sure you include your network staff so that you can estimate the amount of traffic you're about to put on their network. They'll be surprised that you consulted them, and you'll be glad you did. Many designers forget this step and find it out too late when their China link bogs down under the traffic to the US.

Finally, create a support matrix. This is an "on-call" list that shows who gets contacted when something doesn't work. If you're in a worldwide environment as I am, make sure you design a "follow the sun" support scheme and train the technicians in your region on your system so that they can help you during their daylight hours. Or, you can always get up at 2 in the morning to find out someone shut down the FTP server in Paris.

Publish all of this documentation where everyone can get to it and suggest changes. When you create this meta-data system, make sure you make it searchable. Every part – from security to field types – should be linked to every other part. If you design this meta-data system properly from the outset, you'll find it helps you throughout the entire project design.

Informit Articles and Sample Chapters

Larissa T. Moss and Shaku Atre have a great book on BI that also discusses security called Business Intelligence Roadmap: The Complete Project Lifecycle for Decision-Support Applications.(Preview this book in Safari)

Online Resources

The ETL guy no longer keeps his site up to date, but it's a wealth of information on Extracts, Transforms and Loads. He's got a bunch of links from ETL products as well.

< Back Page 324 of 365 Next >

🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Privacy Notice

Overview

Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security

Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children

This site is not directed to children under the age of 13.

Marketing

Pearson may send or direct marketing communications to users, provided that

Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
Such marketing is consistent with applicable law and Pearson's legal obligations.
Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out

Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

As required by law.
With the consent of the individual (or their parent, if the individual is a minor)
In response to a subpoena, court order or legal process, to the extent permitted or required by law
To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
To investigate or address actual or suspected fraud or other illegal activities
To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links

This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020

Email Address