By Craig Harding, Consultant for PerformanceG2
 
 
Conditional Blocks

The goal of this report is to prompt users on which display they would prefer, a list or a chart.

To start, create a simple list report in Report Studio. Using the Cognos sample data, product line, order method and revenue are added to the list. From the toolbox, drag and place a pie chart below the list. Select the chart and in the properties pane, attach this chart to query 1:
 

 
 
Now the chart can be populated from the data items tab.
 

 
 
The consumers of this report need the ability to choose between the displays they want to see. To accomplish this, build a blank prompt page allowing them to select from a drop down list either list or chart. Drag a value prompt to the prompt page with the following settings:
 

 
At this point hit ‘Finish”. The dropdown list has to be populated manually though the static choices property of the value prompt. The two choices added are list and chart.
 

 

 
*You can leave the display value blank. If you do so, Report Studio is smart enough that it will take the use value for the display value.

Now that my prompt page is set, return to the Report Page. In order to conditionally display an object, use the conditional blocks object in the toolbox. Drag this to the bottom of the report.
 

 
 
The next step is to attach the variable to the conditional blocks block variable property. This variable is a Boolean variable that either equals a list or doesn’t.
 

 

 

 
 
We need to test if our parameter is equal to the value ‘List’. If the user selected List this expression will evaluate to true, if the user selected ‘Chart’ from the prompt, then this will evaluate to false.

After this step is completed there is a new property on the conditional blocks object that tell us which block we are currently looking at. First, drag the chart into the ‘No’ block.
 

 

 
Now, select the conditional block again, and switch the current block property to ‘Yes’. The conditional blocks object appears empty because there is currently nothing in the ‘Yes’ block.
 

 
The final step is to drag the entire list inside the ‘Yes’ block.
 

 
Now when the report is run, the user will be prompted for their display of choice.
 
 
Contact the Blogger

 
By Sanjeev Datta, Consultant for PerformanceG2
 
There are multiple ways of enhancing the performance of TM1 using tools and techniques. This week for my 3-part blog series, I will be discussing Spreadsheet Techniques.

 
Spreadsheet Techniques

Following are techniques used on spreadsheets to enhance performance on TM1 servers:
 
DBR: This is a worksheet function valid only in worksheets. It retrieves a value from a specified TM1 cube and can be used to write values to the database when all the arguments are element values.
Syntax: DBR(cube, e1, e2,[...en])

 
DBRW: This is a worksheet function valid only in worksheets. This function is similar to the DBR function but is used to reduce network traffic and hence is extremely useful on Wide Area Networks. When implemented, DBRW function forces TM1 to use “bundles” rather than individual read/writes from/to the database.
Syntax: DBRW(cube, e1, e2,[...en])

 
ELCOMP: This is a worksheet function valid only in worksheets. It returns the name of a child of a consolidated element in a specified dimension. It is similar to the DBR function in that it retrieves a value from a specified TM1 cube but it also results in a round trip between the server and Excel.
Syntax: ELCOMP(dimension, element, index)
 
Dimension Ordering and Cube Optimizer: Dimension ordering can have significant impact on memory consumption as well as recalculation times in a TM1 server environment and its best to sort dimensions in 2 categories: Sparse & Dense Dimensions before creating cubes.

A sparse dimension would be Products & Regions, for example, where not every products is sold in every region. On the other hand, a Dense Dimension would be a month/time where you will always have a Budget amount in every month of the year.

As a general recommendation, the ordering of dimensions should be: smallest sparse to largest sparse followed by smallest dense to largest dense.

In practice, design the cubes with dimensions in “natural business” order and then use the Dimension Optimizer as necessary. Re-ordering does not break the “DB” references.

As a best practice, use the time and measures dimension at the end always.
 
Cube Optimizer: The Cube Optimizer feature in TM1 Server Explorer lets you optimize the created cubes to consume less memory and improve performance. Over time as Business needs and Dimensional priority change, cube optimization is useful especially as re-calculation in TM1 is RAM-based i.e. “on-the-fly”.

The internal re-ordering of cube dimensions in TM1 is valuable for tuning sparse cubes, large dimensions or simply large cubes. As a note, dimensional ordering and cube optimization require twice the amount of the cube size. For example, if a cube size is 50MB, 100MB must be available in memory to perform cube optimization.

 
Other TM1 Techniques

Calculations are defined implicitly by dimension hierarchies and are an order of magnitude faster than Rules and should be used whenever possible.

Rules on the other hand, allow defining any cell as an arbitrary calculation of any other cells in any cube.

For best practice, place calculations you expect to change often in rules, even though the “math” could be done faster with consolidations.

Reduce over-feeding by using Conditional Feeders when using TM1 Rules.

 
 
Contact the Blogger