Transcription

Brio IntelligenceAdministrator’s GuideVersion 6.64980 Great America ParkwaySanta Clara, CA 95054 USA 1(408)496-7400

Brio Intelligence Administrator’s Guide — Version 6.6Part Number 1209913 Copyright 2002 Brio SoftwareAll rights reserved. Printed in the USA.This product and related products and documentation are protected by copyright andare distributed under licenses restricting their use, copying, distribution, anddecompilation. No part of this product or related documentation may be reproducedin any form by any means without prior written permission of Brio Software and itslicensors.Brio Software4980 Great America ParkwaySanta Clara, CA 95054 io.comRefer to the Brio Software License Agreement in this package before installing or usingthe product.If you find any errors or problems with this documentation, please notify BrioSoftware. Brio Software does not guarantee that this document is without error. Theinformation in this document is subject to change without notice.TrademarksBrio , Brio Inform , Brio Intelligence , Brio Performance Applications , BrioPerformance Builder , Brio Performance Suite , Brio Performance Platform , BrioPortal , Brio Reports , Brio Software , Personal SQR , and SQR are trademarksor registered trademarks of Brio Software in the United States and other countries. Allother marks are the trademarks or servicemarks of Brio’s suppliers or partners and arethe property of such third parties.s.

Contents in BriefAbout This BookCHAPTER 1 Understanding ConnectivityCHAPTER 2 Data ModelingCHAPTER 3 Using Metatopics and MetadataCHAPTER 4 Managing the Brio Intelligence RepositoryCHAPTER 5 Auditing Brio Intelligence ActivitiesCHAPTER 6 Scheduling DocumentsCHAPTER A IBM Information CatalogCHAPTER B Brio Intelligence TroubleshootingGlossaryIndex

ivContents in Brief

ContentsAbout This BookAudience . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-ixIn This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-ixTypographic Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-xRelated Documents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-xiiHelp . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-xiiCHAPTER 1Understanding ConnectivityAbout Connection Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-2Working with OCEs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-4Creating OCEs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-4Setting Connection Preferences . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-5Filtering Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-5Modifying Server Date Formats . . . . . . . . . . . . . . . . . . . . . 1-7Creating an OLAP Connection File . . . . . . . . . . . . . . . . . . . . . . . . 1-8Connecting to Essbase or DB2 OLAP . . . . . . . . . . . . . . . . 1-9Connecting to an OLE DB Provider . . . . . . . . . . . . . . . . . 1-9Connecting to MetaCube . . . . . . . . . . . . . . . . . . . . . . . . . . 1-9Modifying OCEs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-11Connecting to Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-12Monitoring Connections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-12Connecting with a Data Model . . . . . . . . . . . . . . . . . . . . . . . . . . 1-13Connecting without a Data Model . . . . . . . . . . . . . . . . . . . . . . . 1-13Setting a Default OCE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-15Automatically Logging On . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-16Using the Connections Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-17Logging On to a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-17Logging Off of a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-18Modifying an OCE Using the Connections Manager . . . . . . . . 1-18Changing Your Database Password . . . . . . . . . . . . . . . . . . . . . . . 1-19

CHAPTER 2viContentsData ModelingAbout Data Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-2Building a Data Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-2Adding Topics to a Data Model . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-2Removing Topics from a Data Model . . . . . . . . . . . . . . . . . . . . . . 2-3Understanding Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-4Automatically Joining Topics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-5Specifying an Automatic Join Strategy . . . . . . . . . . . . . . . . . . . . . . 2-6Manually Joining Topics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-7Showing Icon Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-7Specifying Join Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-8Removing Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-9Using Defined Join Paths . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-9Using Local Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-11Creating Local Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-12Using Local Joins as Limits . . . . . . . . . . . . . . . . . . . . . . . . 2-13Limitations of Local Results and Local Joins . . . . . . . . . . 2-15Working with Topics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-17Changing Topic Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-17Modifying Topic Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-19Modifying Topic Item Properties . . . . . . . . . . . . . . . . . . . . . . . . . 2-20Restricting Topic Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-21Working with Data Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-22Changing Data Model Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-22Setting Data Model Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-23Saving Data Model Options as User Preferences . . . . . . . 2-24Data Model Options: General . . . . . . . . . . . . . . . . . . . . . . 2-26Data Model Options: Limits . . . . . . . . . . . . . . . . . . . . . . . 2-28Data Model Options: Joins . . . . . . . . . . . . . . . . . . . . . . . . 2-30Data Model Options: Topic Priority . . . . . . . . . . . . . . . . . 2-31Data Model Options: Auditing . . . . . . . . . . . . . . . . . . . . . 2-32Automatically Processing Queries . . . . . . . . . . . . . . . . . . . . . . . . 2-33Promoting a Query to a Master Data Model . . . . . . . . . . . . . . . . 2-34Synchronizing a Data Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-35Data Model Menu Command Reference . . . . . . . . . . . . . . . . . . . . . . 2-36

CHAPTER 3Using Metatopics and MetadataAbout Metatopics and Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-2Data Modeling with Metatopics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-3Creating Metatopics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-3Copying Topic Items to a Metatopic . . . . . . . . . . . . . . . . . . . . . . . 3-4Creating Computed Metatopic Items . . . . . . . . . . . . . . . . . . . . . . 3-5Customizing or Removing Metatopics and Metatopic Items . . . 3-6Viewing Metatopics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-6MetaData in Brio Intelligence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-7Using the Open Metadata Interpreter . . . . . . . . . . . . . . . . . . . . . . . . . 3-8Accessing the Open Metadata Interpreter . . . . . . . . . . . . . . . . . . . 3-8Configuring the Open Metadata Interpreter . . . . . . . . . . . . . . . 3-10Metadata Definition: SQL Entry Fields . . . . . . . . . . . . . . 3-11Metadata Definition: Tables . . . . . . . . . . . . . . . . . . . . . . . 3-12Metadata Definition: Columns . . . . . . . . . . . . . . . . . . . . . 3-14Metadata Definition: Joins . . . . . . . . . . . . . . . . . . . . . . . . 3-16Metadata Definition: Lookup . . . . . . . . . . . . . . . . . . . . . . 3-18Metadata Definition: Remarks . . . . . . . . . . . . . . . . . . . . . 3-20CHAPTER 4Managing the Brio Intelligence RepositoryAbout the Brio Intelligence Repository . . . . . . . . . . . . . . . . . . . . . . . . 4-2Administering a Brio Intelligence Repository . . . . . . . . . . . . . . . . . . . 4-3Creating Repository Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-3Confirming Repository Table Creation . . . . . . . . . . . . . . . . . . . . . 4-5Updating Repository Object Descriptions . . . . . . . . . . . . . . . . . . 4-6Deleting Repository Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-7Administering Repository Groups . . . . . . . . . . . . . . . . . . . . . . . . . 4-8Working with Repository Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-10Uploading Documents to the Repository . . . . . . . . . . . . . . . . . . 4-10Modifying Repository Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-12Controlling Document Versions . . . . . . . . . . . . . . . . . . . . . . . . . 4-15CHAPTER 5Auditing Brio Intelligence ActivitiesAbout Auditing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Creating an Audit Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Defining Audit Events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Auditing Keyword Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Sample Audit Events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5-25-35-45-65-7Contentsvii

CHAPTER 6Scheduling DocumentsAbout Brio Broadcast Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-2Broadcast Server Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-2Documents, Jobs, and Scheduling . . . . . . . . . . . . . . . . . . . . . . . . . 6-2Connecting to Job Repositories . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-3Working with Jobs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-5Scheduling Open Documents as Jobs . . . . . . . . . . . . . . . . . . . . . . 6-5Viewing the Broadcast Server Job List . . . . . . . . . . . . . . . . . . . . . . 6-6Adding Jobs Using the BroadCast Server Job List . . . . . . . 6-7Modifying Jobs in the Broadcast Server Job List . . . . . . . . 6-8Deleting Jobs from the Broadcast Server Job List . . . . . . . 6-8Processing Scheduled Jobs to Database Tables . . . . . . . . . . . . . . . 6-8Specifying Job Details . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-10Entering General Information . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-10Including Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-12Defining Actions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-13Run Cycles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-14Variable Limits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-16Job Actions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-18Specifying Job Timing and Frequency . . . . . . . . . . . . . . . . . . . . . 6-22Using Multiple Job Connections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-24CHAPTER AIBM Information CatalogAbout the IBM Information Catalog . . . . . . . . . . . . . . . . . . . . . . . . .Registering Documents to the IBM Information Catalog . . . . . . . .Defining Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Selecting Subject Areas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Administering the IBM Information Catalog . . . . . . . . . . . . . . . . . .Creating Object Type Properties . . . . . . . . . . . . . . . . . . . . . . . . .Deleting Object Types and Properties . . . . . . . . . . . . . . . . . . . . .Administering Documents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Setting Up Object Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .A-2A-2A-4A-4A-5A-5A-6A-7A-8Brio Intelligence TroubleshootingConnectivity Troubleshooting with dbgprint . . . . . . . . . . . . . . . . . .dbgprint and Brio Intelligence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .dbgprint and Broadcast Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .dbgprint and OnDemand Server . . . . . . . . . . . . . . . . . . . . . . . . . . . .dbgprint and Insight/Quickview . . . . . . . . . . . . . . . . . . . . . . . . . . . .B-2B-2B-4B-5B-6CHAPTER BviiiContents

GlossaryIndexContents-ix

-xContents

About This BookWelcome to the Brio Intelligence Administrator’s Guide. This book is designedto help you administer the Brio Intelligence application, part of the suite ofpowerful and easy-to-use business intelligence tools for query, OLAP analysis,and analytical reporting across the extended enterprise.AudienceThe Brio Intelligence Administrator’s Guide is written for Explorer and Designerusers who have access to more advanced features and who may haveresponsibility for implementing the Brio Intelligence application in anenterprise environment.In This BookBrio Intelligence Administrator’s Guide Version 6.6, one of four books thatexplain how to use Brio Intelligence (see “Related Documents” on page xii),describes administrative features such as how to build and customize datamodels and how to manage the Brio Intelligence repository. It also discussesmetadata definitions, database connectivity, and document scheduling. Chapter 1, “Understanding Connectivity,” describes how to connect torelational and multidimensional databases using connection files, includinghow to set up connection files and connection preferences, and how tomanage connections. Chapter 2, “Data Modeling,” provides detailed information about datamodels, including join types and strategies, synchronization, metatopiccreation and views, and data model properties and options.

Chapter 3, “Using Metatopics and Metadata,” explains how to use metadatadefinitions to link Brio Intelligence to information about your database. Chapter 4, “Managing the Brio Intelligence Repository,” describes how tocreate and manage a Brio Intelligence repository, including how to uploaddocuments to and open documents from a repository. Chapter 5, “Auditing Brio Intelligence Activities,” explains how to useauditing features to collect information about data models downloadedfrom the Brio Intelligence repository, including query processing time andmost often used tables and columns. Chapter 6, “Scheduling Documents,” describes the Brio Broadcast Serverand explains how to schedule and distribute Brio Intelligence documentsusing the Broadcast Server. Appendix A, “IBM Information Catalog,” provides instructions forregistering and managing Brio objects in the IBM Visual WarehouseInformation Catalog. Appendix B, “Brio Intelligence Troubleshooting,” describes how to use thedbgprint tool to diagnose Brio Intelligence connectivity problems.In addition, a glossary and index provide definitions and easy access toinformation contained in the book.Typographic ConventionsThis book uses the following type conventions: Options, buttons, or tabs that you need to choose and text that you need totype are indicated in bold.Select Typical Install. Type 1234. Key names are shown in square brackets.Press [Down Arrow] Two key names joined with a plus sign ( ) are consecutive keystrokes. Pressand hold down the first key while pressing the second key.Press [Ctrl Z].xAbout This Book

Options in a menu command path are separated with an arrow. Theexample indicates that you are to open the File menu and choose the Openmenu item.Choose File Open. Note[Ctrl O]When an instruction includes a menu command, the toolbar icon (if one exists) for thecommand appears in the left margin. The keyboard shortcut (if one exists) for the command islisted in brackets at the end of the line. Variables you replace with specific information are shown in italics.sp adduser login id Files, directories, and paths are shown in a monospace font.Sample1.bqy is located in the BrioQuery/Samples directory. TipCaution !A Note, Tip, or Caution is a brief side-note that deserves special attention ordoes not fit within the normal flow of text. These types of information areset off in the text by an icon in the margin.This is an example tip.This is an example caution.Typographic Conventionsxi

Related DocumentsAlong with the Brio Intelligence Administrator’s Guide, there are threeadditional Brio Intelligence books: Getting Started with Brio Intelligence 6.6 – Query and Results provides anoverview of Brio Intelligence and explains the user interface and basiccommands. It includes how to retrieve data, how to query new data andchange existing queries, and how to query a single database as well asmultiple databases. It also covers how to work with query results. Data Analysis and Reporting with Brio Intelligence 6.6 describes how to usethe Brio Intelligence application’s powerful reporting features—pivots,charts, and tables—and the Report Designer to create spectacular reports. Brio Intelligence Object Model and Executive Information Systems explainsthe Brio Intelligence Object Model and how to create custom EISapplications using JavaScript.You may also find the information contained in the Brio Intelligence ServerGuide useful for understanding how the Brio Intelligence application interactswith the Brio Broadcast Server and the Brio OnDemand Server.HelpBrio Intelligence comes with a number of user manuals as well as an extensiveonline help system. If you need help with Brio Intelligence and cannot find theanswers you need in the documentation, and you have a current Brio TechnicalSupport agreement, call Brio Technical Support at 1(800)337-6324 (withinNorth America) or 1(619)610-5769. You may also send an email message [email protected] be prepared to provide your valid customer number and companyname. You also need to know the version of Brio Intelligence you are using.xiiAbout This Book

1Understanding ConnectivityThis chapter describes how to connect to relational and multidimensionaldatabases using connection files, including how to set up connection files andconnection preferences, and how to manage connections. It contains: About Connection Files Working with OCEs Connecting to Databases Using the Connections Manager1-1

About Connection FilesConnectivity is generally one of the most difficult aspects of querying for endusers to master. Client/server database applications rely on a complicated webof listeners, network addresses, and preferences that are difficult for anyone buta database administrator to troubleshoot.Fortunately, Brio Intelligence users can sidestep these potential difficulties byusing a connection file or Open Catalog Extension (OCE). Brio Intelligenceuses OCEs to manage database connections and connect to data sources.With an administrator’s assistance, OCEs enable a stable connection to be setup once and then distributed and reused. End users need only supply adatabase user name and