Submitted by Mitesh Joshi, Tata Consultancy Services
This technique uses Time Partition cube accessed through Data source connection defined for each partitioned file/folder for Time dimensional function usage in historical time reporting.
There are two steps -
Time Partitioned Cube on Time dimension member ( Based on Cognos Proven Practices)
Data Source Creation with more than one source inside Cognos administration
Time Partitioned Cube
A time-based partitioned cube is defined on the time dimension, and each member cube is partitioned, or split, at the appropriate level, such as year, quarter, or month.
The location where the cube is built, you will have two files and one folder:
I. XXXX.mdc – this represents a control cube (.mdc)
Contains the high-level metadata about the overall structure of the cubes.
Maintains a list of all measures, dimensions, root categories, currency records, and the entire structure of the time dimension.
Contains the control information to gather the data from the member cubes.
Serves as the entry point for PowerPlay users to access the time-based partitioned cube.
II. XXXX.vcd – this represents a time-based control file which is an editable ASCII-format text file that maintains an index of the separate cubes in the time-based group.
III. XXXX – this file folder is the location of the member cubes, which holds the following cubes in our example:
PowerPlay users can view each cube independently, or access the entire collection of member cubes as one time-based virtual cube.
Data Source Creation
The Package and Data source are published during Cube Publication. The data source connection XXXX for Cube was published.
We can created a new connection for each Time partitioned Cube or a Logic Link based on various .VCD files of set of .MDC
The following connection string would enable the use of TIME DIMENSIONAL FUNCTION across report dependent on Time Filter.
The Report would select the Datasource connection and in turn the .mdc file.
The functions closingPeriod, lastChild, lastPeriods, lastSibling used for Time based function could use specify Time Members.
Single report with Dimensional Time Function to be used across Time period by changing the connection string, Enabling Historical Reporting across Time Dimension.
Less usage of conditional logic for Year variant report ( Any Time dimension Level variant)
Report created with Defined Connection enabling a special Time Slab reporting from single Package. ( While creating Report you can set Datasource connection at Package Level)
Faster Report based on Historical Data set as the Cube size would be small and only as per Datasource connection string
Exclusions and Exceptions
The examples in this document were tested against IBM Cognos BI 10.1 using the inbuild sample PowerCube across Time Dimension. This technique applies to multidimensional data sources only (OLAP) and not to packages that contain strictly relational sources.