SAP Business objects Enterprise XI 3.1 Desktop Intelligence Access and Analysis Guide

Desktop Intelligence Access and Analysis Guide

BusinessObjects Enterprise XI 3.1

© 2008 Business Objects, an SAP company. All rights reserved. Business Objects owns the following U.S. patents, which may cover products that are offered and licensed by Business Objects: 5,295,243; 5,339,390; 5,555,403; 5,590,250; 5,619,632; 5,632,009; 5,857,205; 5,880,742; 5,883,635; 6,085,202; 6,108,698; 6,247,008; 6,289,352; 6,300,957; 6,377,259; 6,490,593; 6,578,027; 6,581,068; 6,628,312; 6,654,761; 6,768,986; 6,772,409; 6,831,668; 6,882,998; 6,892,189; 6,901,555; 7,089,238; 7,107,266; 7,139,766; 7,178,099; 7,181,435; 7,181,440; 7,194,465; 7,222,130; 7,299,419; 7,320,122 and 7,356,779. Business Objects and its logos, BusinessObjects, Business Objects Crystal Vision, Business Process On Demand, BusinessQuery, Cartesis, Crystal Analysis, Crystal Applications, Crystal Decisions, Crystal Enterprise, Crystal Insider, Crystal Reports, Crystal Vision, Desktop Intelligence, Inxight and its logos , LinguistX, Star Tree, Table Lens, ThingFinder, Timewall, Let There Be Light, Metify, NSite, Rapid Marts, RapidMarts, the Spectrum Design, Web Intelligence, Workmail and Xcelsius are trademarks or registered trademarks in the United States and/or other countries of Business Objects and/or affiliated companies. SAP is the trademark or registered trademark of SAP AG in Germany and in several other countries. All other names mentioned herein may be trademarks of their respective owners.

Third-party Contributors

Business Objects products in this release may contain redistributions of software licensed from third-party contributors. Some of these individual components may also be available under alternative licenses. A partial listing of third-party contributors that have requested or permitted acknowledgments, as well as required notices, can be found at: http://www.businessobjects.com/thirdparty

2008-09-03

Introduction to Desktop Intelligence 17Chapter 1

What is Desktop Intelligence?...................................................................18

Demo materials and samples....................................................................21

Upgrading from earlier versions of Desktop Intelligence...........................21

The Repository..........................................................................................21

Folders and Categories.............................................................................21

Introduction to Accessing Data with Desktop Intelligence 25Chapter 2

Where does the data come from?........................................................18

Presenting and analyzing data.............................................................19

Sharing information..............................................................................19

Security................................................................................................20

Keeping a document's data up-to-date................................................20

Folders.................................................................................................22

Categories............................................................................................22

There are 2 types of folders:................................................................22

There are two types of Categories:......................................................22

What data sources are available?.............................................................26

How do you access data sources?......................................................26

Can all Desktop Intelligence users build data providers?....................28

Who sets up database connections?...................................................29

Restrictive connections........................................................................30

Can you combine data from different sources in one report?..............31

Workflows for accessing data....................................................................31

Building a data provider when you create a new document.................31

Building a query in an existing document.............................................33

Desktop Intelligence Access and Analysis Guide 3

Contents

Editing data providers...........................................................................34

Using the repository...................................................................................36

Exporting to the repository.........................................................................37

To Export a document to the Repository..............................................37

Creating a New Folder.........................................................................37

Exporting to a Category........................................................................38

To place a file in a Category.................................................................38

To schedule export of a document.......................................................38

Managing Categories.................................................................................40

To manage your Categories.................................................................40

To Add a category to the list of categories...........................................40

To Delete a category from the list of categories...................................41

To Edit a category.................................................................................41

Importing from the repository.....................................................................41

To import a document from a folder in the repository ..........................41

Retrieving different instances of a given document.............................42

Sending documents from Desktop Intelligence.........................................42

To send documents to users and groups from Desktop Intelligence.....43

To send documents by email with Desktop Intelligence.......................43

Sending documents from Desktop Intelligence.........................................44

Building Queries on Universes 45Chapter 3

Overview....................................................................................................46

What is a universe?..............................................................................46

Who is responsible for creating universes?.........................................46

What are universe queries?.................................................................47

Demonstration materials......................................................................47

Building a basic query on a universe.........................................................49

Displaying the query panel...................................................................49

Building a query in the Query Panel and running the query.................51

Saving the definition of a query............................................................54

4 Desktop Intelligence Access and Analysis Guide

Contents

Building a more powerful query.................................................................54

Defining scope of analysis....................................................................55

Applying conditions..............................................................................57

Applying sorts.......................................................................................60

Setting options and running a query....................................................61

Running a query on a different universe....................................................62

To run a query on a different universe..................................................62

Building Queries with Other Types of Data Provider 65Chapter 4

Overview....................................................................................................66

Using free-hand SQL.................................................................................66

Creating a report using free-hand SQL................................................66

Editing a free-hand SQL script.............................................................68

Creating or editing a connection for free-hand SQL.............................68

Creating a report showing sales by store and category.......................70

Creating interactive reports using free-hand SQL................................71

Restrictions on free-hand SQL scripts.................................................75

Using stored procedures............................................................................75

What are stored procedures?...............................................................76

How do you use stored procedures in Desktop Intelligence?..............76

Restrictions on stored procedures.......................................................76

Using a stored procedure to retrieve data............................................76

Using personal data files...........................................................................78

What are the benefits of using personal data files?.............................78

Creating a report using a personal data file.........................................79

Using Visual Basic for Applications procedures........................................81

To write a VBA data provider................................................................81

To create a report using a VBA data provider......................................82

Accessing an Outlook inbox using VBA...............................................82

Using XML files..........................................................................................86

What is XML?.......................................................................................86

Desktop Intelligence Access and Analysis Guide 5

Contents

Creating a report using an XML file......................................................88

To set the location of XML files.............................................................90

Combining Data from Different Sources 91Chapter 5

Overview....................................................................................................92

Which data sources are available?............................................................92

Including data from different data sources in the same report..................92

Which data providers can you combine in one report?........................93

Using separate data providers for separate blocks in one report.........93

Displaying data from separate data providers in the same block.........95

Basing a data provider on an existing data provider.................................97

To base a data provider on an existing data provider..........................97

Prompts and linking..............................................................................97

Linking data providers................................................................................98

What situations require you to link data providers?.............................98

Deleting the link between data providers...........................................101

Managing Data Providers 103Chapter 6

Overview..................................................................................................104

Renaming data providers........................................................................104

Why rename data providers?.............................................................105

To rename data providers...................................................................106

Getting statistics on data providers.........................................................106

To get statistics on data providers......................................................106

Purging and deleting data providers........................................................107

To purge or delete a data provider.....................................................107

Using data providers efficiently................................................................107

Reports showing revenue by country and resort, revenue by country.108

6 Desktop Intelligence Access and Analysis Guide

Contents

Introduction to Data Analysis 109Chapter 7

Overview..................................................................................................110

On-report analysis...................................................................................110

Desktop Intelligence drill mode................................................................111

Slice and dice mode................................................................................111

Analyzing Data in Drill Mode 113Chapter 8

Overview..................................................................................................114

What is drill mode?.............................................................................114

How does drill mode work?................................................................114

Hierarchies.........................................................................................115

Using drill mode.......................................................................................116

To switch to drill mode........................................................................116

Drilling down.......................................................................................117

Displaying different values in the Drill toolbar....................................118

Drilling up...........................................................................................118

Undoing drill actions...........................................................................118

Drilling across.....................................................................................119

Drilling on charts......................................................................................120

To drill on charts.................................................................................120

Drilling on multiple hierarchies.................................................................120

To drill on multiple hierarchies............................................................121

To drill up on multiple hierarchies.......................................................121

Getting a different view of your data........................................................121

Changing the data in tables as you drill.............................................122

Using the Drill toolbar.........................................................................124

Analyzing measures in drill mode............................................................126

To expand a measure.........................................................................127

To collapse a measure.......................................................................127

Desktop Intelligence Access and Analysis Guide 7

Contents

Making copies of reports while you work.................................................128

To make a copy of a report.................................................................128

Extending analysis...................................................................................128

To expand the scope of analysis........................................................128

Drilling through to the database to bring in new data.........................129

Bringing in new data using filters.......................................................129

Drilling using custom hierarchies.............................................................131

Editing hierarchies..............................................................................131

Creating hierarchies...........................................................................133

Qualifying data for hierarchies.................................................................133

To requalify local variables and formulas...........................................134

To requalify variables..........................................................................134

To requalify user objects.....................................................................135

Printing from drill mode............................................................................135

To insert Drill toolbar contents as a title.............................................135

To print a report from drill mode.........................................................136

Setting options for working in drill mode..................................................136

To set options for drill mode...............................................................136

Slice and Dice Mode 137Chapter 9

Overview..................................................................................................138

Working in slice-and-dice mode...............................................................138

To display the Slice and Dice Panel:..................................................138

Working with master/detail reports in slice and dice mode................139

To undo a master/detail report............................................................141

Deactivating sections of master/detail reports...................................142

Positioning data horizontally in slice-and-dice mode.........................144

Working with crosstabs and 3-D matrix charts...................................144

Displaying and removing data in Slice-and-Dice Mode......................148

Deleting, renaming and resetting blocks in Slice and Dice mode......148

To transform blocks in Slice-and-Dice mode......................................149

8 Desktop Intelligence Access and Analysis Guide

Contents

Applying further modifications in slice and dice mode.......................149

Filtering and Ranking Data 153Chapter 10

Overview..................................................................................................154

Limiting the data displayed......................................................................154

To insert a filter...................................................................................154

Managing filters..................................................................................155

Creating more complex filters.............................................................158

Ignoring filters.....................................................................................159

Ordering data...........................................................................................160

Sorting data........................................................................................160

To sort months correctly.....................................................................162

Managing multiple sorts.....................................................................163

Using ranking to view the top and bottom values....................................164

To apply a ranking on report data.......................................................165

Managing ranking with filters and sorts..............................................168

Hiding columns and rows of data............................................................168

To hide columns and rows of data......................................................168

Highlighting data......................................................................................169

To create an alerter............................................................................169

Switching alerters off and on..............................................................171

Working with existing alerters.............................................................172

Customizing Queries on Universes 175Chapter 11

Overview..................................................................................................176

Creating user objects...............................................................................176

Why create a user object?..................................................................176

What does a user object consist of?..................................................177

What are the restrictions on user objects?.........................................177

How can an end-user share user objects with other users?..............178

Desktop Intelligence Access and Analysis Guide 9

Contents

Creating, editing and deleting user objects........................................178

To create a time hierarchy for a user object.......................................181

Applying complex conditions on queries.................................................181

To benefit from complex conditions: Which customers made reservations

for 2001 and 2002?............................................................................182

To apply a complex condition on a query...........................................183

Tips for applying complex conditions.................................................187

To edit complex conditions.................................................................190

To delete complex conditions.............................................................190

Applying a condition with a calculation...............................................190

Examining the SQL............................................................................194

Applying a condition with a subquery.................................................194

Subqueries and calculations..............................................................197

Using an existing query in a condition.....................................................198

To return list of resorts/revenues where resort country revenue >

$1000000...........................................................................................198

Applying groups of conditions..................................................................199

Organizing groups of conditions.........................................................199

AND and OR......................................................................................200

Order of precedence..........................................................................201

To apply groups of conditions.............................................................202

To delete groups of conditions............................................................203

Building combined queries......................................................................203

To build a combined query.................................................................203

Restrictions on combined queries......................................................205

Using SQL from Desktop Intelligence queries...................................207

Using and Customizing Lists of Values 209Chapter 12

Overview..................................................................................................210

What is a list of values?...........................................................................210

How are lists of values created?..............................................................210

10 Desktop Intelligence Access and Analysis Guide

Contents

Customizing lists of values in Desktop Intelligence.................................210

Editing lists of values...............................................................................211

Example: To show cities and regions in a list of cities........................211

Assigning personal data to a list of values..............................................212

To assign personal data from a text file..............................................212

To assign personal data from an Excel file.........................................213

To assign personal data from a dBase file.........................................214

To display, refresh and purge lists of values............................................214

Creating Calculations 217Chapter 13

Overview..................................................................................................218

Calculations.............................................................................................218

To add simple calculations to reports.................................................218

Count and Count All...........................................................................220

Making calculations on dimension and detail objects........................220

Calculation examples.........................................................................221

Converting to and from Euros..................................................................223

What is the euro?...............................................................................223

Displaying the euro symbol................................................................224

How does the conversion work?........................................................224

Conversion errors...............................................................................224

Displaying currency formats in Desktop Intelligence..........................225

To convert to euros.............................................................................225

To convert from euros.........................................................................226

Displaying rounding errors.................................................................226

Conversion rates................................................................................227

Triangulation.......................................................................................230

Overview 231Chapter 14

Who should read this chapter..................................................................232

Desktop Intelligence Access and Analysis Guide 11

Contents

What's in this chapter..............................................................................232

Introduction to contexts and extended syntax 233Chapter 15

Semantically-dynamic calculations..........................................................234

Understanding input and output contexts................................................234

How Desktop Intelligence defines input and output contexts.............235

Using your understanding of input and output contexts..........................237

Viewing the extended syntax of a formula..........................................239

Viewing extended syntax by using the Formula Bar..........................239

Viewing extended syntax by using the Define As Variable command.239

Using extended syntax for advanced calculations 241Chapter 16

Defining calculation contexts with extended syntax................................242

How to define input and output contexts.................................................244

Syntax for input and output contexts.......................................................244

To add an input and output context to a formula................................244

Reset contexts.........................................................................................245

How to define reset contexts..............................................................246

To define a reset context:...................................................................246

Using reset contexts in crosstabs......................................................246

Syntax for combining reset, input and output contexts......................248

Modifying contexts with the operators ForEach and ForAll.....................248

Getting the same result: ForAll City vs. In Region.............................249

Using the Rank function and extended syntax...................................250

Defining contexts with keywords........................................................253

Quick reference.......................................................................................257

Frequently used terms........................................................................257

Calculation contexts...........................................................................260

Context operators...............................................................................262

Keywords............................................................................................262

12 Desktop Intelligence Access and Analysis Guide

Contents

Calculation Troubleshooting 265Chapter 17

Overview..................................................................................................266

#COMPUTATION.....................................................................................266

#COMPUTATION in cumulative aggregations...................................266

#COMPUTATION in non-aggregate formulas....................................270

#MULTIVALUE.........................................................................................271

#MULTIVALUE in aggregations..........................................................272

#MULTIVALUE in break headers and footers....................................274

#######...................................................................................................279

#ALERTER..............................................................................................279

#DICT.ERROR.........................................................................................280

To fix this problem:.............................................................................280

To avoid #DICT.ERROR.....................................................................281

#DIV/0......................................................................................................281

To fix this problem:.............................................................................282

#ERROR..................................................................................................282

To fix this problem..............................................................................282

#IERR......................................................................................................283

#IERR in a formula combining measures and dimensions................283

#IERR in an aggregation containing a complex formula....................284

#IERR in a formula using WHERE.....................................................285

#OVERFLOW..........................................................................................285

#SYNTAX.................................................................................................285

#UNKNOWN............................................................................................286

To fix this problem..............................................................................287

Tips and tricks..........................................................................................287

Formulas, Local Variables and Functions 291Chapter 18

Overview..................................................................................................292

Desktop Intelligence Access and Analysis Guide 13

Contents

Formulas..................................................................................................292

Why use formulas?.............................................................................292

Creating formulas...............................................................................293

Displaying the Formula Bar................................................................293

Displaying the Formula Editor............................................................294

Using the Formula Editor....................................................................294

Guidelines on the syntax to use in formulas......................................296

Local variables.........................................................................................296

Why use local variables?....................................................................297

How to recognize local variables........................................................297

Creating a local variable.....................................................................297

Transforming a formula into a local variable......................................298

Creating local variables by grouping values............................................299

To display revenue per semester.......................................................300

To rename a variable..........................................................................300

Adding grouped values to a drill hierarchy.........................................302

Managing formulas and local variables...................................................302

Inserting local variables and formulas in a report...............................302

To edit formulas..................................................................................302

To edit local variables.........................................................................303

To delete formulas and local variables...............................................303

Functions.................................................................................................304

Using Functions..................................................................................305

Using the function help.......................................................................306

Function equivalents in Microsoft Excel..................................................307

Aggregate function equivalents..........................................................307

Numeric function equivalents.............................................................308

Character function equivalents...........................................................310

Date function equivalents...................................................................312

More examples of using formulas............................................................312

14 Desktop Intelligence Access and Analysis Guide

Contents

To create the variable to calculate a three-week rolling average for sales

revenue..............................................................................................313

Launching Desktop Intelligence with the Run Command 325Chapter 19

Overview..................................................................................................326

To use the Run command........................................................................326

Run command options.......................................................................326

Specifying BOUSER, BOPASS and Other Variables.........................328

Desktop Intelligence and Visual Basic for Applications 331Chapter 20

Overview..................................................................................................332

What is a macro?...............................................................................332

What is an add-in?.............................................................................332

Using macros...........................................................................................333

To run a macro...................................................................................333

Using add-ins...........................................................................................334

To install an add-in.............................................................................335

Using an add-in..................................................................................335

To uninstall an add-in.........................................................................335

Exchanging add-ins with other users.................................................336

Converting scripts to macros...................................................................336

To convert a script..............................................................................336

Using the Visual Basic editor...................................................................337

To open the Visual Basic Editor..........................................................337

Desktop Intelligence Access and Analysis Guide 15

Contents

Overview 339Chapter 21

Syntax 341Chapter 22

Options 343Chapter 23

Help Message 347Chapter 24

Error messages 349Chapter 25

Get More Help 351Appendix A

Index 355

16 Desktop Intelligence Access and Analysis Guide

Introduction to Desktop Intelligence

What is Desktop Intelligence?

Desktop Intelligence is an integrated query, reporting and analysis solution for business professionals that allows you to access the data in your corporate databases directly from your desktop and present and analyze this information in a Desktop Intelligence document.

Desktop Intelligence makes it easy to access this data, because you work in familiar business terms and not technical database terms like SQL.

Once you've used Desktop Intelligence to access data, you can present the information in reports as tables, or as sophisticated dynamic documents with drillable charts.

Where does the data come from?

Desktop Intelligence makes it easy to access data from your corporate database because it has a business-intelligent, semantic layer that isolates you from the technical issues of the database. This semantic layer is called a universe. A universe maps to data in the database, using everyday terms that describe your business environment. This means you can select exactly the data that interests you using your own business terminology.

In your company or organization, universes are created by a universe designer, using Business Objects Designer. The designer then makes universes available to you and other users, to access data from the database through an intuitive, user-friendly interface.

Universes are made up of classes and objects.

Objects are elements that map to a set of data from a relational database using business terms. These objects allow you to retrieve data for your documents.

Classes are logical groupings of objects.

Using this interface, you build a Desktop Intelligence using an editor called the Query Panel, by adding and organizing objects from a universe. Objects are elements that map to a set of data from a relational database in terms that pertain to your business situation. When you run the query, Desktop

18 Desktop Intelligence Access and Analysis Guide

Intelligence connects to the database and retrieves the data mapped to the objects you selected.

A query is a type of data provider. The data provider contains the data you have chosen to retrieve from the data source. Using this data set, you can build interactive reports.

Desktop Intelligence lets you access data from a wide range of sources: from relational and multidimensional databases, from packaged applications, from personal data documents, and, using Microsoft Visual Basic for Applications procedures, from virtually any source.

Presenting and analyzing data

Once you have the data you need, you can present it in a number of ways. You can present it in a simple table.

Alternatively, you can create sophisticated reports containing large amounts of data, organized and formatted to make it easy to go directly to pertinent information.

Introduction to Desktop Intelligence

What is Desktop Intelligence?

You can add images and embedded objects and format your documents to high presentation standards.

On-report analysis allows you to switch your business perspective by dragging and dropping data, insert on-report calculations or drill into a report for detailed information.

Sharing information

You can quickly and easily share the documents you have created with other users in your company, either by sending them directly to selected individuals or groups, or by Exporting them to the repository as Folders or Categories. When you distribute documents in these different ways, you use the Desktop Intelligence repository. The repository stores the documents you send so that other users can retrieve and view them. It also stores information about the documents it stores, such as name of sender, date, and also which users in the company have the right to retrieve and view a document.

Desktop Intelligence Access and Analysis Guide 19

Introduction to Desktop Intelligence

What is Desktop Intelligence?

You can Import documents that other users have sent, using WebIntelligence documents which you can open and view in Desktop Intelligence. You can also use InfoView to send documents for scheduled processing.

Note:

For information on sending, retrieving, printing, publishing and scheduling documents, see the InfoView User's Guide. You can open an electronic version of this guide directly from the Desktop Intelligence Help menu.

Security

The repository is set up and administered by the Business Objects administrator who grants all user rights.

The Business Objects administrator does the following:

• defines he parts of the Desktop Intelligence interface you can access

• restricts the availability of Desktop Intelligence functionality, such as

access to certain menu commands

• defines your database connection

• defines the universes you can access for creating and editing queries

The rights accorded to each user define the user's profile. This profile-based security system allows a single document to be distributed to many users -with end users having access only to the information they are authorized to see.

Keeping a document's data up-to-date

Databases are regularly updated with new data. A document generated at a given point in time reflects the data as it existed at that time, but it may be inaccurate now. Desktop Intelligence lets you update the data in a document while keeping the same presentation and formatting, either manually, or automatically at specified times. When you update a document, Desktop Intelligence reconnects to the database, and retrieves the updated data. This is called a document.

20 Desktop Intelligence Access and Analysis Guide

Introduction to Desktop Intelligence

Demo materials and samples

To help you get up and running with Desktop Intelligence, demonstration databases, universes and sample reports are included in the Desktop Intelligence demo kit. There are two demonstration universes, Island Resorts Marketing and eFashion. The examples in this user's guide are based on eFashion and Island Resorts Marketing.

The eFashion demo database contains retail data from a clothing chain. It tracks 211 products (663 product color variations), sold over 13 stores in the US, over three years. The Island Resorts Marketing universe is described in more detail in the section on Demonstration Materials.

Upgrading from earlier versions of Desktop Intelligence

For users who are upgrading from an earlier version of Desktop Intelligence, previously known as BusinessObjects.

Documents created in BusinessObjects from 5.1 to 6.5 are fully compatible with Desktop Intelligence.

The Repository

Desktop Intelligence uses the repository to secure access to your data warehouse and to provide an infrastructure for distributing documents to be shared with others.

You select the documents you want to import from or export to Desktop Intelligence.

Folders and Categories

The Repository organizes documents into Folders and Categories in an orderly system that permits easy access for you and others working with documents.

Desktop Intelligence Access and Analysis Guide 21

Introduction to Desktop Intelligence

Folders and Categories

Folders

Folders are the physical place where documents are stored.

Only one document with a given name may be placed in a folder or category.

It is possible to place documents in several categories.

If necessary, change the name of the document or give it a number to place it in the same folder or category.

Shortcuts and copies may be placed in other folders or categories.

Your Repository is organized into Folders and Categories to help you organize your documents. It is possible to create or delete sub-folders.

Make sure that your document is saved before you export it to the repository.

You are able to browse the Folders structure or the Categories structure.

There are 2 types of folders:

• My Folders with 2 sub-folders

• Favorites (Generally reserved for often used documents)

• Inbox (Generally reserved for documents received from other users

• Public Folders (For shared documents.)

There are two types of Categories:

• Corporate Categories

22 Desktop Intelligence Access and Analysis Guide

• Personal Categories

Introduction to Desktop Intelligence

Folders and Categories

Desktop Intelligence Access and Analysis Guide 23

Introduction to Desktop Intelligence

Folders and Categories

24 Desktop Intelligence Access and Analysis Guide

Introduction to Accessing Data with Desktop Intelligence

What data sources are available?

Desktop Intelligence lets you access data from a wide range of sources. You can access data from a number of sources:

• Universes

• Personal Data Files

• Stored Procedures

• Freehand SQL Server

• XML Data Provider

• VBA Data Provider

How do you access data sources?

Desktop Intelligence lets you access data through a graphical user interface. You need no technical knowledge of the underlying data structures to get the information you want. What you do need, however, is knowledge of your business. To access a data source with Desktop Intelligence, you build a data provider.

The types of data provider that Desktop Intelligence supports are described in the table below:

26 Desktop Intelligence Access and Analysis Guide

Universes

Personal data files

Introduction to Accessing Data with Desktop Intelligence

What data sources are available?

CD InstallDescriptionData provider

A universe consists of classes and objects that represent the parts of a database that contain the data you need, in everyday language that is meaningful to you. In a query on a universe, you select the objects, such as Customer Name, Year, or Region.

You can retrieve data from Excel, dBASE and text files.

Yes

Stored procedures

You can only use stored procedures if your supervisor or IS department has provided them, and if the RDBMS at your site supports them.

A stored procedure is an SQL (Structured Query Language) script, saved and executable on your database.

Desktop Intelligence Access and Analysis Guide 27

Yes

Introduction to Accessing Data with Desktop Intelligence

What data sources are available?

Free-hand SQL

You can use free-hand SQL if you are familiar with SQL, which is the language used to interact with relational databases. In free-hand SQL, you open or write a SQL script, which you then run against the database.

CD InstallDescriptionData provider

Yes

Only in 2-tier mode

XML Data provider

VBA Data provider

You can retrieve data from XML files

Procedures written in Microsoft Visual Basic for Applications (VBA) enable you to retrieve data from almost any data source.

Yes

Can all Desktop Intelligence users build data providers?

Your Desktop Intelligence supervisor can restrict access to certain types of data providers, or even certain objects within a universe. As a result, you might be able to build queries on universes but no other type of data provider, and then be able to use only certain objects in the universe.

The way the supervisor sets up access to data providers and other Desktop Intelligence features depends entirely upon the query and reporting needs of your organization.

28 Desktop Intelligence Access and Analysis Guide

Introduction to Accessing Data with Desktop Intelligence

By default, all Desktop Intelligence users can refresh data providers to get the latest information from their database.

Who sets up database connections?

To access and retrieve data from a database, you need a database connection. For example, if your company or organization stores its corporate data in an Informix database, someone somewhere has to make Desktop Intelligence "talk" to this data source.

In most cases, you, the Desktop Intelligence end user, do not have to concern yourself with setting up database connections. Thus, Desktop Intelligence lets you get the information you need, without technical knowledge of what's going on behind the scenes.

This does not mean that power users cannot define their own database connections. For example, in free-hand SQL, you can define a connection, write an SQL script, then run the script against the connection you created.

The following table describes who sets up database connections for the various Desktop Intelligence data providers.

What data sources are available?

Queries on universes

Stored procedures

Who sets it up?Data provider

The universe designer sets up the connection in the universe, so the connection is hidden when you build or edit queries.

Note:

The supervisor may modify the existing connection or assign a new connection to the universe

The supervisor creates the connection to access a stored procedure.

Desktop Intelligence Access and Analysis Guide 29

Introduction to Accessing Data with Desktop Intelligence

What data sources are available?

Free-hand SQL

Personal data files and XML files

VBA procedures

Who sets it up?Data provider

In free-hand SQL, you can create your own connection to the database. Once you have created the connection, you can make it available to other users.

When you access data in a personal data file or XML file, you select the file and in doing so, you "connect" to it. This is not a technical task, it's just a question of selecting the right file

A VBA procedure runs a VBA macro that retrieves data for your Desktop Intelligence report. The person who creates the macro defines the connection to the data source in the macro code.

Restrictive connections

If you are working with a universe that is set up with a restrictive connection, you need to supply the database username and password to run a query. This username/password is not the one that you use to log onto Desktop Intelligence; it is the username/password of the underlying database (for example an SQL Server database) that the universe accesses. This database normally remains hidden, but the universe designer can set up a restrictive connection to add an extra layer of security. Depending on the type of restrictive connection, you need to supply the database username and password in some or all of the following situations:

• When you first run a query (for more information on running a query, see

"Building a query in the Query Panel and running the query.".

30 Desktop Intelligence Access and Analysis Guide

+ 332 hidden pages