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:
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.
8.0.0.6, Ubuntu 12.04, All
May 06, 2010, 07:20 AM
Roy D
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.
I don't understand how a four-digit integer gets converted to a six-digit date.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
May 06, 2010, 01:51 PM
Dan Satchell
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.
WebFOCUS 7.7.05
May 06, 2010, 06:20 PM
Waz
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
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
May 07, 2010, 02:19 AM
Kofi
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
Client Server 8.1.05: Apache; Tomcat;Windows Server 2012 Reporting Server 8.1.05; Oracle; MS SQL; Windows Server 2012
May 07, 2010, 02:28 AM
Waz
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.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
May 07, 2010, 02:32 AM
Kofi
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
Client Server 8.1.05: Apache; Tomcat;Windows Server 2012 Reporting Server 8.1.05; Oracle; MS SQL; Windows Server 2012
May 07, 2010, 02:40 AM
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
Client Server 8.1.05: Apache; Tomcat;Windows Server 2012 Reporting Server 8.1.05; Oracle; MS SQL; Windows Server 2012
May 10, 2010, 11:45 AM
Francis Mariani
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
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
May 10, 2010, 12:40 PM
Tony A
Francis,
The same way that 36525 gets translated to 31/12/1999 in Excel.
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
May 10, 2010, 12:49 PM
Francis Mariani
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.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
May 10, 2010, 02:48 PM
njsden
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.
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.
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:
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.