30
Contents
12
Choosing Join Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205
Creating Dynamic Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206
Existing Relationships in the Choose Relationship Dialog Box . . .208
Deleting Dynamic Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209
Outer Joins in Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .210
Join Order in Merged Data Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211
Specifying Join Order . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212
Join Conflicts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213
Outer Join Syntax in Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215
Changing Outer Join Syntax for Connections . . . . . . . . . . . . . . . . . . . . . . . 215
Modifying the SQL for Queries . . . . . . . . . . . . . . . . . . . . . . . . . . .216
Modifying SQL in the Query Window . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216
Including SQL Prefixes and Suffixes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217
Building Circular Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .218
Circular Queries with a Correlation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219
Building Circular Queries with a Correlation . . . . . . . . . . . . . . . . . . . . . . . . 221
Circular Queries Without a Correlation . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222
Building Circular Queries Without a Correlation . . . . . . . . . . . . . . . . . . . . . 223
Chapter 13 Exporting Information
225
Exporting Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .225
Preparing Results for Export . . . . . . . . . . . . . . . . . . . . . . . . . . . . .226
Results Options and Separators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 227
Specifying Results Options and Separators . . . . . . . . . . . . . . . . . . . . . . . . 229
Export Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230
Setting Export Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231
Export Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .232
Exporting Results to Text Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 232
29
Contents
13
Exporting Results to Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233
Exporting Results Using E-Mail . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235
Exporting Results to Microsoft Excel . . . . . . . . . . . . . . . . . . . . . . .236
Specifying DDE Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236
DDE Command Examples for Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
Combining Multiple Results Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238
Chapter 14 Publishing and Scheduling with BI Server
241
Publishing with BI Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .241
Requirements for Publishing Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241
Publishing Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
Retrieving Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243
Deleting Published Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
Setting Security for Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
Publishing Standard Report Specifications . . . . . . . . . . . . . . . . . . 245
Setting Security for Standard Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246
Standard Reports in BI Web . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246
Publishing Data Values Results Files . . . . . . . . . . . . . . . . . . . . . . 247
Retrieving Published Data Values Results Files . . . . . . . . . . . . . . . . . . . . . 247
Deleting Published Data Values Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248
Scheduling with BI Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .248
Scheduling Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249
Retrieving Scheduled Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249
Chapter 15 Accessibility and Technical Support
251
General Accessibility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .251
Microsoft Accessibility Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253
6
Contents
14
Technical Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .253
Using the Trace Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254
Index
255
18
1
15
Ch a p t e r
Basic Concepts
This section provides information on the following:
• “Introducing BI Query” on page15
• “Basic Concepts” on page16
Introducing BI Query
BI Query is a query and reporting tool that provides a comprehensive solution for
accessing, analyzing, and presenting data stored in enterprise databases. BI Query
lets you extract the information you need using a data model—a graphical
representation of the database. By using the data model you can form queries
without needing to know SQL (Structured Query Language—the language used
for retrieving data from most databases).
For Administrators
BI Query provides the flexibility to tailor information access to the exact needs of
business users. The administrator makes business-critical information available
while maintaining data security, quality, and integrity.
26
Chapter 1: Basic Concepts
16
For Business Users
BI Query provides an easy-to-use, visual way to query databases, integrate data
with other applications, and generate reports.
BI Query Applications
The BI Query product line consists of three applications—Admin, User, and
Update:
BI Query Admin
Lets the BI Query administrator manage the use of the program by users. The BI
Query administrator can design data models, set permissions for users, set
passwords, and control access to the database and the functionality of BI Query.
With permission from the DBMS administrator, the BI Query administrator can
also update the tables in the database.
BI Query User
Lets users run queries provided by others and, depending on their user
permissions, create ad hoc queries of their own.
BI Query Update
Provides users with the same functionality as BI Query User with the additional
ability—depending on their DBMS permissions—to update tables in the database.
Basic Concepts
The following sections describe the basic concepts behind BI Query’s graphical
approach to extracting information from corporate databases.
Database Components
A database is a collection of related information. The basic components of a
database are as follows:
28
Basic Concepts
17
Tables
In a relational database, information is held in tables. A table usually relates to
something in the real world. For example, a database might store customer names
and addresses in one table, products in another, stock levels in another, purchase
orders in another, and so on.
Columns
Tables are made up of columns and rows. Each column represents an attribute of
the entity that the table represents. For example, customers have names, addresses,
fax numbers, and so on. A table for customer information would have a column
for each of these attributes. (Columns are also known as attributes.)
Rows
Each row in a table is an instance of the entity—that is, each row in the customer
table gives us all the information about a customer—specific name, specific
address, and so on. (Rows are also known as records.)
DBMSs
The collection of programs that manage a database constitute a database
management system (DBMS). A DBMS lets users examine and manipulate data in
“real world” terms—customers, orders, products—without needing to know how
the computer actually stores the information.
Data Model Components
A data model is a graphical representation of the data in a database.
Data object
Relationship
A data model includes data objects
and the relationships between
them.
36
Chapter 1: Basic Concepts
18
When you use BI Query to get information from a database, you work with a data
model. Depending on the BI Query application you are using and the permissions
assigned to you, you may be able to customize data models to suit your needs.
Administrators, who use BI Query Admin to design corporate data models, set
permissions and preferences for each data model. Users of a model can change the
preference settings, but not the permissions.
Data models consist of the following components:
Data Objects
Data objects are rectangular or graphical icons that represent the tables stored in
the database. Each data object contains one or more attributes.
Attributes
An attribute represents a column of data in a database table. When you double-
click a data object in a data model, an attribute window opens, listing the
attributes stored in the data object.
Relationships
A relationship connects two data objects together and indicates that the connected
objects contain at least one attribute in common. For example, an Employee data
object might be related to a Department data object on the basis of a common
Manager attribute. In order to include the attributes from two or more data
objects in a query, the data objects must be related.
Data objects can have more than one relationship, so that you can get different
information using the same objects. BI Query represents relationships as
connecting lines between objects. Relationships can also appear with a diamond
icon and a name.
You could use the
works in
relationship to find
information about an employee who works in a particular
department.
You could use the
managed by
relationship to find
information about an employee who manages a particular
department.
24
Basic Concepts
19
Design Windows
Design windows are the workspace in which administrators design data models
and users formulate queries. Design windows contain the data objects that
represent tables in the database and the relationships that tie them together. Design
windows can also contain buttons (for navigating between Design windows and
automating tasks) and ornaments (such as graphics and text) that can provide
information and improve the usability of the model.
Buttons
Buttons automate frequent activities. They let users connect automatically to the
database, run multiple queries, combine the results, and generate a report—all
with a single click of the mouse. Buttons can also display Design windows, save
results to files, export results to other applications, open associated document files,
and launch other applications such as Visual Basic and Excel.
Ornaments
Ornaments are text and graphical objects such as titles, logos, borders,
backgrounds, and notes that provide additional information, act as visual
organizers, and improve the appearance of Design windows.
Design windows
Ornaments
Buttons
Data objects
Relationships
31
Chapter 1: Basic Concepts
20
Queries
A query is a request for information from a database. In order to retrieve
information using the data objects and relationships in a data model, you create a
query and run it (submit it to the database). The results returned by the database
for a given query are known as a results set.
The first step in creating a query is to select attributes from at least one data object.
You may also want to qualify one or more attributes to restrict the results to the
particular information that interests you—such as the sales information for a
particular store.
The designer of a data model typically creates queries and saves them with the data
model; users of the model can then open the queries from the data model and run
them. The designer can also let users edit existing queries and create their own
(called ad hoc queries).
Reports
Once you have gathered the data you want by querying, you may need to present it
in a report. BI Query has two report generators, BI Query Reports and BI Query
Standard Reports, that let you produce your own professional-looking reports.
BI Query Reports provides a flexible reporting environment that includes tables,
crosstabs, charts, and maps. You can open BI Query Reports from within BI Query
or directly from the BI Query program group (under Hummingbird) in the Windows
Start menu. BI Query Reports has its own online Help system, which you can
access from its Help menu.
For more information on BI Query Reports, see the BI Query Reports
User’s Guide.
BI Query Standard Reports is an integrated component of BI Query. For more
information on Standard Reports, see “Standard Reports in BI Query” on
page 155.
Types of Data Models
BI Query provides two types of data models: split and combined.
Documents you may be interested
Documents you may be interested
