Revelwood Technical Bulletin

Revelwood Technical Bulletin TM1 View Command Audience: This technical bulletin is intended for TM1 power users or TM1 administrators who create reports and reporting templates within their organization. It is expected that the reader is familiar with DBR, DBRW, and DBS worksheet functions. Description: This function creates an optimized view of the cube by minimizing the area where TM1 needs to search in order to populate a report with values. Instead of referring to an entire cube, the VIEW command creates a subsection of the cube for use with TM1 based Excel functions. Therefore, the area that TM1 needs to search is much smaller. All DBR and DBRW formulas that refer to the VIEW function can then access this optimized view. In this way, results are returned much faster. The syntax for the VIEW command is very similar to the DBRW command, with one exception. Any values that are not “singled out” (i.e., only use one element for that dimension) are referred to with an exclamation point ( ! ). For example:  A cube exists with five dimensions. Sales Cube – region, product, category, period, sales measure Revelwood Inc. 14 Walsh Drive Suite 303 Parsippany, NJ 201.984.3030 201.984.3031 fax Revelwood Technical Bulletin  In the worksheet above, the cube name is stored in cell A1. o Dimension 1 = region. Multiple regions are listed as row headers. o Dimension 2 = product. Only one Product element is used (A2). o Dimension 3 = category. Only one Category element is used (A3). o Dimension 4 = period. Multiple periods are listed as column headers. o Dimension 5 = sales measure. Only one Sales Measure element is used (A4). If we create a DBRW worksheet function in the data grid (cells B7 through D9) that refers to the cube in cell A1, then TM1 will search the entire cube for the results even though you are only interested in a very specific area of the cube. This means that it will search all products, categories, sales measures, periods, and all regions, then present the selected values in the report. We can use a VIEW command to tell TM1 to only look at data points associated with one product, one category, and one sales measure, but still look through all months and all regions. We would do this by creating a formula in a new cell with the following VIEW command: =VIEW($A$1,“!”, $A$2, $A$3,“!”, $A$4, ) The previous formula tells TM1 to create a view using the source cube for all regions, one product, one category, one sales measure, and all periods. You would then ensure that the DBRW formulas referenced the cell with the VIEW command instead of the cell with the cube name. Revelwood Inc. 14 Walsh Drive Suite 303 Parsippany, NJ 201.984.3030 201.984.3031 fax Revelwood Technical Bulletin Revelwood Recommendation: Use the VIEW command for all DBRW references within your TM1 Excel reports. In the example below, the cube reference for the DBRW formulas (cell B1) is, in fact, the VIEW function which pre-calculates the area of the cube referenced by the report. Please NOTE: In some rare instances there are times that the Excel worksheet will calculate more efficiently without the use of the VIEW command. These instances tend to be when the dimension being displayed as the rows or columns is extremely large and the size of the cube being references is extremely large. If your data model has these characteristics you made need to test with and without the VIEW command to determine the best methodology to use. Presented by: REVELWOOD PROFESSIONAL SERVICES GROUP 201-984-3025 Author – Lee Lazarow, Senior Consultant Revelwood Inc. 14 Walsh Drive Suite 303 Parsippany, NJ 201.984.3030 201.984.3031 fax