TM1 Forum

Hi,

I need to create a consolidated in template in tm1(active form/Slice report) data to this comes various tm1 cube.
Wanted to if some one can share best practics for doing this.

Regards,
Ravi

Hi Ravi,

The problem that you are going to find with this one is that everybody has their own prefered way of doing things. I'm not sure there is a standard for this. Let's what everybody else thinks.....

Jim.

I half agree with Jim. It's a very general question and there are always multiple ways of achieving the same result, and "best practice" is a pretty loose and slippery concept.

... however the scope of the question is somewhat limited to best practices in building EXCEL REPORTS and I think it is possible to make a few "best practice generalizations" in this area, AND I'm interested in what opinions others might have. So in the interests of getting the ball rolling here are some Excel/TM1 best practices that immediately come to mind (in no particular order.)

- Use the correct VIEW formula (or multiple VIEW formulas) for the report
- Use DBRW not DBR
- Make sure all DBRW formulas contain ONLY the DBRW and no IF statements or multiply or divide by any constants
- Avoid DBS, DBSW, DBSS
- Avoid using VBA
- Parametize server name references
- Limit the number of rows used to something sensible
- Limit the number of sheet tabs used to something sensible
- Ensure your users are using manual calculation mode in Excel (with "calculate before save" switched off)
- Set TM1RebuildOption to the appropriate value for the use case of the particular report (usually False IMO)

For Active Forms there are a few more pointers I would add
- For active forms avoid referencing dynamic subsets and use the MDX argument instead
- For active forms delete all rows in the data range except for the 1st row that contains the TM1RptRow formula when saving
- For active forms that reference different parts of the same cube or data in ohter cubes write separate appropriate VIEW formulas
- Ensure when combining different active forms into a single workbook that all data ranges have unique names and the name is correctly paired with the reference in each TM1RptView formula
- Limit the number of active forms in a tab to 2 (or preferably 1) and likewise limit the number of active forms within a workbook

A good start Lotsa. The only point I would add to that is that I would look to build in the viewer as close to what you want from your report first. This can reduce the amount of shifting around that you do in Excel. I guess this is more for slices than active forms,

Jim.

Lotsa ,

further to your comment "Set TM1RebuildOption to the appropriate value for the use case of the particular report (usually False IMO)" .
Appreciate your and everybodys thoughts on this one.

A couple of our Excel Sheets ( Accessed via TM1 Applications), open and retrieve from our client PC's their Budget Data within 5 seconds ( SLA ) ( all clients are in the same building ). However the first time the user connects and open these Excel Sheets this process takes approx 10 seconds.

This is largely due to additional Calculation thats happening just after the user connects to the Server.

I have set the TM1REBUILDOPTION=0 at the WorkBook Level ( 3 Sheets in All ) and TM1RebuildDefault=F in the clients Tm1p.ini file.

Enviroment is
Cognos Express Xcelerator 9.5.1

Client Machine run
XP, 3.32GHZ with 4GB Ram and Excel 2007.

Cheers
Afshin

lotsaram wrote:I half agree with Jim. It's a very general question and there are always multiple ways of achieving the same result, and "best practice" is a pretty loose and slippery concept.

... however the scope of the question is somewhat limited to best practices in building EXCEL REPORTS

I read it more narrowly than that. I think that the OP was asking about best practices for creating reports from multiple active forms. However since we're here...

lotsaram wrote:and I think it is possible to make a few "best practice generalizations" in this area, AND I'm interested in what opinions others might have. So in the interests of getting the ball rolling here are some Excel/TM1 best practices that immediately come to mind (in no particular order.)

- Use the correct VIEW formula (or multiple VIEW formulas) for the report

Agree muchos. This can have a huge, and I mean huge impact on performance in my experience.

lotsaram wrote:- Use DBRW not DBR

Subject to the condition that the formula not be used as an argument to another TM1 DBRW formula. If it is, the first formula has to be DBR.

lotsaram wrote:- Avoid DBS, DBSW, DBSS

Not sure that I agree but it depends on the context. If it's an output report which just sends some values back up as well, I agree. If it's an input template, I'd much rather have DBSW formulas separated from the input cells than have users punch straight into DBRW formulas. Ther latter approach leaves the formulas too vulnerable to damage (especially unwitting deletion) in my experience, and also having the DBS's separated gives the opportunity to have a "Commit" button on the sheet that allows all of the values to be sent up only when the user is happy with them.

lotsaram wrote:- Avoid using VBA

Disagree, unless the files are intended to be deployed by Web. It's just too useful to ignore it if Excel is available IMHO. I would, however, say that to improve maintainability (a) A lot of standard functions for the workplace should be incorporated into a standard module or modules which can just be plugged into the workbook so that each report is not a "unique experience" for any new developers and (b) There should be strong coding standards in place.

lotsaram wrote:- Parametize server name references
- Limit the number of rows used to something sensible
- Limit the number of sheet tabs used to something sensible
- Ensure your users are using manual calculation mode in Excel (with "calculate before save" switched off)

Multiple yeses.

I'd also add:
- Never round numbers by dividing the formulas (see also Lotsa's point about avoiding including other functions in the the cell), but rather always use formatting;
- Create a template workbook with a standard group of styles (a much under-utilised feature of Excel) and apply formatting with styles, not manually. In this way if a report that was previously in millions suddenly has to be given in thousands, you can change the style and every cell which uses it in that workbook automatically changes. Using a standard set of styles alos prevents your reports from having that "Collection of Ransom Notes" look, which is less than fetching.

Hi lotsaram.


You mentioned that below 2 Points:
-Use the correct VIEW formula (or multiple VIEW formulas) for the report
- Use DBRW not DBR

I currently working one cosolidated template - In this template- I have bring data from various cube- in that case using Use DBRW functions is better.

However, I having currently (*KEY_ERR ) issue - not sure what is the problem- Looked all connectors - but i did not figured out the issue.

any cluse?
Ravi

I have bring data from various cube- in that case using Use DBRW functions is better.

Can you elaborate on that?

However, I having currently (*KEY_ERR ) issue - not sure what is the problem- Looked all connectors - but i did not figured out the issue.

Surely you're not expecting fellow forum members to figure it out for you based on this one sentence of information you provided? Because right now all I can tell you is that the reason you're getting errors is that you are doing something wrong, but this isn't the answer you're expecting, is it?

ravi wrote:However, I having currently (*KEY_ERR ) issue - not sure what is the problem- Looked all connectors - but i did not figured out the issue.

Reasons could be:
- referencing one or more labels that are not listed as an element in the regarding dimension;
- referencing the labels in a different order than the actual cube dimension order (mapping the elements on the wrong dimensions);
- referencing the result of a DBRW formula in the problem DBRW formula (in this case change the inner DBRW to a DBR);
- referencing a numeric cell as an element in a dimension (in this case prefix the numeric cell value with a single quote).

Michel

Michel Zijlema wrote:

ravi wrote:However, I having currently (*KEY_ERR ) issue - not sure what is the problem- Looked all connectors - but i did not figured out the issue.

Reasons could be:
- referencing one or more labels that are not listed as an element in the regarding dimension;
- referencing the labels in a different order than the actual cube dimension order (mapping the elements on the wrong dimensions);
- referencing the result of a DBRW formula in the problem DBRW formula (in this case change the inner DBRW to a DBR);
- referencing a numeric cell as an element in a dimension (in this case prefix the numeric cell value with a single quote).

And the easy solution is to download TM1 Tools and use the Trace feature.

@Alan
Could you please elaborate or provide additional information/reference doc to implement the same?

Create a template workbook with a standard group of styles (a much under-utilised feature of Excel) and apply formatting with styles, not manually. In this way if a report that was previously in millions suddenly has to be given in thousands, you can change the style and every cell which uses it in that workbook automatically changes. Using a standard set of styles alos prevents your reports from having that "Collection of Ransom Notes" look, which is less than fetching.

I guess it must be nearly Halloween, hence the necromancy!

In Excel there is a format object called styles, which is a collection of different format settings, i.e. a green fill, bold, italic, 2 d.p., formatted to millions, could be held in a style called "good millions". This would then be applied in all your worksheets.

If you decide that you want to change all the "good millions" cells to have no fill then you would change the definition of "good millions" style rather than editing the format of the many cells where it has been applied.

random link.
https://computers.tutsplus.com/tutorial ... -cms-21041