IRF Business Objects. Using Excel as a Data Provider in an IRF BO Report. September, 2009

IRF Business Objects

Using Excel as a Data Provider in an IRF BO Report

Why use Excel Data?

 Integrating data from:

 External systems (e.g. foreign accounting and payroll

systems)

 Budget or Estimate Scenarios

 Division/Dept/Account grouping information

 To:

 Add new data objects

 Combine Like measures (expenses, payroll)

 Compare data to find variances, like or mis-matched

data

 Create your own groupings (e.g. Departments,

Table of Contents



Data provider tool – Data Manager



Creating the Excel data provider



Linking the Excel data to a BO

Universe



Using the Excel data in a report tab



Caveats/Best Practices



Troubleshooting

Data provider tool – Data Manager

 Accessed on the

Standard toolbar from the View data button

 Allows;  Renaming providers  Link/Unlink Objects between providers  Edit/Deleting providers  Purge data

Creating the Excel data

provider

 Make sure spreadsheet data is ready!

 Format data in columns

 All data in a column should be formatted the same

 Sub Account and GL account 6 numbers need to be

text and should have preceding zero(s)

 Amounts should have a zero vs. being left blank

 Remove extraneous titles/rows

 The first row should contain column

headers

 Use the same names as universe data objects when

possible

 Columns w/out headers will be auto-named (i.e. F1, F2, etc…)

Creating the Excel data

provider

 On the main menu select Data and then New

Data Provider  Leave the default

selection of “Access new

data in a different way”

and click on the Begin button

 Select Others, leave the

default entry of “Personal

data files” and click on

Creating the Excel data provider

1. Select the Microsoft Excel files

(*.xls) format

2. Click the Browse button to find the Excel file you want to use

3. Select the worksheet from the

Sheet Name dropdown

4. Leave the Field Selection on All

Fields unless you have a range of

cells or range name you want to use

5. Check off First row contains

column names option

Linking the Excel Data to a BO

Universe



To use the Excel data in conjunction

with BO universe data, one or more

dimensions they have in common

must be linked



This tells BO how the data can (and

Linking the Excel Data to a BO

Universe

 Linking is performed in the

Data Manager window on the Definitions tab

 Highlight one of the objects

to be linked and click on the Link to… button

 Select the corresponding

object in another data provider and click the Ok button

 Close the data manager when

Linking the Excel Data to a BO

Universe

 When a linked object is selected, it and all

objects it is linked to will display a

checkmark (in the data manager window)

 Once an object is linked the link button

changes to a un-link button.

 If you make a mistake and link unrelated

objects, select one of the objects and click on the un-link button.

Linking the Excel Data to a BO

Universe

Linking the Excel Data to a BO

Universe

Linked Dimensions

Linked Dimensions

The Un-linked dimensions from one provider cannot be used unlinked dimensions from another provider

Measures should only across providers at the level of the linked dimensions (e.g. not PAR SubAcct No)

Data Provider 1 (BO Universe)

PAR Acct6 No

Empl ID (scrambled)

PAR SubAcct No Empl Name (scrambled)

PAR Acct Name

Pay Amt Total

Data Provider 2 (Excel Spreadsheet) Account No. Empl ID (scrambled) HR Title Name (scrambled) Account Name

Linking the Excel Data to a BO

Universe

Empl Name (scrambled) Account Name

FY09 Revised Budget Linked Dimensions

Linked Dimensions

Making unlinked dimesions in the excel spreadhseet a detail of linked dimensions allows them to be used with the objects in

data provider 1 Data Provider 1 (BO Universe) PAR Acct6 No Empl ID (scrambled) PAR SubAcct No Empl Name (scrambled)

PAR Acct Name

Linking the Excel Data to a BO

Universe

 Linked Dimensions do not have to have the

same name (e.g. SL Acct6, Account No). But make sure they contain the same data!

 Linked dimension objects should have the

same format (e.g. character, numeric, etc.)

 Common values must be in the same case

(Jones and jones are treated unique values)

 A measure should only be aggregated to

the lowest level of data based on the linked data providers

Using The Excel data in a report

tab

 In the Data tab of

the report manager window you can

select by data

provider at the

bottom of the

window to organize the data objects

available by provider

Using The Excel data in a report

tab

 How can I make a table from two data

providers and only show data common to one or both providers?

Once they are linked, each object of linked data providers contains ALL of the values from BOTH queries. If you want to isolate the display to show only records that are in one or both queries, you need to filter for non-null values from

some other non-linked measure or detail (of a linked object) on one or both providers.

For example;

 My BO data Available

 = Not (Isnull (<Pay Amt total>) )

 My Excel data Available

Using The Excel data in a report

tab

 Data displayed by filtering on these

formulas;

My BO Data Available

Filter(s) True (1) False (0) not filtered

My Excel Data Available

True (1) data must be present in both Bo and Excel

data must be present in Excel and not in BO

data must be present in Excel

False (0) data must be present in

BO and not Excel No data will show

data must be present in BO and not Excel not filtered data must be present in

BO

data must be present in

Using The Excel data in a report

tab

 Excel Dept Grouping Example

 This spreadsheet could be added to any BO

report to add the departmental grouping

 Adjustment data Example

 Note – all levels of the hierarchy for Sub Acct 6

Caveats

 Unlinked dimensions (or their details) from

different data providers cannot be used together. BO will ghost the unlinked

dimensions from one provider when an unlinked dimension from another is

selected

 The path to the Excel file is stored in the

BOdoc. So if you share a report or move the Excel file you will need to edit the data provider and browse to the Excel file’s

Caveats

 If your Excel data provider uses a range

definition or name and you want to change to a different spreadsheet (tab) in the Excel file;

 Change the field selection to All Fields

 Select the sheet name (tab) that has the desired

data

 Change the field selection back to range

definition or name and enter the definition or range you need

Caveats

 To filter Section values, the filter needs to

be global

 Open the Format Filters window and drag and

drop your filter to the Global area

 When you bring in Excel data, you get ALL

the data specified (spreadsheet or range). You can filter it once it is in BO, but this also filters the linked data as well (linked data is treated as one large table).

Troubleshooting

 Measures look too big

 This indicates a Aggregation problem, check that the

measures are being used with the correct dimensions and details (turn off aggregation in format table to check)

 Measures are all the same

 This could indicate a Cartesian product, check that the

data providers are linked properly to each other

 Or you are try to aggregate to a lower level than the

links between providers allow

 A linked dimension value appears multiple times

 There may be slight differences in the fields, look for

extra spaces, capitalization or different formatting in the Excel spreadsheet

Troubleshooting

 When I try to create a table (or a Variable or Formula) I get a

message “variables are not compatible”

 A "combined block" that contains data from more than one data

provider can only have objects that meet one of the following criteria:

 A dimension object that is linked

 A detail object associated with a linked dimension object

 Any measure object

 Any un-linked dimension objects from a single (one only) data provider

 You may not include:

 Unlinked dimensions from two (or more) data providers

 Detail objects that are not associated with a valid dimension object that can be used

 Note that measure objects are always allowed, but may or may not provide the proper value based on the linked (or lack of linked) dimensions.

Overview

 Create your data providers (Universe and Excel)  Link the data providers on common data columns

 Change Non-linked spreadsheet dimensions to details

of the linked dimensions to use them in the report.

 Create your report with all the data you need from

Questions?



NSIT/Business Information Services

Support (BIS)

Email:

[email protected]

or check out

NSIT/BIS Website