WIXLIB

BQ-GetStarted.book Page vii Monday, March 11, 2002 11:20 AMUsing Edit Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Changing Workspace Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Inserting Sections and Breaks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Formatting Text and Other Elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Working with Document Sections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Understanding Document Sections . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Adding Sections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Viewing Sections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Moving Between Sections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Duplicating Sections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Renaming Sections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Adding Headers and Footers to Sections . . . . . . . . . . . . . . . . . . . . . . . .Deleting Sections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Setting Brio Intelligence Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Specifying Default Formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Selecting Program Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Customizing Menus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Tools Menu Command Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .CHAPTER -393-403-403-443-493-51Querying Relational DatabasesAbout the Query Section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-2Using Data Models in the Query Section . . . . . . . . . . . . . . . . . . . . . . . . . 4-3Building Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-4Working with Items on the Request Line . . . . . . . . . . . . . . . . . . . . . . . . . 4-4Processing Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-6Saving Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-7Cancelling Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-7Building Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-8Regular Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-8Correlated Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-10Working with Query Section Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-12Processing Results to a Database Table . . . . . . . . . . . . . . . . . . . . . . . . . . 4-12Estimating Query Size . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-14Displaying Database Remarks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-15Preaggregating Data Using Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-15Contentsvii

BQ-GetStarted.book Page viii Monday, March 11, 2002 11:20 AMAppending Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Using Local Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Using Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Setting Query Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Query Menu Command Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .viii4-184-204-214-234-24CHAPTER 5Working with Query ResultsAbout the Results Section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-2Understanding Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-3Enhancing Your Results Set . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-4Limiting Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-4Sorting Results Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-5Adding Computed Items to Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-5Applying Data Functions to Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-5Adding Grouping Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-8Automatically Adding Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-10Breaking Out Dates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-10Working with the Results Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-11Selecting Columns and Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-11Deleting Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-11Formatting Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-11Saving Results Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-12Exporting Result Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-14Results Menu Command Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-14CHAPTER 6Querying Multidimensional DatabasesOLAPQuery Section . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-2Defining OLAPQuery Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-4General OLAPQuery Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-4Database-specific OLAPQuery Options . . . . . . . . . . . . . . . . . . . . . . . . . 6-6Building OLAP Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-9OLAPQuery Section Outliner Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-10OLAPQuery Member and Level Rules . . . . . . . . . . . . . . . . . . . . . . . . . . 6-12Refining OLAPQuery Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-13Specifying a Slicer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-13Drilling Down . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-14Contents

BQ-GetStarted.book Page ix Monday, March 11, 2002 11:20 AMDrilling Up . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Adding Computed Items . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Using OLAPQuery Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Processing OLAP Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Automatically Processing OLAP Queries . . . . . . . . . . . . . . . . . . . . . . . .Working with an OLAPQuery Offline . . . . . . . . . . . . . . . . . . . . . . . . . .Automatically Creating a Results Section . . . . . . . . . . . . . . . . . . . . . . . .Applying Filters and Limits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Applying a MetaCube Filter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Applying Member Selection Limits . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Applying Measure Limits (Essbase and MetaCube Only) . . . . . . . . . . .Applying Variable Limits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Changing Data Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Suppressing Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Adding Totals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Adding Data Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Showing as a Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Formatting OLAPQuery Items . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Drilling Through from a Multi-Dimensional Database to a RelationalDatabase . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Setting Drill-through Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Drilling Through . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .OLAP Menu Command Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .CHAPTER -336-336-346-366-376-376-386-386-406-41Using LimitsAbout Limits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-2Server versus Local Limit Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-2Limit Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-3Limit Dialog Box . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-5Setting Simple Limits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-7Limiting Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-7Limiting Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-8Setting Compound Limits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-9Setting Variable Limits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-10Customizing Limit Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7-12Contentsix

BQ-GetStarted.book Page x Monday, March 11, 2002 11:20 AMCHAPTER 8Working with Computed ItemsAbout Computed Items . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-2Computing New Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-2Computed Items in Brio Intelligence Sections . . . . . . . . . . . . . . . . . . . . 8-3Using Surface Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-5Computed Items and Data Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-5Adding Computed Items . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-6Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-7Reference Items . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-11Inserting Scalar Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-12Using Functions in Brio Intelligence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-14Scalar Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-14Teradata Version 3 OLAP Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-20Methods for Returning the Day of the Week . . . . . . . . . . . . . . . . . . . . 8-25Common Computed Item Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-27Scalar Function Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-31CHAPTER 9Applying SortsSorting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Simple Sorts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Sort Lines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Complex Sorting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Complex Sorting in the Query, Results, and Table Sections . . . . . . . . .Complex Sorting in Chart, Pivot, and OLAPQuery Reports . . . . . . . . .GlossaryIndexxContents9-29-29-39-49-49-6

BQ-GetStarted.book Page xi Monday, March 11, 2002 11:20 AMAbout This BookWelcome to Getting Started with Brio Intelligence 6.6. This book is designed tohelp you learn the Brio Intelligence application, part of an integrated suite ofpowerful and easy-to-use business intelligence tools for query, OLAP analysis,and analytical reporting across the extended enterprise.AudienceGetting Started with Brio Intelligence 6.6 is written for all levels of BrioIntelligence users, from those who need to simply retrieve and view data in areport format, to those who need to build queries and reports as well as analyzedata.In This BookGetting Started with Brio Intelligence 6.6 – Query and Results, one of four booksthat explain how to use Brio Intelligence (see “Related Documents” onpage -xiv), provides an overview of Brio Intelligence and explains the BrioIntelligence user interface and basic commands. It includes how to retrievedata using Brio Intelligence, how to query new data and change existingqueries, and how to query a single database as well as multiple databases. Italso covers how to work with query results. Chapter 1, “Introducing Brio Intelligence,” introduces business intelligencesoftware tools and provides an overview of how anyone can use BrioIntelligence to access and analyze database information. Chapter 2, “Getting Started with Brio Intelligence: A Tutorial,” offers atutorial on basic data analysis techniques and familiarizes you with thepowerful capabilities and features of the Brio Intelligence application.

BQ-GetStarted.book Page xii Monday, March 11, 2002 11:20 AM Chapter 3, “Brio Intelligence Basics,” provides an overview of the BrioIntelligence workspace and describes fundamental Brio Intelligence featuresand functions. Chapter 4, “Querying Relational Databases,” explains how to use BrioIntelligence to connect to and query a relational database. It provides basicinformation about data models and the Brio Intelligence Repository, as wellas how to build and process queries and subqueries. Chapter 5, “Working with Query Results,” explains how to work with theresults sets obtained from your relational database query or data import. Itincludes how to enhance your results set, as well as how to export yourresults to different file formats. Chapter 6, “Querying Multidimensional Databases,” explains how to useBrio Intelligence to connect to and query a multidimensional database. Itdetails how to build, refine, and process OLAP queries and how to applyfilters and limits. Chapter 7, “Using Limits,” explains how to use limits to refine your dataand filter away the data you do not need for more intelligent analysis. Chapter 8, “Working with Computed Items,” describes how to usecalculations to compute new data items. Such calculations are importantfor supplementing the information already stored in the database Chapter 9, “Applying Sorts,” discusses features that enable you to sort datain various Brio Intelligence sections, including sort lines, single sorts, andnested sorts.In addition, a glossary and index provide definitions and easy access toinformation contained in the book.xiiAbout This Book

BQ-GetStarted.book Page xiii Monday, March 11, 2002 11:20 AMTypographic 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]. 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 Conventionsxiii

BQ-GetStarted.book Page xiv Monday, March 11, 2002 11:20 AMRelated DocumentsAlong with the Getting Started with Brio Intelligence 6.6 book, there are threeadditional Brio Intelligence books: 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. Italso explains common report features such as printing, drill-down, andspotlighter. Brio Intelligence 6.6 Administrator’s Guide explains data modeling,including how to modify existing data models, and create new data models.It also discusses metadata definitions, database connectivity, and documentscheduling. Brio Intelligence Object Model and Executive Information Systems explainsthe Brio Intelligence Object Model and how to create custom EISapplications using JavaScript.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 tosupport@brio.com.Please be prepared to provide your valid customer number and compan name.You also need to know the version of Brio Intelligence you are using.xivAbout This Book

BQ-GetStarted.book Page 1 Monday, March 11, 2002 11:20 AM1Introducing Brio IntelligenceWelcome to Getting Started with Brio Intelligence 6.6. This book shows howanyone —both technical and nontechnical people—can use Brio Intelligenceto access and analyze database information.This chapter introduces the conceptual background of business intelligencesoftware tools and provides an overview of database concepts. It also explainsthe Brio Intelligence approach to querying databases. This chapter contains: Brio Intelligence Overview Brio Intelligence Approach to Business Intelligence How Brio Intelligence Works About Brio Intelligence Documents Turning Data into Information1-1

BQ-GetStarted.book Page 2 Monday, March 11, 2002 11:20 AMBrio Intelligence OverviewBrio Intelligence connects business users to data and gives them a complete setof tools to support business decisions including ad hoc client/server querying,reporting, and analysis all in one application. Brio Intelligence provides thesecapabilities: Data extraction Data analysis Reporting and distribution Platform developmentThe Brio Intelligence client interface is highly intuitive and provides an easyto-navigate environment for data exploration and decision making. With aconsistent design paradigm for query, pivot, charting, and reporting, users atany level move fluidly through cascading dashboards—finding answers fast.Trends and anomalies are automatically highlighted, and robust formattingtools enable users to easily build free-form, presentation-quality reports forbroad-scale publishing across their organization.Brio Intelligence FeaturesBrio Intelligence features include:1-2 Support for all industry-standard databases, including AS/400, Informix,Oracle, Sybase, Microsoft SQL Server, SAP, Red Brick Warehouse,Broadbase, and Essbase. A point-and-click interface for intuitive custom query and report building. Support for Microsoft Windows, the MacOS, and Motif (UNIX), withcomplete file compatibility between the platforms. Easy, nonprocedural navigation between query and reporting sections. Client editions that maintain a consistent interface for the client andadministrative editions of Brio Intelligence, but that provide different levelsof access to Brio Intelligence features. An extensive online help system which provides assistance for features anddocument construction. A drag-and-drop Outliner tool for developing reports a

data using Brio Intelligence, how to query new data and change existing queries, and how to query a single database as well as multiple databases. It also covers how to work with query results. Chapter 1, "Introducing Brio Intelligence, " introduces business intelligence software tools and provides an overview of how anyone can use Brio