Modeling

Data modeling user interface in Cognos® Analytics 11.1.0 is significantly changed, and new modeling capabilities are introduced.

New user interface experience for creating base data modules

The manual and intent-based process of creating a base data module was redesigned and enhanced in the following ways:

• Improved source selection.

  You can use search and filtering options to locate different source types in Team content, My content, and Recent folders. Data server connections are grouped in a separate folder for ease of access. You can also upload new files while creating a data module.

• Adding tables manually or engaging the system to discover related tables for you.

  When using data servers or multi-tab spreadsheets as data module sources, a choice dialog box is presented where you can select the option to manually add tables or let the system discover related tables for you. In both cases, after the table selection, the data module is created based on the chosen tables.

For more information, see Creating a data module.

Multifunctional, robust expression editor

The redesigned expression editor provides robust user interface for creating and editing tables, filters, and calculations.

You can create expressions by typing the code or dragging items from the source and functions panels. The validation and data preview capabilities help to quickly verify and troubleshoot the expressions. The code editing capabilities include: inserting comments, function auto-complete, pretty-print, high-contrast mode, and different font sizes. The information panel shows details and provides examples of functions that are used in the expressions.

For more information, see Creating tables using custom SQL.

Create folders in data modules

Starting with this release, you can create folders in data modules to organize tables and columns.

You can create a folder at the root of the data module, within a table, and within another folder. Drag tables, columns, and other folders to add them to folders. Tables can be added only to folders that are for table filters and calculations. Columns can be added only to table-level folders.

Folders can be moved, copied, and deleted. These actions can be undone before the data module is saved.

Create new tables by combining existing tables

You can create new tables to combine data from multiple tables into one query. Tables from all supported sources can be combined.

Depending on the number of source tables that you select, you can create a copy or a view of an existing table, or you can join two tables into a new table. Using the union, intersect, or except SQL operations, you can define a table to merge, compare, or equate similar data from different sources. The new tables can be modeled and used in the same way as other data module tables.

For more information, see Creating tables by combining queries.

Create new tables using SQL

You can create new tables that are based on SQL syntax that you provide. The following types of SQL are supported: Cognos SQL, native SQL, and pass-through SQL. Stored procedures are not supported. The new tables must be associated with valid sources.

Enter the SQL statements in the table editor, and validate the syntax. You can view the syntax errors in the validation panel. You can resolve the errors, or save the SQL table with errors. If the validation is successful, the table is populated with a set of column names that is obtained from the projected columns in the SQL result set. If the syntax is not successfully validated, the table contains no columns. You cannot modify any aspect of the SQL table until it is successfully validated. To remove or reposition columns within the SQL table, you need to modify the SQL.

The SQL-based tables can be modeled and used in the same way as other data module tables.

For more information, see Creating tables using custom SQL.

Support for relative dates

The relative dates feature makes it easier to do as-of-date analysis. Relative date analysis in Cognos Analytics uses a set of prebuilt relative date filters in a sample calendar data module. To do relative date analysis against your data, you must create a data module that maps your data to the sample calendar. This data module can then be used as a source for relative date analysis in reports and dashboards.

The implementation of this feature depends on using the sample calendar data module. The sample calendar data modules, Gregorian Calendar, and a number of fiscal calendars, are included with the Cognos Analytics 11.1.0 base samples. These sample data modules are used to create another data module where at least one date column from your data is associated to a calendar, and at least one measure column from your data is associated to the date column. This association is done by using a new column property named Lookup reference. When this property is specified in a data module for a date column, the relative date filters, such as prior month, current quarter, MTD (month-to-date), appear under the date column. You can use these filters to filter data in reports and dashboards. When this property is specified for a measure column, a set of date-filtered measures appears under the measure column name. You can use these measures in reports and dashboards.

For more information, see Relative date analysis.

Data-level security

You can implement data-level security in data modules by using security filters. A security filter specifies which users, groups, or roles can access specific data values in a source. When the source is used in reports or dashboards, the secured data values are visible only to the specified users, groups, or roles.

Data-level security is supported for data server sources only. You must have write permissions for the related data server connections and signons to use this functionality.

For more information, see Securing data.

Data caching

You can enable data caching in a data module and specify the cache options.

To specify the data cache options at the source level, from the source context menu in the Source panel, click Data cache. To specify the data cache options at the table level, from the table context menu in the Data module panel, click Data cache.

For more information, see Enabling data caching.

Specify column dependencies

The column dependencies feature allows you to define how columns are related to each other. This feature is an equivalent of determinants in Framework Manager, but provides more flexibility because you can specify more than one hierarchy per table, view, query subject, or data set.

There are three scenarios where column dependencies are specified to avoid double counting.

• When a table contains replicated data (denormalized table).

  For example, a table that contains City Population and Country Population has the values for the Country Population repeated for all the cities that belong to a certain country.

• When a table is related to a fact table and the relationship uses columns from the first table that have repeating values (blending data).

  For example, a table that contains data for each calendar date is related to a table that contains data at a year level. The values for each year will be accessed for every date value, ending up with values that are inflated by a factor of 365.

• When measures in a dimension table are involved.

Using the column dependencies feature, you create groups of columns that depend on a specific attribute. The groups are related to each other in an order from coarse to fine granularity.

For more information, see Column dependencies.

Formatting columns

You can view and overwrite the format type and format type properties of columns.

Splitting columns

You can split values in a column into multiple columns. For example, you can split a geographic coordinates column into latitude and longitude columns, a date column into year, month, and day, or an address column into city, street, and postal code.