=VIEW() and #REF!

[CiskoWalt]

HR Cube View
SERVER: development:
CUBE: HR
VIEW: development:HR
development:entity 280-American Movie Classics

HR Cube View
SERVER: development:
CUBE: HR
VIEW: =VIEW("development:HR","!",$C$7,"!","!","!","!","!")
development:entity =SUBNM("development:entity","","280","code_name")

Changed "development:HR" to a cell reference where $C$4 = 'development:' and $C$5 = 'HR'

HR Cube View
SERVER: development:
CUBE: HR
VIEW: =view($C$4&$C$5,"!",$C$7,"!","!","!","!","!")
development:entity =SUBNM("development:entity","","280","code_name")

Result


HR Cube View
SERVER: development:
CUBE: HR
VIEW: #REF! development:entity 280-American Movie Classics


Why can't I modify any of the variables of the =VIEW function? I would like to change the server from budget to prod by changing text in cell C4?

[paulsimon]

Walt

I tried that VIEW function with the Server and Cube as Cell Refs, in 9.1 sp3 and it worked fine. Are you only changing the server:cube in the VIEW function? Is the server still the same? This would rule out any differences in the cubes across servers.

While this should work on a View function, it doesn't on a SUBNM. It has been a long standing issue that, if you do this in a SUBNM the SUBNM will still return a value, but will lose its ability to launch a Subset Editor. Therefore even if you parameterise the Cube name to allow the Server to change, you will probably still have to use a Find and Replace to change the server name in all the SUBNMs. You therefore might as well use Find and Replace for all.

The only other alternative I have found is to keep the Server name the same on both Prod and Dev, but to register the Prod and Dev Servers on different Admin Hosts. Then, simply by changing the Admin Host in your TM1 Options, you can get to either the Dev or Prod Server (Most users will only want the Prod Server anyway - it is probably only the developers who need to swap between the two). In this way you can move any spreadsheets that you have developed from Dev to Prod, without the need for any changes. There is however a potential for confusion. This can be safeguarded to a certain extent by using different User Ids on Dev and Prod.

Unfortunately, TM1 is not alone in the OLAP world in not providing good support for promoting code from Dev to Prod. In fact, the relational world isn't that wonderful at it either.

Regards

Paul Simon

[CiskoWalt]

Paul,

Thanks for your help.

My goal is to improve performance over the WAN. Am told to change DBRx and DBSx to DBRW and DBSW. Was also told to use VIEW instead of a hard-coded reference to the cube (server:cube)

Thought was to create one Worksheet with views to cubes and have all other Wortksheets refer to these views in the DBRW and DBSW functions. The VIEWS could be paramaterized using SUBNM functions. So if you are only working in the current year (2008)and using one Entiy (280) and one BalType (ORIG) pick values from the SUMBN list boxes (rows 8,9, and 10 below)

Cube: PLAN

DIM1 Entity
DIM2 Dept
DIM3 GLACCT
DIM4 PLanYEAR
DIM5 Category
DIM6 BalType
DIM7 Time

Column C
3 Plan Cube View
4 SERVER: development: <= Hard Coded
5 CUBE: plan <= Hard Coded
6 SERVER/Cube: =CONCATENATE(pServer,pCube) <= formula resolves to development:plan
7 VIEW: =view($C$6,$C$9,"!","!",$C$8,"!",$C$10,"!")
8 =+pServer&"PlanYear" =SUBNM("development:PlanYear","","2008") <= Pick List
9 =+pServer&"entity" =SUBNM("development:entity","","280","code_name") <= Pick List
10 =+pServer&"BALTYPE" =SUBNM("development:BALTYPE","","ORIG") <= Pick List


The =View function is as follows: =view($C$6,$C$9,"!","!",$C$8,"!",$C$10,"!")

Where Parameter 1 $C$6 = "development:plan" [server:cube]
Parameter 2 $C$9 = "280-American Movie Classics" [the entity]
Parameter 3 "!" [all dept]
Parameter 4 "!" [all glacct]
Parameter 5 $C$8 = "2008"
Parameter 6 $C$9 = "!" [all category]
Parameter 7 $C$10 = "ORIG"
Parameter8 "!' [all time]

The result #REF!

Thanks,

Walt

[rmackenzie]

The #REF! error is an Excel error telling you one of your cell references (in the VIEW formula) is bad, for some reason.

If you click on the cell with the =VIEW formula you can highlight a cell reference in the function and press F9 to immediately see it's value. You ought to be able to find the dodgy reference by trying this method on each one until you hit it - then you'll know what to correct.

[Steve Rowe]

Your advice on the using the view function might be a little off target.

My understanding is that (in theory) the view function can be of benefit when you change one of the page references via the subnm formula. The view function is a reference to what they call a "stargate view" an object that exists in the server that contains the different "pages or slices" of the view that you are looking at.

Since this stargate view needs to be pre calculated there is an overhead on first look but in theory a performance gain when the different slices of the view are looked at in Excel. This obviously doesn't work if the data is changing often since TM1 can't cache the results. I seem to remember that if the view contains many rows and or columns the effect of this view function was supposed to negligible.

That's the theory as I remember it. In practice I've never seen a workbook benefit from the view function, but I have seen them benefit from it's removal.

I don't hear stargate views talked of anymore, can anyone confirm that they still exists in the 9.1+ servers with the new object model?

HTH
Steve

[CiskoWalt]

Thanks Gents,

I selected one element at a time and pressed F9

Original formula: =view($C$6,$C$9,"!","!",$C$8,"!",$C$10,"!")

1st: =view("development:plan",$C$9,"!","!",$C$8,"!",$C$10,"!")
2nd =view("development:plan","280-American Movie Classics","!","!",$C$8,"!",$C$10,"!")
3rd: =view("development:plan","280-American Movie Classics","!","!","2008","!",$C$10,"!")
5th: =view("development:plan","280-American Movie Classics","!","!","2008","!","ORIG","!")

Contine to get #REF!

Perhaps there is a bug in 8.43

Since Steve does not think there is a benefit to this and it seems to be fragile, I will not use it.

Thanks,

W

W

[CiskoWalt]

I figured it out.

Embarrassed to tell you that the user's Excel Workbook had a range named 'view'

Best,

walt

[Steve Rowe]

Will be interested if you can identify any performance benefits.