OBIEE - Federated Query with Excel Sheet

Bi Server Architecture With Client

About

OBIEE permit to integrate data from different database sources. One of the most common is an Excel Sheet.

To import some measures from Excel, you have to do several actions :

  • Import the data with :
    • one sheet for the measures
    • one sheet for each dimension (Normally, the datasheet for the dimension is the same that in the other database).

The key concept is that OBIEE perform first a query on each database (for instance Excel and an Oracle Database) and then do a union on each dimension. This one is designed in the business model layer by mapping for the same business field, two physical fields.

  • Design the physical join between each excel sheet
  • Map in the business model the dimension field and add the measures in the fact table
  • Modify the presentation layer to add the fields

How to import Excel File in the Physical Layer

You must first create an ODBC data source. Go to on Windows Platform Start/(Control Panel/Administrative Tools)/Data Source ODBC. Then click on the System DNS tab and push the add button.

Odbc Data Source

Select the Microsoft for Excel Driver and finish.

Obiee Odbc Setup

Enter all data, select your worbook location and click Ok.

In OBI Administration Tool, go to File/Import/From Database. Select Connection Type OBDC 3.5 and select the ODBC DNS just created.

To import your worksheet, you must select Table and System Tables as the below picture

Obiee Import Excel

Select what you want.

To ended, you must :

  • disable the connection pooling in the connection pool
  • disable the count_distinct_supported in the features of the database

Support

You can have this error :

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 16001] ODBC error state: 37000
code: -3554 message: [Microsoft][ODBC Excel Driver] Syntax error in ALTER TABLE statement.. [nQSError: 16015] SQL statement execution failed. (HY000)

This error appears when you try to link a Excel Table with an table from another database. It's an internal ODBC error from OBIEE not from the ODBC system.

If you plan to add some measures from Excel, you have also to add each dimension with a Excel Sheet. So, you must have in you excel sheet :

  • a sheet for the measures
  • a sheet for each dimension

Reference





Discover More
Bi Server Architecture With Client
OBIEE - Fact-based vertical partitioning/fragmentation

This technique allow you to : mix two facts table with two different grains (the level based partitioning) perform a densification (to preserve dimension value) : partition vertically your fact...
Bi Server Architecture With Client
OBIEE - Federated Query / Multi-Database or Cross-Database Join

Data can exist in many different places. Instead of moving data to a single database, the BI Server can access multiple databases simultaneously to answer a single query, regardless of whether the databases...
Bi Server Architecture With Client
OBIEE - Repository Design

... .. A consideration to take when designing a subject area is to pay attention at the final user. Do you design for a user:OBIEE Analytics/Reporting...



Share this page:
Follow us:
Task Runner