We are building a data warehouse calendar month dimension table and I want a month column in YYM format based on an integer field in the database table. This works fine with YYMD formats but seems to have a bug with YYM formats (or is it I just don't understand something here?). For the most part the returned ccyy/mm values look good .. but not always. Here is example output and build code:

PAGE 1
ID DT_YYM
1 2008/02
2 2008/03
3 2008/04
4 2008/05
5 2008/06
6 2008/07
7 2008/08
8 2008/09
9 2008/10
10 2008/11
11 2008/12
12 2009/01
13 2009/02
14 2009/03
15 2009/04
16 1913/01
17 1913/02
18 1913/03
19 1913/04
20 1913/05
21 1913/06
22 1913/07
23 1913/08
24 1913/09
25 1913/10
26 1913/11
27 1913/12
28 2010/05
29 2010/06
30 2010/07
31 2010/08
32 2010/09
33 2010/10
34 2010/11

SQL SQLMSS
CREATE TABLE [DW_Test].[dbo].[test_yym]
(
ID int identity NOT NULL,
DT_YYM integer NULL,
)
END
SQL SQLMSS
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1286)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1287)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1288)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1289)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1290)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1291)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1292)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1293)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1294)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1295)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1296)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1297)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1298)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1299)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1300)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1301)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1302)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1303)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1304)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1305)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1306)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1307)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1308)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1309)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1310)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1311)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1312)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1313)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1314)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1315)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1316)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1317)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1318)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1319)
END


FILENAME=test_yym ,SUFFIX=SQLMSS ,$
SEGNAME=TEST_YYM ,SEGTYPE=S0 ,$
FIELD=ID ,ALIAS=id ,USAGE=I11 ,ACTUAL=I4,$
FIELD=DT_YYM ,ALIAS=dt_yym ,USAGE=YYM ,ACTUAL=I4,$



SEGNAME=TEST_YYM ,
TABLENAME=DW_Test.dbo.test_yym ,
CONNECTION=testwh ,
KEYS=1 , $



TABLE FILE TEST_YYM
PRINT *
END

Tried this on (with identical results):
WebFOCUS Version 7.6.7 (Gen branch767:155)
Windows Server 2003 R2
Microsoft SQL Server 2005

and on
WebFOCUS Version 7.6.8 (Gen branch768:141)
Ubuntu 8.10
PostgreSQL 8.4

Try changing the ACTUAL format for your date column to DATE:

FIELD=DT_YYM ,ALIAS=dt_yym ,USAGE=YYM ,ACTUAL=DATE,$

Thanks for the response, Dan.

Unfortunately ACTUAL=DATE does not work. It reads the field but the output is drastically wrong:

 
PAGE 1
ID 	DT_YYM
1 	1903/07
2 	1903/07
3 	1903/07
4 	1903/07
5 	1903/07
6 	1903/07
7 	1903/07
8 	1903/07
9 	1903/07
10 	1903/07
.. etc
 

This appears to me to be a bug but I can't believe no one has picked it up since it would have been wrecking reports for a year now.

Since you said YYMD works, you could define the integer column as YYMD and add a DEFINE to the master to convert that date to YYM.

FILENAME=test_yym ,SUFFIX=SQLMSS ,$
SEGNAME=TEST_YYM ,SEGTYPE=S0 ,$
FIELD=ID ,ALIAS=id ,USAGE=I11 ,ACTUAL=I4,$
FIELD=DT_YYMD ,ALIAS=dt_yymd ,USAGE=YYMD ,ACTUAL=I4,$
DEFINE DT_YYM/YYM = DT_YYMD ; $

That is a possibility .. since the grain of the dimension table needs to be month rather than day we would have to take the integer associated with the first day of each month and store that. Pesky thing!

In the mean time I've relented and reverted to using I6YYM as the USAGE with ACTUAL=I4. Takes me back 20 years! But I might try your suggestion since smart dates are so much better.

Thanks for the help.

One problem with the DEFINE field workaround is that SQL aggregation no longer works.

Can anyone else confirm whether they use Calendar Month dimension tables and have encountered this problem on smart dates? The timescale of my project deliverables does not allow waiting for IB to fix this (if its a bug) but if its just me then maybe I misunderstand something. Otherwise I need to log it for the greater good .. just want to check before I take it further.

Thanks All

FIELD=DT_YYM ,ALIAS=dt_yym ,USAGE=YYM ,ACTUAL=I4,$

I don't understand how a four-digit integer gets converted to a six-digit date.

Roy, can you store your SQL date column with a date or date-time format? Then there wouldn't be any problems with the translation to a WebFOCUS date or with SQL aggregation.

I agree with you Francis.

Looks like the integer is number of number of months since 1900.

How does WF converts that to a date ????

I also agree with Dan, it would be safer to store the date in a date or date time field.

Have a look at the code below. It shows what happend when you convert an integer to a date, YYMD or YYM.

EX -LINES 4 EDAPUT MASTER,tmp_int,CV,FILE
FILENAME=TMP_INT, SUFFIX=FIX,$
SEGNAME=TMP_INT, $
  FIELD=INT,ALIAS=  ,USAGE=I9 ,ACTUAL=A9,$
 
EX -LINES 6 EDAPUT MASTER,tmp_date,CV,FILE
FILENAME=TMP_DATE, SUFFIX=FIX,$
SEGNAME=TMP_DATE, $
  FIELD=INT_I9  ,ALIAS=  ,USAGE=I9  ,ACTUAL=I4,$
  FIELD=INT_YYMD,ALIAS=  ,USAGE=YYMD ,ACTUAL=I4,$
  FIELD=INT_YYM ,ALIAS=  ,USAGE=YYM  ,ACTUAL=I4,$
 
-RUN
 
FILEDEF TMP_INT  DISK tmp_int.ftm 
FILEDEF TMP_DATE DISK tmp_int2.ftm (LRECL 12 RECFM F
 
-RUN
 
-REPEAT INT_LOOP FOR &Cntr FROM 1 TO 1400 ;
-WRITE TMP_INT &Cntr 
-INT_LOOP
 
TABLE FILE TMP_INT
PRINT INT
      INT
      INT
ON TABLE HOLD AS TMP_INT2
END
-RUN

TABLE FILE TMP_DATE
PRINT INT_I9
      INT_YYMD
      INT_YYM
END

quote:

I don't understand how a four-digit integer gets converted to a six-digit date.

Is not ACTUAL=I4 is how WF hold this value inside and is be actually held binary? This be holding many digits not 4?

Am I right in thinking this, I not sure now

Kofi

Actual=I4 does mean that the data is stored as a binary.

But the example at the top writes 4 digit numbers to the database, then reads them as dates, this is the issue.

As Francis always say - check documentation - this be showing -

quote:

In Binary integers:

I1 = single-byte binary integer.

I2 = half-word binary integer (2 bytes).

I4 = full-word binary integer (4 bytes).

I be happier now

Kofi

quote:

the example at the top writes 4 digit numbers to the database

Waz,

This I saw but maybe misunderstood the Francis question. It seem be asking on ACTUAL=I4 and I need to be sure my understand.

I am thinking that you are right in data be number of months since base date - perhaps Roy confirm, although I4 to YYM seem to mean this, yes?

This be the case then maybe DEFCENT and YRTHRESH be important, no?

Kofi

INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1286)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1287)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1288)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1289)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1290)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1291)
INSERT INTO [DW_Test].[dbo].[test_yym] ([DT_YYM]) VALUES (1292)

I don't understand how a four-digit integer gets converted to a six-digit date.

Francis,

The same way that 36525 gets translated to 31/12/1999 in Excel.

T

Tony, thanks for that. 36525 does indeed get transformed to 1999/12/31 when formatted as a date yyyy/mm/dd. 1286 gets transformed to 1903/07 when formatted as yyyy/mm, which is the behaviour that Roy D mentions, so, if Excel does it this way it must be the right way.

Well, it seems that an integer is an integer when "used" as such (USAGE). However, when using a different data type (date-based for instance) WebFOCUS has a way to interpret it:

USAGE=YYMD, ACTUAL=I4

The number is treated as a DATE and it's interpreted as the number of days since Dec. 31, 1900.

USAGE=YYM, ACTUAL=I4

Here the field is calculated as the number of months since Dec.31, 1900.

I could not find the documentation piece that explain how a date is calculated as an offset based on Dec.31, 1900 (well, I'm probably not searching hard enough) but I'll keep trying.

By the way, I tried USAGE=YY thinking that it may probably be interpreted as a number of years since Dec. 31, 1900 but it actually returned the same integer value.

quote:

I don't understand how a four-digit integer gets converted to a six-digit date.

Francis, this document says in page 21:

quote:

SmartDate fields are actually stored as an elapsed interval relative to the base-date of
December 31, 1900. As a result January 1, 1901 has a stored value of 1. Dates with values
before this reference date count in a negative direction and dates after this reference date count in
a positive direction.

This newsletter also has an interesting article about the same topic under the FOCUS Functions and Julian Dates section.

Thank you for all the points mentioned thus far.

When we started building the dimension tables for our star-schemas we began, as is pretty standard, with a day-level calendar dimension. Typically I use the internal number rendered as a smart-date (YYMD etc) by WebFOCUS as the surrogate key as well. This is just a convenience. But I use the same type of number for date columns in the table since it is very direct .. WebFOCUS just needs a USAGE=YYMD on an ACTUAL=I4. As noted by others that means a 4 byte integer field gets stored and the integer is the offset in days from 31 Dec 1900. (Waz put the sort of code we would use to seed the table above in this track).

If you take the following code you'll see that just as day level dates convert to an integer (the afore-mentioned offset) so do months:

  
TABLE FILE DIM_ALL_CALENDAR_DAY
IF CALDAY EQ 20100511
PRINT CALDAY
COMPUTE TESTMTH/YYM = CALDAY ;
COMPUTE I_CALDAY/I9 = CALDAY*1 ;
COMPUTE I_TESTMTH/I9 = TESTMTH*1 ;
END

  
<TABLE BORDER CELLPADDING=1>
<TR>
<TD COLSPAN=4>
<TABLE CELLPADDING=0 WIDTH="100%"><TR>
<TD>
PAGE     1</TD></TR></TABLE></TD>
</TR>
<TR>

<TD VALIGN=BOTTOM>
Calendar Day</TD>
<TD VALIGN=BOTTOM>
TESTMTH</TD>
<TD ALIGN=RIGHT VALIGN=BOTTOM>
I_CALDAY</TD>
<TD ALIGN=RIGHT VALIGN=BOTTOM>
I_TESTMTH</TD>
</TR>
<TR>
<TD>
2010/05/11</TD>

<TD>
2010/05</TD>
<TD ALIGN=RIGHT>
39943</TD>
<TD ALIGN=RIGHT>
1313</TD>
</TR>
</TABLE>

This result leads one to think that you can also use an integer with USAGE=YYM on top to represent a month. And this works .. but not always. My question is why not? To be fair to IB I don't think it is documented. But the integers loaded into SQL Server (above) came from WebFOCUS using something like this code snippet. And within the confines of a program they get respected. It is just that if you put them in a database and then try to read them back again they don't get respected.

When I have a moment I'll try the ACTUAL=DATE suggestion.

Thanks again for your thoughts.
Roy