=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= SEARCHDATABASE.COM | Database Administrator Tip September 5, 2001 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Welcome to the searchDatabase.com Database Administrator Tips newsletter! Today's tip, "Accessing external databases with Excel" can also be viewed online at: http://www.searchDatabase.com/tip/1,289483,sid13_gci765279,00.html Help your fellow database geeks and perhaps win a Toshiba DVD player! Submit your own technical tip here: http://searchdatabase.techtarget.com/tipsPrize/0,289492,sid13_prz764692_cts764388,00.html ******** Sponsored by Lumigent *********** "Eat Your Spinach!" Mom taught you many good habits. If she was a DBA she would have given you a few more tips, like "use Log Explorer to find and fix user errors" and "don't recover from a backup, use Log Explorer to recover column, row or table data selectively and in real time." Get your FREE TRIAL--> http://ad.doubleclick.net/clk;3265854;5058249;r?http://www.lumigent.com/go/searchdb8 ************************************************* "Accessing external databases with Excel" By Barrie Sosinsky It is often useful to access external database files when working with Excel. One of the advantages is that you can import the data into Excel and then manipulate it and format it by using tools that are most likely already familiar. Excel is capable of reading some database files directly, particularly those with a DBF extension. A small file can be downloaded into Excel in its entirety. However, in most cases, you do not need the entire file but are interested only in certain fields. You may want to import certain data and exclude other fields not necessary to your project. In that event you need to query the database and load into your worksheet only a subset of the external database. On a Windows client, the first thing you must do is to install Microsoft Query. You will be prompted to install it when you select the the Create New Query command fron the Get External Data submenu on the Data menu. Rerun the Excel or Microsoft Office setup program and install Query. To work with an external database file once Microsoft Query is installed, use the Create New Query command to launch the Query wizard and indicate the database you want to query. Specify the fields or records that you want, and indicate how you want the data returned: to a worksheet or as a pivot table. If you wish, save the query in a file for use later. You can refresh the file at some point in the future and it will update the values if there have been any changes since it was last used. Run Excel simultaneously with Query and switch back and forth between the programs. As Query initiates, it will display the Choose Data Source dialog box, which contains three tabs. The first tab to the left is Databases, and it lists the data sources that are known to Query. It may be empty unless data sources are defined on your particular system. If you frequently work with external databases, these should be listed here. The second tab is named Queries and contains a list of stored queries that can be reused. This also may or may not be empty. The third tab is named OLAP cubes and is used to store a list of cubes that you want to access more than once. Online analytical processing or OLAP is a way to organize large databases to accommodate the way that you prefer to analyze and manage information. The data in an OLAP database is organized by the level of detail in a hierarchical structure. For example, if you want to track a certain business product, you can track it by where, when, and how by accessing the OLAP dimensions. A location dimension might contain fields for country, region, city, etc., while a when dimension might include the month, date, day, and year fields. Dimensions in an OLAP database can be combined to provide information about intersecting points. Because you can combine several dimensions as you prefer, OLAP databases are called cubes. Excel can connect to an OLAP source by using either the Microsoft DSS Analysis server or other third-party OLAP product that provides data source drivers, compatible with OLD-DB for OLAP. You connect to an OLAP cube the same way you connect to other external data sources. Excel displays data that you retrieve from an OLAP cube as a PivotTable or PivotChart. ABOUT THE AUTHOR: Barrie Sosinsky ([EMAIL PROTECTED]) is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation. FOR MORE INFORMATION: The Best OLAP Web Links: tips, tutorials, and more http://searchdatabase.techtarget.com/bestWebLinks/0,289521,sid13_tax281620,00.html Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums http://searchdatabase.techtarget.com/forums/0,289802,sid13_fid1,00.html Submit your own tip and win a Toshiba DVD player http://searchdatabase.techtarget.com/tipsPrize/0,289492,sid13_prz520733_cts520732,00.html ================================================ SPONSORED BY: **Storage Decisions 2001** ================================================ If you're going to spend $5+ million on storage hardware and software next year, don't miss a must-attend FREE conference developed just for you. Attend Storage Decisions 2001 in Chicago September 26-28. Learn to set strategy/make the smartest decisions to most effectively manage your storage initiatives for 2002. Pre-qualified audience of your peers and NO vendor sales pitches. Visit: http://ad.doubleclick.net/clk;3230220;5058249;w?http://www.StorageDecisions2001.com ======================================= MORE GREAT STUFF ON SEARCHDATABASE! ======================================= This week's Featured Topic: Data warehousing success! In honor of Bill Inmon's "Live Expert Q&A" tommorrow, we present a collection of advice about succesfully implementing a data warehouse: critical guidelines to follow and common pitfalls to avoid. Click the link below for more... http://searchdatabase.techtarget.com/featuredTopic/0,290042,sid13_gci764575,00.html Mark your calendars: BILL INMON, the father of data warehousing, will be presenting "Ten critical data warehousing success factors" live on searchDatabase.com Thursday, September 6 at 1 pm Eastern (18:00 GMT). He will also be available to take your questions. Don't miss this opportunity to chat with the premier data warehousing guru in the world! http://searchdatabase.techtarget.com/onlineEvents/0,289675,sid13,00.html WIN A TOSHIBA DVD PLAYER: Share your knowledge, help your peers, and compete to win a $200 DVD player. Submit your technical tip today: http://searchdatabase.techtarget.com/tipsPrize/0,289492,sid13_prz764692_cts764388,00.html Hot topic in the discussion forums: Sonix is wondering how to best store Blobs in Oracle so that they can be retrieved and saved quickly. Can you help? http://searchdatabase.techtarget.com/forums/0,289802,sid13_fid1,00.html Over 200 DBA, developer, Oracle, DB2, and SQL Server tips FREE to searchDatabase members: http://searchdatabase.techtarget.com/tips/0,289484,sid13_tax281808,00.html Hundreds of hand-picked and reviewed resources about every aspect of database administration, including backup, performance tuning, database design, security, and much more: http://searchdatabase.techtarget.com/bestWebLinks/0,289521,sid13_tax281570,00.html Ask the Expert category of the day: "SQL" http://searchdatabase.techtarget.com/ateAnswers/0,289620,sid13_cid376817_tax285649,00.html SQL guru extraordinaire Rudy Limeback is waiting to tackle your toughest SQL scripting problems. ======================================= LEARNING ZONE FEATURED BOOK OF THE WEEK ======================================= "Oracle 9i SQLJ Programming" By Nirva Morisseau-Leroy Take advantage of greater programming capabilities with SQLJ--a standard language specification that embeds SQL statements in Java--in the Oracle 9i environment. Officially authorized by Oracle Corporation, this authoritative resource explains how to develop hosted applications, achieve remarkable scalability, implement real-time business intelligence features, and reach a new level of programming functionality. Get comprehensive coverage of SQLJ programming techniques, including SQL programming and development, SQLJ stored programs, triggers, and object-relational processing. Oracle9i SQLJ Programming will help you control each piece of the development process. Discover the benefits of Oracle's highly developed SQL-embedded Java programming standard. http://www.digitalguru.com/product_detail.asp?catalog_name=Books&category_name=&product_id=0072190930&partner_id=77 ================================================ If you would like to sponsor this or any TechTarget newsletter, please contact Gabrielle DeRussy at [EMAIL PROTECTED] ================================================ If you no longer wish to receive this newsletter simply reply to this message with "REMOVE" in the subject line. Or, visit http://searchDatabase.techtarget.com/register and adjust your subscriptions accordingly. If you choose to unsubscribe using our automated processing, you must send the "REMOVE" request from the email account to which this newsletter was delivered. Please allow 24 hours for your "REMOVE" request to be processed.
