Prepare an Excel file as ODBC source for Data Advisor

OK, IBM Cognos Express Data Advisor (…) is advertised as the easy way of connecting to your data. Forget complex Framework Manager models, just grab any source, process it with Data Advisor et voilà, one BI application ready to go.

Prepare an Excel file as ODBC source for Data Advisor 1

So what could be more simple then trying to use an Excel file as an ODBC source? Well … hum, it for sure is not difficult but you have to keep a number of things in mind or you will end up frustrated. Ok, what now?

The file I prepared as sample data file is more or less multidimensional. Meaning I have one tab containing the actual data, and several other tabs containing the dimension info. If you look at the file this is not entirely true, but you get the idea. Just note that there are multiple other ways of doing this as well and this is just my way.

The file contains the car sales for the Netherlands for 2012-2013 and has 4 tabs:

  • D_REGION: In this case the Netherlands.
  • D_MONTH: A list of time attributes and an ID, the lowest level is month.
  • D_MODEL: A list of all car models sold and some extra info and an ID.
  • F_SALES: Contains some double/redundant information and the keys to link to the other tabs and the measure.

You can download the file here.

So what to watch out for:

1. Use Named Ranges

If you don’t use named ranges, the ODBC driver will not process the data.

Prepare an Excel file as ODBC source for Data Advisor 2

2. Set Data Types

Data Advisor will not allow you to change data types. So it is the ODBC driver that will decide on the data types. This makes it important to format each column of data correctly. Otherwise you will end up with data types that you don’t want. E.g. a date that you wanted as text “20130201”, now appears as a number (sum aggregation).

3. Include a date field

This only applies when you want to create an OLAP version of your model in Data Advisor and want to use the built-in time hierarchy. That will only work if a field is included that is formatted as a date/time field in Excel.

4. Include keys

If you, just as me, have several tabs of data in some sort of multidimensional setup, then you require a way of linking the tabs together in Cognos Express Data Advisor. The best way to do this is by including a key that can be used to link the different data ranges (tabs in this case) with each other. You could also use the labels themselves but that is less neat and might generate issues in the future. Make sure that the keys are unique and not empty.

5. No empty fields

If you have empty fields, you might end up with funny behaviour, or an OLAP model with dimension structures that you do not want. So don’t leave fields empty. Fill them with a “0” or “NA” or something.

6. Use Excel and not *.txt or *.csv as export format

Very simple. If you use a *.txt or *.csv file, you are not able to set data types for each column. The ODBC driver will then judge on a sample of data what data type it is. In Data Advisor you cannot change data types. So e.g. the field “YYYY” contains “2012, 2013”. So with a *.txt or *.csv this field will be recognized as a number. Drag and drop “YYYY” in e.g. Query Studio and it will just show the sum; “4025”. If you use an Excel file, the ODBC driver will take the data types from the Excel file, so you will not have this problem, as long as you define it as text.

7. Include an aggregator for dimension elements

Include a sort of catch all label for each dimension style element that you have. E.g. in my file I included a column that just says “All Models” for each model. This will make life easier in the different components of IBM Cognos Express.

Prepare an Excel file as ODBC source for Data Advisor 3

That’s about it I guess. Maybe for simple things like my example file, it is easier to just make a big flat file, instead of different tabs. Also if I don’t realy have to use Excel as an ODBC data source, I most definately will not use it, I will just load it in a database of some sort. In the end to make an Excel file work properly as an ODBC data source you almost have to format it very neat like a database.

So to actually create the ODBC connection you do the following:

1. Locate the correct ODBC exe. When you just open the “Data Source (ODBC)” application you will open the file “%windir%\system32\odbcad32.exe”. This in my case didn’t work on a 64bit machine. However the file “%windir%\sysWOW64\odbcad32.exe” did the trick.

2. Once the ODBC Data Source Administrator is open, go to the “System DSN” tab and click “Add”.

ODBC Connection 1  

3. Select the “Microsoft Excel Driver”

ODBC Connection 2

4. Give your data source a name. This name will be visible in Data Advisor, so don’t name it “JohnSource”. And select the source Excel file. Click OK.

ODBC Connection 3

5. Now the data source should be visible in the “System Data Sources” pane.

ODBC Connection 4

Now I know that you can do this from Data Advisor as well, and that creating an ODBC connection isn’t strictly required. Data Advisor will just call the ODBC DSA, so it is the same thing. But, that will be most irritating as Data Advisor will ask for the file location every time you open a Model Definition. You can avoid this buy doing it as described above. 

More ways or just frustrating Excel file formatting stories are welcomed in the comments.

Cognos Service