DOC-008 HOW TO MANAGE THE INGREDIENT ATTRIBUTE FUNCTIONALITY IN INFOSYS+ Kantar Media Spain 26/03/2014 INDEX 1 ABSTRACT ............................................................................................................................................. 3 2 HOW TO USE THE FUNCTIONNALITY .................................................................................................... 4 2.1 IMPLEMENTATION OF INGREDIENT ATTRIBUTES IN INFOSYS+ (OLD WAY) ............................... 4 2.1.1 Filename ................................................................................................................................. 4 2.1.2 Format .................................................................................................................................... 4 2.1.3 Remarks .................................................................................................................................. 4 2.1.4 Example .................................................................................................................................. 5 2.2 2.2.1 2.3 IMPLEMENTATION OF INGREDIENT ATTRIBUTES (NEW METHOD) ............................................ 5 The new XML file .................................................................................................................... 6 XLS2XML TOOL ............................................................................................................................ 7 2.3.1 Converting the XLS into an XML ............................................................................................. 7 2.3.2 Parameters description .......................................................................................................... 7 2.3.3 Renaming and Placing the XML ............................................................................................ 10 3 EDITING THE SYSATTRIBUTES TABLE IN THE SYSINGREDIENTS.MDB ................................................. 11 4 HISTORY .............................................................................................................................................. 15 1 ABSTRACT This document explains how to manage the files related to ingredient attribute functionality, from an operator’s perspective. This document does not explain how to use this functionality from a user’s point of view (User’s help file must be consulted instead). 2 2.1 HOW TO USE THE FUNCTIONNALITY IMPLEMENTATION OF INGREDIENT ATTRIBUTES IN INFOSYS+ (OLD WAY) 2.1.1 FILENAME ATyyyymmdd.n (located in the audience data daily folders) where yyyymmdd is the date to which belongs the file and n is the identifier of the table that it defines (defined in SysIngredients: SysTables) 2.1.2 FORMAT XML file formed by a single section using as identifier the name of the table that it defines, where each component of the section is identified as 'cn ' (where n is the code for the item) and the attributes of each component should be called exactly like the column SysAttributes.SourceName (sensitive to uppercase and lowercase). If you want to use characters not included in ASCII code, it should be specified in the header, by indicating that you desire to use special characters of the encoding windows-1252 We will see how to generate XML files from XLS in the last section of this document. 2.1.3 REMARKS You can omit the file for the dates for which there is no change in the contents, in regard to the previous day. If no file is found for a given date, InfoSys will trace backwards in the data tree to the first date for which the file exists, and will use the information contained in it. Notice that it’s mandatory that ATyymmdd.n should exist at least in the first loaded date, otherwise no attributes would be ever visible. It’s also recommended to have one per year (even if no changes have been made), so that the searches are more efficient. Any record can be left out of the file, in which case the value of the attributes for these records will be NULL (a hyphen will be shown inside InfoSys). Within a given record, any attribute can be omitted, in which case their value will also be NULL. You can add non-used attributes to the records, the purpose of which may be informative (for file maintenance) or possible use in the future. You can only specify attributes for Sofres records, both for ingredients and base ingredients. 2.1.4 EXAMPLE File: \audience\AU2008\AU200803\AU080330\AT080330.4  day 30/3/2008; table Channels (SysTable = 4) <?xml version="1.0" encoding="windows-1252"?>  header: Allow the codification of non-ASCII characters <channels>  Section with the name of the table detailing (this is the only section in this file) <c5001 CodCoverageType="2" CodContents="2" PayTV="0" CodWoner="1" CodCompany="34" Name="24 HORAS" />  Record with code 5001 and the values for attributes CodCoverageType, CodContents, PayTV, CodOwner, CodCompany (the attribute ‘Name’ is purely informative) <c5002 CodCoverageType="3" CodContents="2" PayTV="0" CodWoner="1" CodCompany="5" Name="3/24" /> <c5003 CodCoverageType="3" CodContents="2" PayTV="0" CodWoner="1" CodCompany="100" Name="300" /> <c5004 CodCoverageType="3" CodContents="2" PayTV="0" CodWoner="1" CodCompany="5" Name="33/ESPORTS" /> <c5005 CodCoverageType="2" CodContents="2" PayTV="1" CodWoner="1" CodCompany="39" Name="40 LATINO" />  rest of records </channels> Closing tag of selection 2.2 IMPLEMENTATION OF INGREDIENT ATTRIBUTES (NEW METHOD) From InfoSys+ V1.3, there’s a new way of dealing with channel attributes. For compatibility reasons, the old implementation will be also compatible with the versions of InfoSys+ 1.3 and later. This new methodology is more efficient so we recommend to use it if InfoSys+ 1.3 is used. InfoSys+ (v1.3) will look at “GeneraCfg.xml” for a switch which indicates whether old or new methodology is used. If new methodology is to be used, there is no need for the ATyyyymmdd.n files. This is how the new parameter is specified in the GeneralCfg.XML: <?xml version="1.0" encoding="UTF-8"?> <General> … <DiaryAttrsSrc value="1"/>  … </General> 2.2.1 THE NEW XML FILE From version 1.3, if the new methodology is selected, a new xml file can be created for each ingredient that contains diary attributes and it will be located in the “Config” folder. In the following example, we are setting channels ingredients: ..\Config\DiaryAttrsChannels.xml The internal format of the file is similar to the old one with a difference. The setting of the attribute’s values is stored internally into the file like in the following example: <?xml version="1.0" encoding="utf-8"?> <channels> <d19920101> <c1 CodCoverageType="2" CodContents="1" PayTV="0" CodOwner="1" CodCompany="34" Name="TVE1" /> <c2 CodCoverageType="2" CodContents="1" PayTV="0" CodOwner="1" CodCompany="34" Name="La2" /> <c3 CodCoverageType="2" CodContents="1" PayTV="0" CodOwner="2" CodCompany="18" Name="T5" /> … </d19920101> <d19930101> <c3 CodCoverageType="2" CodContents="1" PayTV="1" CodOwner="2" CodCompany="18" Name="T5" /> </d19930101> … </channels> As you can see, the system is expecting a series of second level existing nodes with dates for the attributes values to be correct. In the example shown: TVE1 is not a “Pay TV” from 1/1/1992 and so on. La2 is not a “Pay TV” 1/1/1992 and so on. T5 is not a “Pay TV” from 1/1/1992 to 31/12/1992, but it turns to “Pay TV” from 1/1/1993 and so on. Next, some aspects to have in mind: - The file must be in the /Config/ folder and it will be the same for all users. - The file name will be always DiaryAttrs${Internal name of the ingredient in the MDB}.xml - The File is loaded into memory while InfoSys+ is loading. This way, attribute’s evaluation of a specific item is much faster and efficient. 2.3 XLS2XML TOOL 2.3.1 CONVERTING THE XLS INTO AN XML XLS2XML tool converts an Excel (from now XLS) document to an XML document. This tool cannot be executed directly: It must be run in a command-line window or by editing and running the batch file convert.bat that we supply with this tool. The file convert.bat that we supply contains a command similar to the following example; in this case this line affects channels: Excel2Xml channels "C:\\XLS2XML\master+.xls" channels CodChannel CodCoverageType CodContents PayTV CodOwner CodCompany Name 2.3.2 PARAMETERS DESCRIPTION Note: If the parameter contains a space in its name, it must be adorned by quotation marks; otherwise infoSys+ will consider its parts as different parameters. - XmlSection: this is the name of the root node of the XML. In the example this is channels. ExcelFile: the absolute path of the XLS file where data is. In the example this is C:\XLS2XML\master+.xls. ExcelSheet: the tab of the XLS file where data is. In the example this is Channels. Column_1: The field name in the first row that will represent the key value. This key value must be unique for each row. In the example this is CodChannel. [Column_i]: A field name in the first row that will represent the parameter with its value added to XML values. Note [] means this parameter can be optional and can be multiple (multiple columns). In the example this is composed by CodCoverageType CodContents PayTV CodOwner CodCompany Name. To better understand this, in the next pages there are the example screenshots of the input XLS and the output XML. Input XLS example Output XML example Some remarks: a) It’s useful to include the field Name in the XLS (and XML); even though it won’t be used by InfoSys+ during the parsing of the attributes, it will serve as a reference for the operator if the XML has to be consulted. b) Some attributes can be ENUM, which means that the choices belong to a closed list of items. That means that the XML will report a number, but InfoSys+ will show a text (taken from a translation table in SysAttribs, defined in the SysAttributes table of SysIngredients DB (check Part 3). In this case, it’s useful to create a tab in the XLS for these tables, which will serve as translation. In the example image you can see that the Excel includes, apart from the Channels tab, a CoverageType tab, CodContents tab, etc… 2.3.3 RENAMING AND PLACING THE XML Once converted, the output XML file will have the name of the first parameter plus .xml. Ex: Channels.XML This XML file must be moved to the first audience day on the audience data where it has to start effect. st For example, if we want it to take effect from 1 January 2006, it must be moved to the folder AU2006\AU200601\AU20060101 on the audience data. Additionally, this file must be renamed to ATyymmdd.n, where yy means the year (in the example 06), mm means the month (01), dd the day (01), and n must be a SysTables identifier (in the example 4). Below is a screenshot of the SysTables contents in the SysIngredients.mdb. If this file must be delivered to clients, the ATyymmdd.n file will have to be included inside the audience load package, and the control file CAyymmdd.txt modified accordingly. When clients load this audience package, the XML will be placed in the right folder. If the new methodology (InfoSys+ 1.3 or later) is used, the operator will have to modify the DiaryAttrs${Internal name of the ingrediente in the MDB}.xml in the Config folder of the operator’s production version of InfoSys+ from the XML created by the XLS2XML tool (by modifying the right date nodes inside such XML), and then include this XML in the audience load package in the same way described above. 3 EDITING THE SYSATTRIBUTES TABLE IN THE SYSINGREDIENTS.MDB The operator can decide to add as many attributes as desired or needed. This is the SysAttributes table of the example: - Column IdSysAttribute: the column key (must be unique for each record). Codes should be higher than 1000, since the ones which are lower are reserved. - IdSysTable: selects where this attribute will apply. Has to be an id of the table SysTables, as seen on Part 2. - Name: the name of the register. - NameAlt: should be the same name as filled in column Name, but in English. - IdSysAttributeType: defines the type of data for this attribute. Has to be an id of the table SysAttributeType, as seen below. If 6 is chosen (ENUM, which means closed list), column TranslationTable must be filled (see its own description). 1. BOOL: True or False. 2. INTEGER: number without decimals. 3. FLOAT: number with decimals. 4. String: phrase. 5. Date: number in date format. ONLY dates will be interpreted correctly if the attribute is set up as DATE (blanks, dates with incorrect format, etc.. won’t be interpreted correctly) The date functionality was extended. The possibility to create a range of dates is now allowed (before only one date could be selected) The dates always need to be entered in following format YYYYMMDD`. Example of the attributes in the china sector (attributes Start_Date and End_Date): <?xml version="1.0" encoding="windows-1252"?> <Sectors> <c1 Panel_Name="XI'AN" Start_Date="19961117" End_Date="20020831" Sample_HH="300 " Classified="6" Remark="CONSTANT" /> <c2 Panel_Name="WUHAN" Start_Date="19961117" End_Date="20040606" Sample_HH="300 " Classified="6" Remark="CONSTANT" /> <c3 Panel_Name="GUANGZHOU" Start_Date="19990214" End_Date="20030807" Sample_HH="400 " Classified="6" Remark="CONSTANT" /> Important: The attributes of the DATE type should be following this format obligatorily. If they existed already but in a different format (for example in china) they should be changed according the new format. In Infosys, the dates will appear following the local configuration of the user: Create an attribute of the DATE type: The date selection dialogue will allow selecting a range or various dates separately: In both cases the dialogue will appear centralized around the today and allows choosing between 01/01/1900 and 01/01/2099. 6. ENUM: a new defined type. (see Translation Table section for more details and an example) - IdSysAttributeSource: defines where to look for this attribute. Has to be an id of the table SysAttributeSource, as seen below. 1. SysIngredients Database: attribute defined in the SysIngredients.mdb. 2. Diary: attribute defined in every day data (XML). 3. Inherited AND: for Boolean data; when an item is compound of base items, if one or more of the base items have this attribute to False, the item will have this attribute to False. It will be True only if all are True. 4. Inherited OR: for Boolean data; when an item is compound of base items, if one or more of the base items have this attribute to True, the item will have this attribute to True. It will be False only if all are False. 5. SysAttribs Database: attribute defined in the SysAttribs.mdb. - SourceName: a reference to the name of the Excel column used in the conversion, or the DB field if type 1, 3, 4 or 5 are selected. TranslationTable: it must be filled only if type 6 is selected in column IdSysAttributeType. The name must be the same name of the table in the SysAttribs.mdb that will contain the allowed values. In this example the SysAttribs.mdb will contain 8 new tables named ChnCoverageType, ChnContents, ChnOwner, ChnCompany… To view an example, this is the ChnOwner table (located in SysAttribs.mdb). This means that the parameter that we are defining can only be of the type “Publica”, “Privada” or “Otros”. Below you can also see how it would be displayed by Infosys+. When creating this table, the system tables TablesPrimaryKeys y ColumnasPrimaryKeys must be populated in order to indicate which the primary key of the new tables will be; however, the primary key will usually be CodLevel1, so the table ColumnsPrimaryKeys does not need to be modified, because the record 0 corresponds to CodLevel1. In TablesPrimaryKeys table the operator must add a new record for each new table, linking it to CodColumn 0. - StartDate: from which date this register will be considered. Pos: the position of the attribute in the view. In this example, for id 4 of the IdSysTable, the positions go from 1 to 7. This position is independent from the position of another IdSysTable id. 4 HISTORY Version Author 1.0 KMSpain Date 26/03/2014 Comments Creation