Skip to main content
insightsoftware Documentation insightsoftware Documentation
{%article.title%}
Published:
Was this article helpful?
0 out of 0 found this helpful

Extracting Longview Data to ODS

When Longview data is required by a third-party application, such as a Business Integration reporting package, it is sometimes necessary for that application to not only require the data for the requested data area but to also be able to retrieve the symbols that relate to that data area. For example, if a third-party application requests data for North American entities, it may need to know how these entities rollup to each other. Therefore, a process must extract symbol information related to the North American entities in addition to the data. The data and the associated metadata are extracted to an Operational Data Store (ODS). An ODS is simply a method to store this extracted information where it is later utilized by a third-party application.

The Longview ODS extraction process deals only with the exporting of data and the associated symbol metadata. It does not deal with how this data is used by any third-party application.

The ODS extraction process generates a series of files that correspond to:

  • the data that has been requested (fact tables)
  • metadata for the symbols whose data was extracted (dimension tables)

When using ODS there are two basic questions that you must answer:

  • What data must be extracted?
    This is usually relatively straight forward and involves defining a data area. This is like other requests for Longview data where it is necessary to specify symbol specifications for each dimension in your system.
  • How must the symbols corresponding to this data be represented?

There are several options for how the symbol metadata is formatted in the dimension tables. You must determine the format that works best for your specific use. This depends on the third-party application that utilizes these files after the ODS extraction process. For additional details, see “Dimension Files”.

The steps involved in ODS extraction are:

  1. Understanding the possible options for creating the dimension files and ensure that one of these options meets the needs of the third-party application.
  2. Creating an ODS attribute and setting attribute values for selected symbols.
  3. Writing the data area specification document.
  4. Writing the export specification document with the required ODS options.
  5. Writing the calling procedure document that runs the export specification using the data area specification.

In this section, you can find information on the following topics:

Creating an ODS attribute and setting attribute values for selected symbols

Entities of the same type (e.g. continent, country, city) do not necessarily need to reside at the same level in the Longview hierarchy. Some branches of the hierarchy could have many levels of detail, whereas other branches of the hierarchy could have little detail. In the entity hierarchy below, note that the entity Toronto is at the third level under the entity Worldwide, whereas Dallas is at the fourth level.

Therefore, if a third-party application needs to create a report showing data for just the symbols that are cities, it cannot presume that the cities are all located at the same level in the hierarchy.

If the ODS output is to contain columns named "Continent", "Country", and "City", it is necessary to associate each entity with one of those values. This is accomplished by creating a new symbol attribute, for example "AZEntityType", and then setting that attribute's value for each entity. For example, the AZEntityType attribute values for the following entity symbols would be:

  • NorthAmerica = "Continent"
  • Canada = "Country"
  • USA = "Country"
  • Toronto = "City"

To set these attributes, the SET ATTRIBUTE command is used.

Syntax

SET ATTRIBUTE SYMBOL AttrName VALUE SymName AttrValue

where:

  • AttrName is the attribute name associated with a particular ODS extract.

    Note: If you want to use one attribute for a particular ODS extract, you must use the ATTRIBUTEMAP command in the ODS export specification document. If you want to use multiple attributes for a particular ODS extract, you must use the ATTRIBUTEMAPFIELD command, and any combination of the ATTRIBUTEMAPDESC, ATTRIBUTEMAPFACT, ATTRIBUTEMAPID, or ATTRIBUTEMAPNAME commands in the ODS export specification document. For more information, see “Writing the export specification document”

  • SymName is the symbol whose attribute value is being set.

  • AttrValue is a string of the following format:

    FieldName[,NameToUse,DescToUse[,FactTableName[,IndexToUse]]]]

    where:

    • FieldName is a string value specifying the target field in the ODS dimension table.

      If a field name is provided and that field name has been subsequently associated with a column number via an ODSMappingTable command, this symbol's information is written to that column in the appropriate dimension table. If no value has been specified for a symbol, the symbol is not written to the dimension table.

    • NameToUse is an optional parameter that returns the symbol's name or a hardcoded value. To use the symbol's name, specify a value of NAME. To use a hardcoded value, enclose the string in double quotation marks. The default for this parameter is NAME.
    • DescToUse is an optional parameter that specifies whether to use the symbol's description or a hardcoded value. To use the symbol's description, specify a value of DESCRIPTION. To use a hardcoded value, enclose the string in double quotation marks. The default for this parameter is DESCRIPTION. To illustrate when a hardcoded value may be applicable, consider the situation where the third-party application needs to know which date is associated with a time period. Returning the description for the January 2010 actuals time period may return "January 2010 Actuals". It may be more useful if this returned a hardcoded value of "01/01/2010" instead.
    • FactTableName is an optional parameter that specifies the name of the fact table to which the data values applicable to this symbol are written. This name is later mapped to the integer value representing a fact table (FactTableNumber). The default value for this parameter is DEFAULT, which is later mapped to fact table 1. Since multiple fact tables can exist for a single dimension only, multiple fact table names can be specified for a single dimension only. For example, if multiple fact tables are to be used to differentiate actuals data from plan data, associate all actuals time periods with a data file name such as "Actuals" and all plan time periods with a data file name such as "Plan". All symbols for dimensions other than time should not have a fact table name associated with them.

    • IndexToUse is an optional parameter used to indicate which integer value should be used to uniquely identify this symbol within its dimension. If IndexToUse is not specified, the Longview symbol index is used. To see how this parameter could be used, consider the case when you want to compare January data in your Actuals fact table with the corresponding January data in your Plan fact table. Since these data values originated from different time period symbols, the symbols indexes for those two time periods are different. These two symbols could be associated to each other by using the same IndexToUse for each of them.

Note: By default, the standard Longview symbol name, symbol description, and symbol index are used. However, the attribute value can be set so that any combination of these can be overridden. For the remainder of this section, the terms UseName, UseDesc, and UseIndex refer to the name, description, and index that are used for a symbol, which could be the standard Longview value or the overridden value.

Using the example above, it is necessary to associate each symbol with its corresponding field in the ODS dimension files. If a data area fixes to a single symbol for a dimension, that symbol's field name must be set to the first field of the dimension table. Since entities vary in this example, each entity to be extracted must be mapped to its appropriate field name.

In this example, the attribute values are set using these commands:

Maintenance on

 

// Prior to running this procedure,

// it is necessary to create the user-defined attribute

// Create Attribute Symbol AZEntityType "Description" "Text" W " "

 

SET ATTRIBUTE Symbol AZEntityType VALUE Cash "Account"

SET ATTRIBUTE Symbol AZEntityType VALUE A1001YTD "Period,NAME,01/01/2010,FactActual,1"

SET ATTRIBUTE Symbol AZEntityType VALUE A1002YTD "Period,NAME,01/01/2010,FactActual,2"

SET ATTRIBUTE Symbol AZEntityType VALUE P1001YTD "Period,NAME,01/01/2010,FactPlan,1"

SET ATTRIBUTE Symbol AZEntityType VALUE P1002YTD "Period,NAME,01/01/2010,FactPlan,2"

SET ATTRIBUTE Symbol AZEntityType VALUE NorthAmerica "Continent"

SET ATTRIBUTE Symbol AZEntityType VALUE Canada "Country"

SET ATTRIBUTE Symbol AZEntityType VALUE USA "Country"

SET ATTRIBUTE Symbol AZEntityType VALUE Toronto "City"

// set for all relevant entities

SET ATTRIBUTE Symbol AZEntityType VALUE DIM3SET "Function"

SET ATTRIBUTE Symbol AZEntityType VALUE DIM4SET "Currency"

SET ATTRIBUTE Symbol AZEntityType VALUE DIM5SET "Segment"

SET ATTRIBUTE Symbol AZEntityType VALUE DIM6SET "Element"

SET ATTRIBUTE Symbol AZEntityType VALUE DIM7SET "Control"

 

Maintenance off

Creating ODS documents

ODS documents include data area specification documents:

  1. Data area specification document—Use this document type to define the area to extract
  2. Export specification document—Use this document type to specify the target and format for the extraction
  3. Calling procedure document—Use this document type to execute the extraction

Writing the data area specification document

A data area specification document is required to define the area that is to be extracted. A sample data area specification document is:

ACCOUNTS    Cash

TIMEPER     A1001YTD, A1002YTD, P1001YTD, P1002YTD

ENTITIES     NorthAmerica#99

FUNCTIONS     Dim3Set

PRODUCTS     Dim4Set

VERSIONS     Dim5Set

CURRENCY    Dim6Set

CONTROLS     Dim7Set

Writing the export specification document

An example export specification document includes the following lines:

// Indicate that:

// - this is an ODS export spec

// - all output files are prefixed with "TestODS"

DATATARGET ODS, "TestODS", EXTERNAL

 

// Specify an optional log file, error file and/or debug file

LOGFILE "Export.log", EXTERNAL

ERRORFILE "Export.err", EXTERNAL

ODSDEBUG "ODSDebug.out", EXTERNAL

 

// (If you are using one symbol attribute for the ODS extract)

// Specify the symbol attribute that is used to determine the structure

// of the dimension tables

ATTRIBUTEMAP AZEntityType

 

// Or

 

// (If you are using multiple symbol attributes for the ODS extract)

// Specify the symbol attributes that are used to determine the structure

// of the dimension tables

ATTRIBUTEMAPDESC AZEntityType1

ATTRIBUTEMAPFACT AZEntityType2

ATTRIBUTEMAPFIELD AZEntityType3

ATTRIBUTEMAPID AZEntityType4

ATTRIBUTEMAPNAME AZEntityType5

 

// Are values to be reversed?

REVERSESIGN ON

 

// Should a time stamp be created?

ODSPUBLISHINGTIME ON

 

// Should parent data be extracted for a dimension?

ODSINCLUDEPARENTDATA ON, ENTITIES

 

// Should symbol properties be included in metadata?

ODSSYMPROPERTIES AZSomeOtherAttr1 ENTITIES

ODSSYMPROPERTIES AZSomeOtherAttr2 ENTITIES

ODSSYMPROPERTIES AZSomeOtherAttr3

ODSSYMPROPERTIES DEBITCREDIT ACCOUNTS

 

// Should system attribute properties be included in metadata?

ODSSYSPROPERTIES SGPCurrentYear

 

// Specify the format of the dimension tables

//ODSSTRUCTURE RECURSIVEHIERARCHYPF, ENTITIES

//ODSSTRUCTURE RECURSIVEHIERARCHYLF, ENTITIES

//ODSSTRUCTURE STAR

ODSSTRUCTURE SNOWFLAKE, ENTITIES

 

// If data is to be written directly to database tables,

// Enter the connection info here

ODSDBOUTPUT ODSDataSourceABC, ODSUser, Passwordxxxx

 

// A symbol with an ATTRIBUTEMAP attribute value of FieldName should appear

// in column fieldNumber of the dimension tables using the command:

// ODSMappingTable ITEM, FieldName, fieldNumber

ODSMAPPINGTABLE ITEM, Continent, 1

ODSMAPPINGTABLE ITEM, Country, 2

ODSMAPPINGTABLE ITEM, City, 3

ODSMAPPINGTABLE ITEM, Account, 1

ODSMAPPINGTABLE ITEM, Period, 1

ODSMAPPINGTABLE ITEM, Function, 1

ODSMAPPINGTABLE ITEM, Currency, 1

ODSMAPPINGTABLE ITEM, Segment, 1

ODSMAPPINGTABLE ITEM, Element, 1

ODSMAPPINGTABLE ITEM, Control, 1

 

// A symbol that has been associated with FactTableName should appear

// in fact table number FactTableNum:

// ODSMappingTable ITEM, FactTableName, FactTableNum

ODSMAPPINGTABLE ITEM, FactActual, 1

ODSMAPPINGTABLE ITEM, FactPlan, 2

An ODS export specification document must contain the following commands:

Command

Description

DATATARGET ODS

The format for this command is:

DATATARGET ODS, FilePrefix, EXTERNAL

This command designates that this is an ODS export specification and that all output files are prefixed by the value as specified in the FilePrefix parameter. The FilePrefix parameter can also be used to specify a relative path to the working directory.

For example, a FilePrefix value of "test\output\testods" places the output files in <current working directory>\test\output, with each file prefixed by "testods".

ODSMAPPINGTABLE

The format for this command is:

ODSMAPPINGTABLE

This command associates a field name with a column index in a dimension table, or a fact table name to a fact table number.

An ODS export specification document must contain at least one of the following commands:

Command

Description

ATTRIBUTEMAP

The format for this command is:

ATTRIBUTEMAP AttributeName

This command specifies which symbol attribute is to be used to format the dimension tables.

This command overrides the ATTRIBUTEMAPFIELD command and the ODSAUTOLEVEL command.

ATTRIBUTEMAPFIELD

The format for this command is:

ATTRIBUTEMAPFIELD AttributeName

Use this command to specify the symbol attribute for the FieldName parameter of the ODS extract.

This command overrides the ODSAUTOLEVEL command.

ODSAUTOLEVEL

The format for this command is:

ODSAUTOLEVEL ON|OFF

This command automatically generates the level information. The default specification for ODSAUTOLEVEL is OFF.

Note: When using automap, the ODSMappingTable still needs to be included to map the levels to columns in the dimension tables. In this case, each field name is DimensionName_L00, DimensionName_L01, ..., DimensionName_Lxx, where xx is the level number.

Note: If the ODS export specification document contains either of the ATTRIBUTEMAP or ATTRIBUTEMAPFIELD commands and the ODSAUTOLEVEL command, and ODSAUTOLEVEL is ON, the field name stored in the attribute value overrides the auto level field name. For more information, see “Field names in ODS output files”

An ODS export specification document can contain the following optional com-mands:

Command

Description

ATTRIBUTEMAPDESC

The format for this command is:

ATTRIBUTEMAPDESC AttributeName

Use this command to specify the symbol attribute for the DescToUse parameter of the ODS extract.

ATTRIBUTEMAPFACT

The format for this command is:

ATTRIBUTEMAPFACT AttributeName

Use this command to specify the symbol attribute for the FactTableName parameter of the ODS extract.

ATTRIBUTEMAPID

The format for this command is:

ATTRIBUTEMAPID AttributeName

Use this command to specify the symbol attribute for the IndexToUse parameter of the ODS extract.

ATTRIBUTEMAPNAME

The format for this command is:

ATTRIBUTEMAPNAME AttributeName

Use this command to specify the symbol attribute for the NameToUse parameter of the ODS extract.

ERRORFILE

The format for this command is:

ERRORFILE FileName, EXTERNAL|INTERNAL

This command specifies the name of the error file to contain data on invalid records.

LOGFILE

The format for this command is:

LOGFILE FileName, EXTERNAL|INTERNAL

This command specifies the name of a log file to contain all variable values, errors, and error codes.

ODSAUTOLEVELDIM

The format for this command is:

ODSAUTOLEVELDIM DimensionName, ColumnName

When ODSAUTOLEVEL is ON, this command allows you to override the default auto level names and apply the desired field names based on dimension and level number.

For each ODSAUTOLEVELDIM entry specified, you need to provide the appropriate mapping information through the ODSMAPPINGTABLE command, in the following format:

ODSMAPPINGTABLE ITEM, ColumnName, ColumnNumber

ODSDBOUTPUT

The format for this command is:

ODSDBOUTPUT "DataSourceName", "UserName", "Password"

The output from an ODS extraction process usually creates a series of text files. However, it is also possible for this process to populate a series of database tables directly.

For more information on dimension table options, see “Dimension Files”

ODSDEBUG

The format for this command is:

ODSDEBUG FileName, EXTERNAL|INTERNAL

This command specifies the name of a log file to contain debugging information.

ODSINCLUDEHIERARCHYLEVEL

The format for this command is:

ODSINCLUDEHIERARCHYLEVEL ON|OFF

This command specifies whether to include the Level column in the fact tables. The default specification for ODSINCLUDEHIERARCHYLEVEL is OFF.

For more information, see “Fact Files”

ODSINCLUDEPARENTDATA

The format for this command is:

ODSINCLUDEPARENTDATA ON|OFF [, DimName][, DimName]...

This command specifies whether to include parent data. When determining this, you must consider whether the third-party application calculates its own totals or not. If it calculates its own totals, you likely do not need to export parent data values. The default specification for ODSINCLUDEPARENTDATA is OFF.

To include parent data for all dimensions, specify: ODSINCLUDEPARENTDATA ON

To include parent data for a single dimension, for example ENTITIES, specify: ODSINCLUDEPARENTDATA ON , ENTITIES

To include parent data for multiple dimensions, for example ENTITIES and ACCOUNTS, specify: ODSINCLUDEPARENTDATA ON , ENTITIES, ACCOUNTS

ODSPATHFROMDB

The format for this command is:

ODSPATHFROMDB, "FolderPathName"

This command specifies a full path from the machine running the SQL Server to the folder containing the output files generated by the ODS extraction process. This command is not required with an Oracle Database Server.

ODSPUBLISHINGTIME

The format for this command is:

ODSPUBLISHINGTIME ON|OFF

This command specifies whether to log the publishing time into an output file or not.

ODSSTRUCTURE

The format for this command is:

ODSSTRUCTURE STAR|SNOWFLAKE|RECURSIVEHIERARCHYLF|RECURSIVEHIERA RCHYPF [, DimName]

This command specifies the structure of the output dimension files. The default is STAR. The optional DimName parameter specifies a dimension to which the specified structure should be applied. The output data for all other dimensions’ defaults to the Star schema if the parameter is given. Otherwise, the output data for all dimensions has the specified structure.

ODSSYMPROPERTIES

The format for this command is:

ODSSYMPROPERTIES DEBITCREDIT|SymbolAttribute [DimName]

This command is used to include symbol properties as part of the metadata that is exported. If a DimName is specified, the DEBITCREDIT or SymbolAttribute value is included for all symbols that were extracted for that specific dimension. Otherwise, the DEBITCREDIT or SymbolAttribute value is included for all symbols that were extracted for all dimensions.

Note: Multiple ODSSYMPROPERTIES commands can be specified in a single ODS export specification document.

The properties that are exported are not used to format the fact files or the dimension files; they are written to a symbol properties file to be used by the calling third-party application.

ODSSYSPROPERTIES

The format for this command is:

ODSSYSPROPERTIES SystemAttribute

This command is used to include system attribute properties as part of the metadata that is exported.

Note: Multiple ODSSYSPROPERTIES commands can be specified in a single ODS export specification document.

The properties that are exported are not used to format the fact files or the dimension files; they are written to a system properties file to be used by the calling third-party application.

Writing the calling procedure document

The following code illustrates an example of a procedure document used to execute an ODS extraction:

// If not connected, connect (for more information on the CONNECT command, see the CONNECT command in the Longview Developer’s Guide)

CONNECT …

 

// Create the data area to be used

CREATE DATAAREA DA1 USING DataArea1.DSP

 

// Download data into this data area

DOWNLOAD DA1

 

// Run your ODS export spec using the just-read data area

RUN EXPORT TestODS.spec ON DA1

 

// Disconnect

DISCONNECT

 

// End of Procedure Document

Note: In the calling procedure document, the DOWNLOAD command or STREAM command can be used to download the data into the data area. The DOWNLOAD command downloads all data and builds the data tree before export. The STREAM command downloads and exports the data one block at a time. For more information, see the Download and Stream commands in the Longview Developer’s Guide.

ODS Output Files

There are several types of output files that may be created:

Output file typestatus

Fact file(s)

Always created

TimeStamp file

Optionally created

Symbol Properties file

Optionally created

System Properties file

Optionally created

Dimension file(s)

Always created

The output from the ODS extraction process is always a set of text files. However, the ODSDBOUTPUT command also allows the extracted data and metadata to be loaded directly into database tables. For simplicity, these sections discuss only the text files.

Note: All ODS output files are generated in the working directory. If a relative path is used in the DATATARGET ODS command, the files are generated in the directory specified, relative to the current working directory.

Fact Files

The fact files are named

FileNamePrefix_FACT_FactTableName_nn.asc

where:

  • FileNamePrefix is the parameter specified in the ODSTARGET line of the export specification document.
  • nn is the FactTableNum.

An example of a fact table name is

ODSTEST_FACT_ACTUAL_01.ASC.

If this file already exists, it is overwritten.

There is one line for each data value retrieved. Each line in a fact table has the following format:

UseIndex1[Level1]{ UseIndex2[Level2]{…{ UseIndexN[LevelN]{Value

Where:

  • N is the number of dimensions.
  • UseIndex1 is either the standard Longview symbol index for the symbol in the Xth dimension or the index specified in the IndexToUse parameter of the ODS attribute.
  • Level1 is the level of the related symbol. The Level column appears in the fact files only if Recursive Hierarchy is specified as the structure of the output dimension files.

An example of a row in a fact table is:

123{393{1{1{1{1{1{1{1000.00

TimeStamp File

If ODSPUBLISHINGTIME ON is specified, a time stamp file is created that includes the date and time of the ODS extraction. This file is named:

FilePrefix_ Publishing_Time.asc

If a file with this name already exists, it is overwritten. If the output is also being written to database tables, and the TimeStamp table is not empty, a new row is added to that table.

Symbol Properties File

If option ODSSYMPROPERTIES is specified, a symbol properties file is created that contains the DEBITCREDIT indicator, if requested, and the values for all requested symbol attributes. This file is named:

FilePrefix_ Symbol_Properties.asc

If a file with this name already exists, it is overwritten.

System Properties File

If option ODSSYSPROPERTIES is specified, a system properties file is created that contains properties and values for all requested system attributes. This file is named:

FilePrefix_ System_Properties.asc

If a file with this name already exists, it is overwritten.

Dimension Files

The format for the dimension files is typically the biggest consideration when performing an ODS extraction process. This decision is primarily influenced by how the third-party application needs to associate dimension metadata with the associated fact files.

There are several types of dimension table output formats:

  • Star
  • SnowFlake
  • Recursive Hierarchy (Parents First or Leafs First)

The number of files for a dimension depends on which ODSSTRUCTURE setting is used for that dimension:

  • Star or Recursive Hierarchy—The system creates one dimension file per dimension/hierarchy combination.

    If two ACCOUNTS hierarchies, three ENTITIES hierarchies, and a single hierarchy from each of the other dimensions are requested, the system creates two ACCOUNTS dimension files, three ENTITIES dimension files, and one file for each of the other dimensions.

  • Snowflake—The system creates one dimension file per dimension/field name.

    Using the same example as above, where the entities are mapped to three fields, the system creates three ENTITIES dimension files (one per field), and one dimension file for each of the other dimensions (assuming they are all mapped to "Continent").

The name of a dimension file depends on which dimension output structure is being used:

Star or Recursive Hierarchy

The dimension files are named:

FilePrefix_DIM_DimName_RootSymbol_nn.asc

where:

  • nn is a sequential number that refers to the fact table. If a dimension has six hierarchies extracted into six different fact tables, the dimension files for that dimension are numbered 1 to 6, each with its own unique root symbol name.

SnowFlake

The dimension files follow a generic naming format:

FilePrefix_DIM_DimName_Lxx_nn.asc

where:

  • xx is a field number.
  • nn is a sequential number that refers to the fact table.

If the ATTRIBUTEMAP command is used in the export specification document, Lxx is replaced by the value specified for AttrValue when the SET ATTRIBUTE command was used.

If ODSAUTOLEVEL is set to ON, and the ODSAUTOLEVELDIM command is used, Lxx is replaced by the ColumnName specified in the ODSAUTOLEVELDIM command. In addition, in the dimension tables generated, the Id column and the level names (Lxx) are replaced by ColumnNameId (the ColumnName suffixed with the string Id).

Note: For more information on field names, see “Field names in ODS output files”

The dimension format is specified by the ODSSTRUCTURE command. It has the following format:

ODSSTRUCTURE STAR|SNOWFLAKE|RECURSIVEHIERARCHYPF|RECURSIVEHIERARCHYLF, ["<DimName>"]

If a DimName is specified:

  • That dimension has the selected format
  • All other dimensions are in the STAR format

If a DimName is not specified:

  • All dimensions are in the format specified in the command
Star format

The Star format creates one file per dimension/hierarchy combination. Within each file, there is one line for each “leaf symbol” within that dimension or hierarchy.

Note: The “leaf symbols” that correspond to the lines in the dimension files are the symbols tat the bottom of the hierarchy.  They may not be “true” leaf symbols.

Each line:

  • contains the full ancestry of a symbol.
  • has the following format:

    UseIndex1{UseName1{UseDesc1{UseIndex2{UseName1{UseDesc1{…

  • has a varying length depending on how deep this symbol is under the main root symbol

Using the earlier example, if the ODS extraction requests NorthAmerica#99:

  • Field1 = Continent (as specified by the ODSMAPPINGTABLE command)
  • Field2 = Country (as specified by the ODSMAPPINGTABLE command)
  • Field3 = City (as specified by the ODSMAPPINGTABLE command)

An example line is:

214{NorthAmerica{North America{Canada{Canada{Toronto{Toronto

SnowFlake format

The SnowFlake format creates one-dimension file per dimension/field name.

Within each file, there is one line for each extracted symbol.

Each line contains the following fields:

  • UseIndex
  • UseIndex (of the parent symbol)
  • UseName
  • UseDesc an example line is:

    2222{123{Canada{Canada

Recursive Hierarchy format

The Recursive Hierarchy format creates one file per dimension/hierarchy combination.

Within each file, there is one line for each extracted symbol within that dimension or hierarchy.

Note: If Recursive Hierarchy is specified as the structure of the output dimension files, the Level field is added to each line of the fact files

Each line contains the following fields:

  • UseIndex
  • UseName
  • UseDesc
  • Level (with respect to its root symbol)
  • Sequence number
  • UseIndex (of the parent symbol)

The lines in the file area order either parent symbols first or leaf symbols first depending on whether RECURSIVEHIERARCHYPF (parent symbols first) or RECURSIVEHIERARCHYLF (leaf symbols first) is specified.

An example line is:

2222{Canada{Canada{1{2{123

Field names in ODS output files

Field names are used in naming the ODS output files, the output table names, and the columns of the tables for the SnowFlake and Star formats. Field names are determined by several factors.

  • When ODSAUTOLEVEL is set to OFF, the field name is determined by the symbol attribute specified in the ATTRIBUTEMAP command or the ATTRIBUTEMAPFIELD command.

    Note: If both the ATTRIBUTEMAP command and the ATTRIBUTEMAPFIELD command are specified, the field name specified in the ATTRIBUTEMAP command is used

  • When ODSAUTOLEVEL is set to ON, the name designated for each level is determined by matching corresponding ODSAUTOLEVELDIM entries with ODSMAPPINGTABLE entries. Any levels that are not named by matching entries are assigned a default name DimensionName_Lxx, where xx refers to the level number. These default names must be included as ODSMAPPINGTABLE entries.

    Note: The ODS export specification document can contain the ATTRIBUTEMAP command and the ATTRIBUTEMAPFIELD command when ODSAUTOLEVEL is set to ON. In this scenario, the system checks if the attribute is set for each symbol. If at least one symbol has the attribute set for a given level, the field name chosen in the attribute overrides the auto level field name. Multiple symbols at the same level should not have the attribute set to different field names.

Rules for the ODS extraction process

Prior to creating the documents, you need for performing the ODS extraction process, you need to be aware of the following rules that govern the process and guide the creation of the documents:

  • The positive/negative weightings in the Longview hierarchies are ignored.
  • The sign for values relating to "Credit" accounts are reversed automatically, if REVERSESIGN is set to ON.
  • Only numeric data is extracted by this feature. String data is ignored.
  • Multiple fact tables can be specified for a single dimension only. Therefore, fact table names that map to values other than 1 are allowed only in zero or one dimension.
  • All fact table numbers must be sequential.
  • Field names in ODS output tables must adhere to the following rules:
    • The first character in the field name must be an upper- or lower-case character.
    • Subsequent characters in the field name can be any alpha-numericcharacter, or the following special characters: dollar sign ( $ ), pound ( # ), or underscore ( _ ).
  • The FieldName fields in the ODSMappingTable must be unique.
  • Field names specified in the ODSMAPPINGTABLE and ODSAUTOLEVELDIM commands cannot exceed 64 characters.
  • The FieldNum values for the symbols in any given hierarchy must be in ascending order as the hierarchy is traversed from the root symbol to the leaf symbols.
  • The first FieldNum for each hierarchy should have a value of 1.
  • Once a FieldNum value is encountered, it should not be repeated for any of its descendants.
  • Whenever a FieldNum value of 1 is encountered, a new sub-hierarchy is started.
  • All the symbols in the sub-hierarchy must belong to the same fact table.
  • A symbol may exist in a dimension file without having any corresponding values in a data file. All lines in the generated data file correspond to a line in each of the dimension files.
  • For the SnowFlake dimension file format, multiple hierarchies can be included, if they are independent and there are no level conflicts.
  • Alternate rollups of symbols are not supported in the SnowFlake dimension file format.

Note: In Oracle only, any object (table name, index name, or column name) exceeding 30 characters is truncated and suffixed with a unique numerical identifier

 
Published:

Extracting Longview Data to ODS

When Longview data is required by a third-party application, such as a Business Integration reporting package, it is sometimes necessary for that application to not only require the data for the requested data area but to also be able to retrieve the symbols that relate to that data area. For example, if a third-party application requests data for North American entities, it may need to know how these entities rollup to each other. Therefore, a process must extract symbol information related to the North American entities in addition to the data. The data and the associated metadata are extracted to an Operational Data Store (ODS). An ODS is simply a method to store this extracted information where it is later utilized by a third-party application.

The Longview ODS extraction process deals only with the exporting of data and the associated symbol metadata. It does not deal with how this data is used by any third-party application.

The ODS extraction process generates a series of files that correspond to:

  • the data that has been requested (fact tables)
  • metadata for the symbols whose data was extracted (dimension tables)

When using ODS there are two basic questions that you must answer:

  • What data must be extracted?
    This is usually relatively straight forward and involves defining a data area. This is like other requests for Longview data where it is necessary to specify symbol specifications for each dimension in your system.
  • How must the symbols corresponding to this data be represented?

There are several options for how the symbol metadata is formatted in the dimension tables. You must determine the format that works best for your specific use. This depends on the third-party application that utilizes these files after the ODS extraction process. For additional details, see “Dimension Files”.

The steps involved in ODS extraction are:

  1. Understanding the possible options for creating the dimension files and ensure that one of these options meets the needs of the third-party application.
  2. Creating an ODS attribute and setting attribute values for selected symbols.
  3. Writing the data area specification document.
  4. Writing the export specification document with the required ODS options.
  5. Writing the calling procedure document that runs the export specification using the data area specification.

In this section, you can find information on the following topics:

Creating an ODS attribute and setting attribute values for selected symbols

Entities of the same type (e.g. continent, country, city) do not necessarily need to reside at the same level in the Longview hierarchy. Some branches of the hierarchy could have many levels of detail, whereas other branches of the hierarchy could have little detail. In the entity hierarchy below, note that the entity Toronto is at the third level under the entity Worldwide, whereas Dallas is at the fourth level.

Therefore, if a third-party application needs to create a report showing data for just the symbols that are cities, it cannot presume that the cities are all located at the same level in the hierarchy.

If the ODS output is to contain columns named "Continent", "Country", and "City", it is necessary to associate each entity with one of those values. This is accomplished by creating a new symbol attribute, for example "AZEntityType", and then setting that attribute's value for each entity. For example, the AZEntityType attribute values for the following entity symbols would be:

  • NorthAmerica = "Continent"
  • Canada = "Country"
  • USA = "Country"
  • Toronto = "City"

To set these attributes, the SET ATTRIBUTE command is used.

Syntax

SET ATTRIBUTE SYMBOL AttrName VALUE SymName AttrValue

where:

  • AttrName is the attribute name associated with a particular ODS extract.

    Note: If you want to use one attribute for a particular ODS extract, you must use the ATTRIBUTEMAP command in the ODS export specification document. If you want to use multiple attributes for a particular ODS extract, you must use the ATTRIBUTEMAPFIELD command, and any combination of the ATTRIBUTEMAPDESC, ATTRIBUTEMAPFACT, ATTRIBUTEMAPID, or ATTRIBUTEMAPNAME commands in the ODS export specification document. For more information, see “Writing the export specification document”

  • SymName is the symbol whose attribute value is being set.

  • AttrValue is a string of the following format:

    FieldName[,NameToUse,DescToUse[,FactTableName[,IndexToUse]]]]

    where:

    • FieldName is a string value specifying the target field in the ODS dimension table.

      If a field name is provided and that field name has been subsequently associated with a column number via an ODSMappingTable command, this symbol's information is written to that column in the appropriate dimension table. If no value has been specified for a symbol, the symbol is not written to the dimension table.

    • NameToUse is an optional parameter that returns the symbol's name or a hardcoded value. To use the symbol's name, specify a value of NAME. To use a hardcoded value, enclose the string in double quotation marks. The default for this parameter is NAME.
    • DescToUse is an optional parameter that specifies whether to use the symbol's description or a hardcoded value. To use the symbol's description, specify a value of DESCRIPTION. To use a hardcoded value, enclose the string in double quotation marks. The default for this parameter is DESCRIPTION. To illustrate when a hardcoded value may be applicable, consider the situation where the third-party application needs to know which date is associated with a time period. Returning the description for the January 2010 actuals time period may return "January 2010 Actuals". It may be more useful if this returned a hardcoded value of "01/01/2010" instead.
    • FactTableName is an optional parameter that specifies the name of the fact table to which the data values applicable to this symbol are written. This name is later mapped to the integer value representing a fact table (FactTableNumber). The default value for this parameter is DEFAULT, which is later mapped to fact table 1. Since multiple fact tables can exist for a single dimension only, multiple fact table names can be specified for a single dimension only. For example, if multiple fact tables are to be used to differentiate actuals data from plan data, associate all actuals time periods with a data file name such as "Actuals" and all plan time periods with a data file name such as "Plan". All symbols for dimensions other than time should not have a fact table name associated with them.

    • IndexToUse is an optional parameter used to indicate which integer value should be used to uniquely identify this symbol within its dimension. If IndexToUse is not specified, the Longview symbol index is used. To see how this parameter could be used, consider the case when you want to compare January data in your Actuals fact table with the corresponding January data in your Plan fact table. Since these data values originated from different time period symbols, the symbols indexes for those two time periods are different. These two symbols could be associated to each other by using the same IndexToUse for each of them.

Note: By default, the standard Longview symbol name, symbol description, and symbol index are used. However, the attribute value can be set so that any combination of these can be overridden. For the remainder of this section, the terms UseName, UseDesc, and UseIndex refer to the name, description, and index that are used for a symbol, which could be the standard Longview value or the overridden value.

Using the example above, it is necessary to associate each symbol with its corresponding field in the ODS dimension files. If a data area fixes to a single symbol for a dimension, that symbol's field name must be set to the first field of the dimension table. Since entities vary in this example, each entity to be extracted must be mapped to its appropriate field name.

In this example, the attribute values are set using these commands:

Maintenance on

 

// Prior to running this procedure,

// it is necessary to create the user-defined attribute

// Create Attribute Symbol AZEntityType "Description" "Text" W " "

 

SET ATTRIBUTE Symbol AZEntityType VALUE Cash "Account"

SET ATTRIBUTE Symbol AZEntityType VALUE A1001YTD "Period,NAME,01/01/2010,FactActual,1"

SET ATTRIBUTE Symbol AZEntityType VALUE A1002YTD "Period,NAME,01/01/2010,FactActual,2"

SET ATTRIBUTE Symbol AZEntityType VALUE P1001YTD "Period,NAME,01/01/2010,FactPlan,1"

SET ATTRIBUTE Symbol AZEntityType VALUE P1002YTD "Period,NAME,01/01/2010,FactPlan,2"

SET ATTRIBUTE Symbol AZEntityType VALUE NorthAmerica "Continent"

SET ATTRIBUTE Symbol AZEntityType VALUE Canada "Country"

SET ATTRIBUTE Symbol AZEntityType VALUE USA "Country"

SET ATTRIBUTE Symbol AZEntityType VALUE Toronto "City"

// set for all relevant entities

SET ATTRIBUTE Symbol AZEntityType VALUE DIM3SET "Function"

SET ATTRIBUTE Symbol AZEntityType VALUE DIM4SET "Currency"

SET ATTRIBUTE Symbol AZEntityType VALUE DIM5SET "Segment"

SET ATTRIBUTE Symbol AZEntityType VALUE DIM6SET "Element"

SET ATTRIBUTE Symbol AZEntityType VALUE DIM7SET "Control"

 

Maintenance off

Creating ODS documents

ODS documents include data area specification documents:

  1. Data area specification document—Use this document type to define the area to extract
  2. Export specification document—Use this document type to specify the target and format for the extraction
  3. Calling procedure document—Use this document type to execute the extraction

Writing the data area specification document

A data area specification document is required to define the area that is to be extracted. A sample data area specification document is:

ACCOUNTS    Cash

TIMEPER     A1001YTD, A1002YTD, P1001YTD, P1002YTD

ENTITIES     NorthAmerica#99

FUNCTIONS     Dim3Set

PRODUCTS     Dim4Set

VERSIONS     Dim5Set

CURRENCY    Dim6Set

CONTROLS     Dim7Set

Writing the export specification document

An example export specification document includes the following lines:

// Indicate that:

// - this is an ODS export spec

// - all output files are prefixed with "TestODS"

DATATARGET ODS, "TestODS", EXTERNAL

 

// Specify an optional log file, error file and/or debug file

LOGFILE "Export.log", EXTERNAL

ERRORFILE "Export.err", EXTERNAL

ODSDEBUG "ODSDebug.out", EXTERNAL

 

// (If you are using one symbol attribute for the ODS extract)

// Specify the symbol attribute that is used to determine the structure

// of the dimension tables

ATTRIBUTEMAP AZEntityType

 

// Or

 

// (If you are using multiple symbol attributes for the ODS extract)

// Specify the symbol attributes that are used to determine the structure

// of the dimension tables

ATTRIBUTEMAPDESC AZEntityType1

ATTRIBUTEMAPFACT AZEntityType2

ATTRIBUTEMAPFIELD AZEntityType3

ATTRIBUTEMAPID AZEntityType4

ATTRIBUTEMAPNAME AZEntityType5

 

// Are values to be reversed?

REVERSESIGN ON

 

// Should a time stamp be created?

ODSPUBLISHINGTIME ON

 

// Should parent data be extracted for a dimension?

ODSINCLUDEPARENTDATA ON, ENTITIES

 

// Should symbol properties be included in metadata?

ODSSYMPROPERTIES AZSomeOtherAttr1 ENTITIES

ODSSYMPROPERTIES AZSomeOtherAttr2 ENTITIES

ODSSYMPROPERTIES AZSomeOtherAttr3

ODSSYMPROPERTIES DEBITCREDIT ACCOUNTS

 

// Should system attribute properties be included in metadata?

ODSSYSPROPERTIES SGPCurrentYear

 

// Specify the format of the dimension tables

//ODSSTRUCTURE RECURSIVEHIERARCHYPF, ENTITIES

//ODSSTRUCTURE RECURSIVEHIERARCHYLF, ENTITIES

//ODSSTRUCTURE STAR

ODSSTRUCTURE SNOWFLAKE, ENTITIES

 

// If data is to be written directly to database tables,

// Enter the connection info here

ODSDBOUTPUT ODSDataSourceABC, ODSUser, Passwordxxxx

 

// A symbol with an ATTRIBUTEMAP attribute value of FieldName should appear

// in column fieldNumber of the dimension tables using the command:

// ODSMappingTable ITEM, FieldName, fieldNumber

ODSMAPPINGTABLE ITEM, Continent, 1

ODSMAPPINGTABLE ITEM, Country, 2

ODSMAPPINGTABLE ITEM, City, 3

ODSMAPPINGTABLE ITEM, Account, 1

ODSMAPPINGTABLE ITEM, Period, 1

ODSMAPPINGTABLE ITEM, Function, 1

ODSMAPPINGTABLE ITEM, Currency, 1

ODSMAPPINGTABLE ITEM, Segment, 1

ODSMAPPINGTABLE ITEM, Element, 1

ODSMAPPINGTABLE ITEM, Control, 1

 

// A symbol that has been associated with FactTableName should appear

// in fact table number FactTableNum:

// ODSMappingTable ITEM, FactTableName, FactTableNum

ODSMAPPINGTABLE ITEM, FactActual, 1

ODSMAPPINGTABLE ITEM, FactPlan, 2

An ODS export specification document must contain the following commands:

Command

Description

DATATARGET ODS

The format for this command is:

DATATARGET ODS, FilePrefix, EXTERNAL

This command designates that this is an ODS export specification and that all output files are prefixed by the value as specified in the FilePrefix parameter. The FilePrefix parameter can also be used to specify a relative path to the working directory.

For example, a FilePrefix value of "test\output\testods" places the output files in <current working directory>\test\output, with each file prefixed by "testods".

ODSMAPPINGTABLE

The format for this command is:

ODSMAPPINGTABLE

This command associates a field name with a column index in a dimension table, or a fact table name to a fact table number.

An ODS export specification document must contain at least one of the following commands:

Command

Description

ATTRIBUTEMAP

The format for this command is:

ATTRIBUTEMAP AttributeName

This command specifies which symbol attribute is to be used to format the dimension tables.

This command overrides the ATTRIBUTEMAPFIELD command and the ODSAUTOLEVEL command.

ATTRIBUTEMAPFIELD

The format for this command is:

ATTRIBUTEMAPFIELD AttributeName

Use this command to specify the symbol attribute for the FieldName parameter of the ODS extract.

This command overrides the ODSAUTOLEVEL command.

ODSAUTOLEVEL

The format for this command is:

ODSAUTOLEVEL ON|OFF

This command automatically generates the level information. The default specification for ODSAUTOLEVEL is OFF.

Note: When using automap, the ODSMappingTable still needs to be included to map the levels to columns in the dimension tables. In this case, each field name is DimensionName_L00, DimensionName_L01, ..., DimensionName_Lxx, where xx is the level number.

Note: If the ODS export specification document contains either of the ATTRIBUTEMAP or ATTRIBUTEMAPFIELD commands and the ODSAUTOLEVEL command, and ODSAUTOLEVEL is ON, the field name stored in the attribute value overrides the auto level field name. For more information, see “Field names in ODS output files”

An ODS export specification document can contain the following optional com-mands:

Command

Description

ATTRIBUTEMAPDESC

The format for this command is:

ATTRIBUTEMAPDESC AttributeName

Use this command to specify the symbol attribute for the DescToUse parameter of the ODS extract.

ATTRIBUTEMAPFACT

The format for this command is:

ATTRIBUTEMAPFACT AttributeName

Use this command to specify the symbol attribute for the FactTableName parameter of the ODS extract.

ATTRIBUTEMAPID

The format for this command is:

ATTRIBUTEMAPID AttributeName

Use this command to specify the symbol attribute for the IndexToUse parameter of the ODS extract.

ATTRIBUTEMAPNAME

The format for this command is:

ATTRIBUTEMAPNAME AttributeName

Use this command to specify the symbol attribute for the NameToUse parameter of the ODS extract.

ERRORFILE

The format for this command is:

ERRORFILE FileName, EXTERNAL|INTERNAL

This command specifies the name of the error file to contain data on invalid records.

LOGFILE

The format for this command is:

LOGFILE FileName, EXTERNAL|INTERNAL

This command specifies the name of a log file to contain all variable values, errors, and error codes.

ODSAUTOLEVELDIM

The format for this command is:

ODSAUTOLEVELDIM DimensionName, ColumnName

When ODSAUTOLEVEL is ON, this command allows you to override the default auto level names and apply the desired field names based on dimension and level number.

For each ODSAUTOLEVELDIM entry specified, you need to provide the appropriate mapping information through the ODSMAPPINGTABLE command, in the following format:

ODSMAPPINGTABLE ITEM, ColumnName, ColumnNumber

ODSDBOUTPUT

The format for this command is:

ODSDBOUTPUT "DataSourceName", "UserName", "Password"

The output from an ODS extraction process usually creates a series of text files. However, it is also possible for this process to populate a series of database tables directly.

For more information on dimension table options, see “Dimension Files”

ODSDEBUG

The format for this command is:

ODSDEBUG FileName, EXTERNAL|INTERNAL

This command specifies the name of a log file to contain debugging information.

ODSINCLUDEHIERARCHYLEVEL

The format for this command is:

ODSINCLUDEHIERARCHYLEVEL ON|OFF

This command specifies whether to include the Level column in the fact tables. The default specification for ODSINCLUDEHIERARCHYLEVEL is OFF.

For more information, see “Fact Files”

ODSINCLUDEPARENTDATA

The format for this command is:

ODSINCLUDEPARENTDATA ON|OFF [, DimName][, DimName]...

This command specifies whether to include parent data. When determining this, you must consider whether the third-party application calculates its own totals or not. If it calculates its own totals, you likely do not need to export parent data values. The default specification for ODSINCLUDEPARENTDATA is OFF.

To include parent data for all dimensions, specify: ODSINCLUDEPARENTDATA ON

To include parent data for a single dimension, for example ENTITIES, specify: ODSINCLUDEPARENTDATA ON , ENTITIES

To include parent data for multiple dimensions, for example ENTITIES and ACCOUNTS, specify: ODSINCLUDEPARENTDATA ON , ENTITIES, ACCOUNTS

ODSPATHFROMDB

The format for this command is:

ODSPATHFROMDB, "FolderPathName"

This command specifies a full path from the machine running the SQL Server to the folder containing the output files generated by the ODS extraction process. This command is not required with an Oracle Database Server.

ODSPUBLISHINGTIME

The format for this command is:

ODSPUBLISHINGTIME ON|OFF

This command specifies whether to log the publishing time into an output file or not.

ODSSTRUCTURE

The format for this command is:

ODSSTRUCTURE STAR|SNOWFLAKE|RECURSIVEHIERARCHYLF|RECURSIVEHIERA RCHYPF [, DimName]

This command specifies the structure of the output dimension files. The default is STAR. The optional DimName parameter specifies a dimension to which the specified structure should be applied. The output data for all other dimensions’ defaults to the Star schema if the parameter is given. Otherwise, the output data for all dimensions has the specified structure.

ODSSYMPROPERTIES

The format for this command is:

ODSSYMPROPERTIES DEBITCREDIT|SymbolAttribute [DimName]

This command is used to include symbol properties as part of the metadata that is exported. If a DimName is specified, the DEBITCREDIT or SymbolAttribute value is included for all symbols that were extracted for that specific dimension. Otherwise, the DEBITCREDIT or SymbolAttribute value is included for all symbols that were extracted for all dimensions.

Note: Multiple ODSSYMPROPERTIES commands can be specified in a single ODS export specification document.

The properties that are exported are not used to format the fact files or the dimension files; they are written to a symbol properties file to be used by the calling third-party application.

ODSSYSPROPERTIES

The format for this command is:

ODSSYSPROPERTIES SystemAttribute

This command is used to include system attribute properties as part of the metadata that is exported.

Note: Multiple ODSSYSPROPERTIES commands can be specified in a single ODS export specification document.

The properties that are exported are not used to format the fact files or the dimension files; they are written to a system properties file to be used by the calling third-party application.

Writing the calling procedure document

The following code illustrates an example of a procedure document used to execute an ODS extraction:

// If not connected, connect (for more information on the CONNECT command, see the CONNECT command in the Longview Developer’s Guide)

CONNECT …

 

// Create the data area to be used

CREATE DATAAREA DA1 USING DataArea1.DSP

 

// Download data into this data area

DOWNLOAD DA1

 

// Run your ODS export spec using the just-read data area

RUN EXPORT TestODS.spec ON DA1

 

// Disconnect

DISCONNECT

 

// End of Procedure Document

Note: In the calling procedure document, the DOWNLOAD command or STREAM command can be used to download the data into the data area. The DOWNLOAD command downloads all data and builds the data tree before export. The STREAM command downloads and exports the data one block at a time. For more information, see the Download and Stream commands in the Longview Developer’s Guide.

ODS Output Files

There are several types of output files that may be created:

Output file typestatus

Fact file(s)

Always created

TimeStamp file

Optionally created

Symbol Properties file

Optionally created

System Properties file

Optionally created

Dimension file(s)

Always created

The output from the ODS extraction process is always a set of text files. However, the ODSDBOUTPUT command also allows the extracted data and metadata to be loaded directly into database tables. For simplicity, these sections discuss only the text files.

Note: All ODS output files are generated in the working directory. If a relative path is used in the DATATARGET ODS command, the files are generated in the directory specified, relative to the current working directory.

Fact Files

The fact files are named

FileNamePrefix_FACT_FactTableName_nn.asc

where:

  • FileNamePrefix is the parameter specified in the ODSTARGET line of the export specification document.
  • nn is the FactTableNum.

An example of a fact table name is

ODSTEST_FACT_ACTUAL_01.ASC.

If this file already exists, it is overwritten.

There is one line for each data value retrieved. Each line in a fact table has the following format:

UseIndex1[Level1]{ UseIndex2[Level2]{…{ UseIndexN[LevelN]{Value

Where:

  • N is the number of dimensions.
  • UseIndex1 is either the standard Longview symbol index for the symbol in the Xth dimension or the index specified in the IndexToUse parameter of the ODS attribute.
  • Level1 is the level of the related symbol. The Level column appears in the fact files only if Recursive Hierarchy is specified as the structure of the output dimension files.

An example of a row in a fact table is:

123{393{1{1{1{1{1{1{1000.00

TimeStamp File

If ODSPUBLISHINGTIME ON is specified, a time stamp file is created that includes the date and time of the ODS extraction. This file is named:

FilePrefix_ Publishing_Time.asc

If a file with this name already exists, it is overwritten. If the output is also being written to database tables, and the TimeStamp table is not empty, a new row is added to that table.

Symbol Properties File

If option ODSSYMPROPERTIES is specified, a symbol properties file is created that contains the DEBITCREDIT indicator, if requested, and the values for all requested symbol attributes. This file is named:

FilePrefix_ Symbol_Properties.asc

If a file with this name already exists, it is overwritten.

System Properties File

If option ODSSYSPROPERTIES is specified, a system properties file is created that contains properties and values for all requested system attributes. This file is named:

FilePrefix_ System_Properties.asc

If a file with this name already exists, it is overwritten.

Dimension Files

The format for the dimension files is typically the biggest consideration when performing an ODS extraction process. This decision is primarily influenced by how the third-party application needs to associate dimension metadata with the associated fact files.

There are several types of dimension table output formats:

  • Star
  • SnowFlake
  • Recursive Hierarchy (Parents First or Leafs First)

The number of files for a dimension depends on which ODSSTRUCTURE setting is used for that dimension:

  • Star or Recursive Hierarchy—The system creates one dimension file per dimension/hierarchy combination.

    If two ACCOUNTS hierarchies, three ENTITIES hierarchies, and a single hierarchy from each of the other dimensions are requested, the system creates two ACCOUNTS dimension files, three ENTITIES dimension files, and one file for each of the other dimensions.

  • Snowflake—The system creates one dimension file per dimension/field name.

    Using the same example as above, where the entities are mapped to three fields, the system creates three ENTITIES dimension files (one per field), and one dimension file for each of the other dimensions (assuming they are all mapped to "Continent").

The name of a dimension file depends on which dimension output structure is being used:

Star or Recursive Hierarchy

The dimension files are named:

FilePrefix_DIM_DimName_RootSymbol_nn.asc

where:

  • nn is a sequential number that refers to the fact table. If a dimension has six hierarchies extracted into six different fact tables, the dimension files for that dimension are numbered 1 to 6, each with its own unique root symbol name.

SnowFlake

The dimension files follow a generic naming format:

FilePrefix_DIM_DimName_Lxx_nn.asc

where:

  • xx is a field number.
  • nn is a sequential number that refers to the fact table.

If the ATTRIBUTEMAP command is used in the export specification document, Lxx is replaced by the value specified for AttrValue when the SET ATTRIBUTE command was used.

If ODSAUTOLEVEL is set to ON, and the ODSAUTOLEVELDIM command is used, Lxx is replaced by the ColumnName specified in the ODSAUTOLEVELDIM command. In addition, in the dimension tables generated, the Id column and the level names (Lxx) are replaced by ColumnNameId (the ColumnName suffixed with the string Id).

Note: For more information on field names, see “Field names in ODS output files”

The dimension format is specified by the ODSSTRUCTURE command. It has the following format:

ODSSTRUCTURE STAR|SNOWFLAKE|RECURSIVEHIERARCHYPF|RECURSIVEHIERARCHYLF, ["<DimName>"]

If a DimName is specified:

  • That dimension has the selected format
  • All other dimensions are in the STAR format

If a DimName is not specified:

  • All dimensions are in the format specified in the command
Star format

The Star format creates one file per dimension/hierarchy combination. Within each file, there is one line for each “leaf symbol” within that dimension or hierarchy.

Note: The “leaf symbols” that correspond to the lines in the dimension files are the symbols tat the bottom of the hierarchy.  They may not be “true” leaf symbols.

Each line:

  • contains the full ancestry of a symbol.
  • has the following format:

    UseIndex1{UseName1{UseDesc1{UseIndex2{UseName1{UseDesc1{…

  • has a varying length depending on how deep this symbol is under the main root symbol

Using the earlier example, if the ODS extraction requests NorthAmerica#99:

  • Field1 = Continent (as specified by the ODSMAPPINGTABLE command)
  • Field2 = Country (as specified by the ODSMAPPINGTABLE command)
  • Field3 = City (as specified by the ODSMAPPINGTABLE command)

An example line is:

214{NorthAmerica{North America{Canada{Canada{Toronto{Toronto

SnowFlake format

The SnowFlake format creates one-dimension file per dimension/field name.

Within each file, there is one line for each extracted symbol.

Each line contains the following fields:

  • UseIndex
  • UseIndex (of the parent symbol)
  • UseName
  • UseDesc an example line is:

    2222{123{Canada{Canada

Recursive Hierarchy format

The Recursive Hierarchy format creates one file per dimension/hierarchy combination.

Within each file, there is one line for each extracted symbol within that dimension or hierarchy.

Note: If Recursive Hierarchy is specified as the structure of the output dimension files, the Level field is added to each line of the fact files

Each line contains the following fields:

  • UseIndex
  • UseName
  • UseDesc
  • Level (with respect to its root symbol)
  • Sequence number
  • UseIndex (of the parent symbol)

The lines in the file area order either parent symbols first or leaf symbols first depending on whether RECURSIVEHIERARCHYPF (parent symbols first) or RECURSIVEHIERARCHYLF (leaf symbols first) is specified.

An example line is:

2222{Canada{Canada{1{2{123

Field names in ODS output files

Field names are used in naming the ODS output files, the output table names, and the columns of the tables for the SnowFlake and Star formats. Field names are determined by several factors.

  • When ODSAUTOLEVEL is set to OFF, the field name is determined by the symbol attribute specified in the ATTRIBUTEMAP command or the ATTRIBUTEMAPFIELD command.

    Note: If both the ATTRIBUTEMAP command and the ATTRIBUTEMAPFIELD command are specified, the field name specified in the ATTRIBUTEMAP command is used

  • When ODSAUTOLEVEL is set to ON, the name designated for each level is determined by matching corresponding ODSAUTOLEVELDIM entries with ODSMAPPINGTABLE entries. Any levels that are not named by matching entries are assigned a default name DimensionName_Lxx, where xx refers to the level number. These default names must be included as ODSMAPPINGTABLE entries.

    Note: The ODS export specification document can contain the ATTRIBUTEMAP command and the ATTRIBUTEMAPFIELD command when ODSAUTOLEVEL is set to ON. In this scenario, the system checks if the attribute is set for each symbol. If at least one symbol has the attribute set for a given level, the field name chosen in the attribute overrides the auto level field name. Multiple symbols at the same level should not have the attribute set to different field names.

Rules for the ODS extraction process

Prior to creating the documents, you need for performing the ODS extraction process, you need to be aware of the following rules that govern the process and guide the creation of the documents:

  • The positive/negative weightings in the Longview hierarchies are ignored.
  • The sign for values relating to "Credit" accounts are reversed automatically, if REVERSESIGN is set to ON.
  • Only numeric data is extracted by this feature. String data is ignored.
  • Multiple fact tables can be specified for a single dimension only. Therefore, fact table names that map to values other than 1 are allowed only in zero or one dimension.
  • All fact table numbers must be sequential.
  • Field names in ODS output tables must adhere to the following rules:
    • The first character in the field name must be an upper- or lower-case character.
    • Subsequent characters in the field name can be any alpha-numericcharacter, or the following special characters: dollar sign ( $ ), pound ( # ), or underscore ( _ ).
  • The FieldName fields in the ODSMappingTable must be unique.
  • Field names specified in the ODSMAPPINGTABLE and ODSAUTOLEVELDIM commands cannot exceed 64 characters.
  • The FieldNum values for the symbols in any given hierarchy must be in ascending order as the hierarchy is traversed from the root symbol to the leaf symbols.
  • The first FieldNum for each hierarchy should have a value of 1.
  • Once a FieldNum value is encountered, it should not be repeated for any of its descendants.
  • Whenever a FieldNum value of 1 is encountered, a new sub-hierarchy is started.
  • All the symbols in the sub-hierarchy must belong to the same fact table.
  • A symbol may exist in a dimension file without having any corresponding values in a data file. All lines in the generated data file correspond to a line in each of the dimension files.
  • For the SnowFlake dimension file format, multiple hierarchies can be included, if they are independent and there are no level conflicts.
  • Alternate rollups of symbols are not supported in the SnowFlake dimension file format.

Note: In Oracle only, any object (table name, index name, or column name) exceeding 30 characters is truncated and suffixed with a unique numerical identifier

 
For an optimal Community experience, Please view on Desktop