In this post we will use a relational data source to create an OLAP model definition in IBM Cognos Data Advisor (yes, that is a meager attempt of SEO). As you will see it is rather easy, given that you have a suitable data source that is well prepared.
Actually this post is a variation on this post “Create a relational Data Advisor Model Definition“; it might be useful to have a look, it is also slightly more entertaining I think.
This post assumes that you have a data source at the ready that is suitable for an OLAP 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.
I am a big fan of TM1 and PowerPlay as in my experience OLAP BI solutions are much more easy to use. Let’s say that they connect more easily with the native brain cell operations of most users. So doing this in Data Advisor might be fun. After all, then we have yet another Cognos way of creating an OLAP model (Transformer; dimensionally modeled Framework Manager models, cube designer, TM1 Cubes, …). Anyhow …
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”.
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 “OLAP” for model type. Go to the “Advanced” tab, and if it is possible for you, check the “Data resides on server” check box.
3. If things now evolve as expected, you should now see the “Select Data” pane with the different tables. Uncheck the items you are not planning to use. It 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.
4. 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”. Note that you cannot choose a cardinality here in contrast with a relational Data Advisor Model Definition.
5. Also different from a relational model definition is the third pane; “Create Dimensions”. This is basically a lightweight cube designer. On the left you can see your different tables and on the right you can create dimensions. You basically have three types:
- Values, these are obviously the measures
- Time, this is a generated time dimension where you can choose the layout from a set of predetermined settings. Note that you can only use this dimension if you have a date item somewhere in your tables, date item as in a field with data type “date”. In the dataset I use, this is the item “MM/YYYY”, which is basically of the format 01/01/2012.
- Other dimensions, which are … other dimensions. Important here is that you have no empty items, otherwise this will lead to additional “unknown” items in the final cube. And also important, just as with a relational model, is that everything that you want to call a dimension must preferably be unique at the lowest level. Otherwise you might end up with funny behaviour in Cognos BI.
6. As you can see, a model is already pre-populated. Best thing to do is to delete the pre-populated items and create your own dimensions from scratch. So delete the dimensions and the items in “Values” and “Time”.
7. Now drag the measures to “Values”, drag the date item (data type “date”) to “Time”, then right-click on the canvas, create and fill the dimensions as you want them. Make sure that the hierarchies (top-down order) you create are actual hierarchies (so country-province-city is ok, city-province-country is not). Also make sure that you don’t have doubles on the lowest level, so a hierarchy 2012-1 will give funny results as the “1” will also appear under 2013. The “1” will be treated as the same element, so the last value loaded on “1” will be shown both at 2012-1 and 2013-1. To avoid this use a hierarchy like 2012-201201 and 2013-201301, so both lowest level elements are unique. Next you can double-click the labels and change the naming to something more readable. I advice to do this in this step and not in “Select Data” or “Define Relationships” as this gave me a lot of errors (…). Below you will note that I created a second time dimension, this is a personal choice, just gives me some extra options for report making.
8. When you click on the looking-glass icon for each dimension you can preview the data. Do this, just as a check.
9. Also a good time to save your OLAP 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.
10. The moment of truth, creating the cube. Just hit the button. If you get errors, my money goes to security restrictions.
11. If everything went well you should be able to find a fresh smelling package on Cognos Connection. Isn’t that great.
12. Before we jump up and down from happiness, just make sure the thing works. Open Advisor or something else and play a bit around with the package and check that the observed behaviour matches the expected behaviour.
Now a note. You will notice that when you open the package in Report Studio or Business Insight Advanced that the naming of the dimensions is really dreadful. Actually you are not looking at the Data Advisor model definition. You are looking at a TM1 cube that was generated when you pushed “Create Cube”. This triggered a process to create and populate dimensions and a cube in TM1. You can see this in Architect (below). And there is the funny/stupid naming. As this is TM1 you cannot change the names of dimensions or cubes, unless you recreate them. And even if you could, this would “break” the “connection” with the Data Advisor model definition. OK, they will tell you that you are supposed to use the package only in Advisor, but what if you are suddenly bored with making analyses and you want a *.pdf report? Mhh, makes you think (well no).
And another note. If you are with me then you know that we actually created a TM1 cube. This means you have lost the connection with your original source. So if you update your source, you will not see that data updated in the cube (the OLAP model definition). This is a serious drawback and different from a relational model definition. You can create this process yourself in TM1 Architect, no problem. But what is then the point of using Data Advisor to make the cube? You will be better off to create it in TM1 in the first place. You will have more control and options, and you can get rid of the silly naming of the dimensions. So just as with the relational model definition I have strong reservations about actually using it. Yes for an end-user it might be easier to do. But from the moment you want to share it, change the naming, make it update itself, you need to go to TM1; so exit the end-user. And if you don’t want to do all that and you need just a cube for yourself, well I call that a pivot table in Excel.