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
Articles Related
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.
Select the Microsoft for Excel Driver and finish.
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
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