29 September 2017

How to Create a Data Module using Cognos Analytics

Cognos Analytics introduced enormous changes compared to its previous versions. Besides creating Dashboards, it is possible to modulate data without using Framework Manager, a huge step in data integration and modulation. This article is a practical guide for this new feature, and shows not only the key features, but other curiosities too.

1.1. What is a Data Module?

In order to explore the data, it is necessary a model of it first. Creating relationships between tables, defining identity keys or creating measures and KPI’s. When a user drags and drops a column attribute and a measure, the correlation between both tables is only available because it exists a relationship.

Framework Manager is frequently used to create this capability, but sometimes, in order to add more meaning to a model in a fast and simple way, we can integrate it using Cognos Analytics, by fusing together many sources of data, including not only files but also relational databases.

1.2.     Practical Guide

 

1.1.1.      First steps:

 

This practical guide is divided in two sections:

1: How to create a data module using Excel files and database tables (watch video).

2: How to create a data module using OLAP sources

This guide was created using the gosales database and cube and with the version 11.0.6, so, it is possible that some features won’t be visible in older versions.

1.1.2.      How to create a data module using excel files and database tables

 

  • Select New – Data Module;
    • Choose from data servers the go sales database and from upload files, the two excel files (Sales.xls and Returns.xls). Press Start;
    • Move the two excel sources to the data module;
    • Choose the order method and return reason tables from go sales database and drag it into the data module.
  • Select the column Order ID from Sales.xls and go to properties. Put the usage as an Identifier.
    • Repeat the process and put the Row ID and Sales Staff as an Identifier.
    • Put the usage of Customer Segment as an attribute
    • Put the usage of product colour as an Identifier.
    • Do the same to order method code column.
    • Put the returned reason as an identifier
    • Put the Reason Description as an attribute.
  • You can easily hide an attribute without deleting it. Let’s hide the Reason Description AR. Go to properties and check the option “This item is hidden from users”.
  • Repeat the process to the Order Method table.
  • Sort the column Order Method En. Go to properties – advanced and check the option – sort.
  • Select the Sales and Returns tables and with right-click press Join. Choose the proper join type and cardinality using the following diagram:

 

 

 

 

 

 

 

 

 

 

 

 

>        Repeat the process to join Sales and Order Method tables

>        Repeat the process to join Returns and Return Reason

>       Create a custom calculation to count the number of returns.

o   Select the “New data module” and then “Create custom calculation”

o   Write the expression.

o   Click “Validate” and “Ok”

>       Save the Data module

>       Create a new Dashboard with the data module as your source.

>        Explore the data.

 

1.1.3.      How to Create a data module using OLAP sources?

 

An OLAP cube cannot be opened in a data module, so it is necessary to use that data in another way, the best way is creating data sets. In this case, we’re going to create two data sets.

The creation itself is similar to a report studio list – we drag and drop the columns that we want until we have enough data.

>       Go to Team Content and create a data set of the gosales cube.

>       Select the option Create Data Set with a right click on the cube.

>       Drag the columns: Sales Staff Code, First Name, Manager, City, Country ad Sales Region of the Sales Staff dimension.

>       Save the data set.

>       Create another data set.

>       Drag the columns: Product Color Code and Product Color of Products Dimension.

>       Save the Data set

When we work with data sets of OLAP sources, we need to refresh the data after building the cube. For that, it is necessary to create a Schedule to refresh the data automatically. In our case, we’re going to refresh immediately.

>       Select the dataset and then choose refresh:

 

 

>       To create a Schedule, go to properties and create a new Schedule:

 

 

After this, we can use the data sets in the data module

>       Go to the data module created in section 1.

>       Add the 2 data sets as source.

>       Drag the 2 data sets to your data module.

>       Sales Staff data set:

o   Put the sales staff code as an identifier

o   Put the Sales Region as a region in the Represent option. à Cognos provide us the option “Represents” – basically this option helps us in charts like maps (for geographical attributes) and in time expressions for calculations (for time attributes).

>       Product data set:

o   Put the product colour code as an identifier.

>       Select the Sales and Product tables and with right-click press Join. Choose the proper join type and cardinality using the following diagram:

>        Repeat the process to join Sales and Sales Staff tables

>       Since we do not have dimensions or hierarchies, the only way to drill down/up the data is creating a navigation group. We’re going to create one for the products:

o   Select Product Category and “Create navigation group”

o   Drag and Drop the columns: Product Sub-category, Product Name, Product Container.

 

>       Save the data module.

>       Explore the data.

>       Create a dashboard and use the data module as source.

 

Conclusion

Data module is definitely a great capability in Cognos Analytics. You can use data modeling to access and shape data from data servers or uploaded files in a very simple way. You can also: create calculations; define data filters; referencing additional tables; reviewing and updating metadata properties that include aggregation settings and labels.

Other users can access data modules if you save the data module in a folder that users, groups, and roles have appropriate permissions to access. This procedure uses the same idea as saving a report or dashboard into a folder that controls who can access it.

Data modules can be used in both dashboards and reports. A dashboard can be assembled from multiple data modules.

However, it is important to say that data modeling in Cognos Analytics does not replace IBM Cognos Framework Manager, IBM Cognos Cube Designer, or IBM Cognos Transformer, which remain available for more complex modeling.

 

 

 

 

      Rita Ramos

Blog