Are you a Cognos Planning client looking to simplify maintenance of your system? As a business analytics consultant specializing in Planning, I despise the maintenance of access tables. It seems like the process is still living in the 1970s. As a result, we are sharing with you today how to simplify maintenance of Cognos Planning Access Tables with D-Cubes. This technique allows for creating and updating Cognos Planning Access Tables by just changing the data values in an Cognos Planning Analyst D-Cube. I believe this is way better than manually maintaining the Access Tables in Cognos Planning!

First of all, managing Access Tables for large and highly distributed Cognos planning models can be a labor-intensive and time consuming process. This is especially the case when the access rights change frequently. The Contributor Administration Console - Access Tables editor interface can be a royal pain.

Use an IBM Cognos Planning Analyst cube to manage access and update the Contributor Admin Access Tables. Once you create the Analyst D-Cube, you will be able to assign and maintain the data in the cube then export the cube data and import it into Contributor Admin. You can even put the process in a macro to automate it. This method is so much easier, more visual, and flexible. At least that’s my opinion. If you have simple access tables that don’t change, you might just want to use the Contributor Admin Console.

Step1 - Create a D-list with 3 items: READ, WRITE, and HIDDEN as shown below:

Step 2 – Create the Access Table cube in IBM Cognos Planning – Analyst by choosing the dimensions on which access levels will be determined.

An example of the Access Table in Contributor Admin is shown above.

In this example, we will create a cube with 2 dimensions (AcctsIncStmt and the e.List). To format this cube for consumption by Contributor Administration Console as an imported Access Table, a D-Cube format is applied. Make sure the D-Cube is open and active. From the Analyst drop-down menus choose: D-Cube > Format. For format type, choose D-List and select the D-List created in Step1. This will leverage the option of Read, Write and Hidden (No Data).

Above the rows correspond to AcctsIncStmt and the columns correspond to the e.List. Page holds the access [READ/WRITE/NODATA/HIDE] because we set up the format as the D-List formatted.

Step 3 Populate the cells in the cube.

The Analyst D-Cube can be populated manually, automatically via d-link, automatically via formula or some combination of the aforementioned. Note that while not every cell needs to be populated, a default access level can be specified when importing this Access Table into Contributor. Any blank values will be populated with the default access level, in this case NO DATA.

Step 4 – Exporting the Access Table from IBM Cognos Planning - Analyst

Now that the Access Table D-cube has been created and populated, the next step is to export the table from IBM Cognos Planning Analyst in a format that the Cognos Planning Contributor Admin understands. This is accomplished by having the Access Table D-cube you created open and then selecting D-Cube from Analyst Main Menu bar and then choosing Export Selection involves only the items needed for the Access Table. Choose the following options from the Export D-Cube settings:

a) Select Ascii File and then click the ellipsis to set the export file location; b) In the Format box Select Tab as the separator c) Format Column Headings None d) In the Groups box Select Multiple Column e) In the Dimension Order box ensure that the detail dimension is first, the e-list dimension is second (if applicable), and the data dimension is last. The dimension order MUST correspond to the order required by the Access Table.

In order to suppress items that have not been granted READ, WRITE, HIDDEN or NO DATA access levels. Click on the Zeros tab and highlight the line that represents Rows. When the import is executed, the Base Level Access will be set to NO DATA and any untagged dimension items will be set to NO DATA in the Contributor access table.

Now that you’ve created the Export file from the Analyst D-Cube defining the access levels, you will import the Access Table data into IBM Cognos Planning – Contributor. To import the tab-delimited file that was created above, open Cognos Planning – Contributor Administration Console, expand the Datastores, expand the application, expand Development, expand Access Tables and Selections, and choose Access Tables.

In Access Tables, select the correct dimension and cubes that the access table will be applied to and select the Import access table radio button. If you are using the eList in the access table, check the Include e.List checkbox.

Next, click the Add button. After the new row has been added, select it and click on the Import button. Click the ellipsis to select the file that was exported from IBM Cognos Planning – Analyst. Choose the Base Access Level that will be applied to the access table that is being imported. You might want to select NO DATA, which will result in NO DATA being applied to any items not defined in the import file. Also, ensure that Options – Import is checked. First row contains columns headers should remain unchecked.

After these selections have been made, click OK and the import should run with no errors. This will stage the updates to the access table but before they are applied in Contributor you will need to run a GTP process.

Note: View of the Access Table is a different order than the order required by the import file.

Once you set this process up, you could automate it with a macro. By leveraging an Analyst D-Cube to define the access table you will simplify the maintenance. This is recommended when you have complex access tables, they change often or they just seem inefficient.

In conclusion, these are a few short steps you can create a cube to define and maintain access tables in Analyst, export the definitions and import them into the Contribution Administrator. For more information on Cognos Planning check out the IBM knowledge center for the latest product documentation and user guides.

Let’s now talk about how challenging it can be to define the security in Cognos Planning. The maintenance time of just waiting for the GTP processes to run can be frustrating. But there is an alternative. You know IBM has a more powerful planning and budgeting solution, called TM1 (Performance Management). The beauty of TM1 is that assigning security even down to the cell level is much easier. You can make changes in your model and not even have to do a sync or GTP process!

Here at Lodestar Solutions, we feel the future of Cognos Planning is in question! So, we encourage you to start considering and defining your migration to a better tool. Check out our blog on the benefits of TM1 over Cognos Planning to learn more information.

Lodestar Solutions wants you to be educated, so we created a library of videos that will help you evaluate TM1. Check out our Move to TM1 Program to get access to our library of videos and templates and there is NO COST TO YOU. Finally, if you have specific questions, please contact us at Coaching@lodestarsolutions.com and one of our analytics coaches will get right back to you.