irf multiple data providers

8/4/2019 IRF Multiple Data Providers

1/26

1

IRF Business Objects

Using Multiple IRF Data Providers in aBO Report

November, 2009


2/26

2

Why use Multiple Data Providers?

Pull disparate datasets that have common criteria that relate them FR Account Status; sub-account, encumbrance, transaction and account

attribute data

Pull disparate datasets where one provides the In List data for theother (but can also be used for

Pull mapped GL accounts for SL accounts Pull account data for list of current stimulus accounts

Add objects (measures or details) to a row on common dimensions(pulling data in same provider puts data in same column) Add current Account name to multi-month range Pull YTD or End of period data along with multi-month range Pull mapped GL account attributes info for SL accounts Add debit/credit account attribute info for transactions Compare Year over year monthly data


3/26

3

Table of Contents

Data provider tools Data Manager

Examples Common condition between providers Adding additional data providers

Dependant providers

Linking data between providers

Caveats/Best Practices Troubleshooting

Overview


4/26

4

Data provider tool Data Manager

Accessed on theStandard toolbar fromthe View data button

Allows;

Renaming providers

Link/Unlink Objectsbetween providers

Edit/Deleting

providers Purge data

Export raw data


5/26

5

Common Condition betweenProviders

Each data provider pulls results for a specific area of data todisplay on report tabs, but they share common conditions.

For example in FRAcctStatus.rep the SL Account 6 and CalendarYear/Mo conditions are common across all the data providers

SL Sub Account Encumbrances Transactions SL Account Attributes

CalYrMo CalYrMo CalYrMo CalYrMo

SL Acct6 No SL Acct6 No SL Acct6 No SL Acct6 No

SubAcct No Encum SubAcct No Trans SubAcct No Acct Name

SubAcct Name Encum No Trans No Acct Ver No

Last Activity Dt Encum Desc Trans Desc PI Name

Co-PI Name

Revised Budget Original Encum Amt Trans Amt Acct Admin 1 Name

Curr Mo Amt Liquidated Curr Mo Acct Admin 2 Name

Fiscal YTD Amt Liquidated to Dt Acct Admin 3 Name

Budget Bal Avail Open Encum Amt


6/26

6

Adding additional data providers

On the main menu select Dataand then New Data Provider

Select either Build a newquery on the universe currentlyin use or Access new data in a

different way and click on theBegin button

If using a different universe,select Universe and click onthe Next Button

Select the universe to query; Financial Reporting Transaction Tracking Payroll Matrix or OBS reporting


7/26

7

Adding additional data providers

On the query panel start by addingthe common conditions and thenadditional conditions and resultobjects needed for the new query


8/26

8

Dependant Providers

One data provider pulls informationthat is used as part of an In Listcondition for another query. Similarto a Sub Query, but additional objectspulled in first query can be used inthe report as well.

Note: Items with the same name indifferent providers are automaticallylinked


9/26

9

Dependant Providers (demo 1)

Data Provider #1

Result Objects:SL Acct6 NoAcct Name

SubAcct NoGL Acct6 (Mapped)Revised Budget Amt

Curr Mo AmtOpen Encum AmtFiscal YTD AmtBudget Bal Amt

Conditions:Cal YrMo(s)

Dept(s)

Data Provider #2

Result Objects:GL Acct6 No

Acct Name (GL)

GL Acct10 Control NoSL Acct6 (Mapped)

Begin Bal AmtCurr Mo Amt

Open Encum AmtFiscal YTD Amt

Conditions:Cal YrMo(s)Dept(s) (GL)GL Acct6 No

GL Acct6 in list ofMapped GL Acct6


10/26

10

Dependant Providers

Create the first data provider that will havethe list of accounts or other data requiredfor the second provider

In the second provider; Drag the result object to match in the list to the

condtions (i.e. GL Acct6 No.0

Select the In List Operator

Pick the Select Query Results operand Select the matching result object from the first

provider


11/26

11

Dependant Providers (Demo 2)

Data Provider #1(Stimulus Accounts)


Gift Grant Contract Cd

Conditions:

Latest Update MonthAcct Name (ARRA%)

Gift Grant Contract Cd (S%)

Data Provider #2(Stimulus Trans)

Result Objects:

SL Acct6 No

Acct NameTrans SubAcct No

Sub Acct Name...

Trans Amt

Conditions:

Range of MonthsSubAcct (1%)Entry Cd (valid Trans)

SL Acct6 No

SL Acct6 in list of

Stimulus Accounts


12/26

12

Linking data between Providers

Linking similar or matcheddimensions between queries to createa common data set (or extended datacube)

Note: these data providers will

commonly have common conditionsand/or be dependant providers


13/26

13


To use the data from differentproviders together, one or moredimensions they have in commonmust be linked (dimensions with thesame name are auto-linked)

This tells BO how the data can (and

cant) be used together Note: there are occasions where auto-

linked objects need to be un-linked)


14/26

14


Linking is performed in theData Manager window on theDefinitions tab

Highlight one of the objectsto be linked and click on the

Link to button

Select the correspondingobject in another dataprovider and click the Okbutton

Close the data manager whendone linking objects


15/26

15


When a linked object is selected, it and allobjects it is linked to will display acheckmark (in the data manager window)

Once an object is linked the link buttonchanges to a un-link button.

If you make a mistake and link unrelatedobjects, select one of the objects and click

on the un-link button.


16/26

16


In the Data tab ofthe report managerwindow you can

select by dataprovider at thebottom of thewindow to organize

the data objectsavailable byprovider


17/26

17


Demo 3 Pulling data for a range of months pullsdifferent account names for the same account. Weonly want to show the current account name.

Data Provider #2


Conditions:Latest Update Month

SL AcctOr

Dept No

Data Provider #1

Result Objects:CalYrMo

SL Acct6 NoAcct NameSubAcct No

Sub Acct Name

Curr Mo Amt

Conditions:Range of Months

Dept No

SL Acct6 in list ofProvider #1 Sl Acct6(s)


18/26

18


The linked Acct Name objects arecommon items in the same column.

When Un-linked they become

separate and a detail can be createdfrom the current names provider (#2)to the account number that is aseparate column Only Linked dimensions, details based on

them or measures can used withdimensions from either provider.


19/26

19


Demo 4 Pulling the same data (forcomparison) for two different periods.

Data Provider #2

Result Objects:

CalYrMoFiscal Mo

SL Acct6 NoAcct Name

Curr Mo Amt

Fiscal YTD Amt

Conditions:

Fiscal YrSL Acc6(s)t

Data Provider #1

Result Objects:

CalYrMoFiscal Mo

SL Acct6 NoAcct Name

Curr Mo Amt

Fiscal YTD Amt

Conditions:

Fiscal Yr thru latestClose

SL Acct6(s)

All CommonDimensions

AutomaticallyLinked


20/26

20


Selecting the Fisical Mo and not CalYrMo todisplay data moves the measures into rowswhere they can be compared.

CalYrMo Fiscal Mo SL Acct6 No Acct Name Curr Mo Amt Fiscal YTD Amt Curr Mo Amt Fiscal YTD Amt

200807 1 123455 Xxxxxx 10000 10000 8000 8000

200808 2 123455 Xxxxxx 7500 17500 9000 17000

200809 3 123455 Xxxxxx 4500 22000 8500 25500

200810 4 123455 Xxxxxx 11000 33000 7000 32500

200811 5 123455 Xxxxxx 12000 45000

200812 6 123455 Xxxxxx 8000 53000

200901 7 123455 Xxxxxx 9000 62000

200902 8 123455 Xxxxxx 7500 69500

200903 9 123455 Xxxxxx 6500 76000

200904 10 123455 Xxxxxx 8500 84500200905 11 123455 Xxxxxx 11000 95500

200906 12 123455 Xxxxxx 7000 102500

200907 1 123455 Xxxxxx 8000 8000

200908 2 123455 Xxxxxx 9000 17000

200909 3 123455 Xxxxxx 8500 25500

200910 4 123455 Xxxxxx 7000 32500

data set #2


21/26

21


Linked Dimensions do not have to have thesame name (e.g. SL Acct6, Account No).But make sure they contain the same data!

Linked dimension objects should have thesame format (e.g. character, numeric, etc.)

Common values must be in the same case(Jones and jones are treated uniquevalues)

A measure should only be aggregated tothe lowest level of data based on the linkeddata providers


22/26

22

Caveats

Unlinked dimensions (or their details) from differentdata providers cannot be used together. BO willghost the unlinked dimensions from one providerwhen an unlinked dimension from another is selected

Editing a data provider relinks all un-linked objects toobjects with the same name in the other dataproviders (if you un-linked an object you will need toun-link it again)

To filter Section values, the filter needs to be global

Open the Format Filters window and drag and dropyour filter to the Global area


23/26

23

Troubleshooting

Measures look too big

This indicates a Aggregation problem, check thatthe measures are being used with the correctdimensions and details (turn off aggregation informat table to check)

Measures are all the same

This could indicate a Cartesian product, check thatthe data providers are linked properly to each

other Or you are try to aggregate to a lower level than

the links between providers allow


24/26

24

Troubleshooting

When I try to create a table (or a Variable or Formula) I get amessage variables are not compatible

A "combined block" that contains data from more than one dataprovider 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.


25/26

25

Overview

Different flavors of multiple data providers Providers with common parameters

Providers dependant on other providers

Providers with linked objects

Link the data providers on common data columns

Change Non-linked spreadsheet dimensions to detailsof the linked dimensions to use them in the report.

Create your report with all the data you need fromeither data provider


26/26

26

Questions?

NSIT/Business Information ServicesSupport (BIS)

Email: [email protected]

or check out NSIT/BIS Website

(http://nsit.uchicago.edu/groups/bis/)
mailto:[email protected]://nsit.uchicago.edu/groups/bis/http://nsit.uchicago.edu/groups/bis/http://nsit.uchicago.edu/groups/bis/http://nsit.uchicago.edu/groups/bis/mailto:[email protected]:[email protected]:[email protected]

irf multiple data providers

Documents