Are you looking to simplify D-List maintenance?
Simplifying D-List maintenance with a D-Cube in Cognos Planning can save you time and frustrations. Do you spend a lot of time maintaining your models and not have very much time for analyzing? Using a D-Cube in Cognos Planning to create or maintain D-Lists could be the solution you are looking for to save time and ease frustration.
Steps For Using D-Cubes To Create or Maintain Your Cognos Planning D-List
Below is a summary of steps to using D-Cubes to create and/or maintain D-Lists.
Create an All items D-List – This is a D-List that contains all source items. Later, we will show you how to filter this list to a subset which will reflect your new list. This could be accounts, locations, departments, etc. For this blog, we will be using GL accounts as you can see below. You may want to create this with a link to an OBDC connection or your source file.
Next, create a Yes / No D-List – This is a simple D-List of yes and no. When you create it, make sure you put the Yes first so the IID number is 1. We will use the IID in a calculation later.
Create a Calculation D-List – This list contains the calculations that will be used to determine which D-list will contain specific items from the All Items D-List you created in the first step. So basically, as we set the Y/N flag in the cube, that will determine when the account will be included. For example, if an account from the All Items DList has a yes next to Bal Sheet Flag, it will appear on the Balance Sheet D-List.
Ensure that 1 item of your calculation D-List is formatted as a D-List format selecting the All Items D-List. In our example, the Accounts item on the Calculation D-List is set to D-List format.
Create Flag Item(s) and D-List format with the Yes No Flag D-List - The flag items in the Calculation D-List will determine whether an item from the All Items D-List belongs to a particular D-List subset of items. In our example, we create Flags for the Balance Sheet Income Statement and Other Inc/Exp. The Flags will be assigned a Format of D-List Format. You will select the Yes/No Flag you created earlier as the D-List for the format. Please note you will want to set all the “Flag” Items, like Income Statement Flag and Other Inc/Exp Flag, to D-List format selecting your Yes/No D-list.
The Next Step Is Calculating Items...
Create Calculation Items - The calculation is a conditional formula that is the secret sauce to simplifying D-List maintenance with a D-Cube in Cognos Planning. The calculation will look at the flag D-List item. If it’s set to Yes, then it will use the respective account from the All Items D-List. If it is set to No or blank, it ignores the account from the All Items D-List.
In our example, we put the calculation on the Balance Sheet item. Then, we used the calculation IF (Bal Sht Flag)=1 THEN Accounts ELSE 0. Please note that the 1 represents a Yes from our Yes No Flag D-List because that's the IID number for Yes. If you have added and deleted items or put No before yes in your Yes No Flag D-List, you may need to use a different number. If something seems off, look up the correct IID number from the Yes No Flag List. On the other hand, it might just be easier to create a new Yes No Flag making sure you put Yes first.
Now Set D-List Format Calculations with the [Step 1]All Items D-List - For the DList items in the calculcation D-List, you need to assign a D-List Format to all the calculation items. In our example, we will assign a D-List Format to our Balance Sheet, Income Statement, and Other Inc/Exp. The format will leverage the All Items D-List we created in step one. Please note the Flag line items should all have a D- List format using the Yes No format. Make sure you don’t assign them the wrong D-List Format.
Note: Additional items [or calculation items] can be added as needed if a hierarchical d-list is needed.
Create D-Cube – Create a new Cognos Planning D-Cube using the All items D-List you created in step 1 and the Calculation D-List you created.
Populate the D-Cube - You will now populate your cube so the accounts are listed from All Items D-List.
- If the original source of the All Item D-List is a File Map, add a duplicate column and select the second/duplicate column as data. Then use a d-link to import the data.
- If the original source of the All Item D-List is an ODBC connection, use a D-Link and create [via SQL] a new column from the original All Item column.
- If it was manual, you will need to manually populate it.
Next, populate the Flag columns with a Yes [in the column(s) to which the row belongs. The name of the item in the row should populate the corresponding column. For example, if you put a Yes in the Bal Sht Flag column, the account should appear in the Balance Sheet Column. Remember to Save your work.
Create D-Lists that are a Subset of the All Items D-Dist – Now you will create D-Lists that represent the subset of the All Items D-List based on the values in the cube you just created.
Create/Update the Subset D-List(s) using the Import from D-Cube Data. Then select the appropriate “calculated item(s)” to import. (Don’t grab the flags). In our example, we select the Balance Sheet item so we can create the D-List for just the Balance Sheet items.
Ensure the All Items D-List is the row dimension and the Calc D-List is the Column.
Finally, ensure the Suppress Zero Rows box is checked - The first column shown is the row column from the D-Cube. In most instances, it should be skipped when selecting attributes. (The where setting depends on what type of order is needed for the d-list: alphabetical, hierarchical, etc.)
Congrats! You have set up the D-Lists needed. Also created is the D-Cube to define which accounts should be assigned to your new subset D-List. Furthermore, you can modify your subset D-List by just updating the information in the cube. Use this method to simplify D-List maintenance with a D-Cube in Cognos Planning. It can save you time and frustrations.
"What's the next step for us concerning Cognos Planning?"
It’s now time to talk more about your time and frustrations with Cognos Planning! Did you know that IBM has a much more powerful tool? One that handles much larger models! A tool that doesn’t require time consuming Sync and Go To Production processes. IBM Cognos Performance Management TM1 is an extremely powerful tool. It allows for an even easier way to maintain your account structures. Here at Lodestar Solutions, we feel the future of Cognos Planning is in question! IBM’s lifecycle page tells you how long IBM will support IBM Cognos Planning.
We encourage you to start considering and defining your migration to a better tool. To learn the benefits of TM1 over Cognos Planning check out our blog.
Lodestar Solutions wants you to be educated, so we are creating a library of videos that will help you evaluate TM1. Check out our Move to TM1 Program and remember that there is NO COST!
If you have specific questions, please contact us at Coaching@lodestarsolutions.com and one of our Analytic Coaches will get back to you.
If you learned something from this blog, we encourage you to refer back to the previous tips in this series....
Check out Tip #1 on what your future plans should be for Cognos Planning by CLICKING THIS LINK.
For Tip #2 on the questions most asked by Cognos Planning clients, learn more by CLICKING THIS LINK.