DBRW = *Key_Err and DBR takes too long

wkinki

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

wzsycdh

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.

AM哥

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

jxs0103

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.

wcb79

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

Esuom

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.

zerofeng111

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.

wyjzcom

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).

315196952

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.