The import technique for textfiles can also be used in a similar form for other data sources based on ODBC. Jedox comes bundled with a Microsoft Access database, which contains the same data as the “Testimport.txt” file, but in a file called “Testimport.mdb”. It can usually be found in the folder C:\Users\Public\Documents\Jedox\Jedox Suite in Windows 10.
We will work with the database “Demo” and create a new cube called “Market” for our imports, with the dimensions as shown here:
The data to be imported are from the years 2002 and 2003. Add these elements to the dimension “Years”.
Link to ODBC source
Before you can import data from an ODBC database, you must log on to the database source name (DSN):
- Select Start–Control Panel–Administrative Tools–Data Sources (ODBC).
- Click “System DSN” and select “Add”.
- Select the Microsoft Access driver, and click “Finish”.
- Enter “Palotest” as data source name in the next menu. Enter “Testimport.mdb” in the Description field and select this file as your database
Importing in Jedox
After you have entered the settings, open a blank Excel worksheet.
Select cell A1 and open the Import Wizard from the Jedox menu.
Select “ODBC Query” as data source:
Click Next and select “Palotest” in the DSN field.
Because no user name or password has been assigned, these fields may remain blank.
To retrieve data from the database, an SQL query must be formulated in the text box. Enter the following SQL statement under heading 2:
SELECT * FROM Sheet1
This entry retrieves data from a table called Sheet1, which is the only table in the Access database. It contains all data that we want to import.
The result appears as below:
Note that you have the option to store all SQL statements. This option allows you to reuse them later, rather than recreate them repeatedly.
After you click Next, you will see the import step, which you already know from importing text files.
The remainder of the procedure is the same as described in article Flat Textfiles as sources.