Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  Performance Management Framework (PMF)    [SOLVED] Time key convert error in measure load

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Time key convert error in measure load
 Login/Join
 
Gold member
posted
PMF 5.1.2 Oracle

Custom time dimension, weekly granularity.

Measure has one dimension, Time.
Date Mapping: Source Data contains Time Dim.

Measure sample data looks good.
Trying to preview measure load data before load, but getting error

Looks like the code is trying to convert the measure file Date_Key (A4) to a Date (HYYMDs) field:
MEASURE_DATE / HYYMDs= MEAS_REG_STUDENTS.DATE_KEY ;

If I change the measure date field into HYYMDs in master file, and change the key data into an actual date, no error, but all record mismatches of course.

Error is shown on the bottom of the following "view source"
==============================
IS_DEFINED: N
SET MSG=ON
JOIN DATE_KEY IN meas_reg_students TO TIME_KEY IN TIMEHLD TAG T AS JT
SET MSG=ON
SET ALL=OFF
DEFINE FILE meas_reg_students CLEAR
END
DEFINE FILE meas_reg_students ADD
LEVEL_01_VALUE/A30=TIMEHLD.TIME_LEVEL01_VALUE ;
LEVEL_02_VALUE/A30=TIMEHLD.TIME_LEVEL02_VALUE ;
LEVEL_03_VALUE/A30=TIMEHLD.TIME_LEVEL03_VALUE ;
T_HASH/A94='T' ||'/' || LEVEL_01_VALUE ||'/' || LEVEL_02_VALUE ||'/' || LEVEL_03_VALUE ;
T_HASH2/A3='T' ;
STATUS/A1 WITH DATE_KEY = 'A' ;
ACTUAL_NUMERATOR/D20.2 WITH DATE_KEY = 0.0 ;
ACTUAL_DENOMINATOR/D20.2 WITH DATE_KEY = 1.0 ;
BENCHMARK_NUMERATOR/D20.2 WITH DATE_KEY = 0.0 ;
BENCHMARK_DENOMINATOR/D20.2 WITH DATE_KEY = 1.0 ;
FORECAST_NUMERATOR/D20.2 WITH DATE_KEY = 0.0 ;
FORECAST_DENOMINATOR/D20.2 WITH DATE_KEY = 1.0 ;
STRETCH_NUMERATOR/D20.2 WITH DATE_KEY = 0.0 ;
STRETCH_DENOMINATOR/D20.2 WITH DATE_KEY = 1.0 ;
TARGET_NUMERATOR/D20.2 WITH DATE_KEY = 0.0 ;
TARGET_DENOMINATOR/D20.2 WITH DATE_KEY = 1.0 ;
BENCHMARK/D20.2 WITH DATE_KEY = 100.00 ;
FORECAST/D20.2 WITH DATE_KEY = 100.00 ;
STRETCH/D20.2 WITH DATE_KEY = 100.00 ;
TARGET/D20.2 WITH DATE_KEY = 100.00 ;
MEASURE_SERIES/I11 WITH DATE_KEY = 21 ;
END
DEFINE FILE meas_reg_students ADD
ACTUAL / D20.2 = MEAS_REG_STUDENTS.REGISTERED ;
MEASURE_DATE / HYYMDs = MEAS_REG_STUDENTS.DATE_KEY ;
(FOC227) THE FOCEXEC PROCEDURE CANNOT BE FOUND: MEMPCD07
END
0 ERROR AT OR NEAR LINE 1 IN PROCEDURE MEMPCD06FOCEXEC *
(FOC36346) INCORRECT USE OF DATE-TIME FIELD OR CONSTANT
BYPASSING TO END OF COMMAND
Failure in meas_loaderlook
==============

Thank you, Endre

This message has been edited. Last edited by: Endre,


WebFocus 7.6.8
iWay Data Migrator 7.6.8
PMF 5.1
 
Posts: 83 | Registered: March 28, 2006Report This Post
Master
posted Hide Post
Fromt the code you embed, it looks as if possibly the Field Maps for your Measure Load are trying to map the date directly from the date field.

This isn't possible in WebFOCUS - the date must be converted to HYYMDS.

A4s don't naturally convert to dates. You'll have to figure out the format of the date in that A4 and use WebFOCUS functions to convert it.


Bob Jude Ferrante
Director of Business and Development
WebFOCUS Performance Management
Bob_Ferrante@ibi.com
917-339-5105

I'll take any questions about PMF - business or technical - anytime!

 
Posts: 919 | Registered: March 26, 2003Report This Post
Gold member
posted Hide Post
Bob,

I was incorrect. I'm setting
"Date Mapping= Measure Dt determines Time Dim" in the Measure fields mapping.

This way I get the MEASURE_DATE field mapping window to map the Time Key in the Measure data.

So it should know that I have a key (A4) for date, not an actual date, right?

That said, I'm looking at the manual, the drop down for Date Mapping should have:
Measure Dt determines Time Dim
Time Dim determines Measure Dt (First)
Time Dim determines Measure Dt (Last)
Time Dim determines Measure Dt (Load Dt within)
Time Dim Independent, Measure Dt always Load Dt.
Time Dim and Measure Dt Independent

I only have two choices:
Measure Dt determines Time Dim
Source Data contains Time Dim

thnx
Endre


WebFocus 7.6.8
iWay Data Migrator 7.6.8
PMF 5.1
 
Posts: 83 | Registered: March 28, 2006Report This Post
Master
posted Hide Post
Not any longer. The main doc is out of date as of V 5.1.2. Refer to the New Features doc for the update. That's why we have the update doc.

There are now only two options. If you want to use a real date instead of a dimension reference ou have to make sure t is processed as an HYYMDs. So you must convert that A4.

thanks


Bob Jude Ferrante
Director of Business and Development
WebFOCUS Performance Management
Bob_Ferrante@ibi.com
917-339-5105

I'll take any questions about PMF - business or technical - anytime!

 
Posts: 919 | Registered: March 26, 2003Report This Post
Gold member
posted Hide Post
Ok... I pick up the doc update.

I don't want to use real date in the measure data. I want to use a time_key (A4) to the custom time dimension.

To do this, I use
"Date Mapping= Measure Dt determines Time Dim".. am I not correct?

E.


WebFocus 7.6.8
iWay Data Migrator 7.6.8
PMF 5.1
 
Posts: 83 | Registered: March 28, 2006Report This Post
Master
posted Hide Post
No, you would say the time key would be the the dimension reference. You wuld then split the key up into its components to refer to the Y/Q/M values in the Time Dimension, however you have it set up.

What you are not mentioning is what do the contents of the A4 field look like?


Bob Jude Ferrante
Director of Business and Development
WebFOCUS Performance Management
Bob_Ferrante@ibi.com
917-339-5105

I'll take any questions about PMF - business or technical - anytime!

 
Posts: 919 | Registered: March 26, 2003Report This Post
Platinum Member
posted Hide Post
Hey Endre,

That drop down is misleading, since neither option really applies - because you are getting the Time data from your external table.

That said, choose the 'Source Data Contains Time Dimension' option. You will see no change on the form, this is OK. Your preview should work fine - provided of course that the Key in your Measures source table matches the Key in the source table for your Time dimension.

Future releases are hiding this drop down altogether to avoid this confusion.

EricH
 
Posts: 164 | Registered: March 26, 2003Report This Post
Gold member
posted Hide Post
The Time_key field (A4) in the measure file is '0001','0002','0003', etc... It just points to a row in the Time Dimension.

The Time dimension has a key field corresponding to the above.

I'm not following you about the "You would then split the key up into its components".

thnx E.


WebFocus 7.6.8
iWay Data Migrator 7.6.8
PMF 5.1
 
Posts: 83 | Registered: March 28, 2006Report This Post
Master
posted Hide Post
hm... we don't support that method directly. Not sure why the measures would be using PMF's own internal keys rather than using some sort of date that can be mapped. You can modify your measure load source or send it out to an an extract that re-populates the data into the Year/Quarter/Month components (or whatever components your Time Dimension is set up to use).

To do that you would JOIN the rows of the Measure data back to the Dimensions table and print the values for TIME_LEVELnn_VALUE and include those in a measure source extract and use that as your data source. You can then use Measure Data contains Time Dimension and map each field out in Field Maps.

Hope that makes sense, and helps.


Bob Jude Ferrante
Director of Business and Development
WebFOCUS Performance Management
Bob_Ferrante@ibi.com
917-339-5105

I'll take any questions about PMF - business or technical - anytime!

 
Posts: 919 | Registered: March 26, 2003Report This Post
Gold member
posted Hide Post
Bob,

I would rather use the internal key. I thought if I use custom time dimension, I had to use a surrogate key that also exists in the time dimension.

As a side note, why do I need a time_key in the custom time dimension if I'm not using it in the measure data? If I understand you correctly


WebFocus 7.6.8
iWay Data Migrator 7.6.8
PMF 5.1
 
Posts: 83 | Registered: March 28, 2006Report This Post
Master
posted Hide Post
Sorry, i think i'm confusing you. If you're using custom time, then follow Eric's path, not mine.
thanks


Bob Jude Ferrante
Director of Business and Development
WebFOCUS Performance Management
Bob_Ferrante@ibi.com
917-339-5105

I'll take any questions about PMF - business or technical - anytime!

 
Posts: 919 | Registered: March 26, 2003Report This Post
Gold member
posted Hide Post
So as Eric suggested, I use 'Source Data Contains Time Dimension' option.

But when I try to "Save" I get pop up error:

You must select a source field to map this field to source data: MESURE_DATE

Something is fishy here :-)

E.


WebFocus 7.6.8
iWay Data Migrator 7.6.8
PMF 5.1
 
Posts: 83 | Registered: March 28, 2006Report This Post
Silver Member
posted Hide Post
Endre,

Try using a time_key that is a total of 8 characters long. (ie YYYYMMDD). We had a similar issue when time_key was shorter.

This is referencing PMF 5.11. Since the product group just changed the time dimension in PMF 5.2 -- verify the exact format required by looking under the covers at the loader code.

See #6 On TIME_KEY in: http://forums.informationbuild...61084022/m/863104701

FYI -- When I used the 'Source Data Contains Time Dimension' option -- I still had to set up the time dimension with the 'Measure Dt Determines Time Dim' option prior. (It was as if the loader was ignoring the 'Source Data Contains Time Dimension' option and using the 'Measure Dt Determines Time Dim' anyway.

This message has been edited. Last edited by: SethW,


WF 7.65. Solaris. PMF 5.11 on Oracle 10g
 
Posts: 48 | Location: New York | Registered: March 25, 2009Report This Post
Gold member
posted Hide Post
It is working fine and I'm not sure what really happened.

I do know that we were using all the source data from flat comma delimited files. You have to remember that PMF joins these files when loading measures. Focus doesn't join to flat comma delimited files. You need at least FOC indexed data with index being on the key field(s).

So this could be the problem. Now all of our source data is FOC Indexed


WebFocus 7.6.8
iWay Data Migrator 7.6.8
PMF 5.1
 
Posts: 83 | Registered: March 28, 2006Report This Post
Platinum Member
posted Hide Post
To get back to Seth's comment, it does not matter what format the Time Key is as long as it is the same in both your Measures source data and your Dimension source data. We have used A4 & I11 with no problems.

ALso, just to repeat (since the message might have gotten overlooked) when loading Time from an external data source in PMF 5.1.2, use the 'Source Data Contains Time Dimension' option and things will work fine. In the next release, this option is removed entirely from the form.

EricH
 
Posts: 164 | Registered: March 26, 2003Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  Performance Management Framework (PMF)    [SOLVED] Time key convert error in measure load

Copyright © 1996-2020 Information Builders