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,

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,

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

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

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.

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?

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

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.

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,

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

Sorry, i think i'm confusing you. If you're using custom time, then follow Eric's path, not mine.
thanks

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.

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,

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

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