企业绩效管理网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 443|回复: 5

ViewConstruct not helping me speed things up in Exce ...

[复制链接]

82

主题

406

帖子

593

积分

高级会员

Rank: 4

积分
593
QQ
发表于 2014-3-20 02:59:13 | 显示全部楼层 |阅读模式
I have some pretty large spreadsheets that I update monthly. If I simply open one up, it takes approximately 15 minutes to calculate as it is full of DBRW formulas. They DBRW's are very similar and reference a very specific set of data.

I went into Architect and created a view that contains these same values for dimensions, unchecked the "Skip Consolidated Values" and "Skip Zero/Blank" as well to be safe. I then threw a ViewConstruct('mycube','myview'); in an epilogue of a process and ran it. It only takes about 10 seconds to run which has me a bit concerned. Naturally, when I open my Excel sheet, I'm still looking at the full 15 minutes to update so something isn't working for me.

Do you have any tips as to how to successfully use ViewConstruct to precache large sets of data? I really need to speed this up for our reporting process and I'm a bit stumped. Thanks
回复

使用道具 举报

79

主题

383

帖子

562

积分

高级会员

Rank: 4

积分
562
QQ
发表于 2014-3-20 04:46:43 | 显示全部楼层
ChrisF79 wrote:I have some pretty large spreadsheets that I update monthly.  If I simply open one up, it takes approximately 15 minutes to calculate as it is full of DBRW formulas.  They DBRW's are very similar and reference a very specific set of data.

I went into Architect and created a view that contains these same values for dimensions, unchecked the "Skip Consolidated Values" and "Skip Zero/Blank" as well to be safe.  I then threw a ViewConstruct('mycube','myview'); in an epilogue of a process and ran it.  It only takes about 10 seconds to run which has me a bit concerned.  Naturally, when I open my Excel sheet, I'm still looking at the full 15 minutes to update so something isn't working for me.

Do you have any tips as to how to successfully use ViewConstruct to precache large sets of data?  I really need to speed this up for our reporting process and I'm a bit stumped.  Thanks

Two suggestions:
1/ Make sure that the calculation mode in Excel is manual, not automatic.
2/ If the cube reference is hard coded, take a look in the Reference Guide for what the  function. It doesn't always help but I've had some reports show astronomical improvements in calculation time when changed from hard coded cube references to a View function.

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复 支持 反对

使用道具 举报

91

主题

407

帖子

615

积分

高级会员

Rank: 4

积分
615
QQ
发表于 2014-3-20 05:03:33 | 显示全部楼层
Thanks for the fast reply.  Calculation mode is set to manual.  I'll open a file and hit F9 and sit back and wait.  It is incredibly tedious.

So looking at the VIEW function, I would be using that in lieu of DBRW's?
回复 支持 反对

使用道具 举报

70

主题

390

帖子

554

积分

高级会员

Rank: 4

积分
554
QQ
发表于 2014-3-20 05:56:58 | 显示全部楼层
ChrisF79 wrote:Thanks for the fast reply.  Calculation mode is set to manual.  I'll open a file and hit F9 and sit back and wait.  It is incredibly tedious.

So looking at the VIEW function, I would be using that in lieu of DBRW's?

No, the View function is used to define, for want of a better term, a virtual mini-cube based on the fixed (title) elements. It replaces the cell which contains the cube name. (Or if you have the cube name hard coded in your DBRWs, replace the hard coding with a reference to a cell that contains the View function.) The DBRWs then look to that mini-cube rather than needing to sift through the whole thing.
回复 支持 反对

使用道具 举报

83

主题

421

帖子

617

积分

高级会员

Rank: 4

积分
617
QQ
发表于 2014-3-20 05:57:10 | 显示全部楼层
Thanks.  I'll definitely give that a shot.
回复 支持 反对

使用道具 举报

76

主题

396

帖子

582

积分

高级会员

Rank: 4

积分
582
QQ
发表于 2014-3-20 06:07:38 | 显示全部楼层
ChrisF79 wrote:So looking at the VIEW function, I would be using that in lieu of DBRW's?
No. You would be using  VIEW in place of or in addition to a harecoded string reference for the server:cubemane.

As Alan says it is not always a silver bullet but I have seen many reports recalculate.in 5 second or less after properly implementing view formula(s) versus over 5 minutes without.
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|小黑屋|企业绩效管理网 ( 京ICP备14007298号   

GMT+8, 2018-12-16 15:45 , Processed in 0.153053 second(s), 12 queries , Memcache On.

Powered by Discuz! X3.1 Licensed

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表