Create a relational Data Advisor Model Definition

That’s a big mouthful of words. Given that your data source is prepared properly it is rather easy. But in the same take I will add that my conclusion is that I will keep using Framework Manager and not Data Advisor to create relational packages. But first let’s see what it takes.

This post assumes that you have a data source at the ready that is suitable for a relational model. If you don’t have such a thing or you have no idea what I am rambling on about, take a look at this post. It contains a sample data set that you can use. And better still it’s about cars.

So the data set I will be using contains 4 “tables”:

  • D_REGION: In this case the Netherlands with an ID.
  • 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.

 This is how it roles:

1. Open Data Advisor and click “Create a new Model Definition

Data Advisor Relational 1

2. The model definition properties pane is shown. Give your model a name, make it a good one as this will be shown in the Cognos environment. Select your data source, if you don’t have one check here. Select where you want the package to be stored, I strongly advice never to store packages in “My Folders”, not even for dev or test purposes. Finally select “Relational” for model type.

Data Advisor Relational 2

3. If the Cognos Gods look upon you with benevolence, you should now see the “Select Data” pane with the different tables. Chances are that they don’t, then you probably have a data source or formatting issue, Cognos errors usually start to appear when you want to create the package, not at this stage yet. It also might be a good idea at this stage to hover over all the items to check what data type they were given. If you see items that are e.g. number and that should be text, change it now in the data source.

Data Advisor Relational 3

4. The first thing to do is to clean everything up as much as possible. So deselect all the items you are not going to use, but keep the keys that you need to stitch the tables together. You can also change the labels of each item (double click), might make them more readable.

Data Advisor Relational 4

5. Then move over to the “Define Relationships” pane. This is the place where you are going to tell how the different tables are linked. The method is easy, click a key in one table and drag it to the corresponding key in another table. In my example this means I will take e.g. “Model ID” form “Car Sales” and drag and drop it onto “Model ID” in “Car Model”. You then have to define the cardinality between the tables. If you have a decent relational star style setup, so a fact table surrounded with dimensions, you will have in 99% of the cases a *:1 relationship between fact and dimension. Read, you can have one or more occurrences of the ID in the fact table and only one single occurrence of the ID in the dimension table. If your data does not correspond to this, try to change it, or expect some potentially “funny” behaviour later on in one of the studios in Cognos.

Data Advisor Relational 5

6. Now might be a good time to save the model definition. Now, please make sure you save the model definition file in a location where you will find it again in let’s say 6 months. 

Data Advisor Relational 9

7. Almost done. Last step is to create the model itself. So we already created the model definition, but before we can use it we have to create the model. If IBM wouldn’t employ people to make our lives more difficult then they ought to be, it would just be called “Publish Package”. And Data Advisor would be part of Framework Manager with a “light” security profile. But hey, who am I? OK enough with the rambling, hit the “Create Model” button and wait for the magic to happen.

Data Advisor Relational 6

Data Advisor Relational 7

8. If everything went well, this means that if you just discovered that you don’t have the rights to publish something to the Cognos content store, it did not go well, you should be able to find a fresh smelling package on Cognos Connection. Isn’t that great.

Data Advisor Relational 10

9. Before we jump up and down from happiness, just make sure the thing works. Open Query Studio and play a bit around with the package and check that the observed behaviour matches the expected behaviour.

Data Advisor Relational 11

Note: If you use the data source based on Excel that I used, you will very easily run into errors containing something with “local processing”. This is related to the options of the database engine, in this case none as it is an Excel file. In Framework Manager you can change this and make the Cognos server do the work, thus working around the problem. In the case of Data Advisor, I did not find (yet) what a solution for this might be. Also in Framework Manager you have more options to play with data types and other options. As I stated in the intro, I stick to Framework Manager for now as I still have to meet the perfect data source.

As for Data Advisor, it is “entry level” model building, I suspect more people then usual working with Excel and other files, so good luck.

Cognos Service