Excel and Oracle Data Sources

building semantic layer
1

In a message dated 00-04-07 11:39:18 EDT, you write:

I have a common field that links the 2 data sources together but I can’t
get
BO to link them correctly. I created a univers using ODBC for the excel
file
and my standard universe for oracle. I created a Link between the 2 universes as well. However, when I try to grab info from both sources into
a
report BO simply adds the table/field name of the excel file to the SQL statement it’s handing off to oracle which promptly thows a fit because it doens’t know what that table/field name are. Any ideas?

BusinessObjects cannot access two different data sources in the same universe. In order to use the Link feature, your two (or more) sources must share the same connection.

What you can do to get around this is create a universe for your Oracle data, and a second universe for your ODBC/Excel data. Then create two queries in a document, and link appropriately.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

2

You explaination didn’t make sense to me. First you say that the multiple queries must use the same connection but in the resolution you say that you need to make 2 queries (which don’t use the same connection) and then link them. Can you please elaborate?

DRathbun@AOL.COM 04/08/00 02:16PM >>>
In a message dated 00-04-07 11:39:18 EDT, you write:

I have a common field that links the 2 data sources together but I can’t
get
BO to link them correctly. I created a univers using ODBC for the excel
file
and my standard universe for oracle. I created a Link between the 2 universes as well. However, when I try to grab info from both sources into
a
report BO simply adds the table/field name of the excel file to the SQL statement it’s handing off to oracle which promptly thows a fit because it doens’t know what that table/field name are. Any ideas?

BusinessObjects cannot access two different data sources in the same universe. In order to use the Link feature, your two (or more) sources must share the same connection.

What you can do to get around this is create a universe for your Oracle data, and a second universe for your ODBC/Excel data. Then create two queries in a document, and link appropriately.

Listserv Archives (BOB member since 2002-06-25)

3

In a message dated Mon, 10 Apr 2000 5:08:15 PM Eastern Daylight Time, Ian Humphries Ian.Humphries@POWERQUEST.COM writes:

You explaination didn’t make sense to me. First you say that the multiple queries must use the same connection but in the resolution you say that you need to make 2 queries (which don’t use the same connection) and then link them. Can you please elaborate?

It goes like this:
Multiple Queries in One Document
One Universe per Query
One Connection (data source) per universe

You cannot link two data sources in Designer. You will have two separate universes, one for Oracle and one for Excel (via ODBC).

Now create a document. For the first data provider, select your Oracle universe and create a query. Run it. Create a Second data provider for the same document, but this time based on your Excel data. Run it.

Invoke the Data Manager (looks like a cube on the toolbar, or select Data + View from the menu). You will have to select the common objects (values) from each data provider and link them. Then you have data from two queries in one document.

But you have to use two universes to do this, as you cannot link across two different “connections” (such as Oracle and Excel) in one universe. Better? :slight_smile:

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

4

Another thing that might make things clearer is to modify the statements below that say “One Universe per Query” and “You cannot link two data sources in Designer. You will have two separate universes, one for Oracle and one for Excel (via ODBC).”

Actually, the second data source (Excel) is not a universe, it is simply a data provider that is brought into the BO user module as a query against a Personal Data File. The semantics of “universe” vice “data provider” may be part of the sticking point.

My tuppence wurth.

Tony Burton

In a message dated Mon, 10 Apr 2000 5:08:15 PM Eastern Daylight Time, Ian Humphries Ian.Humphries@POWERQUEST.COM writes:

You explaination didn’t make sense to me. First you say that the multiple
queries must use the same connection but in the resolution you say that you need to make 2 queries (which don’t use the same connection) and then link them. Can you please elaborate?

It goes like this:
Multiple Queries in One Document
One Universe per Query
One Connection (data source) per universe

You cannot link two data sources in Designer. You will have two separate universes, one for Oracle and one for Excel (via ODBC).

Now create a document. For the first data provider, select your Oracle universe and create a query. Run it. Create a Second data provider for the same document, but this time based on your Excel data. Run it.

Invoke the Data Manager (looks like a cube on the toolbar, or select Data + View from the menu). You will have to select the common objects (values) from each data provider and link them. Then you have data from two queries in one document.

But you have to use two universes to do this, as you cannot link across two different “connections” (such as Oracle and Excel) in one universe. Better? :slight_smile:

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

5

Are we really talking about the same thing here?

Ian said he had added the Excel data source (via ODBC) to the universe. I would suggest that Excel is best accessed at the reporter-level rather than the designer-level by using a Personal Data File data provider.

If he built a report as follows, I think it would suffice: 1. Create “query 1” using the Oracle-only universe (as several people
have commented, each BO Universe has one and only one connection) 2. Insert a new table beside the table for “query 1”. When prompted to
specify data access, choose Personal Data Files NOT Universe. 3. Browse for the Excel spreadsheet. Then Run.
4. Click on the View Data button (the cube on the toolbar).
5. Link the common dimensions between the two queries (clicking on the
dimension in “query 1” then on the link to button on the definition tab). 6. Alter the report to have only one table/crosstab/chart/whatever
driven from the two data providers.

Does this make sense?

One word of warning, be careful trying to project measures from either data provider by dimensions that are not linked…

Steve

Listserv Archives (BOB member since 2002-06-25)

6

I just tried this and it almost works :wink:

After linking the 2 common fields and add in all the fields from the Oracle datasource that I need BO won’t let me add in the excel fields. Now what? :slight_smile:

Ian

Steve.Kerr@LVL.CO.UK 04/11/00 09:57AM >>>
Are we really talking about the same thing here?

Ian said he had added the Excel data source (via ODBC) to the universe. I would suggest that Excel is best accessed at the reporter-level rather than the designer-level by using a Personal Data File data provider.

If he built a report as follows, I think it would suffice: 1. Create “query 1” using the Oracle-only universe (as several people
have commented, each BO Universe has one and only one connection) 2. Insert a new table beside the table for “query 1”. When prompted to
specify data access, choose Personal Data Files NOT Universe. 3. Browse for the Excel spreadsheet. Then Run.
4. Click on the View Data button (the cube on the toolbar).
5. Link the common dimensions between the two queries (clicking on the
dimension in “query 1” then on the link to button on the definition tab). 6. Alter the report to have only one table/crosstab/chart/whatever
driven from the two data providers.

Does this make sense?

One word of warning, be careful trying to project measures from either data provider by dimensions that are not linked…

Listserv Archives (BOB member since 2002-06-25)

7

In a message dated Tue, 11 Apr 2000 11:38:21 AM Eastern Daylight Time, “Charles (Tony) Burton” tburton@BUSINESSOBJECTS.COM writes:

Another thing that might make things clearer is to modify the statements below that say “One Universe per Query” and “You cannot link two data sources in Designer. You will have two separate universes, one for Oracle and one for Excel (via ODBC).”

Actually, the second data source (Excel) is not a universe, it is simply a data provider that is brought into the BO user module as a query against a Personal Data File. The semantics of “universe” vice “data provider” may be part of the sticking point.

Tony:

As part of the original question, the poster stated that they had created two universes: one for Oracle, and one (via ODBC) for Excel. Then they tried using the “linking” feature of Designer to combine the two.

I agree, normally I would simply read the Excel file directly as a personal data file.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

8

In a message dated Tue, 11 Apr 2000 12:11:17 PM Eastern Daylight Time, Ian Humphries Ian.Humphries@POWERQUEST.COM writes:

I just tried this and it almost works :wink:

After linking the 2 common fields and add in all the fields from the Oracle datasource that I need BO won’t let me add in the excel fields. Now what? :slight_smile:

Ian

Remember that you can only include measures or linked dimensions or details associated with linked dimensions. By default when you use Excel as a personal data file, numeric values are treated as measures and non-numeric values are treated as dimensions. You may need to evaluate the structure of your Excel file and change object types.

For example, if you have a primary key value in your Excel file that links (uniquely) to a value in your Oracle query, then other objects currently defined as dimensions could potentially be reclassified as Detail objects. Details MUST have a relationship with a dimension object (probably your key). Once you do this, you can include them in your document.

If you leave the objects as dimensions but do NOT link them then they are not valid, and therefore cannot be included in your document.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

9

So your saying that if I have measures and dimensions in my oracle data I can’t have dimensions in my excel? This is what I’m trying to accomplish :

Oracle :

Code # of calls Country
ABC123 12 USA

Excel

Code Description Most likely resolution
ABC123 Dumb user error Hang up

All fields are dimensions except the (# of calls)

I want to combime all fields by linking via the code…sorry for the excessive sarcasm but its that time of day.

Ian

Remember that you can only include measures or linked dimensions or details associated with linked dimensions. By default when you use Excel as a personal data file, numeric values are treated as measures and non-numeric values are treated as dimensions. You may need to evaluate the structure of your Excel file and change object types.

For example, if you have a primary key value in your Excel file that links (uniquely) to a value in your Oracle query, then other objects currently defined as dimensions could potentially be reclassified as Detail objects. Details MUST have a relationship with a dimension object (probably your key). Once you do this, you can include them in your document.

If you leave the objects as dimensions but do NOT link them then they are not valid, and therefore cannot be included in your document.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

10

Ian,

Yes. That is what Dave is saying. Or more accurately, if you have dimensions in one data provider and have different dimensions in a second data provider you can get problems.

Consider this: Two data providers, each with 2 dimensions and 1 measure. DP1 = product code, supplier, no of orders. DP2 = product code, week number, no of sales.

We link on product code. Now we can report product code/no of orders/no of sales.

However if we try to report product code/supplier/no of orders/no of sales then kaboom! Since there is no way for BO to determine how many sales could be attributed to a particular supplier it reports all sales for each supplier (ditto for week number dimension). This is not a limitation of BO, rather a physical impossibility.

In your case I would recommend that you make the extra Excel columns detail objects. This is assuming that for any dimension (code) there is only ever one description and likely resolution.

Hope this helps,

Steve

Listserv Archives (BOB member since 2002-06-25)

11

Ian,

To follow what Steve and Dave stated, for your particular example here is what you would have to do:

In order for you to create a report using all of the fields first you must ask yourself is Description and Most Likely Resolution a detail of the CODE (does it have a relationship to the Code)? If the answer is no, then the query you are trying to create is not possible. However if the answer is yes then link the Oracle and Excel data in Business Objects by the CODE. Then go under DATA - VIEW. Highlight the object Description, in the EXCEL data provider, and click on the Definition tab. Change the Qualification from Dimension to Detail. When the box appears asking “chose the dimension that this detail gives extra information on” chose CODE. Repeat this process for Most Likely Resolution.

You should now have from the EXCEL file the Code as a dimension, and Description and Most Likely Resolution as details. Excel and Oracle should be linked by the field Code. You can now create a report with all of the fields in it.

Stephanie

Listserv Archives (BOB member since 2002-06-25)

12

In a message dated Tue, 11 Apr 2000 7:37:13 PM Eastern Daylight Time, Ian Humphries Ian.Humphries@POWERQUEST.COM writes:

So your saying that if I have measures and dimensions in my oracle data I can’t have dimensions in my excel?

Nope, this is not what I am saying at all. Read on…

This is what I’m trying to accomplish :

Oracle :

Code # of calls Country
ABC123 12 USA

Excel

Code Description Most likely resolution
ABC123 Dumb user error Hang up

All fields are dimensions except the (# of calls)

I want to combime all fields by linking via the code…sorry for the excessive sarcasm but its that time of day.

Ian

There have been a couple of other answers already that I think provide the solution, but for “closure” I’ll add my two cents worth as well.

In your Excel file, you have to answer the question:

Is there more than one DESCRIPTION or MOST LIKELY RESOLUTION for each CODE? If the answer is “Yes”, then you have a problem. If the answer is “No”, then you need to change the second two columns to Detail objects. A Detail is supporting information, it has no meaning on its own. It requires an associated Dimension object to give it context.

Stephanie provided excellent instructions on how to accomplish the switch from dimension to detail in her earlier response.

The key dimension object in your case is the CODE, which you have already linked. That is one of the purposes of a dimension object: linking.

As I mentioned before, all non-numeric values in your Excel file are going to be created as Dimensions by default. It is up to you to examine the data and determine the “true” relationships between the columns, and reset any object that provide supporting (non-key) information to details.

There is no restriction on detail objects in a combined query, as long as their associated dimension object has been linked. There is no restriction on measure objects at all.

So, your final report objects would be:
Oracle: Code (Dimension), Country (Dimension), Number of Calls (Measure) Excel: Code (Dimension), Description (Detail associated with Code), Most Likely Resolution (Detail associated with Code) Link: Oracle.Code with Excel.Code

At this point every object in your two data providers would be valid in a combined block.

But then you say, “But wait, what about the Country dimension? It’s not linked!” That’s okay. The rule is that a combined block can contain:

Any Measure
Any Detail associated with a linked Dimension Any Linked Dimension from either data provider Any additional Dimension from one data provider

So you can include Country. But if there were any other unlinked dimensions from your Excel file, they would be invalid. I think Steve provided an explanation on why that is the case.

This is one of the more confusing issues that can arise when using BusObj. I cover this in great detail (pun!) when I teach the Designer class. At some point I will probably even finish the tech note that I have been trying to get published on our web site for the past… well, a long time. :slight_smile:

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

13

In a message dated Wed, 12 Apr 2000 10:56:21 AM Eastern Daylight Time, DRathbun@AOL.COM writes:

There is no restriction on detail objects in a combined query, as long as their associated dimension object has been linked. There is no restriction on measure objects at all.

Minor edit: change the phrase “combined query” to “combined block” in the previous paragraph. A combined query is a totally different subject… :slight_smile:

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)