Loading Data into a Cube using IBM Planning Analytics Workspace

By: James Miller

January 13, 2021

IBM Planning Analytics stores data that you need for your planning, analysis, and other reporting in multidimensional cubes. Each cube typically has a specific purpose. A cube must have at least two dimensions. The number of dimensions that there are in a cube depends on the purpose of the cube.

Loading a Cube

Once you have a cube modeled and “built”, it is highly recommended that you load data into the cube as part of the cube design validation process. To do that, you can create a TurboIntegrator (TI) process and a TI chore so that you can initially load data into the cube but also schedule regular data updates to the cube (yes, its “normal” to periodically update data during the development phase of a project, so having a TI process and TI chore are very helpful).

Cube Loading Alternatives

Rather than taking the time (say, before the cube’s dimensionality is “set”), to build a load process there is an easier way (assuming you are using Planning Analytics Workspace or PAW). That is, Modelers (i.e. users who have been assigned the Modeler or Administrator role in PAW) can import data directly into a cube by simply “dragging” a text or csv formatted file directly onto a cube in the content tree (or by right-clicking the cube in the content tree and select Import data). Doing this also includes options to clear and load or update data.

Note: You must be a member of the ADMIN group for the underlying TM1 database to be able to import data into cubes.

See: https://www.ibm.com/support/knowledgecenter/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_prism_gs.2.0.0.doc/t_paw_modeling_add_users_ug.html?view=kc for instructions.

Having the ability to drag and drop data files from your desktop directly into a prototyped cube is a huge timesaver when evaluating a cube design and, using PAW, there is the ability to “drop” files as part of other tasks – such as developing a TurboIntegrator process.

Data Setup

As an example, suppose we have created a cube named Finance, made up of seven dimensions: Version, Entity, Department, Project, Year, Period and Account. Using MS Windows notepad, we can put together a simple file of data with fields separated by commas. Below is a sample of a file showing four rows or records of data:

It is important that the data you want to import be formatted as correctly as possible, noting the correct number of fields and valid values for each of those fields, for example, although as we’ll see, you will have some opportunity during the import, to map or otherwise “connect” fields to dimensions (to find out more, see “Dimension import file formats” online at https://www.ibm.com/support/knowledgecenter/SSD29G_2.0.0/com.ibm.swg.ba.cognos.tm1_prism_gs.2.0.0.doc/c_paw_cube_import_format.html#concept_jr4_kcz_kcb.

Dropping the File

After you have a file set up, you can select the file with your mouse, move the mouse, which will “drag the file” along with it (be sure to hold down the right mouse button the entire time) and “drop it” onto the cube you want to load:

Import Preview

Once you drop your file, you will see an import data file preview dialog (like the one shown below) where you can verify and/or update the parameters: selected file name, the delimiter (field separator), the files quote character and (number of) header rows.

Once the displayed parameters look correct, you can click Continue.

Mapping

Next, you’ll see a “source column” to “cube dimension” mapping, which you can change, as well as some Cube import settings that control various data load “behaviors”. You even have the ability to save your choices as a TI process which you can run later.

Until all of “mapping” values are selected, the import is disabled, and you will not be able to go forward (although you can Cancel or go Back to the preview dialog). Once you have mapped each source column appropriately and you are satisfied with your mappings and “behavior settings” you can click Import data (which should now be enabled).

Import Results

Once you click import data, Planning Analytics will attempt to import your data. After the processing is completed, you will see the Cube import completed popup:

This is a handy dialog since it not only indicates that the load has completed, but it also provides load statistics: Rows read (from the file) but also the number of cube cells updated as well as any errors the process encountered. In our example we see that 5 rows were read (1 header row and 4 data rows) but only 3 cube cells were actually changed. The data row that was not loaded was due to the fact that it contained an invalid value (“Element Name (Key)”) for Entity.

Next Steps

Of course, in this example you might go back and remedy the invalid data in the file and then rerun the process. Another option might be to have elected to Save this import, and saved the process as (for example) “FinanceLoader:”:

Then, from the content tree, you can right-click the process name and select Edit process:

The newly saved process is added to the current sheet and is placed in an edit mode for you. From there, you can preview the processes “Data Source tab” showing a sampling of the files data:

If you are more familiar with looking at processes from within TM1 Architect (shown below) you’ll notice some similarities:

The real differences between looking at TurboIntegrator processes in Architect vs PAW, is with the Script view (versus the Advanced tab in architect). Clicking on the “Script” tab in PAW, you see a much more advanced “environment”:

The Planning Analytics Workspace (PAW) process editor includes numerous features that simplify the creation and validation of TurboIntegrator processes – many of the features are NOT available when editing processes using Architect.

To find out more about the PAW process editor, you can go to:https://www.ibm.com/support/knowledgecenter/SSD29G_2.0.0/com.ibm.swg.ba.cognos.pa_trial.2.0.0.doc/r_paw_use_the_process_editor.html.

Ask QueBIT

Want to discuss how best to perform other prototyping and development tasks within the PAW interface? Have a different question? You can always reach out to QueBIT at support@quebit.com for assistance. We’re here to help!

QueBIT’s Analytics Online Instructor-led Training Courses

  • IBM Planning Analytics Developer
  • IBM Planning Analytics Advanced Reporting
  • IBM Planning Analytics Advanced Developer

Visit learn.quebit.com

QueBIT’s Analytics Online Instructor-led Training

  • IBM Cognos Analytics Authoring Reports Fundamentals
  • IBM Cognos Analytics Authoring Reports with Multidimensional Data
  • IBM Cognos Analytics Authoring Reports Fundamentals

Visit learn.quebit.com

Subscribe to QueBIT Notifications

Can’t find what you’re looking for? →

    Contact Help Desk

    This form collects your contact information so that we can correspond with you. For more information on how we are committed to protecting and respecting your privacy, please review our Privacy Policy.

    I consent to QueBIT collecting my contact information.