企业绩效管理网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 521|回复: 8

DBRW = *Key_Err and DBR takes too long

[复制链接]

69

主题

365

帖子

511

积分

中级会员

Rank: 3Rank: 3

积分
511
QQ
发表于 2014-3-19 07:24:24 | 显示全部楼层 |阅读模式
Hi all,

I am working with 9.5.2 and excel 2007.

I have an active form with rates and units down the rows and days across the columns. At the top of the active form the user can select a supplier from a drop down list. This active form was working fine until yesterday.

What is happening:

when the first supplier is selected "unallocated supplier" the DBRW's in the columns work as they should, displaying the results and allowing the user to input. If any other supplier is selected from the drop down the *Key_Err message is displayed in every DBRW cell. If you click on the cell and press F2 and enter the correct value for the cell is displayed - until you refresh or rebuild the active form in which case it reverts back to *Key_Err.

What I have tried:

1. I have used the Trace TM1 Formula tool - it displays no errors.
2. I have checked that every supplier in the drop down is an element of the dimension.
3. I have changed the DBRW formula to DBR. This brings back the correct results, however as this is a daily report displaying every day in the quarter (90+ days) the report takes 17 seconds to refresh as opposed to 3 seconds when DBRW's are used. As this is an input sheet this would be unacceptable to end users.
4. I have changed the first reference in the DBRW which refereneces the cube to hard coded. Eg:
before the formula read:

=DBRW($B$9,$F$15,$H$15,$B383,$B$563,$A$563,X$379,$C383,$C$2,X$382)

now it reads:

=DBRW("tm1_tcms:Daily Report",$F$15,$H$15,$B383,$B$563,$A$563,X$379,$C383,$C$2,X$382)

When I have had this problem in the past doing this has solved the problem. However - not this time.

If anyone has any suggestions as to what else I could try I would be most grateful.

Cheers
回复

使用道具 举报

76

主题

403

帖子

583

积分

中级会员

Rank: 3Rank: 3

积分
583
QQ
发表于 2014-3-19 09:16:01 | 显示全部楼层
Hi tosca,

This sounds like either a circular reference error or a dependent DBRW formula issue. I think the giveaway is that it worked when you changed it to DBR (because the formulas refresh individually, rather than in a group, this means that if one of the cells depends on a DBRW formula reference it can obtain the value because it is not being refreshed simultaneously).

Try and locate the cell, possibly in the suppliers list, which is also a DBRW.
回复 支持 反对

使用道具 举报

72

主题

394

帖子

548

积分

中级会员

Rank: 3Rank: 3

积分
548
QQ
发表于 2014-3-19 09:28:22 | 显示全部楼层
Hi Chrostopher,

Thank you for your post - you were absolutely correct it is a dependent DBRW issue.

Until yesterday the user would select a Purchase Order No and a Supplier from a drop down. These then form the parameters of the units and rates that they send to the cube via DBRW. The source of the drop down lists were 2 lists of SUBNM formulas showing all the Purchase Order No's and all of the Suppliers. And the above worked fine.

However, yesterday I was informed that the PO No / Supplier is a 1-2-1 relationship therefore the user wants to select a PO No but wants the Supplier to be selected automatically. So I created a PO/Supplier look up cube. Then replaced the Supplier SUBNM formula with a DBRW to the look up cube to select the correct Supplier. Now, as you pointed out this won't work due to the dependent DBRW formula.

I have now created an Attribute of the PO_No dim called "Supplier" and edited the TI that updates the PO_No dim to populate the supplier attribute. I have changed the DBRW formula to DBRA to bring the Supplier into the worksheet. I have checked and the DBRW that references the DBRA works fine and I do not get *Key_Err.

Just to confirm (even though it seems to work)...the dependent DBRW issue does not exist in the same way of the DBRW is referencing a DBRA formula??

Thanks very much for your help Chistopher - I wouldn't have know about the dependency issue otherwise.

Cheers
回复 支持 反对

使用道具 举报

71

主题

366

帖子

474

积分

中级会员

Rank: 3Rank: 3

积分
474
QQ
发表于 2014-3-19 09:45:11 | 显示全部楼层
tosca1978 wrote:I have now created an Attribute of the PO_No dim called "Supplier" and edited the TI that updates the PO_No dim to populate the supplier attribute. I have changed the DBRW formula to DBRA to bring the Supplier into the worksheet. I have checked and the DBRW that references the DBRA works fine and I do not get *Key_Err.
You didn't have to do that, you could have left the lookup in a regular cube. You just needed to change the formula that pulls from the lookup cube to a DBR instead of a DBRW.
回复 支持 反对

使用道具 举报

86

主题

396

帖子

578

积分

中级会员

Rank: 3Rank: 3

积分
578
QQ
发表于 2014-3-19 09:59:46 | 显示全部楼层
Hi Tomok,

Thanks - thats good to know. That would have been a lot easier/quicker than going down the attribute route and I guess using the DBR formula wouldn't have hit performance too much as it's only one column with a few hundred elements in it.

Given that they both deliver the same result I will probably change the DBRA to DBR. The reason being is that the Look Up Cube itself is quite useful for end users to use in isolation for a quick check on the PO/Supplier combination.

Cheers
回复 支持 反对

使用道具 举报

86

主题

415

帖子

595

积分

中级会员

Rank: 3Rank: 3

积分
595
QQ
发表于 2014-3-19 10:25:39 | 显示全部楼层
Hi tosca,

You are very welcome, and thanks for writing such a clear, detailed issue description. Others could learn a lot by following this format.
回复 支持 反对

使用道具 举报

77

主题

392

帖子

547

积分

中级会员

Rank: 3Rank: 3

积分
547
QQ
发表于 2014-3-19 10:53:55 | 显示全部楼层
Another reason to use DBR rather than DBRA is that DBR evaluates faster. (Just for completeness, DBRW is fastest (but can't be used in this case), then DBR, then DBRA.)

So should you keep your Attribute rather than reverting to the lookup cube, it will be faster to DBR onto the }ElementAttributes_ cube than to use DBRA.
回复 支持 反对

使用道具 举报

67

主题

355

帖子

505

积分

高级会员

Rank: 4

积分
505
QQ
发表于 2014-3-19 11:44:54 | 显示全部楼层
Andy Key wrote:Another reason to use DBR rather than DBRA is that DBR evaluates faster.
Where did you draw that conclusion from and how certain are you it's true?

I have run some tests and, if anything, in my particular environment DBRA seems to be marginally faster than DBR for large datasets (thousands of dimension elements times a dozen different attributes or so).
回复 支持 反对

使用道具 举报

70

主题

353

帖子

518

积分

中级会员

Rank: 3Rank: 3

积分
518
QQ
发表于 2014-3-19 11:52:27 | 显示全部楼层
Got the info direct from an IBMer that I trust to know what they're talking about.

No idea if it depends on version or interface. The IBMer knew that we were using 9.5.2 FP1 via Active Forms and TM1Web.
回复 支持 反对

使用道具 举报

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

本版积分规则

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

GMT+8, 2018-4-27 14:53 , Processed in 0.227401 second(s), 11 queries , Memcache On.

Powered by Discuz! X3.1 Licensed

© 2001-2013 Comsenz Inc.

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